Parameter Sensitive Plans: Reading Query Variants in Query Store

One parameterized statement can need different plans for rare and common values. Query variants let SQL Server keep those alternatives, and Query Store shows how they connect to the dispatcher.

Verify the Feature Before Interpreting Extra Plans

Parameter Sensitive Plan optimization arrived in SQL Server 2022 at database compatibility level 160. It also operates under level 170 in SQL Server 2025. The optimizer identifies eligible parameterized equality predicates whose distributions justify different cardinality ranges.

A dispatcher evaluates the incoming parameter range and routes execution to a variant. The variants are ordinary execution plans suited to their selected ranges. They are not a separate hand-written procedure for every possible value.

I check compatibility level and the scoped configuration before diagnosing a missing variant. An upgraded engine can still run a database under an older compatibility level. The engine version alone does not establish that the feature is active.

SELECT name, compatibility_level FROM sys.databases WHERE database_id = DB_ID();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';
SELECT actual_state_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

Query Store needs to capture the statement for the catalog queries below. It provides the durable relationship view, although PSP does not require Query Store to perform its plan selection. Check collection state separately from optimization eligibility.

Build a Deliberately Skewed Equality Filter

Use a disposable database at compatibility level 160 or 170. The generator uses GENERATE_SERIES, which requires SQL Server 2022 or later. The table provides a common category of one million rows and a rare category of ten rows, with a noncovering index.

CREATE TABLE dbo.PSPVariantDemo
(
    EntryID int NOT NULL PRIMARY KEY,
    CategoryID int NOT NULL,
    Metric int NOT NULL,
    Padding char(200) NOT NULL
);
INSERT dbo.PSPVariantDemo
SELECT value, CASE WHEN value <= 1000000 THEN 1 ELSE 2 END,
       value % 100, REPLICATE('x', 200)
FROM GENERATE_SERIES(1, 1000010);
CREATE INDEX IX_PSPVariantDemo_Category ON dbo.PSPVariantDemo(CategoryID);
UPDATE STATISTICS dbo.PSPVariantDemo WITH FULLSCAN;
GO
CREATE PROCEDURE dbo.ReadPSPVariantDemo @CategoryID int
AS
BEGIN
    SELECT SUM(CONVERT(bigint, Metric)) AS TotalMetric
    FROM dbo.PSPVariantDemo
    WHERE CategoryID = @CategoryID;
END;
GO

The requested data counts describe setup, not observed performance. The optimizer still decides whether the distribution and cost difference deserve PSP. This example cannot promise a particular set of plans on every installation.

On my SQL Server 2025 test instance at level 170, this fixture produced one dispatcher and two variant plans. One of my runs listed only one variant, so repeat the calls when a variant seems missing. With 100,000 common rows instead of one million, no dispatcher appeared at all. The skew has to be large before PSP steps in.

The query needs Metric from qualifying rows, so the CategoryID index is deliberately not covering. Rare and common values therefore offer different access-path costs. A fully covering index can change that trade-off and the optimizer's choice.

Execute Both Populations to Create Query Variants

Enable actual plans and run the procedure with both values. Avoid OPTION (RECOMPILE) while demonstrating reusable variants. Recompiling every execution addresses parameter visibility through a different mechanism.

EXEC dbo.ReadPSPVariantDemo @CategoryID = 2;
EXEC dbo.ReadPSPVariantDemo @CategoryID = 1;
EXEC dbo.ReadPSPVariantDemo @CategoryID = 2;
EXEC dbo.ReadPSPVariantDemo @CategoryID = 1;

Compare the returned plans and their parameter information. Inspect seeks, scans, lookups, and estimated versus actual rows. Do not claim that a specific call used a seek until the actual plan confirms it.

A variant's statement text can include PLAN PER VALUE and predicate_range information. Those annotations are generated internally. Do not copy PLAN PER VALUE into application SQL as a hint you expect to request manually.

Variants cover ranges selected from statistics. Two distinct input values can use the same variant when they fall within the same range. Their existence does not establish one plan for each literal value.

Join Parent Queries to Their Query Variants

sys.query_store_query_variant supplies query_variant_query_id, parent_query_id, and dispatcher_plan_id. Join the variant query identifier to sys.query_store_plan.query_id. Join the dispatcher identifier to plan_id, not query_id.

SELECT v.parent_query_id, v.query_variant_query_id,
       v.dispatcher_plan_id, vp.plan_id AS VariantPlanID,
       vp.plan_type_desc AS VariantPlanType,
       dp.plan_type_desc AS DispatcherPlanType,
       qt.query_sql_text AS ParentText,
       TRY_CONVERT(xml, vp.query_plan) AS VariantPlanXml,
       TRY_CONVERT(xml, dp.query_plan) AS DispatcherPlanXml
