This blog is part of the series The TSQL Classes: Stored Procedure
The link to previous post are given below.
In last class, I talked about parameter sniffing; a plan cache side effect. In this class I’ll talk about ways to resolve parameter sniffing.
The methods to resolve parameter sniffing issues are listed below.
- Using local variables with in procedures.
- Using with recompile option when creating a procedure.
- Using optimize for query hint.
Let’s take them one by one.
1. Using local variables with in procedures.
DROP TABLE fruit GO CREATE TABLE fruit (Sno int identity,frtname varchar(20),frttype int) GO -- Insert dummy data INSERT INTO fruit Values('Apple',rand()*1000) GO 10000 INSERT INTO fruit Values('Mango',rand()*1000) GO 10 CREATE unique clustered index ix_sno on fruit(sno) GO CREATE INDEX ix_frtname on fruit(frtname) include(frttype) GO
The above script creates and fills a table fruit with values.
CREATE Proc usp_fetchfruits @frtname varchar(20) AS BEGIN SELECT * FROM fruit WHERE frtname=@frtname END GO -- procedure with local variables CREATE Proc usp_fetchfruits_localvar @frtname varchar(20) AS BEGIN DECLARE @fruitname varchar(20) SET @fruitname=@frtname SELECT * FROM fruit WHERE frtname=@fruitname END GO
The above query creates two procedures usp_fetchfruits and usp_fetchfruits_localvar. The procedure usp_fetchfruits_localvar uses local variable in where clause. Let’s now compare the execution plan of the above procedures.
The above snapshot compares the execution plan of the two procedures. The difference in optimizers estimated and actual number of rows in the 2nd plan is because the optimizer doesn’t knows the actual parameter values passed to the where clause, thus the optimizer creates a generic plan based on available statistics. The table fruit has a total of 10010 rows and the optimizers estimated number of rows is 5005 which is exactly the half of the total count.
This method does resolves performance issues but it might not be that effective if actual number of rows are way greater than the average estimated rows.
2. Using with recompile option when creating a procedure.
The parameter sniffing problem occurs because the cached plan from the first execution is used in future procedure executions. If the procedure is being compiled and plan is prepared again for each and every execution than parameter sniffing will be resolved. This is what With Recompile does when used in procedure definition. It recompiles the procedure to create a new plan every time procedure is executed.
CREATE Proc usp_fetchfruits_withrecompile @frtname varchar(20) WITH RECOMPILE AS BEGIN SELECT * FROM fruit WHERE frtname=@frtname END
In above snapshot, in both the execution plans the optimizer’s estimated and actual numbers of records are equal because the plan has been recompiled for each of the parameter values instead of cached plan reuse for the second execution.
The problem with this method is that resources are being used to recompile procedure every time. The plan is even recompiled for parameters values which might have benefited from the cached plan.
3. OPTIMIZE FOR query hint.
Using OPTIMIZE FOR query hint we can have SQL Server optimize a query based on a specific parameter value.
CREATE Proc usp_fetchfruits_optimizefor @frtname varchar(20) AS BEGIN SELECT * FROM fruit WHERE frtname=@frtname OPTION (OPTIMIZE FOR (@frtname = 'Mango')) END
In the above procedure, no matter what parameter value is supplied the select query will be optimized for the value “Mango”.
The above snapshot compares the execution plan for parameter values “Apple” and “Mango”. In both the plans the estimated values remain 10000 which is the total count for parameter “Apple”.
The SQL Server 2008 extends the OPTIMIZE FOR query hint so as to optimize a query for an unknown value. This is somewhat similar to the method using local variable.
CREATE Proc usp_fetchfruits_optimizeforunknown @frtname varchar(20) AS BEGIN SELECT * FROM fruit WHERE frtname=@frtname OPTION (OPTIMIZE FOR UNKNOWN) END
The above procedure uses OPTIMIZE FOR UNKNOWN query hint to get rid of parameter sniffing issue.
The above snapshot compares the execution for parameter values Apple and Mango. The optimizer estimates an average number of rows returned as it did when using local variables.
Another way is to create multiple stored procedures and call them up based on the parameter values. For example, In this case we can create 2 different procedures one for parameter value Mango and one for parameter value Apple and call them up based on the parameter value supplied.
In this class, I talked about ways to improve performance caused because for parameter sniffing issue.