SQL Server 2022 Parameter Sensitive Plan (PSP) Optimization

A killer feature of the next generation of Intelligent Query Processing!

Photo by Caspar Camille Rubin on Unsplash

Introduction

Parameter Sensitive Plan Optimization is one of the new features of SQL Server 2022 and it is part of the family features known as Intelligent Query Processing that improve the performance of existing workloads without changes to the application code.

Parameter Sensitive Plan Optimization

Parameter Sensitive Plan Optimization in SQL Server 2022 further enhances Intelligent Query Processing by addressing the scenario where a single cached execution plan for a parameterized query is not optimal for all possible values those parameters can take. This issue is related to the execution plan saving and re-using mechanism implemented by SQL Server. The execution plan saving and re-using mechanism aims to increase the response time of queries because SQL Server will not have to compile a new plan each time the same query is run because it will cache the one previously saved and parameterized. This technique optimizes the compilation time of a query but in the presence of non-uniform data distributions it can produce a degradation of performance, a phenomenon known as Parameter Sniffing. More information are available in the article Queries that have parameter sensitive plan (PSP) problems.

Parameter Sensitive Plan will allow you to keep multiple active execution plans in the plan cache for a single parameterized query, each execution plan will be optimized and will host different data sizes depending on the values assumed by the parameters. Whenever SQL Server detects the need to use an execution plan other than the one saved in the plan cache for a parameterized query, it will calculate the optimal execution plan for the current parameters values.

Test environment setup

To demonstrate how Parameter Sensitive Plan works we will use the PSP database which can be created in a SQL Server 2022 instance with the following script.

USE [master];
GO
-- Drop database PSP if exists
IF (DB_ID('PSP') IS NOT NULL)
BEGIN
  ALTER DATABASE [PSP]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [PSP];
END;
GO
-- Create database PSP
CREATE DATABASE [PSP]
  ON PRIMARY 
  (
    NAME = N'PSPData'
    ,FILENAME = N'C:\SQL\DBs\PSPData.mdf'
  )
  LOG ON 
  (
    NAME = N'PSPLog'
    ,FILENAME = N'C:\SQL\DBs\PSPLog.ldf'
  );
GO
-- Set recovery model to SIMPLE
ALTER DATABASE [PSP] SET RECOVERY SIMPLE;
GO

Let’s add the dbo.Tab_A table to the newly created PSP database. The following script creates the table and inserts some sample data.

USE [PSP];
GO
DROP TABLE IF EXISTS dbo.Tab_A;
GO
CREATE TABLE dbo.Tab_A
(
  Col1 INTEGER
  ,Col2 INTEGER
  ,Col3 BINARY(2000)
);
GO
-- Insert some data into the sample table
SET NOCOUNT ON;
BEGIN
  BEGIN TRANSACTION;
DECLARE @i INTEGER = 0;
WHILE (@i < 10000)
  BEGIN
    INSERT INTO dbo.Tab_A (Col1, Col2) VALUES (@i, @i);
    SET @i+=1;
  END;
COMMIT TRANSACTION;
END;
GO
-- There are much more rows with value 1 than rows with other values
INSERT INTO dbo.Tab_A (Col1, Col2) VALUES (1, 1)
GO 500000
SET NOCOUNT OFF;
GO
-- Create indexes
CREATE INDEX IDX_Tab_A_Col1 ON dbo.Tab_A
(
  [Col1]
);
GO
CREATE INDEX IDX_Tab_A_Col2 ON dbo.Tab_A
(
  [Col2]
);
GO

The data distribution in the dbo.Tab_A table is not uniform, there are more rows (500,001 rows out of 510,000) with value 1 in columns Col1 and Col2 than rows with other values.

Let’s consider the following stored procedure which performs a simple search on the dbo.Tab_A table for columns Col1 and Col2.

CREATE OR ALTER PROCEDURE dbo.Tab_A_Search
(
  @ACol1 INTEGER
  ,@ACol2 INTEGER
)
AS BEGIN
  SELECT * FROM dbo.Tab_A WHERE (Col1 = @ACol1) AND (Col2 = @ACol2);