FROM sys.query_store_query_variant AS v
JOIN sys.query_store_query AS q ON q.query_id = v.parent_query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS vp ON vp.query_id = v.query_variant_query_id
LEFT JOIN sys.query_store_plan AS dp ON dp.plan_id = v.dispatcher_plan_id
WHERE q.object_id = OBJECT_ID(N'dbo.ReadPSPVariantDemo')
ORDER BY v.parent_query_id, v.query_variant_query_id, vp.plan_id;

The parent identifies the original parameterized statement. Each variant query can have its own plan history. Keep all three identifiers in diagnostic output so later comparisons do not confuse those levels.

The dispatcher itself does not contribute ordinary execution runtime statistics like its variants. Summarize the child variants when measuring the original statement's executed work. Looking only for dispatcher runtime rows leaves the report incomplete.

Read the Dispatcher Boundaries

Open the dispatcher XML or extract ParameterSensitivePredicate attributes. LowBoundary and HighBoundary describe the statistics-derived ranges considered during dispatch. Read the associated predicate and statistics reference alongside those numbers. The XML methods need QUOTED_IDENTIFIER ON, which SSMS sets by default. From sqlcmd without the -I switch, this query fails with error 1934.

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT p.plan_id,
       n.x.value('(@LowBoundary)[1]', 'nvarchar(100)') AS LowBoundary,
       n.x.value('(@HighBoundary)[1]', 'nvarchar(100)') AS HighBoundary,
       n.x.query('Predicate') AS PredicateXml
FROM sys.query_store_plan AS p
CROSS APPLY (SELECT TRY_CONVERT(xml, p.query_plan) AS PlanXml) AS c
CROSS APPLY c.PlanXml.nodes('//ParameterSensitivePredicate') AS n(x)
WHERE p.query_id IN
(
    SELECT query_id FROM sys.query_store_query
    WHERE object_id = OBJECT_ID(N'dbo.ReadPSPVariantDemo')
);

Boundary values must be interpreted with the actual predicate and data type. They are not a business classification you should persist in application code. Statistics changes can cause the dispatcher to be rebuilt with different boundaries.

I keep the dispatcher and variant XML together when reviewing a complaint. A variant alone shows its chosen work, but the dispatcher explains how a call reaches it. A collection of plans without the routing rule is only half the diagram.

Respect the Eligibility and Variant Limits

Which parameter distribution caused the complaint? Check its statistics and equality predicate before expecting this feature to help. PSP selects a limited set of interesting predicates and bounded variants, rather than generating every possible combination without limit.

The feature currently targets equality predicates. It does not make every range search or optional-parameter pattern eligible through the same rule. SQL Server 2025 adds other PSP improvements, but the example here remains an equality-filter demonstration.

Check the Runtime Population of Query Variants

Group runtime statistics by variant plan and interval when comparing execution behavior. Use count_executions to weight average duration, and keep minimum and maximum beside it. An active interval can contain several summary rows, so combine those rows instead of assuming the first one is complete.

A variant used for a rare population should not be judged against the common population's total work alone. The plans serve different cardinality ranges. Compare each with representative calls in its own range, then evaluate the original statement's overall behavior across the workload.

If the catalog query returns nothing, check whether the procedure was captured and whether a dispatcher was actually compiled. Verify the feature configuration and statistics before clearing anything. Broad plan-cache clearing is unnecessary for identifying a missing Query Store relationship and disrupts unrelated work.

Retain the actual input values used in a controlled test, but protect sensitive parameters in production diagnostics. Query Store's variant text describes internal routing information. It does not provide a complete record of every caller's original parameter values.

Check the report after statistics or workload changes. A previously useful boundary can be rebuilt as the distribution changes. Keep each captured dispatcher with its own time and plan identifier rather than attaching today's boundaries to an old variant history.

Judge the Plans by the Work They Perform

Compare representative rare and common calls using actual plans and measured runtime evidence from your own server. Several plans are not automatically a cache problem. Their purpose is to avoid forcing substantially different populations through one compromise.

A dispatcher is a traffic controller, not a mind reader. Poor statistics, a changed workload, or an expensive query expression still needs investigation. Keep those causes separate from the presence of multiple variants.

Query variants explain why an eligible statement can use several physical plans. Inspect the dispatcher and runtime evidence before judging whether those query variants serve their assigned ranges well.

Related reading on this blog: SQL SERVER 2022: Parameter Sensitive Plan Optimization (PSPO) and Forcing a Plan in Query Store and Checking That It Held.

Keeping multiple query variants is not duplicate confusion, it is one statement receiving plans for different cardinality ranges.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.