Parameter Sniffing vs. Parameter Sensitive Plan Optimisation: SQL Server's Reality

Parameter sensitive plan optimisation (PSPO) is a SQL Server 2022 feature that automatically generates and caches multiple execution plans for a single parameterised query, each tuned to different data distributions. It directly addresses parameter sniffing, where a plan compiled for one parameter value performs poorly for others. PSPO works by identifying skewed predicates at compile time and creating up to three plan variants based on row count thresholds, reducing the need for manual workarounds like OPTION (RECOMPILE) or plan guides.

That's the textbook answer. The operational reality is more nuanced, and understanding where PSPO helps, where it falls short, and what you still need to do manually is what separates a well-tuned SQL Server from one that's quietly bleeding performance.

What Is Parameter Sniffing and Why Does It Still Matter?

Parameter sniffing has been the source of more emergency calls to DBAs than almost any other SQL Server behaviour. It happens because SQL Server compiles a query plan the first time a stored procedure or parameterised query executes, then caches that plan for reuse. The problem is that the cached plan is optimised for the parameter values used during that first compilation.

Consider a sales database where 90% of orders belong to a handful of large customers and the remaining 10% are spread across thousands of small ones. A plan compiled for a large customer might use a clustered index scan, which is efficient for returning thousands of rows. Run that same plan for a small customer expecting two rows and you've got a full table scan where a seek would finish in milliseconds.

Here's a simplified example that demonstrates the problem:

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID;
END

If this procedure is first called with CustomerID = 1 (a large customer with 500,000 rows), SQL Server caches a scan-based plan. Every subsequent call, including those for small customers expecting 5 rows, reuses that plan. The result is predictable: small customer queries run far slower than they should.

This is not a bug. It's SQL Server doing exactly what it's designed to do. The issue is that a single cached plan can't be optimal across wildly different data distributions.

How Parameter Sensitive Plan Optimisation Works in SQL Server 2022

PSPO is enabled by default in SQL Server 2022 under database compatibility level 160. Rather than caching one plan per parameterised statement, the optimiser identifies "parameter sensitive predicates" during compilation and creates up to three plan variants, called dispatcher plans, each covering a different row count range.

Microsoft defines three buckets based on estimated row counts:

• Low selectivity - estimated rows above a high threshold (typically covering large result sets)
• Medium selectivity - estimated rows in the middle range
• High selectivity - estimated rows below a low threshold (small, targeted result sets)

When a query executes, the dispatcher plan evaluates the actual parameter value, estimates the expected row count, and routes execution to the appropriate variant. Each variant is independently compiled and cached.

This is a meaningful improvement. In testing on skewed datasets, PSPO can reduce query duration by 60-80% for the parameter values that were previously getting the wrong plan. Microsoft's internal benchmarks showed consistent improvements on TPC-H style workloads with non-uniform data distributions.

To check whether PSPO is active in your environment:

-- Check database compatibility level
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

-- Verify PSPO is not disabled at database scope
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';

If you're on compatibility level 160 and the configuration shows 1, PSPO is active.

Where PSPO Falls Short in Real-World Environments

Here's where the marketing story diverges from operational reality. PSPO is genuinely useful, but it's not a comprehensive fix for parameter sniffing. Several significant limitations affect how much it helps in production.

It only handles single-predicate skew. PSPO analyses one predicate at a time. If your query has multiple columns with skewed distributions, PSPO may not generate useful variants for the combined selectivity problem. Multi-column skew is common in real schemas.

It doesn't apply to all query shapes. PSPO has documented exclusions. Queries with parallel plans, queries referencing views or inline table-valued functions, and certain join patterns may not qualify for PSPO optimisation. You can identify these in execution plans by looking for the absence of the "Dispatcher" operator.

Three buckets isn't always enough. Some distributions need finer granularity. A column with values ranging from 1 row to 10 million rows might need more than three plan variants to cover the full range effectively.

It doesn't replace query design. If your stored procedure has parameter sniffing issues because of poor indexing, outdated statistics, or genuinely problematic T-SQL patterns, PSPO won't fix those. It optimises plan selection, not the underlying query.

