SQL Server 2016 Parameter Sniffing as a Database Scoped Configuration

By:   |   Comments (3)   |   Related: More > SQL Server 2016


In the previous tip we outlined the new functionality related to SQL Server 2016 Database Scoped Configuration Options.  In this tip we are going to demonstrate how parameter sniffing can be controlled at the database level now instead of at the instance level or by modifying stored procedures.


SQL Server uses parameter sniffing to create the execution plan for the parameters specified in the query or as variables. To learn more about parameter sniffing see these tips: Analyzing SQL Server Performance Impact of Parameter Sniffing and Different Approaches to Correct SQL Server Parameter Sniffing.

Normally SQL Server parameter sniffing is helpful for query optimization, but the benefits are workload dependent.  If we have workloads with too many ad-hoc queries this might cause issues for parameter sniffing. With SQL Server 2014 we have different options including:

  • Using query hints like OPTIMIZE FOR, RECOMPILE, OPTIMIZE FOR UNKNOWN, etc.
  • Optimize for ad hoc workload

We also have the option to disable parameter sniffing by enabling Trace Flag 4136 at the instance level, but this will impact all databases.  Generally each SQL Server instance supports numerous instances and applications, so we don't want each database and application to get impacted.  With SQL Server 2016 we can turn off parameter sniffing at the database level by using Database Scoped Configuration Options.

Before we make any changes, we can see the default behavior of the SQL Server using the sys.database_scoped_configurations DMV.

Query the SQL Server DMV sys.database_scoped_configurations

We can see that by default parameter sniffing is set to ON, so queries will use this by feature by default.

To validate SQL Server is using parameter sniffing, if we run the below query in the AdventureWorks2016 database and review the actual execution plan, we can see that for the same query with different parameters we are getting different execution plans.  The first uses a Clustered Index Scan while the second is using a NonClustered Index Scan and a Key Lookup.

Different SQL Server query plans based on the parameters passed

Query statistics including the actual number of rows and estimated number of rows

Now if we create a stored procedure and run the code with different parameters, we get the following query plans that match based on the parameters passed:

CREATE PROCEDURE sp_personaddress (@city nvarchar(20))
      SELECT *     
      FROM Person.Address
      WHERE city=@city

Matching query plans for the stored procedure execution

Since stored procedures are precompiled with the execution plan created on the initial execution, the stored procedure will use the same plan for subsequent executions regardless of the parameters.  However, sometimes we might want to generate the execution plan based on the parameter passed.  To do so, we will turn off the parameter sniffing with SQL Server Management Studio or via T-SQL at the database level as shown below.

Turn off parameter sniffing at the database level with SQL Server Management Studio


Now we will clear the cache and run the stored procedures again.

SQL Server Stored Procedure execution matching both parameters

SQL Server Stored Procedure execution matching both parameters

Now we can see that same execution plan is used with different execution parameters.  Be sure to enabled this option and test properly based on the workload in non-production environments prior to introducing this setting in a production environment.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, January 22, 2017 - 10:06:23 AM - levenyu Back To Top (45475)

I'd like to post a clarification to my previous post.

According to different online sources, setting Parameter Sniffing to OFF, in SQL Server 2016 that is, either via UI in SSMS or via ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; script, does not create a different execution plan for different parameter's values passed, as it would in case of using OPTION (RECOMPLIE) query hint and as someone would expect.

What it does instead is - SQL Server Query Optimizer generates some optimal execution plan of the query, which will fit all parameters, and thus it functions the same way as OPTION(OPTIMIZE FOR UNKNOWN ) query hint does. 





So, I'd like to ask a question.

Why to set PARAMETER_SNIFFING = OFF; at the entire database level?

The same effect/outcome, yet with higher level of granularity, can be achieved at the query level via its OPTION(OPTIMIZE FOR UNKNOWN ) hint when that needed behavior is justified somehow, because there is always some kind of trade-off.

I’d appreciate a clarification to the above question.

Thank you!


Saturday, January 21, 2017 - 11:33:17 AM - Levenyu Back To Top (45461)

 I agree with the comment posted by Marcel. With parameters sniffing OFF and after eviction of the previously cached execution plan, new plans should be different depending on passed parameter's values. That is the point of setting it OFF.


Thursday, December 29, 2016 - 6:51:55 AM - Marcel Inowe Back To Top (45062)

Hi Rajendra,

You wrote "However, sometimes we might want to generate the execution plan based on the parameter passed. To do so, we will turn off the parameter sniffing...." but the execution plan was the same when the parameter sniffing was ON.

I thought that with parameter sniffing OFF the execution plan would be the same as ad-hoc query, that is, different execution plan per query execution with different parameter.

Is not this?