END

During the initial compilation of the stored procedure, the statistics available for the columns in the WHERE clause are used to identify non-uniform distributions and evaluate the most “at risk” parameterized predicates, up to three predicates out of all available. The predicate used in the stored procedure affects columns Col1 and Col2, the non-clustered indexes created on these columns gave rise to the creation of the related statistics.

The following query shows the histogram steps that portray the data distribution on the Col1 column, where range_high_key shows the existing Col1 input values.

SELECT
  sh.* 
FROM
  sys.stats AS s
CROSS APPLY
  sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS sh
WHERE
  (name = 'IDX_Tab_A_Col1') AND (s.object_id = OBJECT_ID('dbo.Tab_A'));
GO

The output is shown in the following picture where you can see that the range_high_key with the value 1 has 500,001 records, while the most of the other values have a few records.

Similar situation for the IDX_Tab_A_Col2 index.

The first execution of the stored procedure dbo.Tab_A_Search sets the parameters @ACol1 and @ACol2 to 1, which corresponds to the 500K records data-set.

EXEC dbo.Tab_A_Search @ACol1 = 1, @ACol2 = 1;

The following picture shows the execution plan chosen by SQL Server for this input parameters.

The execution plan shows that a Table Scan has been chosen as the data access method, which is very efficient for this scenario where lots of rows are returned. Plan caching and re-use of this execution plan is the default behavior for parameterized queries in any previous version of SQL Server and Azure SQL Database. If this execution plan were used to retrieve a few rows it would not be efficient.

Using a database with compatibility level set to 160 in SQL Server 2022, Parameter Sensitive Plan can detect these cases for equality predicates like the one used in this example (WHERE Col1 = @ ACol1) and it will allow you to keep multiple active plans in the plan cache for the same query. Each plan will be used and re-used only for executions that return a number of rows similar to the number of rows returned from the first execution of the stored procedure. It follows that during an execution that returns very few rows, we will have a more efficient execution plan with a different data access method and different plan operators. A direct pointing operation on the index (Seek) will be used instead of a total scan (Scan).

EXEC dbo.Tab_A_Search @ACol1 = 33, @ACol2 = 33;
GO
EXEC dbo.Tab_A_Search @ACol1 = 33, @ACol2 = 25;
GO

The following execution plan shows a much more efficient index seek for this executions where a few rows are returned.

For execution plans suitable to be optimized with Parameter Sensitive Plan, the initial compilation produces a dispatcher plan that contains the optimization logic (dispatcher expression). The dispatcher plan maps to query variants based on the predicates cardinality range boundary values. Each variant is linked to an execution plan in which you will find the most suitable operators to handle the dataset that is estimated to be returned by that specific query variant.

Dispatcher plans are automatically updated in case of significant changes to the data distribution. Execution plans linked to query variants are recompiled independently as needed.

The undocumented trace flag 12619 allows you to obtain detailed information about the optimizations performed by the Parameter Sensitive Plan into the Query Store.

The sys.query_store_query_variant system view allows you to monitor the relationships between a query and its variants and allows the creation of reports on all query variants associated with a query with parameters, it represents an alternative to the data aggregation via query_hash.

The following query retrieves data from the plan cache and it shows the presence of two active execution plans for the same query, the first one is optimal for extracting a large number of rows, the second one is optimal for extracting a few rows.

SELECT
  usecounts
  ,plan_handle
  ,objtype
  ,text
FROM
  sys.dm_exec_cached_plans 
CROSS APPLY
  sys.dm_exec_sql_text (plan_handle)
WHERE
  (text LIKE '%Tab_A%')
AND
  (objtype = 'Prepared');
GO

Summary

Query Store enabled by default in SQL Server 2022 combined with the new generation of Intelligent Query Processing allow to improve performance in some common scenarios, without changes to the T-SQL code. Parameter Sensitive Plan (PSP) Optimization represents one of these improvements because it allows to keep multiple active execution plans in the plan cache for a single parameterized query solving the famous problem known with the name of Parameter Sniffing.

Enjoy SQL Server 2022!