Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Parameter Sniffing as a Database Scoped Configuration


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

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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.

Solution

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
  • SET PARAMETERIZATION FORCED

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))
AS
      SELECT *     
      FROM Person.Address
      WHERE city=@city
GO

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

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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. 

Sources:

http://www.sqlshack.com/sql-server-2016-parameter-sniffing/

https://www.mssqltips.com/sqlservertip/4286/sql-server-2016-parameter-sniffing-as-a-database-scoped-configuration/

 

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

 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

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?


Learn more about SQL Server tools