Just a few hours ago, I finished my very last consulting engagement of the year Comprehensive Database Performance Health Check. My client had hired me after reading my recent series on Parameter Sniffing. In this blog post, we will discuss Database Scoped Configuration – Parameter Sniffing.
Before continuing this blog post, I strongly suggest that you read the following blog posts to get an understanding of the background of the concept which I am going to discuss today.
- 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 the question is how we can get rid of the parameter sniffing at the entire server level. The answer is very simple if you are using SQL Server 2017 or 2019. The newer version of SQL Server can give you exactly the same behavior which you can get it by either using a local variable in the SP or using Optimize for Uknown query hint.
Let us see how you can do that with the help of Database Scoped Configuration – Parameter Sniffing.
Database Scoped Configuration – Parameter Sniffing
There are two different ways to disable the traditional parameter sniffing. Let see both the methods.
Method 1: T-SQL
Run the following script for your database and disable parameter sniffing at the database level. Please replace the database name WideWorldImporters with your own database name.
USE [WideWorldImporters] GO ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = Off; GO
Method 2: Via SSMS
Please see the following image which displays the database property. Over here you can disable the parameter sniffing.
Once you disable the parameter sniffing, create the following stored procedure.
-- Let us run the original stored procedure CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT) AS SELECT * FROM WideWorldImporters.Sales.Orders WHERE CustomerID = @CustomerID 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
You can clearly notice from the execution plan that the execution plan does not sniff any particular parameter and executes the query at the average row count for statistics.
Once again, please note that this method removes the parameter sniffing but not necessarily fixes your performance issues. If you for sure want to properly estimate your row count, you can use recompilation. However, there are some negative stories associated with this method as well, I will share them some other time in the future.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Thank Pinal for all four articles about PS (parameter sniffing). Now it is clear why PS happens and what are the options to avoid the performance issue. All provided solutions are trying to make SQL Server to not cache the execution plan.
Now the question comes up that if caching execution plan cause PS issues and we are telling SQL Server to not cache execution plan, why SQL Server has the execution plan caching mechanism? Why Microsoft does not drop this feature?