DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query

The DISABLE_PARAMETER_SNIFFING hint makes one query ignore the parameter value it was first compiled for. SQL Server plans for the average value instead. This post compares the hint with its three neighbors on one skewed table. You can see which one fits which workload.

What the DISABLE_PARAMETER_SNIFFING Hint Changes

SQL Server compiles a stored procedure the first time it runs. It reads the parameter value of that call, estimates the rows from it and caches the plan. Every later call reuses that plan, even when its value needs a different one. That is parameter sniffing. It helps when values behave alike and hurts when one value is far larger than the rest.

The hint switches the reading off for one query. The optimizer ignores the value and estimates from the average number of rows per value. SQL Server 2016 SP1 added it as a USE HINT. The database scoped option PARAMETER_SNIFFING = OFF does the same for every query in a database. Trace flag 4136 did it for a whole server before the hint existed, and it needs sysadmin rights. The hint is the narrow tool. It touches one query and nothing else.

The demo table has one huge customer. Customer 1 owns 40,000 of 100,000 orders, and every other customer owns about 20. An index on CustomerID exists, and the query needs Amount too, so a seek costs a key lookup per row.

IF DB_ID(N'SniffHintDemo') IS NULL CREATE DATABASE SniffHintDemo;
GO
USE SniffHintDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID    int NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount     decimal(10,2) NOT NULL
);
INSERT INTO dbo.Orders (OrderID, CustomerID, Amount)
SELECT TOP (100000) n, CASE WHEN n <= 40000 THEN 1 ELSE n % 3000 + 2 END, n % 90 + 10
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);

Four Procedures, One Query

Each procedure runs the same query. The first has no hint. The second has DISABLE_PARAMETER_SNIFFING. The third has OPTIMIZE FOR UNKNOWN, which also ignores the value. The fourth has RECOMPILE, which compiles on every call.

CREATE OR ALTER PROCEDURE dbo.OrdersDefault @CustomerID int
AS SELECT COUNT(*) AS OrderCount, SUM(Amount) AS TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID;
GO
CREATE OR ALTER PROCEDURE dbo.OrdersNoSniff @CustomerID int
AS SELECT COUNT(*) AS OrderCount, SUM(Amount) AS TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID
   OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));
GO
CREATE OR ALTER PROCEDURE dbo.OrdersUnknown @CustomerID int
AS SELECT COUNT(*) AS OrderCount, SUM(Amount) AS TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID
   OPTION (OPTIMIZE FOR UNKNOWN);
GO
CREATE OR ALTER PROCEDURE dbo.OrdersRecompile @CustomerID int
AS SELECT COUNT(*) AS OrderCount, SUM(Amount) AS TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID
   OPTION (RECOMPILE);

The next script calls every procedure for the big customer first and for customer 500 second. After each call it reads last_logical_reads, the logical reads of that call.

SET NOCOUNT ON;
DECLARE @result TABLE (ProcName sysname, BigCustomerReads bigint, SmallCustomerReads bigint);
DECLARE @proc sysname, @sql nvarchar(400), @big bigint, @small bigint;
DECLARE procs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.procedures WHERE name LIKE N'Orders%' ORDER BY name;
OPEN procs;
FETCH NEXT FROM procs INTO @proc;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'EXEC dbo.' + QUOTENAME(@proc) + N' @CustomerID = 1;';
    EXEC (@sql);
    SELECT @big = last_logical_reads FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID() AND object_id = OBJECT_ID(N'dbo.' + @proc);
    SET @sql = N'EXEC dbo.' + QUOTENAME(@proc) + N' @CustomerID = 500;';
    EXEC (@sql);
    SELECT @small = last_logical_reads FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID() AND object_id = OBJECT_ID(N'dbo.' + @proc);
    INSERT @result VALUES (@proc, @big, @small);
    FETCH NEXT FROM procs INTO @proc;
END;
CLOSE procs;
DEALLOCATE procs;
SELECT ProcName, BigCustomerReads, SmallCustomerReads FROM @result ORDER BY ProcName;

The default procedure compiled for customer 1, so it scans the table. That is right for the big customer at 324 reads. It makes customer 500 pay 324 reads for 20 rows. The hint and OPTIMIZE FOR UNKNOWN plan for the average customer and seek. Customer 500 pays 44 reads. Customer 1 now pays 85,090, because the seek does 40,000 key lookups. RECOMPILE is the only variant that serves both, at the price of a compile on every call.

Read the Plans

The plan cache shows why. This query reads the compiled value, the estimated rows and the first scan or seek of each procedure’s plan.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT OBJECT_NAME(st.objectid) AS ProcName,
       qp.query_plan.value('(//ParameterList/ColumnReference/@ParameterCompiledValue)[1]', 'nvarchar(20)') AS CompiledFor,
       qp.query_plan.value('(//RelOp[@PhysicalOp="Index Seek" or @PhysicalOp="Clustered Index Scan"]/@EstimateRows)[1]', 'float') AS EstimatedRows,
       qp.query_plan.value('(//RelOp[@PhysicalOp="Index Seek" or @PhysicalOp="Clustered Index Scan"]/@PhysicalOp)[1]', 'nvarchar(40)') AS Operator
FROM sys.dm_exec_procedure_stats AS ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE ps.database_id = DB_ID() AND OBJECT_NAME(st.objectid) LIKE N'Orders%'
ORDER BY ProcName;

The default plan carries the value it was compiled for and an estimate of 40,000 rows. The hint and OPTIMIZE FOR UNKNOWN carry no value, and both estimate 33.3222 rows. That number is 100,000 rows divided by 3,001 distinct customers. The recompiled plan shows the estimate for its last call, 20 rows.

Hint, Unknown or Recompile

A reader asked how the DISABLE_PARAMETER_SNIFFING hint differs from OPTION (RECOMPILE). The recompile option compiles on every call and sniffs each value. The hint never sniffs, so it needs one compile. The two answer opposite questions.

OPTIMIZE FOR UNKNOWN gives the same plan as the hint here, and both gave the same reads. The difference is reach. The hint covers every parameter of the query. OPTIMIZE FOR UNKNOWN can name one parameter and leave the others sniffed.

You could argue that turning sniffing off is the safe default, because the plan never depends on who called first. It is predictable, and here it is predictably wrong for the biggest customer. Use the hint when the first caller is unrepresentative and a plan for the average value is acceptable. The other fixes have their own posts.

For the database wide switch, read Database Scoped Configuration: Turn Off Parameter Sniffing. For a fresh plan on every call, read OPTION (RECOMPILE) Hint: When a Fresh Plan Pays Off. For the older local variable trick, read Parameter Sniffing Local Variable: What the Trick Fixes.

What to Remember

First find out whether sniffing is the cause. Open the cached plan and read the compiled value. A value that belongs to a rare customer is the sign. Then compare reads for a big value and a small one.

Sniffing, the hint and RECOMPILE trade plan quality against compile cost. Measure a big value and a small value before you choose. Use the DISABLE_PARAMETER_SNIFFING hint for one query. Use the database scoped option only when you have measured every query in that database. Remove the demo when you finish.

USE master;
GO
IF DB_ID(N'SniffHintDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SniffHintDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SniffHintDemo;
END;

Parameter sniffing is not the bug, it is a bet that every caller looks like the first one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.