Turning off Parameter Sniffing for a SQL Server Database by Default

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

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles