What is Parameter Sensitive Plan Optimization (PSPO)?

Parameter Sensitive Plan Optimization (PSPO)

In SQL Server 2022, Parameter Sensitive Plan Optimization (PSPO) is an intelligent query processing feature that automatically addresses a long-standing performance issue known as parameter sniffing. This optimization allows the database engine to generate and cache multiple execution plans for a single stored procedure or parameterized query, each tailored to different parameter values.

The Problem: Parameter Sniffing

Parameter sniffing occurs when the SQL Server Query Optimizer “sniffs” the value of the parameter passed during the first execution of a stored procedure. It then generates an execution plan that is optimal for that specific value. This plan is cached and reused for all subsequent executions of the same procedure, regardless of the new parameter values.

This approach is efficient when all parameter values lead to a similar number of rows. However, when data distribution is skewed (meaning some values return many rows while others return few), a cached plan optimized for a “mouse” (low-selectivity) parameter can perform terribly when executed with an “elephant” (high-selectivity) parameter.

The Solution: Parameter Sensitive Plan Optimization (PSPO)

PSPO fundamentally changes this behavior by using a new type of execution plan called a dispatcher plan. When a parameterized query is first compiled, the optimizer analyzes the data distribution using statistics. If it detects that a parameter’s values are highly skewed, it creates a dispatcher plan that acts as a “smart router.”

Instead of a single plan, the dispatcher plan contains logic. It selects the most appropriate execution plan at runtime. This selection is based on the cardinality of the incoming parameter value. This allows SQL Server to store multiple, distinct query plans in the plan cache and the Query Store. A low-selectivity parameter gets an efficient index seek plan. Meanwhile, a high-selectivity parameter gets an optimized clustered index scan plan.

Step-by-Step Example

Let’s illustrate how PSPO works with a simple scenario involving skewed data.

1. Setup the Database and Table

First, create a new database and a table with skewed data. In this example, we’ll create an Orders table. A few CustomerID values will have a very large number of orders. Most will have only a few orders.

2. Create the Stored Procedure

Next, create a simple stored procedure that retrieves orders based on the CustomerID.

SQL

CREATE OR ALTER PROCEDURE GetOrdersByCustomerID
    @CustomerID INT
AS
BEGIN
    SELECT *
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
END;
GO

3. Demonstrate Parameter Sniffing (Pre-SQL Server 2022)

In older versions of SQL Server, if you execute the procedure with a “mouse” value first (e.g., a customer with few orders), the optimizer would choose an efficient Index Seek plan.

If you then execute the same procedure with an “elephant” value (e.g., a customer with many orders), SQL Server reuses the cached Index Seek plan, even though an Index Scan or a different plan would be much more efficient for the large number of rows. This results in poor performance.

4. Enable and Use PSPO in SQL Server 2022

To take advantage of PSPO, the database compatibility level must be set to 160 (for SQL Server 2022) or higher. Query Store must also be enabled.

SQL

ALTER DATABASE [CustomerDB] SET COMPATIBILITY_LEVEL = 160;
GO
ALTER DATABASE [CustomerDB] SET QUERY_STORE = ON;
GO

Once enabled, simply running the stored procedure will allow the optimizer to automatically detect the parameter sensitivity.

5. How PSPO Works Under the Hood

When a query with a skewed parameter is executed, SQL Server compiles a dispatcher plan. This plan is stored in the Query Store. This plan lacks the actual query logic. Instead, it intelligently routes the execution to a more specific query plan. This specific plan is called a query variant, and it is based on the incoming parameter value.

• A query variant is a compiled plan for a specific range of parameter values (e.g., one for low-selectivity values and another for high-selectivity values).
• When a request comes in, the dispatcher plan quickly evaluates the parameter. Then it selects the pre-compiled variant that is most likely to be optimal.
• The Query Store helps manage these multiple plans, providing detailed insights into which variant is being used and its performance.

This process ensures that a low-selectivity parameter value gets an Index Seek plan. A high-selectivity value gets a more appropriate Clustered Index Scan plan. All of this happens without requiring manual hints or OPTION (RECOMPILE). This adaptability provides stable and consistent performance for a wide range of workloads.