In June, I wrote about a parameter-sniffing incident that turned a query that normally finished in 200 milliseconds into a 90-second outage. We stabilized the system by forcing the known-good plan in Query Store, then split small and large customers into separate code paths as the permanent fix. That left me with a question: if the database had been running SQL Server 2022 or later, would Parameter Sensitive Plan optimization have prevented the incident? I rebuilt the essential part of that workload to find out.

The short answer is probably yes for that query—but parameter sniffing is not a solved problem.

Parameter Sensitive Plan (PSP) optimization can keep multiple plans for one parameterized statement and route each execution to the appropriate plan. It is an excellent fit for equality predicates over skewed data. It also has eligibility rules, operational subtleties, and failure cases that DBAs need to understand before relying on it. This article recreates the problem, shows how to prove that PSP is active, and compares it with the fixes we have traditionally used.

The Problem PSP is Designed to Solve

Parameter sniffing itself is not a defect. When SQL Server compiles a parameterized statement, it uses the current parameter value to estimate cardinality. A plan tailored to the value being executed is usually better than a generic plan. The trouble begins when SQL Server reuses that cached plan for values with very different data volumes. Consider an orders table with this distribution:

For an ordinary customer, an index seek followed by a small number of key lookups can be ideal. For the enterprise customer, hundreds of thousands of lookups can cost far more than scanning the clustered index. Neither plan is best for both values. Before SQL Server 2022, the cached plan was often the one that won the compilation lottery.

PSP changes that model. An eligible statement receives a dispatcher plan. The dispatcher divides parameter values into cardinality ranges, or buckets, and routes each execution to a corresponding query variant. Every variant has its own cached execution plan. Microsoft documents up to three cardinality ranges per selected predicate. PSP evaluates as many as three eligible predicates while deliberately limiting variants to control plan-cache and Query Store growth. The engine—not the DBA—chooses the boundaries from statistics. See Microsoft's Parameter Sensitive Plan optimization documentation.

Build a skewed test workload

This lab requires SQL Server 2022 or later. Run it in a disposable environment: it creates a database and loads one million rows into an Orders table. Change the database name if necessary.

use master;
go

drop database if exists PSPDemo;
go

create database PSPDemo;
go

alter database PSPDemo
set query_store = on
    (
        operation_mode = read_write
      , query_capture_mode = all
      , interval_length_minutes = 1
    );
go

use PSPDemo;
go

create table dbo.Orders
(
    OrderID bigint not null
  , CustomerID int not null
  , OrderDate date not null
  , TotalAmount decimal(12, 2) not null
  , OrderStatus char(1) not null
  , Notes char(100) not null
  , constraint PK_Orders
        primary key clustered (OrderID)
);
go

with E1 (N)
as (select 1
    from
    (
        values
            (0)
          , (0)
          , (0)
          , (0)
          , (0)
          , (0)
          , (0)
          , (0)
          , (0)
          , (0)
    ) as d (N) )
   , E2 (N)
as (select 1
    from E1           as a
        cross join E1 as b)
   , E4 (N)
as (select 1
    from E2           as a
        cross join E2 as b)
   , E6 (N)
as (select 1
    from E4           as a
        cross join E2 as b)
   , Numbers
as (select top (1000000)
           row_number() over (order by (select null)) as n
    from E6)
insert dbo.Orders
(
    OrderID
  , CustomerID
  , OrderDate
  , TotalAmount
  , OrderStatus
  , Notes
)
select n
     , case
           when n <= 400000 then
               1
           else
               2 + convert(int, (n - 400001) / 100)
       end
     , dateadd(day, -convert(int, n % 1095), convert(date, '2025-01-01'))
     , convert(decimal(12, 2), 10.00 + (n % 50000) / 100.0)
     , case n % 4
           when 0 then
               'N'
           when 1 then
               'P'
           when 2 then
               'S'
           else
               'C'
       end
     , replicate(char(65 + n % 26), 100)
from Numbers;
go

create index IX_Orders_CustomerID on dbo.Orders (CustomerID);
go

update statistics dbo.Orders
with fullscan;
go

create or alter procedure dbo.usp_GetCustomerOrders @CustomerID int
as
begin
    set nocount on;

    select OrderID
         , OrderDate
         , TotalAmount
         , OrderStatus
         , Notes
    from dbo.Orders
    where CustomerID = @CustomerID;
end;
go

The nonclustered index intentionally does not cover every selected column. That gives the optimizer a meaningful choice between a seek with key lookups and a clustered scan. It is a teaching device, not an indexing recommendation. Confirm the skew before testing:

