Forum Discussion

SavioFerraz's avatar
SavioFerraz
Icon for Super User rankSuper User
10 months ago

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

Hi everyone,

I’m facing a challenging performance issue in a SQL Database environment (Azure SQL Database / SQL Server), and I’d appreciate community insights to confirm whether this behavior is expected or if there is a deeper root cause.

🟦 Scenario

I have a stored procedure that queries a large table (~450M rows) with selective and non-selective filters.
The table has:

A clustered columnstore index

Three filtered nonclustered indexes

A datetime2 partitioning column

Multiple predicates depending on customer and region

The stored procedure receives two parameters:

@CustomerId INT,
@RegionCode VARCHAR(5)


Depending on parameter selectivity, the optimizer should choose:

Index seek → narrow execution plan for highly selective customers

Columnstore scan → batch mode for broad regional queries

However…

🟥 Problem

Whenever AUTO_UPDATE_STATISTICS kicks in, my stored procedure suddenly regresses:

SQL Server generates a plan optimized for a non-selective parameter.

That plan becomes cached.

All selective parameter executions switch to a full table scan in columnstore, even when a targeted nonclustered index is available.

CPU usage spikes by 10×.

Query duration increases from 80 ms → 18 seconds.

Attempts so far:

OPTION (RECOMPILE): fixes regression but too expensive for high call volume

OPTIMIZE FOR UNKNOWN: generates a generic but still suboptimal plan

Forced plan via Query Store: breaks when schema evolves

Updating stats with FULLSCAN: temporary improvement only

Creating additional filtered indexes: no impact on plan stability

This appears to be classic parameter sniffing, but much more extreme due to columnstore interactions.

❓ Questions

Is this pattern expected when columnstore indexes are combined with parameter-sensitive predicates?

Is there a best practice for eliminating parameter sniffing without forcing RECOMPILE on a high-throughput stored procedure?

Would splitting the stored procedure into two versions (Selective vs Broad) actually help, or does Query Store override that?

Is this a known issue related to recent engine changes in Azure SQL Database?

Should I consider converting the filtered nonclustered indexes into aligned or partitioned versions?

Any guidance would be greatly appreciated — this issue is impacting production workloads.

Thanks in advance!

sql database

2 Replies

  • benibix's avatar
    benibix
    Icon for Helper III rankHelper III
    3 days ago

    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.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi SavioFerraz, 
    Thank you for reaching out to the Microsoft Fabric Community Forum.

     

    This scenario needs deeper review by the Azure SQL engine team. Please submit this case in the Azure SQL Q&A forum under the Azure SQL Database category. The link provided here: Azure SQL Database - Microsoft Q&A

     

    Please raise a thread there in Azure SQL Database forum. So the appropriate experts can provide accurate guidance.

    Thank you for using the Microsoft Fabric Community Forum.