Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

When a stored procedure or parameterised query is first compiled, SQL Server builds the plan for the parameter values used on that first call and reuses it afterwards. If those values were unusual (a customer with ten rows instead of ten million, or the other way round), everyone else gets a plan that suits them badly. The classic symptom is a procedure that is fast for some values and slow for others, or that suddenly becomes slow after a restart, failover or statistics update.

Find candidates in Query Store

Query Store records each plan a query has used and how it performed.

USE [YourDB];
SELECT TOP (20) q.query_id,
       OBJECT_NAME(q.object_id) AS object_name,
       COUNT(DISTINCT p.plan_id) AS plan_count,
       CAST(MIN(rs.avg_duration) / 1000 AS decimal(16,1)) AS best_avg_ms,
       CAST(MAX(rs.avg_duration) / 1000 AS decimal(16,1)) AS worst_avg_ms,
       qt.query_sql_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, q.object_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY MAX(rs.avg_duration) - MIN(rs.avg_duration) DESC;

Confirm it

SELECT ps.execution_count,
       ps.min_elapsed_time / 1000 AS min_ms,
       ps.max_elapsed_time / 1000 AS max_ms,
       ps.cached_time, qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.database_id = DB_ID(N'YourDB')
  AND ps.object_id = OBJECT_ID(N'YourDB.dbo.YourProc');

A large gap between min_ms and max_ms is a strong hint. Open the plan, right-click the SELECT operator and choose Properties: Parameter List shows the compiled value next to the runtime value. Compare the estimated and actual row counts on the main operators.

Short-term fix (change: needs approval on production)

Mark just that procedure for recompilation on its next call:

USE [YourDB];
EXEC sp_recompile N'dbo.YourProc';

Never clear the whole plan cache to fix one procedure.

Longer-term fixes

• Force a known good plan with Query Store: EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;
• Add OPTION (RECOMPILE) to the problem statement if it runs infrequently. It costs a compile every time.
• Use OPTION (OPTIMIZE FOR (@CustomerID = 1234)) when one value represents the typical case, or OPTIMIZE FOR UNKNOWN to plan for the average.
• Split the procedure so very different cases (one row versus a whole range) call different procedures with their own plans.
• On SQL Server 2022 at compatibility level 160, Parameter Sensitive Plan optimisation can keep several plans for one query automatically.
• Copying parameters into local variables behaves like OPTIMIZE FOR UNKNOWN. It works, but it is easy to undo by accident, so prefer the explicit hint.

From my SQL Server DBA handbook.