select CustomerID
     , count_big(*) as OrderCount
from dbo.Orders
where CustomerID in ( 1, 2, 3000 )
group by CustomerID
order by CustomerID;

Customer 1 should have 400,000 rows. Customers 2 and 3000 should have 100 rows each.

Reproduce traditional parameter sniffing

Start at compatibility level 150, where PSP does not work. Clear only this database's procedure cache, not the entire instance, and compile the procedure for a small customer with this code.

ALTER DATABASE PSPDemo SET COMPATIBILITY_LEVEL = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SET STATISTICS IO, TIME ON;
GO
EXEC dbo.usp_GetCustomerOrders @CustomerID = 2; -- compiles for 100 rows
EXEC dbo.usp_GetCustomerOrders @CustomerID = 1; -- reuses that plan for 400,000
GO
SET STATISTICS IO, TIME OFF;

Enable Include Actual Execution Plan in SSMS before running the batch. Do not stop at the execution time: open both actual plans and inspect the Parameter List, estimated rows, actual rows, and physical operators.

What the Actual Plans Should Show

The one-million-row lab is deliberately shaped so the evidence is visible in the plans. The first execution is compiled for customer 2. The second execution keeps that compiled value even though the runtime value is customer 1.

The mismatch in the second row is the proof of bad plan reuse. The Key Lookup is executed roughly 400,000 times because the plan was compiled for 100 rows. In the actual-plan XML, the parameter section should show a compiled value of 2 and a runtime value of 1 for that second execution. Now reverse the compilation order:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SET STATISTICS IO, TIME ON;
GO
EXEC dbo.usp_GetCustomerOrders @CustomerID = 1; -- compiles for 400,000 rows
EXEC dbo.usp_GetCustomerOrders @CustomerID = 2; -- reuses the large-value plan
GO
SET STATISTICS IO, TIME OFF;
GO

This produces the opposite evidence:

The scan is reasonable for customer 1, but wasteful for customer 2 because it reads the million-row clustered index to return 100 rows. The exact costs, logical reads, and timings vary with hardware, memory, SQL Server build, and cost estimates. The repeatable evidence is the change in plan shape when the compilation value changes, followed by reuse of that shape for the other value. That is the production failure pattern in miniature: one plan is excellent for ordinary customers and disastrous for the outlier, or vice versa.

Turn on PSP

PSP is available in SQL Server 2022 and later and requires database compatibility level 160 or higher. It is enabled by default, but the script sets it explicitly so the test is unambiguous.

ALTER DATABASE PSPDemo SET COMPATIBILITY_LEVEL = 160;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SET STATISTICS IO, TIME ON;
GO
EXEC dbo.usp_GetCustomerOrders @CustomerID = 2;
EXEC dbo.usp_GetCustomerOrders @CustomerID = 1;
EXEC dbo.usp_GetCustomerOrders @CustomerID = 3000;
EXEC dbo.usp_GetCustomerOrders @CustomerID = 1;
GO
SET STATISTICS IO, TIME OFF;
GO

This time the initial compilation can create a dispatcher. Executing different values causes the relevant variants to compile. Customers 2 and 3000 should route to a low-cardinality variant, while customer 1 should route to a high-cardinality variant.

The Two PSP Plans to Look For

Open all four actual-plan tabs and compare the operator trees. A successful run should group the executions like this:

Those two operator trees are the visual proof that SQL Server is no longer making both populations share one active plan. The repeated execution for customer 1 should return to the same high-cardinality variant instead of compiling a third plan. In each variant's statement text, look for an internal hint resembling this:

PLAN PER VALUE
(
    ObjectID = ...,
    QueryVariantID = ...,
    predicate_range(...)
)

The low- and high-cardinality plans should have different QueryVariantID values. The actual numbers are assigned by SQL Server, so do not expect them to be 1 and 2 or to appear in execution order. QueryVariantID is also different from Query Store's plan_id. If both parameter populations show the same operator tree and the same QueryVariantID, this run has not demonstrated PSP; use the diagnostic checks later in the article. Do not copy the PLAN PER VALUE hint into application code. Microsoft identifies it as an internally generated hint that cannot be used directly. Its presence in ShowPlan, combined with the two different operator trees, is evidence that PSP routed the statement. The dispatcher is not a conventional plan that processes rows. It evaluates the runtime value, maps it to a cardinality bucket, and invokes a variant. That is why two executions of the same stored procedure can use genuinely different active plans without OPTION (RECOMPILE).

