Parameter Sniffing with Database Scoped Configurations in SQL Server 2016

I have seen some of the basics that I learnt more than two decades don’t seem to change. One of the traditional performance tuning I used to get bumped into was around parameter sniffing working with SQL Server. Having said that, there are various settings in SQL Server which were at the server level till SQL Server 2014. Most of them can be set via sp_configure setting or trace flags at server level. Before we move forward and explain the feature, it is important to learn the basics – more of a primer to those who haven’t encountered this. Let us take a step back and understand parameter sniffing in SQL Server.

What is parameter sniffing?

Parameter sniffing is a phenomenon in SQL Server where SQL optimizer would “sniff” the value of parameter during compile time. SQL Server query optimizer has freedom to choose complete different plan for same query with different parameter values. Due to parameter sniffing, it is observed that cached plan is picked up which might cause slow performance.

Let’s look at example now to understand it. We would create database object.

USE [master]

GO

IFDB_ID('SniffingDB')ISNOTNULL

BEGIN

ALTERDATABASE [SniffingDB] SETSINGLE_USERWITHROLLBACKIMMEDIATE

DROPDATABASE [SniffingDB]

END

GO

CREATEDATABASE SniffingDB

GO

USE SniffingDB

GO

CREATETABLE SkewedDataTable(

id INTidentity

,Gender CHAR(1)

,WideColumn CHAR(7000)DEFAULT'A'

)

GO

SETNOCOUNTON

GO

INSERTINTO SkewedDataTable DEFAULT

VALUES('F')

GO 995

INSERTINTO SkewedDataTable(Gender)

VALUES ('M')

GO 5

CREATEINDEX idx_SkewedDataTable_gender ON SkewedDataTable(Gender)

GO

There is special thing with the data above. We have deliberately populated skewed data in gender column by inserting 995 values with “F” and 5 values with “M”. This would cause different query plan for both the values. We would create stored procedure which would use that column in predicate.

CREATEPROCEDURE CountGender @Gender CHAR(1)

AS

BEGIN

SELECT*

FROM SkewedDataTable

WHERE Gender = @Gender

END

If we run the parameter with “M” and “F”, we would get different plan, provided it’s not already generated. To demonstrate it, we can run below script in the database which uses DBCC

FREEPROCCACHE.

USE SniffingDB

GO

SETSTATISTICSPROFILEON

GO

DBCC FREEPROCCACHE

GO

EXEC CountGender'M'

GO

EXEC CountGender'F'

GO

DBCC FREEPROCCACHE

GO

EXEC CountGender'F'

GO

EXEC CountGender'M'

GO

SETSTATISTICSPROFILEOFF

Once we run above, we would see 4 plans in the output. First two and last two would be same. This is because of plan reuse.

How do we solve it?

Parameter sniffing may not be a problem in all scenarios but if there is a skewed data distribution then we may want to disable the parameter sniffing. Microsoft has provided server level trace flag 4136 which can be used to disable it. This is documented in Microsoft knowledge base article 980653.

What is new in SQL Server 2016?

The problem with the trace flag is that it would affect all the databases on the instance. As we can see below in the database properties window, we do have option to turn ON and OFF for “Parameter Sniffing” option.

Here is the command to turn off the sniffing at database level.

USE [SniffingDB]

GO

ALTERDATABASESCOPEDCONFIGURATIONSETPARAMETER_SNIFFING=OFF;

GO

Once above command is run and if we execute the same procedure again, we can notice that irrespective of the value passed, the plan would be the same for all four executions.

You might wonder, what is the meaning of “Parameter Sniffing For Secondary” is? It is useful in the scenarios where availability group is deployed and we want different setting for optimizer on primary and secondary replica. There are three possible values:

• Primary – Same value on secondary which is set on primary
• ON – Turn it OFF.
• OFF – Turn it ON.

Other than parameter sniffing, there are more setting at database level which we would cover in some other blog.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.