dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted December 9, 2019

Turning off Parameter Sniffing for a SQL Server Database by Default

By Greg Larsen

Have you been experiencing vastly different execution times for the same query over time? If so, you might be experiencing what is known as “Parameter Sniffing”. Parameter sniffing is used by the query optimizer when compiling a query. The optimizer sniffs, which is basically looking at the query text of the query you want to execute and at the parameters being passed. If the query text and the parameters are similar to an existing compiled query plan, then SQL Server might consider using an already existing plan to run your query. It does this to speed up the query, instead of recompiling a similar query to a query plan already in cache. Compiling queries takes up valuable resources.

The problem with parameter sniffing is that all similar queries might need different execution plans to run efficiently. For instance, suppose you are searching for a set of records that have a specific date range. If the day range covers just a few days, then SQL Server would only need to return a small set of records. But if the data range covers a couple of years, then SQL Server would return a lot more records than the query with a data range of a month. An optimized execution plan to return a month’s worth of records most likely will be very inefficient at returning a couple years’ worth of records. But because parameter sniffing is turned on by default both these queries might use the same plan.

As mentioned, parameter sniff is on by default. But there are situations where you might not want to have parameter sniffing turned on for all databases. Therefore, in SQL Server 2016, Microsoft decided to provide a new database scoped configuration that allows you to turn off parameter sniffing for a specific database. To determine the database scoped configuration for a specific database run the following command within in the context of the database in question:

SELECT * FROM sys.database_scoped_configurations;

When you run this command, you might see something like this:

Database Sniffing

Here you can see my current database has the PARAMETER_SNIFFING value of “1”. This means parameter sniffing is turned on.

If I wanted to turn off parameter sniffing for this database all I would need to do is run the following command:

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

If I check the database scope configuration after I run this command, you can see that the PARAMETER_SNIFFING parameter value is now zero (“0”):

Database Sniffing Configuration

When parameter sniffing is turned off, SQL Server will build the query plan based on the statistics available for each query. This might be beneficial for your workflow, but then again it might not. Remember to benchmark before and after if you plan to turn off parameter sniffing in order to determine which setting provides your environment with the best performance.

# # #

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM