Database Scoped Configuration: Turn Off Parameter Sniffing

A database scoped configuration lets you turn off parameter sniffing for one database with a single statement. No procedure changes. No hint on any query. SQL Server stops sniffing for every plan in that database.

One Setting for One Database

With parameter sniffing, SQL Server builds a plan from the values of the first call. It reuses that plan for every later call. The local variable trick and the query hints work for one procedure or one query. A scoped setting does it for the whole database. The statement is ALTER DATABASE SCOPED CONFIGURATION, and the option is PARAMETER_SNIFFING. It exists in SQL Server 2016 and later. The default is ON.

In Management Studio, the same switch is on the Options page of the database properties. Look for the group named Database Scoped Configurations. The view sys.database_scoped_configurations shows the value for the current database. It has a second column for a readable secondary replica. The demo builds on the view.

Build the Demo

The demo needs skewed data. The script creates a database named ScopedSniffDemo with an orders table of 100,000 rows. Customer 1 owns 50,000 rows, and a thousand other customers own 50 each. The database runs at compatibility level 150, which keeps the classic sniffing behavior. One procedure counts the orders of a customer. A second procedure calls it and logs the estimated rows and the logical reads. Run the script on a test server.

IF DB_ID(N'ScopedSniffDemo') IS NULL CREATE DATABASE ScopedSniffDemo;
GO
USE ScopedSniffDemo;
GO
ALTER DATABASE ScopedSniffDemo SET COMPATIBILITY_LEVEL = 150;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.CallLog;
GO
CREATE TABLE dbo.Orders (
    OrderID    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Notes      nvarchar(100) NOT NULL
);
INSERT INTO dbo.Orders (CustomerID, Amount, Notes)
SELECT CASE WHEN n % 2 = 0 THEN 1 ELSE 2 + n % 2000 END, n % 97 + 0.5, REPLICATE(N'x', 100)
FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS t;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
CREATE TABLE dbo.CallLog (CallID int IDENTITY(1,1) PRIMARY KEY, CustomerID int, EstimatedRows float, LogicalReads bigint);
GO
CREATE OR ALTER PROCEDURE dbo.OrdersByCustomer @CustomerID int
AS
SELECT COUNT(*) AS OrdersFound, SUM(Amount) AS Total FROM dbo.Orders WHERE CustomerID = @CustomerID;
GO
CREATE OR ALTER PROCEDURE dbo.CallAndLog @CustomerID int
AS
BEGIN
    CREATE TABLE #Out (OrdersFound int, Total decimal(18,2));
    INSERT #Out EXEC dbo.OrdersByCustomer @CustomerID = @CustomerID;
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    INSERT dbo.CallLog (CustomerID, EstimatedRows, LogicalReads)
    SELECT @CustomerID,
           qp.query_plan.value('(//RelOp[@PhysicalOp="Index Seek" or @PhysicalOp="Clustered Index Scan"]/@EstimateRows)[1]', 'float'),
           ps.last_logical_reads
    FROM sys.dm_exec_procedure_stats AS ps
    CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
    WHERE ps.database_id = DB_ID() AND ps.object_id = OBJECT_ID(N'dbo.OrdersByCustomer');
END;

Sniffing On, the Default

First read the setting. Then call the procedure for customer 3, who has 50 orders, and for customer 1, who has 50,000. Customer 3 goes first, so the plan is built for the small value.

SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name = N'PARAMETER_SNIFFING';
TRUNCATE TABLE dbo.CallLog;
EXEC sp_recompile N'dbo.OrdersByCustomer';
EXEC dbo.CallAndLog 3;
EXEC dbo.CallAndLog 1;
SELECT CallID, CustomerID, EstimatedRows, LogicalReads FROM dbo.CallLog ORDER BY CallID;

The plan expects 50 rows, because customer 3 was the first caller. Customer 1 reuses the plan and reads 159,470 pages. That is parameter sniffing at work. Your page reads can differ by a few pages.

Switch It Off

Now run the statement. The script counts the cached procedure plans before and after. A change to a scoped setting clears the plan cache of that database. It then repeats the calls in both orders. At the end it switches sniffing back on, so the database returns to its default. Note the GO after the statement. When the calls share a batch with the ALTER, the plan still sniffs the value. Give the new setting its own batch.

DECLARE @before int = (SELECT COUNT(*) FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID());
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
SELECT @before AS PlansBefore, (SELECT COUNT(*) FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID()) AS PlansAfter;
GO
TRUNCATE TABLE dbo.CallLog;
EXEC dbo.CallAndLog 3;
EXEC dbo.CallAndLog 1;
EXEC sp_recompile N'dbo.OrdersByCustomer';
EXEC dbo.CallAndLog 1;
EXEC dbo.CallAndLog 3;
SELECT CallID, CustomerID, EstimatedRows, LogicalReads FROM dbo.CallLog ORDER BY CallID;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;

A new scoped setting applies from the next batch. Even a statement that sets the same value clears the cache, so run it when the server is quiet. The estimate is 99.9 rows for every call, in both orders. That is the average for one customer, and it does not depend on who calls first. The procedure plan is still cached, because the log reads it from the cache. Only the way the plan is built changed.

What the Switch Does Not Fix

The result matches a local variable inside the procedure. The database scoped configuration reaches further, because it needs no code change and covers every procedure and every query. That is also the danger. Customer 1 still reads 159,470 pages, because the average plan suits neither extreme. A plan that sniffing built well for its callers now gets an average guess instead.

Think of it as a decision to trade the best plan for a predictable one, for the whole database.

The switch suits a database where sniffing causes most slow calls. It suits one that nobody can edit, such as a vendor application. It does not suit a database where sniffing helps most calls.

If one query is the problem, a hint on that query is the narrower tool. The query hint version is in DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query. It does what this setting does, for one statement. For skewed data, a plan per call is better still. The comparison is in Parameter Sniffing Fixes Compared: Which One to Use.

Why Does SQL Server Cache Plans at All?

You could ask why SQL Server caches plans when caching causes sniffing. Compiling a plan costs CPU. A cached plan skips that cost on every later call. For most procedures the first plan serves everyone well. The cache is a large gain, and sniffing is a small price. Notice that this setting does not remove the cache. The plan above is still cached. Only OPTION (RECOMPILE) makes SQL Server compile every call.

What to Remember

A database scoped configuration for PARAMETER_SNIFFING changes every plan in the database at once. The change clears the cached plans, so expect a burst of compiles after it. Read the value in sys.database_scoped_configurations, and write down the old one before you change it.

Test the switch on a copy first. The undo is the same statement with ON. When you finish, run the cleanup script.

USE master;
GO
ALTER DATABASE ScopedSniffDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ScopedSniffDemo;

A database wide switch is not a tuning step, it is a trade you make for every query at once.

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