DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • Common Mistakes to Avoid When Writing SQL Code
  • Monitoring and Managing the Growth of the MSDB System Database in SQL Server
  • Memory-Optimized Tables: Implementation Strategies for SQL Server

Trending

  • Create POM With LLM (GitHub Copilot) and Playwright MCP
  • Code of Shadows: Master Shifu and Po Use Functional Java to Solve the Decorator Pattern Mystery
  • Build Your Private Cloud at Home
  • Effective Exception Handling in Java and Spring Boot Applications
  1. DZone
  2. Data Engineering
  3. Databases
  4. Resolving Parameter Sensitivity With Parameter Sensitive Plan Optimization in SQL Server 2022

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

Learn how SQL Server 2022's Parameter Sensitive Plan Optimization (PSP) solves parameter sniffing by caching multiple execution plans for parameterized queries.

By 
arvind toorpu user avatar
arvind toorpu
DZone Core CORE ·
May. 30, 25 · Analysis
Likes (1)
Comment
Save
Tweet
Share
2.7K Views

Join the DZone community and get the full member experience.

Join For Free

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. 

Parameter Sensitive Plan Optimization (PSP)

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:        

MS SQL
 
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:        

MS SQL
 
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.        

MS SQL
 
-- 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.        

MS SQL
 
-- 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:        

MS SQL
 
-- 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:        

MS SQL
 
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.         

Database optimization sql

Opinions expressed by DZone contributors are their own.

Related

  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • Common Mistakes to Avoid When Writing SQL Code
  • Monitoring and Managing the Growth of the MSDB System Database in SQL Server
  • Memory-Optimized Tables: Implementation Strategies for SQL Server

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: