Parameter-sensitive plans and skewed data distributions

Overview

Parameter-sensitive plans happen when one reusable execution plan is not equally good for all parameter values. A query might be fast when a parameter matches a small number of rows, but slow when the same cached plan is reused for a parameter that matches millions of rows. This is common when data is skewed, meaning values are not evenly distributed.

This topic matters because many production SQL Server workloads use stored procedures, parameterized SQL, ORMs, and reusable plans. Plan reuse is usually good because it avoids repeated compilation, but it can become a problem when different parameter values need different plan shapes. A selective customer ID might need an index seek and nested loops join. A high-volume customer ID might need a scan, hash join, parallelism, and a larger memory grant.

For interviews, this topic tests whether a candidate understands query optimization beyond syntax. Strong candidates can explain parameter sniffing, cardinality estimation, histograms, skew, plan reuse, Query Store evidence, and modern SQL Server features such as Parameter Sensitive Plan optimization.

Core Concepts

Parameter Sensitivity

Parameter sensitivity means query performance depends heavily on the runtime parameter value. A single query shape can have very different row counts depending on the value passed.

Example:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;

If customer 101 has 5 orders and customer 999 has 2,000,000 orders, the optimal plan may be different for each customer. Reusing one cached plan can cause poor performance for one side of the distribution.

Skewed Data Distributions

A data distribution is skewed when values are unevenly represented.

Examples:

• One tenant has most of the rows in a multi-tenant table.
• Most orders have Status = 'Completed', but very few have Status = 'PendingReview'.
• A country column has many rows for US and very few for smaller markets.
• A date column has most active queries against recent rows.

Skew matters because the optimizer estimates row counts from statistics. If a predicate value is common, a scan or hash join may be cheaper. If a predicate value is rare, an index seek and nested loops join may be cheaper.

Parameter Sniffing

Parameter sniffing is the process where SQL Server uses the parameter value available during compilation to estimate cardinality and choose a plan. Parameter sniffing is not automatically bad. It often improves performance because the optimizer can use a real value instead of a generic guess.

The problem occurs when:

• The first compiled value is not representative.
• The cached plan is reused for very different values.
• Data distribution is skewed.
• The query shape has large plan differences between selective and nonselective values.

In interviews, avoid saying "parameter sniffing is bad." The more accurate answer is: parameter sniffing is normal and useful, but it can create parameter-sensitive plan problems under skewed distributions.

Plan Reuse

SQL Server caches execution plans to avoid recompiling every execution. Reusing plans reduces CPU and improves throughput. However, plan reuse assumes the cached plan is reasonably good for later executions.

Parameter-sensitive workloads break that assumption when one plan cannot serve all values well.

Example plan differences:

• Rare value: index seek, key lookup, nested loops.
• Common value: clustered index scan, hash join, parallel plan.
• Small result: low memory grant.
• Large result: larger memory grant.

The same SQL text can need different physical strategies based on parameter value.

Cardinality Estimation And Statistics

Cardinality estimation is the optimizer's estimate of how many rows an operator will process. These estimates affect join choice, index access, memory grants, parallelism, and operator ordering.

Statistics contain distribution information such as histograms. Histograms can help estimate row counts for specific values, but they have limits:

• A histogram has a limited number of steps.
• Not every value gets its own histogram entry.
• Correlated columns can be hard to estimate.
• Local variables and expressions can hide useful parameter values.
• Stale statistics can make estimates inaccurate.

When estimated rows differ greatly from actual rows, SQL Server may choose a poor plan.

Recognizing Parameter-Sensitive Plan Problems

Symptoms include:

• The same stored procedure is fast for one parameter and slow for another.
• Clearing cache or recompiling temporarily "fixes" the query.
• Query Store shows the same query with multiple plans and high performance variation.
• Actual execution plans show large estimated-vs-actual row differences.
• A plan optimized for a rare value performs badly for a common value.
• A plan optimized for a common value performs badly for a rare value.
• Logical reads, CPU, duration, or memory grants vary widely by parameter value.