Prove it with Query Store

Graphical plans show the operator difference; Query Store proves that both variants belong to one dispatcher. SQL Server exposes the parent-child relationship through sys.query_store_query_variant. This query finds the plans, but keep in mind if your plans are not 1 and 2, the IN clause needs to be changed.

EXEC sys.sp_query_store_flush_db;
GO

SELECT
    qsp.plan_type_desc,
    qsp.plan_id,
    qsp.query_id,
    qsqv.parent_query_id,
    qsqv.query_variant_query_id,
    qsqv.dispatcher_plan_id,
    qsq.count_compiles,
    SUM(ISNULL(qsrs.count_executions, 0)) AS executions,
    MAX(qsrs.last_execution_time) AS last_execution_time,
    CONVERT(xml, qsp.query_plan) AS query_plan
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_query AS qsq
    ON qsq.query_id = qsp.query_id
LEFT JOIN sys.query_store_query_variant AS qsqv
    ON qsqv.query_variant_query_id = qsp.query_id
LEFT JOIN sys.query_store_runtime_stats AS qsrs
    ON qsrs.plan_id = qsp.plan_id
WHERE qsp.plan_type IN (1, 2)
  AND
  (
      qsq.object_id = OBJECT_ID(N'dbo.usp_GetCustomerOrders')
      OR qsqv.parent_query_id IN
      (
          SELECT query_id
          FROM sys.query_store_query
          WHERE object_id = OBJECT_ID(N'dbo.usp_GetCustomerOrders')
      )
  )
GROUP BY
    qsp.plan_type_desc,
    qsp.plan_id,
    qsp.query_id,
    qsqv.parent_query_id,
    qsqv.query_variant_query_id,
    qsqv.dispatcher_plan_id,
    qsq.count_compiles,
    qsp.query_plan
ORDER BY qsp.plan_type_desc, qsp.plan_id;

For the four-execution PSP batch above, the result should have this shape. The numeric IDs below are illustrative; Query Store assigns IDs on each database, and they can change after Query Store cleanup or a new plan is captured.

The two different plan_id values, 202 and 203 in this example, prove that Query Store holds two query-variant plans. Both rows point to the same parent query and dispatcher plan. Open each query_plan XML cell in SSMS: one should contain the seek-and-lookup tree, and the other the clustered-scan tree shown above. The execution counts are two and two because customers 2 and 3000 share the low-cardinality variant, while customer 1 was executed twice through the high-cardinality variant. The IDs on your server will almost certainly differ from this example. Match rows by plan_type_desc and the parent/dispatcher relationships, not by a hard-coded number. Also remember that QueryVariantID in ShowPlan identifies a dispatcher bucket; it is not the same value as plan_id or query_id in Query Store. The dispatcher itself has no Query Store runtime statistics; executions belong to the variants. Monitoring queries written before PSP can therefore undercount resource use unless they include sys.query_store_query_variant.

See Microsoft's catalog-view documentation. That detail matters in production. A monitoring query that aggregates only plans attached directly to the original query can miss the work performed by its children.

Would PSP have prevented the 90-second outage?

The original incident had the characteristics PSP wants:

• A parameterized stored procedure
• An equality predicate on CustomerID
• A useful statistic on that column
• Severe, stable data skew
• Different plans appropriate for small and large values
• Repeated executions that benefit from plan reuse

At compatibility level 160 or later, the optimizer could create separate low- and high-cardinality variants. A statistics update might rebuild the dispatcher after a significant distribution change, but the next enterprise execution would no longer have to donate its plan to every ordinary customer. For this statement, PSP likely would have prevented the bad-plan reuse that caused the outage. Likely is deliberate. PSP is cost-based and eligibility-driven. SQL Server does not promise a variant merely because a human can see skew. The safe conclusion comes from testing the real statement, statistics, indexes, and representative values, then verifying the dispatcher and variants in ShowPlan or Query Store.

What PSP does not fix

PSP is not magic and will not work in every situation. Here are a few things PSP does not fix.

It is not available below compatibility level 160

Installing SQL Server 2022 or 2025 is not enough. A migrated database left at compatibility level 150 will not use PSP. That is easy to miss during an engine upgrade.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

PSP still centers on equality predicates

Microsoft's current documentation says PSP works with equality predicates. Do not assume that a range such as OrderDate >= @StartDate, a leading-wildcard search, or complicated optional-filter logic will qualify. SQL Server 2025 at compatibility level 170 expands PSP to DML statements, broadens tempdb support, and improves the treatment of multiple eligible predicates on the same table. Those changes widen the feature, but they do not turn every parameter-sensitive statement into a PSP candidate.

