SQL SERVER – Disable Parameter Sniffing with DISABLE_PARAMETER_SNIFFING Query Hint

The journey of consulting is always a fun one with lots of learning. Recently during the Comprehensive Database Performance Health Check, I had a great conversation about how to disable Parameter Sniffing and DISABLE_PARAMETER_SNIFFING Query Hint. The DBA at my client’s side had read my earlier summary post about parameter sniffing and wanted me to add more post which was not covered in the posts.

Before you continue this blog post, I strongly suggest you read the following posts to understand the context of Parameter Sniffing and why we want to disable it.

• SQL SERVER – Parameter Sniffing Simplest Example
  In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
• SQL SERVER – Parameter Sniffing and Local Variable in SP
  It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
  You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
  This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
  The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
• Performance and Recompiling Query – Summary
  This post summarizes the entire series of parameter sniffing, performance and recompiling query.

Now, let us discuss this query hint DISABLE_PARAMETER_SNIFFING.

Earlier I discussed that if you want to disable the parameter sniffing at the database level you can always disable it with the help of Database Scoped Configuration. However, there are moments one can face where they want to disable parameter sniffing at a single query. In those scenarios, you can use the query hint DISABLE_PARAMETER_SNIFFING.

Let us create the following stored procedure with a query hint and run them.

-- Let us run the original stored procedure
CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
GO

Now enable the execution plan for your SSMS session and run the stored procedure with two different parameters.

-- Sample Stored Procedure
EXEC GetCustomerOrders 1060
EXEC GetCustomerOrders 90
GO

Now compare the execution plan and you will notice that instead of taking any one parameter, the stored procedures will run with the average data received from the statistics.

So essentially, when you use the query hint DISABLE_PARAMETER_SNIFFING for a stored procedure, it will disable parameter sniffing for that query or procedure. Now, do not think parameter sniffing is bad for your system, the matter of fact, it is enabled by the default shows that for most of the queries it is advantages. You should only disable it for certain queries with proper consultation. We discuss this in detail during the Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)