SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.
New calls for the same stored procedure will be faster as it will not be compiled again; the SQL Server Query Optimizer will use the same execution plan for each execution with the same parameters values, which is optimized for these values, and any call for this stored procedure in the same way, which may or may not be optimal for the new values.
Parameter Sniffing is useful for reusing the same execution plan for the same query with the same parameters values, as the initial compiling process can be expensive. But it may lead to using a less efficient execution plan to execute all queries with the same shape, which will cause performance degradation. The optimal plan for a specific set of parameters may not be suitable for other parameters, you may pass a parameter value that returns one record, where another value could return hundreds, thousands or millions of records, and the plan that handles one record may not serve the value that returns thousands of records with the same performance.
In most cases, the data distribution within the database is homogeneous, which is where parameter sniffing is helpful, but in some cases, with non-homogeneous distribution, parameter sniffing can be a problem. So that, to decide if you will or will not use parameter sniffing within your environment, you need to investigate your workload. If most of your workload consists of stored procedures calls, you can take benefits from the parameter sniffing, but if you have a lot of ad-hoc queries running within your workload, then it is better not to use the parameter sniffing.
Parameter sniffing is enabled by default in SQL Server, you can disable it by turning on the Trace Flag 4136 at the instance level, which will affects all databases hosted in the same instance that may not be acceptable for instance with many databases serving different workload types. SQL Server 2016 introduces the use of Database Scoped Configuration which allows us to configure the parameter sniffing at the database level, rather than configure it only at the instance level. We will go through many ways to configure the parameter sniffing within the demo in this article.
Let’s start our demo to see how parameter sniffing works and how to overcome the issue when it negatively affects stored procedure performance. First we will create two new tables; the Employees table with the employees’ information and the Employee_Departement table that contains the list of company departments, where the Employee table has the foreign key EmpDepID referenced to the DepID from the Employee_Departement table as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
USE SQLShackDemo GO CREATE TABLE Employees ( EmpID INT NOT NULL , EmpName VARCHAR(50) NOT NULL , EmpAddress VARCHAR(50) NOT NULL , EmpDEPID int NOT NULL , EmpBirthDay DATETIME , PRIMARY KEY CLUSTERED ( EmpID ) ) GO CREATE TABLE Employee_Department ( DepID INT NOT NULL , DepName VARCHAR(50) NOT NULL , PRIMARY KEY CLUSTERED ( DepID ) ) GO CREATE INDEX IX_Employees_EmpDEPID ON Employees(EmpDEPID) GO USE [SQLShackDemo] GO ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_EmpDep] FOREIGN KEY([EmpDEPID]) REFERENCES [dbo].[Employee_Department] ([DepID]) GO ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_EmpDep] GO |
Once the tables are created successfully, we will fill these two tables with test data using ApexSQL Generate test data generation tool, as follows:
We will create a simple stored procedure that reads from the two tables and take the department name as an input parameter:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROCEDURE EmpPerDEP @DepName VARCHAR(50) AS SELECT * FROM [SQLShackDemo].[dbo].[Employees] EMP JOIN [SQLShackDemo].[dbo].[Employee_Department] ED ON EMP.EmpDEPID=ED.DepID WHERE ED.DepName =@DepName GO |
If we use the created stored procedure to search for the Sales department:
|
1 2 3 4 5 6 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'Sales' GO |
The optimal execution plan for this parameter value will be like:
Again, if we clear the plan cache and use the stored procedure to search for the HR department:
|
1 2 3 4 5 6 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'HR' GO |
The optimal execution plan for that parameter value will be like:
As mentioned previously, the parameter sniffing is enabled by default, and we can makes sure that it is enabled on the SQLShackDemo database by querying the Parameter Sniffing Database Scoped Configuration option:
|
1 2 3 4 5 |
USE SQLShackDemo GO SELECT name, value FROM sys.database_scoped_configurations where name= 'PARAMETER_SNIFFING' |
Which will show us that this option is enabled:
If we clear the plan cache and run the two stored procedure calls together, first search for the HR then search for the Sales departments:
|
1 2 3 4 5 6 7 8 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'HR' GO exec EmpPerDEP 'Sales' GO |
Parameter sniffing will create an optimal execution plan for the first HR search and use it in all ways in the second call for the Sales department as in the below execution plans comparison:
Again, if we clear the plan cache and run the two stored procedure calls together, opposite to the previous order, where we will search for the Sales department first then search for the HR departments:
|
1 2 3 4 5 6 7 8 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'Sales' GO exec EmpPerDEP 'HR' GO |
Parameter sniffing will create an optimal execution plan for the first Sales search and force its usage in the second call for the HR department as in the below execution plans comparison:
The previous results show us how parameter sniffing will use the first created execution plan in all stored procedure calls with same or different parameters’ values. This plan may or may not be optimal for all values as we mentioned in this article, which may cause performance issues.
To overcome parameter sniffing performance issue that could occur due to forcing the same plan usage for all stored procedures parameters values we can use the WITH RECOMPLIE option in the stored procedure definition, which will force the stored procedure compilation at each execution, creating a new execution plan for each parameter value. The previous stored procedure can be modified to recompile at each run as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER PROCEDURE EmpPerDEP @DepName VARCHAR(50) WITH RECOMPILE AS SELECT * FROM [SQLShackDemo].[dbo].[Employees] EMP JOIN [SQLShackDemo].[dbo].[Employee_Department] ED ON EMP.EmpDEPID=ED.DepID WHERE ED.DepName =@DepName GO |
If we clear the plan cache and run the two stored procedure previous calls:
|
1 2 3 4 5 6 7 8 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'Sales' GO exec EmpPerDEP 'HR' GO |
A separate execution plan will be created for each call which is suitable for each parameter value as below:
Using WITH RECOMPILE statement is the simplest solution for parameter sniffing performance issues, but you need to take into consideration that trecompilation process is expensive, as it will increase the CPU consumption if this stored procedure is called very frequently.
If the stored procedure contains more than one query, and you know that the parameter sniffing will cause performance issue only in one query, you can use the OPTION (RECOMPILE) query hint to recompile this part of the stored procedure only. The previous stored procedure can be modified to recompile only a specific query as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER PROCEDURE EmpPerDEP @DepName VARCHAR(50) AS SELECT * FROM [SQLShackDemo].[dbo].[Employees] EMP JOIN [SQLShackDemo].[dbo].[Employee_Department] ED ON EMP.EmpDEPID=ED.DepID WHERE ED.DepName =@DepName OPTION(RECOMPILE) GO |
As our stored procedure has only one query in it, the same previous result will be found in the following execution plan after executing the same two queries:
Another method that can be used to resolve the parameter sniffing performance issue is using the OPTION (OPTIMIZE FOR (@VARIABLE=VALUE)) query hint, that will generate an optimized plan that is generated when using a specific parameter value. In other words, if you know that using a specific value for that parameter will generate the best plan, you can specify that value in the query hint as a reference for the stored procedure optimization. If you are not sure which value will generate the best performance, you can use the OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)) query hint which will help in optimizing a plan in between, that may be suitable for all parameters. For me, you need to be aware when using it as it may generate a plan that may harm system performance. The previous stored procedure can be modified as below to use the OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN)) query hint as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER PROCEDURE EmpPerDEP @DepName VARCHAR(50) AS SELECT * FROM [SQLShackDemo].[dbo].[Employees] EMP JOIN [SQLShackDemo].[dbo].[Employee_Department] ED ON EMP.EmpDEPID=ED.DepID WHERE ED.DepName =@DepName OPTION(OPTIMIZE FOR UNKNOWN ) GO |
Calling the stored procedure twice again:
|
1 2 3 4 5 6 7 8 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'Sales' GO exec EmpPerDEP 'HR' GO |
The in between execution plan that could be optimal for both executions will be like:
In SQL Server 2016, Database Scoped Configurations are introduced, which allows us to configure these configurations at the database level. One of these options is the Parameter_Sniffing which we can easily disable it at the database level, enabling us to configure each database with its own workload type with the suitable configurations.
The Parameter_Sniffing option can be disabled using the below ALTER DATABASE statement:
|
1 2 3 |
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; |
Or simply from the Options tab of the Database Properties window, by changing the Parameter_Sniffing value to OFF as follows:
If we execute the same previous stored procedure calls again after disabling the parameter sniffing:
|
1 2 3 4 5 6 7 8 |
DBCC FREEPROCCACHE() GO exec EmpPerDEP 'HR' GO exec EmpPerDEP 'Sales' GO |
The SQL Server Query Optimizer will generate the optimal query that will fit all parameters, which is like the below execution plans in our case:
The same result will be found if you reverse the two queries.
Conclusion
Parameter Sniffing is the process of looking to the first passed parameters values when compiling the stored procedure in order to create an optimal execution plan that fits these parameters values and use it for all values. But the generated execution plan may not be optimal for all the parameter’s values, leading to performance problems in some cases. You should understand your workload well in order to decide if you will keep the parameter sniffing enabled or disable it using one of the query hints mentioned in this article, or from the SQL Server 2016 Database Scoped Configurations. In some cases you need also to rewrite your query to have the best execution plan and the optimum performance. It is better to simulate you workload in a test environment to study its performance and decide how to tune it and what is the suitable way to do that.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021