SQL Server tries to optimize the execution of your stored procedures by creating compiled execution plans. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”. Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with parameter values that have different cardinality. In this article, I will explain what parameter sniffing is, and then explore different ways of dealing with the performance problems that are occasionally caused to parameter sniffing.
What is Parameter Sniffing?
I am sure that the word “sniffing” provides everyone with a slightly different mental image of what it means; some good and some bad. Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed. By “first time”, I really mean whenever SQL Server is forced to compile or recompile a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.
Not all execution plans are created equal. Execution plans are optimized based what they need to do. The SQL Server engine looks at a query and determines the optimal strategy for execution. It looks at what the query is doing, uses the parameter values to look at the statistics, does some calculations and eventually decides on what steps are required to resolve the query. This is a simplified explanation of how an execution plan is created. The important point for us is that those parameters passed are used to determine how SQL Server will process the query. An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation.
To further, understand parameter sniffing, let me show you an example that demonstrates how parameter-sniffing causes a stored procedure to use more resources based on the parameters passed.
An Example That Uses Excessive I/O Due to Parameter Sniffing
To demonstrate parameter sniffing and the performance differences when different parameters are used, let me show you an example. My example will use two different sets of parameters to call my stored procedure. I will show you how the second execution call will consume more I/O, then the originally passed parameters. The difference will depend on the set of parameters used to compile the stored procedure
Prior to showing you my stored procedure, let me first provide you the code I used to create and populate my table from which my stored procedure selected data:
|
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 |
SET NOCOUNT ON; DROP TABLE BillingInfo; CREATE TABLE BillingInfo( ID INT IDENTITY, BillingDate DATETIME, BillingAmt MONEY, BillingDesc varchar(500)); DECLARE @I INT; DECLARE @BD INT; SET @I = 0; WHILE @I < 1000000 BEGIN SET @I = @I + 1; SET @BD=CAST(RAND()*10000 AS INT)%3650; INSERT BillingInfo (BillingDate, BillingAmt) VALUES (DATEADD(DD,@BD, CAST('1999/01/01' AS DATETIME)), RAND()*5000); END ALTER TABLE BillingInfo ADD CONSTRAINT [PK_BillingInfo_ID] PRIMARY KEY CLUSTERED (ID); CREATE NONCLUSTERED INDEX IX_BillingDate ON dbo.BillingInfo(BillingDate); |
In the above code, you can see that I created a table named “BillingInfo“. I then populated that table with a million different records randomly setting the BillingDate‘s and BillingAmt‘s columns . My table has a primary clustered index key on the ID column, and a non-clustered index on BillingDate. There is about 10 years worth of data in this table, starting with a BillingDate of “1999/01/01”.
To show you how parameter sniffing effects stored procedure executions I will be using this stored procedure:
|
1 2 3 4 5 6 7 |
CREATE PROC [dbo].[DisplayBillingInfo] @BeginDate DATETIME, @EndDate DATETIME AS SELECT BillingDate, BillingAmt FROM BillingInfo WHERE BillingDate between @BeginDate AND @EndDate; |
I will execute this stored procedure twice. Each test will call this stored procedure using a different set of parameter values.
My first test will run the following statements:
|
1 2 3 4 5 6 7 8 9 |
SET STATISTICS IO ON; DBCC FREEPROCCACHE; EXEC dbo.DisplayBillingInfo @BeginDate = '1999-01-01', @EndDate = '1999-12-31'; EXEC dbo.DisplayBillingInfo @BeginDate = '2005-01-01', @EndDate = '2005-01-03'; |
I turned on statistics so I can show the I/O generated by each execution. I also ran “DBCC FREEPROCCACHE;” statement so I could make sure my stored procedure was not already in the procedure cache. This allowed the first EXEC statement to compile my stored procedure.
By looking at my two different stored procedure calls you can see the first execution has a begin date and an end date that represents a years’ worth of billing information. Whereas the second call, is only returning three days worth of data. As already state the compiled execution plan will be based on the first EXEC statement above. When I run these statements here is the execution plan used for both of these stored procedure executions:
As you can see a “Clustered Index Scan” operation was performed. Below is the I/O information that was displayed when I ran the script above. The first set of statistics is associated with the first EXEC statement and the second set of statistics is for the second EXEC statements:
|
1 2 3 4 |
Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Now let me run the second test. Here is the code for the second test:
|
1 2 3 4 5 6 7 8 9 |
SET STATISTICS IO ON; DBCC FREEPROCCACHE; EXEC dbo.DisplayBillingInfo @BeginDate = '2005-01-01', @EndDate = '2005-01-03'; EXEC dbo.DisplayBillingInfo @BeginDate = '1999-01-01', @EndDate = '1999-12-31'; |
Here I have swapped the order of the two different EXEC statements. This way the first EXEC statement now calls the stored procedure using the smaller date range as parameters. This short date range will be the one that are sniffed in order to create the compiled execution plan. When I run this test, here is the execution plan that each stored procedure execution will use:
Here you can see that this time an Index Seek operation was used to resolve the query. Below are the statistics for the second test:
|
1 2 3 |
Table 'BillingInfo'. Scan count 1, logical reads 2965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BillingInfo'. Scan count 1, logical reads 337040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
Note that this time, using an Index Seek operation, my first query performed less logical reads to resolve the stored procedure execution using the small date range, then an index scan operation took in the first test. However, for the one year date range the Index Seek operation was much more I/O intensive compared to the first test, and performed almost 100 times more I/O to resolve the query.
Using two different tests created different compiled execution plan depending on the parameters passed when the stored procedure was compiled. By looking at these two different tests, you should have a better understanding of parameter sniffing and the effects it can have on your store procedure performance. Let me next explore some options you can take to resolve the issue caused by parameter sniffing.
How to Deal With Parameter Sniffing
There are a number of ways to deal with the parameter sniffing issue. Keep in mind all options may not be acceptable in every situation. Let me walk you through each option.
Option 1: With Recompile
The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan. To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed. This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure, like so:
|
1 2 3 4 5 6 7 8 9 10 |
DROP PROC [dbo].[DisplayBillingInfo] GO CREATE PROC [dbo].[DisplayBillingInfo] @BeginDate DATETIME, @EndDate DATETIME WITH RECOMPILE AS SELECT BillingDate, BillingAmt FROM BillingInfo WHERE BillingDate between @BeginDate AND @EndDate; |
Once you have dropped and re-created the DisplayBillingInfo stored procedure, then you can test out what happens with the recompile option. This can be done by running the following code, while including the actual execution plan information:
|
1 2 3 4 5 6 7 8 9 |
DBCC FREEPROCCACHE; EXEC dbo.DisplayBillingInfo @BeginDate = '2005-01-01', @EndDate = '2005-01-03'; EXEC dbo.DisplayBillingInfo @BeginDate = '1999-01-01', @EndDate = '1999-12-31'; |
When you run this code, you will find that the first execution performs an index seek operation and the second execution performs an index scan operation.
The drawback of this option is the store procedure is recompiled with every execution. This means, you are incurring additional system resources to compile this procedure with each execution. Depending on the performance gains you get with different sets of parameter values, will determine if there is value in using this option to overcome the parameter sniff issue.
Option 2: Disabling Parameter Sniffing
Another method of resolving the parameter sniffing issue is to disable parameter sniffing altogether. This is not done with a switch or database option, but can be done from within the script of your stored procedure code. Here is an example of how I created my stored procedure so parameter sniffing is disabled:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DROP PROC [dbo].[DisplayBillingInfo] GO CREATE PROC [dbo].[DisplayBillingInfo] @BeginDate DATETIME, @EndDate DATETIME WITH RECOMPILE AS DECLARE @StartDate DATETIME; DECLARE @StopDate DATETIME; SET @StartDate = @BeginDate; SET @StopDate = @EndDate; SELECT BillingDate, BillingAmt FROM BillingInfo WHERE BillingDate between @StartDate AND @StopDate; |
To disable parameter sniffing, all I did was to change the way the parameter values were used within the stored procedure. By creating two different local variables (@StartDate and @EndDate) inside my procedure, setting those variables to the passed parameters, and then using the local variables in the BETWEEN condition, I was able to disable parameter sniffing. Parameter sniffing is disabled because the optimizer is not able to identify the parameters’ values in the actual SELECT statement. Because SQL Server cannot tell what parameter values where used to call the stored procedure, the optimizer creates a generic plan based on the statistics.
When I execute my stored procedure using the code above, using either a narrow range of dates or a years’ worth of dates, the compiled execution plan always does an “index scan” operation. I can tell parameter sniff is turned off because I know that the short range of dates would normally have created an index seek operation.
Option 3: Creating Multiple Stored Procedures
As I said before, there is no ideal solution to resolving the problems with parameter sniffing. This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values.
In my example, I have two different sets of parameters. One set of parameters has a short range of dates, where the dates are between 2005-01-01 and 2005-01-03. Whereas the second set of dates have a large range between 1999-01-01 and 1999-12-31. We know that the optimal plan for the first range of dates was to perform an ‘index seek’ operation, and the second range is more optimal if a index scan operation is performed. In order to optimize the performance for these two different date ranges we will need to create two different stored procedures; one for each. We’ll then follow by having a third stored procedure that determine which stored procedure to call, basing the decision on the number of days between the @BeginDate and @EndDate parameter values. Here are my three stored procedures:
|
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 |
CREATE PROC [dbo].[DisplayBillingInfoNarrow] @BeginDate DATETIME, @EndDate DATETIME AS SELECT BillingDate, BillingAmt FROM BillingInfo WHERE BillingDate between @BeginDate AND @EndDate; GO CREATE PROC [dbo].[DisplayBillingInfoWide] @BeginDate DATETIME, @EndDate DATETIME AS SELECT BillingDate, BillingAmt FROM BillingInfo WHERE BillingDate between @BeginDate AND @EndDate; GO DROP PROCEDURE [dbo].[DisplayBillingInfo]; GO CREATE PROC [dbo].[DisplayBillingInfo] @BeginDate DATETIME, @EndDate DATETIME AS IF DATEDIFF(DD,@BeginDate, @EndDate) < 4 EXECUTE DisplayBillingInfoNarrow @BeginDate, @EndDate ELSE EXECUTE DisplayBillingInfoWide @BeginDate, @EndDate GO |
The first procedure I created was DisplayBillingInfoNarrow and the second was DisplayBillingInfoWide. They are identical, except for the name. The third stored procedure DisplayBillingInfo has the same name as my original stored procedure, but logic in the stored procedure is different. The code now determines the number of days between the @BeginDate and @EndDate and if it is less than four it called the narrow stored procedure DisplayBillingInfoNarrow, otherwise it calls the wide stored procedure DisplayBillingInfoWide.
To test out which index operation is performed now, I can run my normal test code:
|
1 2 3 4 5 6 7 8 9 |
DBCC FREEPROCCACHE; EXEC dbo.DisplayBillingInfo @BeginDate = '2005-01-01', @EndDate = '2005-01-03'; EXEC dbo.DisplayBillingInfo @BeginDate = '1999-01-01', @EndDate = '1999-12-31'; |
When I run this test and display the actual execution plan information I can see that the first EXEC statement does an index seek operation, and the second call does an index scan operation. Additionally, if I try different date ranges, with a date range of less than 4 days an index seek operation will be always be performed and any range that is greater than or equal to 4 days will perform an Index Scan operation.
Using multiple stored procedures allowed me to point the execution call to the appropriate stored procedure that was compiled the first time with either a narrow range, or a wide range. Doing this allowed me to ensure that each time my stored procedure was run, regardless of the parameter values, it would call the stored procedure with the efficient plan for that execution call. Of course using this method does require you to maintain multiple stored procedures, which does cause additional overhead.
Overcoming Issues Related to Parameter Sniffing
Cached plans that are created based on one set of parameters may not always run optimally for a different set of parameters, due to parameter sniffing. You should now have a better understanding of how parameter sniffing might make your application use more resources depending on what parameters where use when a stored procedure was executed the first time. If you should find a stored procedure that is performing poorly due to parameter sniffing, now you have options for how you might re-write your code to overcome the issues related to parameter sniffing.
Load comments