SQL Database – Complex Issue: Parameter Sniffing Causing Severe Query Regression After Statistics

Hi SavioFerraz,

Yes, this is the expected pattern with a columnstore table: the two good plans are very different (seek vs batch-mode scan), so whichever parameter compiles first after the statistics update wins for everyone. The statistics update isn't the cause, it only triggers the recompile. Since you've already tried the usual hints, these are the options left that don't cost a recompile on every call:

1. Parameter Sensitive Plan optimization. With database compatibility level 160, the optimizer can keep up to three plans for one query, picked by how selective the parameter value is. It targets exactly this case: an equality predicate (@CustomerId) on skewed data. Check with SELECT compatibility_level FROM sys.databases and raise it if it's lower. Query Store then shows the variants as separate plans.
2. Split the procedure by selectivity. A small dispatcher looks up the customer's size (a summary table of row counts, or a list of the big customers) and calls one of two inner procedures with the same query. Each keeps its own cached plan, so a broad regional run can never replace the plan the selective customers use.
3. A Query Store hint instead of a forced plan. sys.sp_query_store_set_hints attaches a hint to just this query without changing code, for example OPTION (OPTIMIZE FOR (@CustomerId = 12345)) with a typical selective customer. Unlike a forced plan it is a hint, not a fixed plan, so it doesn't break when the schema changes.

I'd try 1 first since it's one setting, and keep 2 as the most predictable fallback.