Parameter Sniffing, Parameter Sensitive Plan Optimization, RECOMPILE, and Dynamic Search

Learning outcomes

ServiceHub’s customer lookup is deliberately skewed: customer 1 owns tens of thousands of rows, while most customers own only a few. Parameterized SQL is desirable because it supports plan reuse and safe application binding, but a plan compiled for one cardinality can be poor for another. That normal compile-time behavior is parameter sniffing; the operational problem is harmful parameter sensitivity when one reusable plan cannot serve materially different parameter classes.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubQSLab') IS NULLBEGIN  CREATE DATABASE ServiceHubQSLab;END;GOALTER DATABASE ServiceHubQSLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubQSLab SET QUERY_STORE = ON(  OPERATION_MODE = READ_WRITE,  QUERY_CAPTURE_MODE = AUTO,  WAIT_STATS_CAPTURE_MODE = ON,  INTERVAL_LENGTH_MINUTES = 5,  MAX_STORAGE_SIZE_MB = 100,  SIZE_BASED_CLEANUP_MODE = AUTO,  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7));GOUSE ServiceHubQSLab;GOIF SCHEMA_ID(N'lab11') IS NULL EXEC(N'CREATE SCHEMA lab11 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab11.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab11.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab11_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (40000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab11.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,notes)  SELECT CASE WHEN n <= 24000 THEN 1 ELSE 2 + n % 3999 END,         CASE WHEN n <= 27000 THEN 'N01' WHEN n <= 35000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 23 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 27000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(20 + (n % 8000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 41=0 THEN REPLICATE('x',180) END  FROM n;  CREATE INDEX IX_lab11_Customer    ON lab11.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);  CREATE INDEX IX_lab11_RegionStatus    ON lab11.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GOCREATE OR ALTER PROCEDURE lab11.GetOrdersByCustomer  @customer_id intASBEGIN  SET NOCOUNT ON;  SELECT work_order_id,region_code,status,opened_at,amount  FROM lab11.WorkOrderFact  WHERE customer_id=@customer_id  ORDER BY opened_at,work_order_id;END;GO

The lab is intentionally skewed. Customer 1 is common; most other customer IDs are rare. This makes the customer_id = @customer_id predicate a candidate for different access strategies. Whether the optimizer actually creates different physical shapes on your hardware/build is an observation, not a promised outcome.

1. Parameter sniffing is how SQL Server learns a useful compile-time value

When a parameterized statement compiles, SQL Server can use the current parameter value to estimate cardinality and choose a plan. The plan is then eligible for reuse. That is not a bug. If future values have similar selectivity, sniffing is exactly what you want. Harm appears when data is highly skewed and one cached plan is expensive for another important parameter population.

Do not use instance-wide DBCC FREEPROCCACHE to demonstrate this. sp_recompile marks the disposable module for recompilation on its next execution, containing the experiment to the lab procedure.

USE ServiceHubQSLab;GOEXEC sys.sp_recompile N'lab11.GetOrdersByCustomer';EXEC lab11.GetOrdersByCustomer @customer_id=812; -- rare value compiles firstEXEC lab11.GetOrdersByCustomer @customer_id=1;   -- common value reuses that planGOEXEC sys.sp_recompile N'lab11.GetOrdersByCustomer';EXEC lab11.GetOrdersByCustomer @customer_id=1;   -- common value compiles firstEXEC lab11.GetOrdersByCustomer @customer_id=812; -- rare value reuses that planGOSELECT q.query_id,p.plan_id,p.plan_type_desc,p.last_compile_start_time,       p.last_execution_time,qt.query_sql_textFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY p.last_compile_start_time,p.plan_id;GO

Capture actual plans and local STATISTICS IO, TIME for both parameter classes if you want to prove a regression. Do not infer harmful sensitivity merely because the sniffed value differs. The question is whether representative runtime/resource behavior becomes materially unstable.

2. RECOMPILE and OPTIMIZE FOR solve different problems

OPTION (RECOMPILE) asks SQL Server to compile that statement for each execution and discard that statement plan afterward. It can be effective when parameter values differ dramatically and execution cost dominates compile cost, but it removes plan reuse and prevents memory-grant feedback from learning on that recompiled statement. It can also increase compilation CPU under high request rates.

OPTIMIZE FOR (@p = value) deliberately optimizes for one chosen representative value; OPTIMIZE FOR UNKNOWN avoids using the runtime sniffed value and relies on statistical/general estimates. Both are static policies. They can be good if the chosen policy reflects the workload, or bad when distribution changes.

USE ServiceHubQSLab;GODECLARE @customer_id int=1;SELECT work_order_id,region_code,status,opened_at,amountFROM lab11.WorkOrderFactWHERE customer_id=@customer_idOPTION (RECOMPILE);GODECLARE @customer_id int=1;SELECT work_order_id,region_code,status,opened_at,amountFROM lab11.WorkOrderFactWHERE customer_id=@customer_idOPTION (OPTIMIZE FOR UNKNOWN);GODECLARE @customer_id int=812;SELECT work_order_id,region_code,status,opened_at,amountFROM lab11.WorkOrderFactWHERE customer_id=@customer_idOPTION (OPTIMIZE FOR (@customer_id=1));GO

3. Dynamic search should remain parameterized

Optional-search screens often build predicates from supplied filters. Concatenating values into SQL text creates injection risk, unstable text, type mismatches, and plan-cache fragmentation. If dynamic SQL is justified to create different predicate shapes, generate only the trusted SQL structure and bind values with sys.sp_executesql. That gives the optimizer statement shapes it can reason about while retaining explicit parameter metadata.

USE ServiceHubQSLab;GODECLARE @customer_id int=NULL,@region_code char(3)='E03';DECLARE @sql nvarchar(max)=N'SELECT work_order_id,customer_id,region_code,status,opened_at,amountFROM lab11.WorkOrderFactWHERE 1=1';IF @customer_id IS NOT NULL SET @sql += N' AND customer_id=@p_customer';IF @region_code IS NOT NULL SET @sql += N' AND region_code=@p_region';SET @sql += N' ORDER BY opened_at,work_order_id;';EXEC sys.sp_executesql  @sql,  N'@p_customer int,@p_region char(3)',  @p_customer=@customer_id,@p_region=@region_code;GO

Never concatenate untrusted column names, operators, or values without a whitelist/identifier-quoting strategy. Dynamic SQL is not a magic parameter-sensitivity fix; it is a design option when genuinely different predicate sets should compile as different statement shapes.

4. Parameter Sensitive Plan optimization: one statement, multiple variants

Parameter Sensitive Plan (PSP) optimization was introduced in SQL Server 2022 and requires database compatibility level 160 or higher. For eligible equality predicates, SQL Server can create a dispatcher plan that evaluates runtime cardinality ranges and routes execution to separately compiled query variants. SQL Server 2025 with compatibility 170 expands PSP support, including more DML scenarios, tempdb support and improvements around multiple eligible predicates.

PSP is adaptive plan reuse—not “compile every execution.” A query might remain a normal compiled plan if it is not eligible or if the optimizer does not detect useful skew. Query Store exposes dispatcher/variant relationships explicitly.

USE ServiceHubQSLab;GOSELECT compatibility_levelFROM sys.databases WHERE database_id=DB_ID();SELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name IN ('PARAMETER_SENSITIVE_PLAN_OPTIMIZATION','PARAMETER_SNIFFING');GOEXEC lab11.GetOrdersByCustomer @customer_id=1;EXEC lab11.GetOrdersByCustomer @customer_id=812;EXEC lab11.GetOrdersByCustomer @customer_id=2401;GOSELECT p.plan_id,p.query_id,p.plan_type_desc,p.is_forced_plan,       qv.parent_query_id,qv.dispatcher_plan_id,qv.query_variant_query_idFROM sys.query_store_plan AS pLEFT JOIN sys.query_store_query_variant AS qv  ON qv.query_variant_query_id=p.query_idWHERE p.query_id IN(  SELECT q.query_id  FROM sys.query_store_query AS q  JOIN sys.query_store_query_text AS qt ON qt.query_text_id=q.query_text_id  WHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%')OR qv.parent_query_id IN(  SELECT q.query_id  FROM sys.query_store_query AS q  JOIN sys.query_store_query_text AS qt ON qt.query_text_id=q.query_text_id  WHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%')ORDER BY COALESCE(qv.parent_query_id,p.query_id),p.plan_type_desc,p.plan_id;GO

If you see only Compiled Plan, do not manufacture a conclusion. Verify compatibility/configuration, statistics/skew, predicate eligibility, and the actual Showplan/XEvent evidence. PSP is an optimizer choice under documented eligibility constraints, not a guarantee for every parameterized procedure.

5. Production judgment

The hierarchy for ServiceHub is: first prove sensitivity across representative parameter classes; then improve statistics/query/index design if they are wrong; then let PSP/modern compatibility solve the problem when eligible; then choose a scoped fallback such as statement recompile, carefully selected optimization policy, parameterized dynamic SQL, or a governed Query Store hint. Do not trade a measurable regression for invisible compilation storms or unsafe dynamic SQL.

Authoritative references

• Parameter Sensitive Plan optimization — dispatcher/query variants, eligibility and SQL Server 2025 improvements
• sys.query_store_query_variant — parent, dispatcher and variant relationships
• Query processing architecture guide — parameterization, compilation and plan reuse
• Query hints — RECOMPILE, OPTIMIZE FOR and USE HINT semantics
• sp_executesql — parameterized dynamic SQL
• SQL Server 2025 build versions — servicing baseline