SQL Server 2022 has lot of improved and advanced features compared to its predecessor versions. This includes new Server roles, improved Query Store, TempDB performance, Intelligent Query Processing, Contained Availability Groups, Database Ledger, etc.
In today's article we will see a practical demonstration of once such feature - Parameter Sensitive Plan Optimization(PSPO). We will see the challenges of parameterized stored procedures in older versions and how PSP optimization addresses the challenges and improves query execution plans in this new version.
This article only covers the practical demonstration using a sample database. Please refer to the official documentation from Microsoft to learn the detailed theory behind Parameter Sensitive Plan Optimization.
Parameter Sniffing Challenges in Older Versions
Parameter Sensitive Plan Optimization feature is designed to address the challenge of parameter sniffing in parameterized queries. SQL Server caches the query plans of these queries based on the initial parameter value. This created the problem of 'parameter sniffing' as the generated plans are based on the initial parameters, which may not be optimal for subsequent execution of the query with different parameters, especially for skewed data.
Demonstration with Dummy Skewed Data
Lets see an example of Parameter Sniffing and observe the behavior of SQL Server.
Note: We will use SQL Server 2022 Enterprise Edition in this article for demo purposes.
The script below creates a table, named Users, containing hotel check-in details of travelers across 4 different cities of India. It also inserts 1 million skewed(non-uniformly distributed) dummy user records in the table.
CREATE TABLE dbo.Users(
ID INT,
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
City NVARCHAR(100),
HotelName NVARCHAR(100),
CheckInDate DATE
);
GO
;WITH Numbers AS (
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.Users(ID, FirstName, LastName, City, HotelName, CheckInDate)
SELECT
RowID,
'Rahul',
CASE WHEN RowID % 2 = 1 THEN 'Sharma' ELSE 'Pandey' END,
CASE
WHEN RowID % 10 = 1 THEN 'Udaipur'
WHEN RowID % 1000 = 5 THEN 'Jaipur'
WHEN RowID % 1000000 = 3 THEN 'New Delhi'
ELSE 'Agra'
END,
CASE WHEN RowID % 2 = 1 THEN 'Oberoi' ELSE 'Taj' END,
DATEADD(DAY, -1 * (RowID % 100), CAST(GETDATE() AS DATE))
FROM Numbers;
GONext, use this script to add a Non-Clustered Index on the City column.
CREATE INDEX IX_City ON dbo.Users(City) GO
After you add the index, execute the Select query below to check the data distribution(frequency) of each 'City'. You will notice a highly skewed distribution as 'New Delhi' has only 1 record, while 'Agra' has 898999 records in the table.
SELECT City AS TravelDestination, COUNT(*) AS NoOfVisitors FROM dbo.Users GROUP BY City ORDER BY NoOfVisitors GO
Now that our data is ready, create a parameterized Stored Procedure using below script. It takes 'City' as input parameter and returns top 1000 records from User table that matches the parameter.
CREATE OR ALTER PROCEDURE dbo.UserDestination @City NVARCHAR(100) AS BEGIN SELECT TOP 1000 * FROM dbo.Users WHERE City = @City ORDER BY ID END GO
Next, clear the SQL Sever Plan cache, enable the Statistics and IO. Set the Compatibility Level of the database to 130(SQL 2016) to observe the behavior of execution plans in lower version.
DBCC FREEPROCCACHE GO SET STATISTICS IO, TIME ON GO ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 130 -- SQL 2016 GO
Finally, execute the Stored Procedure with different City names by enabling the 'Include Actual Execution Plan' option.
USE DBTest EXEC dbo.UserDestination 'New Delhi'; -- 1 Row(s) EXEC dbo.UserDestination 'Jaipur'; -- 1000 Row(s) EXEC dbo.UserDestination 'Udaipur'; -- 100000 Row(s) EXEC dbo.UserDestination 'Agra'; -- 898999 Row(s) GO
Image 1If you look at the actual execution plan of each execution, you will notice irrespective of the number of records for each city, all the four executions use the same plan - Index Seek with Row ID lookup on Heap, then Inner Join and Sort.
Now, clear the Plan Cache again, change the order of execution and then observe the behavior.
DBCC FREEPROCCACHE GO USE DBTest EXEC dbo.UserDestination 'Agra'; -- 898999 Row(s) EXEC dbo.UserDestination 'New Delhi'; -- 1 Row(s) EXEC dbo.UserDestination 'Jaipur'; -- 1000 Row(s) EXEC dbo.UserDestination 'Udaipur'; -- 100000 Row(s) GO
Image 2This time again all four execution uses the same plan but different from previous run - Entire Table Scan, then Sorting, followed by Parallelisms and Top 1000 rows.
How Parameter Sniffing impacts Query Plans
Notice that, in the first image, when the stored procedure was first executed with 'New Delhi' as input parameter, SQL sniffed this value and generates an execution plan based on the number of records of 'New Delhi'. As it has only 1 record, SQL generated the optimal plan for the query with Index Seek and Row lookup. Then it uses the same plan for three subsequent parameters. Even for the parameter like Agra (898999 records), it looks up for 898999 times which is not an optimal way.
In the second image, when we ran the stored procedure after clearing cache with 'Agra' as first parameter, SQL sniffed this value and generated an execution plan based on the number of records of 'Agra'. As it has 898999 records, SQL generated the best possible plan for this query which is Table Scan(instead of seek and lookup as in previous case). Likewise, it uses the same plan for three subsequent parameters. For parameter like 'New Delhi', lookup was the optimal way to fetch the records, but still due to parameter sniffing, it scans the entire table to fetch 1 row.
Parameter Sensitive Plan Optimization(PSPO) in SQL 2022
Now that we understand the problem we had in older versions, let see how PSPO solves this issue in SQL Server 2022.
Using the below script, clear the Plan cache again, set the Compatibility Level of the database to 160(SQL 2022) and execute the stored procedures with different parameters.
DBCC FREEPROCCACHE GO ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 160 -- 2022 GO USE DBTest EXEC dbo.UserDestination 'New Delhi'; -- 1 Row(s) EXEC dbo.UserDestination 'Jaipur'; -- 1000 Row(s) EXEC dbo.UserDestination 'Udaipur'; -- 100000 Row(s) EXEC dbo.UserDestination 'Agra'; -- 898999 Row(s) GO
This time, SQL did not sniff the first parameter and generated multiple plans (3) based on different parameter and number of records. For 'New Delhi' (1 record), it generated - Index Seek with Row ID lookup on Heap, then Inner Join and Sort. And for 'Agra' (898999 records), it generated Table Scan, then Sorting, followed by Parallelisms and Top 1000 rows. In both cases, it uses the optimal query plan to give the results.
SQL Server now caches three execution plans(Low, Medium and High) in its plan cache instead of one based on different parameter values cardinality range. In the image below, notice that while executing the queries, SQL internally adds a OPTION(PLAN PER VALUE(...)) query hint. This hint includes QueryVariantID which identifies the specific plan variant used for a certain parameter value range. It also has a predicate_range which defines estimated number of rows (cardinality) that the plan is efficient for.
Enabling and Disabling PSPO
PSPO is enabled by default in SQL Server 2022. However due to any reason if you want to disable it at database level, use this script:
ALTER DATABASE [DBTest] SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
If you want to enable it again, you can do so using this code:
ALTER DATABASE [DBTest] SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
Limitations of PSPO
With benefits, PSPO has some limitations as well. The main ones are:
- Caching and enabling multiple execution plans for a single parameterized query, increase overall memory and CPU usage and plan cache size, make it a concern for applications with limited resources.
- Where data is not skewed enough, PSPO doesn't works. While it reduces the problem of parameter sniffing, it doesn't provide a universal solution. With three execution plans, there are chances of more parameter sniffing. However, in most scenarios it overcomes this problem and provides improved overall query performance.
Conclusion
To summarize, PSPO is a valuable feature of SQL Server 2022 and it significantly improves the query performance. Also it reduces the problem of parameter sniffing to some extent as well. However it is important to understand when this feature is not optimal for all your queries. Careful consideration and disabling this feature when not needed ensures optimal performance of your SQL Server.
