Parameter Sensitive Plan (PSP) in SQL Server

Have you ever tuned a stored procedure that runs efficiently for almost every user, only to watch it slow down to a crawl for a specific customer? Same server, same database query, but different parameters. This is a classic database performance bottleneck known as the Parameter Sensitive Plan (PSP) problem.

In this guide, we will analyze why PSP issues occur, how to detect them in your database, and how to address them—including using the built-in optimization features available in SQL Server 2022.

What is a Parameter Sensitive Plan?

When SQL Server executes a stored procedure for the first time, it compiles an execution plan. To build an efficient plan, the query optimizer looks at the specific parameter values supplied during that initial execution. This mechanism is called parameter sniffing.

Once compiled, SQL Server saves (caches) this execution plan and reuses it for subsequent runs, even if the next set of parameters is vastly different.

This behavior becomes problematic when your database has unevenly distributed data. For example:

• Customer A has only 5 orders.
• Customer B has 2 million orders.

If the compiled plan was built based on Customer A's data, it will perform poorly when executed for Customer B. The plan was designed to retrieve a tiny, targeted result set, not a massive one.

A PSP issue occurs when SQL Server reuses an execution plan compiled for one parameter value on a different parameter value that requires a significantly different data retrieval strategy due to the volume of data returned.

‍Step-by-Step Breakdown: How the PSP Issue Occurs

Here is a clear breakdown of how SQL Server falls into the PSP trap:

Why does this cause a performance drop?

An execution plan optimized for 3 rows will typically use an Index Seek to fetch the data. Trying to retrieve 2 million rows using an Index Seek is highly inefficient. It forces the database engine to perform millions of individual lookups. For large datasets, a clustered index scan or a hash join is much faster.

A Practical Example in T-SQL

Let us look at a simple stored procedure designed to fetch customer orders:

-- Fetch orders for a given customer
CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC;
END;

Now here is how the PSP problem plays out:

-- First run: CustomerID 999 has only 10 rows.
-- SQL Server builds and caches an Index Seek plan.
EXEC dbo.GetOrdersByCustomer @CustomerID = 999;

-- Later run: CustomerID 1 has 2,000,000 rows.
-- SQL Server reuses the same Index Seek plan—resulting in very slow execution times.
EXEC dbo.GetOrdersByCustomer @CustomerID = 1;

The Result

A query that should finish in under 100 milliseconds now takes 45 seconds because it relies on an execution plan unsuited to the volume of data.

How to Identify PSP Problems in the Plan Cache

You can identify potential PSP issues by inspecting the plan cache. If there is a wide gap between the minimum and maximum execution times for the same query, it is a strong indicator of a parameter-sensitive plan.

Run the following query to check your plan cache:

SELECT
    qs.execution_count,
    qs.min_elapsed_time, -- Fastest run duration
    qs.max_elapsed_time, -- Slowest run duration (a large gap indicates PSP)
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%GetOrdersByCustomer%'
ORDER BY qs.max_elapsed_time DESC;

What to look for

If the min_elapsed_time is 80,000 microseconds (80ms) and the max_elapsed_time is 45,000,000 microseconds (45 seconds) for the exact same query, you are likely dealing with a PSP issue.

Workarounds for Older Versions (SQL Server 2019 & Earlier)

Before SQL Server 2022, database administrators had to rely on manual workarounds, each with its own trade-offs:

Option A: OPTION (RECOMPILE)

This forces SQL Server to discard the cached plan and build a fresh execution plan every single time the query runs.

SELECT * FROM dbo.Orders 
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

• Pros: Safely resolves the PSP issue for highly volatile data distributions.
• Cons: Increases CPU overhead; not suitable for procedures executed hundreds or thousands of times per minute.

Option B: OPTIMIZE FOR UNKNOWN

This tells the query optimizer to ignore the actual parameter value during compilation and use average statistics instead.

SELECT * FROM dbo.Orders 
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

• Pros: Results in highly consistent, safe execution plans.
• Cons: You lose the performance benefits of a highly tailored plan for specific parameters.

Option C: Using Local Variables

