Resolving Parameter Sensitivity With Parameter Sensitive Plan Optimization in SQL Server 2022

For years, database administrators and developers working with SQL Server faced a persistent performance issue known as parameter sniffing. This problem arises because SQL Server’s traditional query plan caching mechanism generates only a single execution plan for a parameterized query. That plan is based on the parameter values used during the query's first execution. 

While efficient in many cases, this behavior leads to significant performance degradation when parameter values vary widely in terms of their data selectivity or distribution. For example, a plan optimized for a parameter value that filters a small subset of rows can be reused for a parameter that retrieves millions of rows, causing inefficient scans and resource overuse. 

The consequence of parameter sniffing is poor performance during subsequent query executions that do not fit the original parameter profile. SQL Server professionals often relied on workarounds like query hints, plan guides, or dynamic SQL to address this issue, creating additional maintenance challenges. Recognizing the need for an automated solution, Microsoft introduced Parameter Sensitive Plan Optimization (PSP) in SQL Server 2022, a feature designed to eliminate the inefficiencies caused by parameter sniffing. PSP enables SQL Server to create and cache multiple execution plans for a single parameterized query, with each plan optimized for a specific range or category of parameter values. This ensures that the engine selects the most appropriate plan for each execution based on the parameter value at runtime, delivering consistent and efficient performance.   

PSP is automatically enabled when the database compatibility level is set to 160 (SQL Server 2022). It applies to queries that involve equality predicates on columns with unevenly distributed data. 

For example, consider a Sales table with millions of rows and a RegionID column where one region accounts for 90% of the data. A stored procedure that filters sales data by RegionID might suffer from severe performance issues in a pre-PSP environment if the execution plan for the densely populated region is reused for less populated ones.   

Here’s how such a scenario might look. First, assume the Sales table is created and populated:        

CREATE TABLE Sales (
    SaleID INT IDENTITY PRIMARY KEY,
    RegionID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Populate table with skewed data
INSERT INTO Sales (RegionID, SaleAmount, SaleDate)
SELECT CASE 
           WHEN ABS(CHECKSUM(NEWID())) % 10 = 0 THEN 1  -- RegionID 1 (90% of rows)
           ELSE ABS(CHECKSUM(NEWID())) % 100 + 2        -- Other regions
       END,
       RAND() * 1000,
       DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 1000, GETDATE())
FROM sys.all_objects a, sys.all_objects b
WHERE a.object_id < 100 AND b.object_id < 100;

Next, a stored procedure queries the table:        

CREATE PROCEDURE GetSalesByRegion  
    @RegionID INT  
AS  
BEGIN  
    SELECT SaleID, SaleAmount, SaleDate  
    FROM Sales  
    WHERE RegionID = @RegionID;  
END;  

-- Execute the procedure with two different parameters
EXEC GetSalesByRegion @RegionID = 1;  -- Region with 90% of rows
EXEC GetSalesByRegion @RegionID = 50; -- Rare region

In a pre-PSP environment, the execution plan generated for @RegionID = 1 (likely using a clustered index scan due to the large row count) is reused for @RegionID = 50. This leads to poor performance, as a scan plan is suboptimal for queries involving low-cardinality regions, which could have benefited from an index seek. PSP in SQL Server 2022 solves this by caching separate plans optimized for high and low-selectivity parameter values, ensuring optimal performance across different regions.

Let's Validate  

To test PSP and validate its behavior, start by enabling the Query Store if it is not already active and setting the database compatibility level to 160. This ensures that PSP is enabled for the workload.        

-- Enable Query Store and set compatibility level
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

ALTER DATABASE [YourDatabaseName]
SET COMPATIBILITY_LEVEL = 160;

Create the Sales table and populate it with the skewed data as described in the introduction. This provides a realistic workload for testing PSP. Then, define and execute the stored procedure multiple times using different RegionID values, representative of both high-cardinality (hot) and low-cardinality (cold) data.        

-- Run queries with different parameters
EXEC GetSalesByRegion @RegionID = 1;  -- Hot value
EXEC GetSalesByRegion @RegionID = 50; -- Cold value

To validate PSP, capture the cached execution plans and inspect whether multiple plans are being generated for the same query. Use the following queries to observe PSP activity:        

-- View cached plans
SELECT cp.objtype, cp.usecounts, qs.query_text, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qs
WHERE qs.text LIKE '%GetSalesByRegion%';

-- Inspect Query Store runtime stats
SELECT qs.query_id, p.plan_id, p.is_parameter_sensitive_plan, rs.first_execution_time, rs.last_execution_time, rs.avg_duration
FROM sys.query_store_query qs
JOIN sys.query_store_plan p ON qs.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qs.query_sql_text LIKE '%GetSalesByRegion%';

Additionally, enable detailed statistics to measure query performance:        

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Execute queries and compare results
EXEC GetSalesByRegion @RegionID = 1;
EXEC GetSalesByRegion @RegionID = 50;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Before PSP, forcing a single execution plan can simulate the pre-2022 behavior, showing inefficiencies when the same plan is reused for different parameter values. This may result in high logical reads, excessive CPU usage, and longer runtimes for low-cardinality cases. 

After enabling PSP, SQL Server should generate separate plans for hot and cold values, significantly improving execution times. For example, a query for RegionID = 1 sees no regression, while the same query for RegionID = 50 improves from 8 seconds to 2 seconds post-PSP. Query Store confirms that multiple plans are being cached for the same query, each optimized for different parameter ranges.

Conclusion  

The Parameter Sensitive Plan Optimization (PSP) feature in SQL Server 2022 addresses one of the most persistent performance issues caused by parameter sniffing. By creating and caching multiple execution plans for queries involving parameterized equality predicates with skewed data distributions, PSP ensures that SQL Server selects an optimal plan for every execution. This advancement dramatically improves query stability and avoids the inefficiencies caused by a one-size-fits-all approach to plan reuse.   

For database administrators and developers, PSP reduces the need for manual tuning techniques like query hints or plan guides. Instead, the feature automatically adapts to workload variations when the database compatibility level is set to 160. While PSP applies only to specific query shapes, its impact is substantial for OLTP environments with data skew. By enabling PSP and monitoring Query Store, DBAs can ensure consistent performance and make informed decisions about query optimization in SQL Server 2022.