Parameter Sniffing with Database Scoped Configurations in SQL Server 2016
Author: Eric Russo | | June 9, 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.
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
If you’re confused about Oracle’s extended support deadlines, you are not alone. Here’s an overview of what’s in store for 11g through 19c.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.