Assigning the parameter to a local variable inside the stored procedure stops parameter sniffing, producing a similar effect to OPTIMIZE FOR UNKNOWN.

DECLARE @LocalID INT = @CustomerID;
SELECT * FROM dbo.Orders 
WHERE CustomerID = @LocalID;

• Pros: Stops parameter sniffing.
• Cons: Forces the optimizer to rely on average statistical estimates, which can reduce query precision.

Option D: Plan Guides

You can manually specify which execution plan SQL Server must use for a query. For details on syntax and setup, refer to Microsoft's developer guidance on SQL Server Plan Guides.

• Pros: Provides precise control without modifying stored procedure code.
• Cons: Highly difficult to maintain over time as schema changes and query patterns evolve.

The Proper Fix – SQL Server 2022

SQL Server 2022 introduced a native, built-in solution called PSP Optimization (part of the Intelligent Query Processing family).

If you want to explore more performance and high-availability improvements in this release, check out our guide on SQL Server 2022 Contained Always On Availability Groups.

Instead of caching a single execution plan for all parameter values, SQL Server can now generate and maintain multiple plans tailored to different data volumes. It utilizes Cardinality Estimator (CE) feedback mechanisms to adapt dynamically based on execution history.

Best Part:

You do not need to change any stored procedures or add any hints. SQL Server 2022 handles everything automatically in the background.

How to Turn It On

The best part of this feature is that you do not need to rewrite your T-SQL queries or add hints. SQL Server handles this in the background. To enable it, you must meet three prerequisites:

1. Run SQL Server 2022 or newer.
2. Set the database compatibility level to 160.
3. Ensure Query Store is enabled.

You can enable these settings using the following script:

-- Step 1: Set compatibility level to SQL Server 2022
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 160;

-- Step 2: Turn on Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Step 3: Allow more plans to be cached per query
ALTER DATABASE YourDatabase
SET QUERY_STORE (MAX_PLANS_PER_QUERY = 200);

Monitoring PSP Issues Using Query Store

To identify and monitor queries where execution times vary significantly, you can query the Query Store catalog views:

SELECT TOP 20
    OBJECT_NAME(qsq.object_id) AS proc_name,
    qsrs.count_executions,
    qsrs.min_duration / 1000.0 AS min_ms,
    qsrs.max_duration / 1000.0 AS max_ms,
    qsrs.avg_duration / 1000.0 AS avg_ms,
    (qsrs.max_duration * 1.0) / NULLIF(qsrs.min_duration, 0) AS variance_ratio
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.count_executions > 100
  AND qsrs.max_duration > qsrs.min_duration * 10
ORDER BY variance_ratio DESC;

SSMS UI Tip: If you prefer a visual interface over scripting, open SQL Server Management Studio (SSMS), navigate to your database, expand Query Store, and click on the Regressed Queries dashboard.

Key Takeaways & Final Recommendations

1. PSP is caused by uneven data: Parameter sniffing is highly efficient for uniform databases, but uneven data distribution breaks the "one-size-fits-all" plan model.
2. Monitor variance: Consistently check min_elapsed_time vs max_elapsed_time in your plan cache to catch issues before users experience slow response times.
3. Upgrade where possible: If you are running SQL Server 2019 or older, OPTION (RECOMPILE) remains a reliable quick fix for critical, low-frequency queries. However, planning an upgrade to SQL Server 2022 allows you to leverage automated, multi-plan PSP Optimization without manual code adjustments.
4. If you are on SQL Server 2019 or earlier and cannot upgrade, sometimes the licensing and maintenance overhead makes open-source alternatives appealing. To see how other businesses optimized costs, read our case study on how FinTech Plus Migrated to Aurora PostgreSQL and Saved $120K. If you are considering a similar migration path, explore our Oracle & MSSQL to PostgreSQL Migration Services.

Struggling with slow SQL Server queries or unexplainable database performance drops?

Let our team of certified database experts help.

Learn more about our Mydbops MS SQL Server DBA Services to optimize your workloads, improve stability, and implement seamless database modernizations.