OPTION (RECOMPILE) solves the problem differently

OPTION (RECOMPILE) produces a new plan using the current values on every execution. That can provide finer specialization than a small set of PSP buckets, but it spends compilation CPU each time and does not reuse a variant. Adding RECOMPILE to the parent also prevents PSP from operating for that statement. It is not “PSP plus recompile”; it is a different strategy.

Disabling parameter sniffing also disables PSP

Trace flag 4136, the PARAMETER_SNIFFING = OFF database-scoped configuration, and USE HINT('DISABLE_PARAMETER_SNIFFING') suppress the sniffing behavior PSP needs. If an old workaround disabled parameter sniffing broadly, PSP cannot quietly take over until you revisit that decision.

Buckets cannot model every value independently

PSP groups cardinalities into ranges. Values in one bucket share a variant even when their ideal plans are not identical. That is the compromise that preserves reuse without allowing unlimited cache growth.

Bad estimates are still bad inputs

The dispatcher boundaries come from statistics. Missing, stale, or insufficient statistics can keep the optimizer from recognizing the skew correctly. PSP reduces sensitivity to one cached plan; it does not make statistics optional.

The root cause may not be parameter sensitivity

PSP will not repair a missing index, non-SARGable predicate, implicit conversion, blocking chain, memory pressure, or a query that is expensive for every value. Multiple variants help only when multiple plans are genuinely appropriate. Here is how the traditional options compare

For an eligible SQL Server 2022+ query, PSP is usually worth testing before you add a permanent hint. It preserves plan reuse, accommodates more than one population, and requires no application change. It does not eliminate the other tools. A query that runs five times per hour may be simplest with RECOMPILE; a vendor query may need a Query Store hint; known enterprise accounts may still justify explicit branches.

Diagnosing why PSP did not appear

If an apparently eligible query produces no dispatcher, start with the basics:

SELECT compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN
(
    'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION',
    'PARAMETER_SNIFFING'
);

Then confirm that the statement is parameterized, uses an equality predicate, and has statistics capable of exposing the skew. Check for RECOMPILE, OPTIMIZE FOR UNKNOWN, or a setting that disables parameter sniffing. For a deeper investigation, Extended Events exposes parameter_sensitive_plan_optimization_skipped_reason and query_with_parameter_sensitivity. List the possible skip reasons on the server:

SELECT map_key, map_value
FROM sys.dm_xe_map_values
WHERE name = N'psp_skipped_reason_enum'
ORDER BY map_key;

That is more reliable than guessing why the optimizer declined to create variants.

What I would deploy

For a production database moving to compatibility level 160 or 170, I would use this sequence:

1. Enable and correctly size Query Store before changing the compatibility level.
2. Capture a representative workload with ordinary and outlier parameter values.
3. Test the new compatibility level outside production.
4. Identify statements that produce dispatchers and compare every important variant.
5. Update monitoring so it includes variant runtime and wait statistics.
6. Retain a rollback mechanism through Query Store hints or plan forcing.
7. Alert on regressions even when PSP is present.

PSP reduces the probability of one class of incident. It does not replace Query Store, realistic performance tests, or regression monitoring. Keep the SQL Server build current as well. Microsoft lists a PSP-related Query Store issue as resolved in SQL Server 2022 CU7 and a readable-secondary issue as resolved in SQL Server 2025 CU1. Review the current cumulative-update guidance for the version you operate instead of testing only RTM media.

Closing

The old model was one parameterized statement, one cached plan, and a great deal of luck about which value compiled it. PSP replaces that with a dispatcher and a controlled collection of reusable variants. For the customer-order query behind my earlier outage, that is almost exactly the behavior we needed: ordinary customers get the small-result plan, the enterprise outlier gets the large-result plan, and neither population dictates the plan for the other. But PSP is a targeted optimization, not an amnesty for every parameter-sniffing problem. It needs the right compatibility level, an eligible predicate, meaningful statistics, and a workload whose skew maps usefully into a few cardinality ranges. It also has to be visible in your monitoring, because variant plans change how Query Store statistics should be aggregated. The practical rule is simple: do not ask whether SQL Server 2022 or 2025 supports PSP. Ask whether this statement produced a dispatcher, which variants it created, and how each variant behaved for production values. That turns PSP from a feature-box checkbox into something you can safely rely on.