Diagnostic questions:

• Which parameter values are fast and slow?
• Is data distribution skewed?
• Does the query have one plan or multiple plans?
• Which value compiled the cached plan?
• Are statistics current?
• Does SQL Server version and compatibility level support PSP optimization?

Query Store Evidence

Query Store is one of the best tools for parameter-sensitive workload investigation. It can show:

• Multiple plans for the same query.
• Runtime statistics per plan.
• High variation in duration or reads.
• Plan history before and after a regression.
• Whether a forced plan helped or hurt.

Queries with multiple plans are not automatically bad, but they are candidates for review.

SELECT
    q.query_id,
    COUNT(DISTINCT p.plan_id) AS plan_count,
    MIN(rs.avg_duration) AS best_avg_duration,
    MAX(rs.avg_duration) AS worst_avg_duration,
    MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration), 0) AS duration_ratio,
    qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY duration_ratio DESC;

This kind of query helps identify plan-unstable statements that deserve deeper analysis.

Parameter Sensitive Plan Optimization

Parameter Sensitive Plan optimization is a modern SQL Server feature that can generate multiple plan variants for a single parameterized query. Instead of forcing one reusable plan to serve all parameter values, SQL Server can use a dispatcher plan that routes execution to a query variant based on cardinality ranges.

Conceptually:

• SQL Server identifies an eligible parameter-sensitive predicate.
• It creates a dispatcher plan.
• It creates multiple query variants.
• At runtime, the dispatcher evaluates the parameter value.
• SQL Server executes the variant that matches the estimated cardinality range.

This can reduce the need for manual workarounds in skewed workloads.

Dispatcher Plans And Query Variants

A dispatcher plan is a cached plan that contains the routing logic. Query variants are separate plans optimized for different parameter value ranges.

For example, a query filtering by tenant might have:

• Variant 1 for small tenants.
• Variant 2 for medium tenants.
• Variant 3 for very large tenants.

The variants can use different join strategies, memory grants, index choices, or degrees of parallelism.

This matters for interviews because it shows that SQL Server can now handle some parameter-sensitive cases adaptively, but the feature has eligibility rules and does not remove the need for good schema, indexes, and query design.

PSP Optimization Requirements And Limits

Parameter Sensitive Plan optimization depends on SQL Server version, database compatibility level, and query eligibility. It is not guaranteed for every parameterized query.

Potential limitations include:

• The query may not match eligible predicate patterns.
• Query variants increase plan cache and Query Store plan counts.
• Query Store max_plans_per_query can matter for heavily variant queries.
• Local variables can prevent useful parameter sensitivity.
• Manual hints can disable or override relevant optimizer behavior.
• Bad indexes or stale statistics can still produce poor plans.

Modern engine features help, but they do not fix every modeling or query design problem.

Optional Parameter Patterns

Optional filters are a common source of parameter-sensitive behavior:

SELECT
    ProductId,
    CategoryId,
    Name,
    Price
FROM dbo.Products
WHERE CategoryId = @CategoryId
   OR @CategoryId IS NULL;

When @CategoryId is NULL, the query returns all categories and a scan may be reasonable. When it is not NULL, an index seek may be better. A single cached plan has to be valid for both cases, which can lead to conservative plans.

Modern SQL Server versions include Optional Parameter Plan Optimization for some optional parameter patterns. Without that feature, common fixes include dynamic SQL, branching, separate procedures, or carefully chosen recompilation.

Mitigation Strategies

Common mitigation strategies include:

• Updating statistics so estimates reflect current data.
• Creating indexes that support both selective and common values where practical.
• Rewriting the query to separate very different cases.
• Using OPTION (RECOMPILE) for statements where per-execution optimization is worth the compile cost.
• Using dynamic SQL with parameterization to produce better plans for optional filters.
• Using OPTIMIZE FOR only when a representative value is known.
• Using Query Store plan forcing as a short-term mitigation.
• Using Query Store hints for targeted operational fixes.
• Enabling appropriate compatibility levels for PSP optimization.