For environments running SQL Server 2019 or earlier, none of this applies at all. Parameter sniffing remains entirely a manual problem to solve. A proper sql server performance audit will often surface sniffing issues as the primary driver of inconsistent query times, and the remediation options on older versions are more labour-intensive.

What Are the Proven Workarounds for Parameter Sniffing?

Whether you're on SQL Server 2022 with PSPO or an older version, you need a toolkit of approaches. Here are the options ranked by their trade-offs:

1. OPTION (RECOMPILE) - Forces a new plan on every execution using the current parameter values. Eliminates sniffing but adds compilation overhead. Suitable for queries that run infrequently but vary widely in parameter selectivity. Not appropriate for high-frequency queries.

2. OPTIMIZE FOR hint - Tells the optimiser to compile for a specific value or for UNKNOWN (which uses average statistics). Useful when you know the "typical" parameter value. OPTIMIZE FOR UNKNOWN often produces mediocre plans for all values rather than a good plan for any.

3. Plan guides - Allow you to attach query hints to specific query text without modifying application code. Powerful but maintenance-heavy. Use them when you can't touch the application.

4. Local variables - Assigning parameters to local variables inside a procedure prevents sniffing because SQL Server can't see the actual value at compile time. It uses average statistics instead. This is the same trade-off as OPTIMIZE FOR UNKNOWN.

5. Multiple stored procedures - Split one procedure into several, each optimised for a specific parameter range. More code to maintain, but gives you explicit control over each plan.

6. Query Store forced plans - If you find a good plan, force it using Query Store. This is a reasonable short-term fix but requires ongoing monitoring as data distributions change.

PSPO effectively automates a version of option 5, but with the limitations described above. For complex environments, you'll likely still reach for manual workarounds for queries that PSPO doesn't cover.

How Does AI Query Optimisation Change the Picture?

There's growing interest in ai query optimisation tools that promise to automatically detect and resolve performance issues including parameter sniffing. The honest assessment is that these tools are useful for triage and pattern detection, but they don't replace DBA expertise for resolution.

AI-assisted tools can scan execution plan caches, identify queries with high variance in execution times across different parameter values, and flag probable sniffing candidates. That's genuinely valuable for large environments where manual review of thousands of queries isn't practical. Some tools integrate with Query Store data to surface plan regressions automatically.

What they can't do reliably is make the contextual judgements that good sql server query optimisation requires: understanding the business rules behind a query, knowing whether a schema change is feasible, or evaluating the downstream impact of forcing a plan. Those decisions still need a DBA who understands the environment.

Key Takeaways

• Parameter sensitive plan optimisation in SQL Server 2022 automatically creates up to three cached plan variants for parameterised queries with skewed data distributions, reducing the impact of parameter sniffing without manual intervention.
• PSPO requires database compatibility level 160 and is enabled by default. It does not apply to all query shapes, and single-predicate limitations mean complex queries may still need manual tuning.
• On SQL Server 2019 and earlier, parameter sniffing remains entirely a manual problem. OPTION (RECOMPILE), plan guides, and Query Store forced plans are the primary tools.
• AI query optimisation tools can accelerate detection of sniffing issues at scale, but they don't replace DBA judgement for remediation decisions.
• A targeted sql server performance audit will identify whether parameter sniffing is a significant contributor to your performance problems and which specific queries need attention.

What to Do Next

Parameter sniffing is one of the most common findings in DBA Services' sql server performance audit engagements. In our experience, roughly two-thirds of environments running mixed OLTP workloads have at least one high-impact stored procedure affected by sniffing, often one that's been causing intermittent complaints for months or years without a clear diagnosis.

If you're on SQL Server 2022, it's worth verifying that PSPO is actually helping your most problematic queries, not just assuming it is. If you're on an older version, the issue won't resolve itself.

DBA Services offers SQL Server health checks that specifically identify parameter sniffing hotspots, assess whether PSPO is working effectively in your environment, and provide concrete, prioritised recommendations. We work with your existing team rather than replacing them. Contact us to discuss what a health check would look like for your environment.