Parameter Sniffing with Database Scoped Configurations in SQL Server 2016

By | In Remote DBA | June 09th, 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.

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.

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

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.

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.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).