No single mitigation is always best. The right choice depends on execution frequency, compile cost, data skew, business criticality, and SQL Server version.

Branching For Different Selectivity

If parameter values clearly fall into different categories, branching can let each statement compile independently.

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerId int = NULL
AS
BEGIN
    IF @CustomerId IS NULL
    BEGIN
        SELECT OrderId, CustomerId, OrderDate, TotalAmount
        FROM dbo.Orders
        WHERE OrderDate >= DATEADD(day, -7, SYSUTCDATETIME());
    END
    ELSE
    BEGIN
        SELECT OrderId, CustomerId, OrderDate, TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId;
    END
END;

This avoids forcing one predicate pattern to handle all cases. It works best when branches are meaningfully different and easy to reason about.

OPTION RECOMPILE

OPTION (RECOMPILE) tells SQL Server to compile the statement for each execution.

SELECT
    OrderId,
    CustomerId,
    OrderDate
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

Benefits:

• Uses current parameter values.
• Can produce better estimates for skewed values.
• Avoids reusing a bad cached plan.

Trade-offs:

• More compilation CPU.
• Less plan reuse.
• Not ideal for very high-frequency queries.
• Can hide deeper indexing or query design issues.

Use it when execution cost dominates compile cost and parameter values vary enough to require different plans.

OPTIMIZE FOR

OPTIMIZE FOR tells SQL Server to optimize as if a specific parameter value were used.

SELECT
    OrderId,
    CustomerId,
    OrderDate
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 101));

This is useful only when the chosen value is representative enough for the workload. It can make performance worse for other values if distribution is highly skewed.

OPTIMIZE FOR UNKNOWN asks SQL Server to use a more generic estimate rather than sniffing the exact value. That can stabilize performance, but it may also prevent excellent plans for selective values.

Dynamic SQL For Optional Filters

Dynamic SQL can be appropriate when optional filters produce many different query shapes. The goal is to generate only the predicates that are needed while still parameterizing values safely.

DECLARE @sql nvarchar(max) = N'
SELECT OrderId, CustomerId, Status, OrderDate
FROM dbo.Orders
WHERE 1 = 1';

IF @CustomerId IS NOT NULL
    SET @sql += N' AND CustomerId = @CustomerId';

IF @Status IS NOT NULL
    SET @sql += N' AND Status = @Status';

EXEC sys.sp_executesql
    @sql,
    N'@CustomerId int, @Status varchar(20)',
    @CustomerId = @CustomerId,
    @Status = @Status;

The important rule is to parameterize values. Dynamic SQL that concatenates user input is a security problem and a plan-cache problem.

Common Mistakes

Common mistakes include:

• Blaming parameter sniffing without proving skew or plan reuse issues.
• Disabling parameter sniffing globally for one bad query.
• Using local variables to hide parameters and accepting poor generic estimates.
• Adding OPTION (RECOMPILE) everywhere.
• Forcing a plan that is good for one parameter but bad for another.
• Ignoring statistics quality.
• Ignoring data distribution and tenant skew.
• Treating average performance as enough when tail latency matters.
• Using unsafe dynamic SQL.

Best Practices

Best practices include:

• Confirm the problem with actual plans, Query Store, and representative parameter values.
• Compare estimated and actual row counts.
• Identify whether data is skewed.
• Keep statistics updated.
• Build indexes around real access patterns.
• Use Query Store to observe plan variation and regressions.
• Prefer targeted mitigations over global settings.
• Consider modern PSP optimization behavior before adding manual workarounds.
• Use OPTION (RECOMPILE), OPTIMIZE FOR, dynamic SQL, and plan forcing only with a clear reason.
• Revisit mitigations as data and workloads change.