This blog is part of the series The TSQL Classes: Stored Procedure.
In the last class I talked about plan caching and how it speeds up the stored procedure execution time. In this class I’ll look talk about parameter sniffing problem; which can be referred to be a side effect of plan caching.
BOL defines parameter sniffing as
“When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.” (http://msdn.microsoft.com/en-us/library/ms190439(SQL.100).aspx)”
Let’s see how a parameter value is being sniffed into the cached execution plan of a procedure.
-- Create A demo table fruite
CREATE TABLE fruite (Sno int identity,frtname varchar(20),frttype int)
-- Insert dummy data
INSERT INTO fruit Values('Apple',rand()*1000)
INSERT INTO fruit Values('Mango',rand()*1000)
CREATE unique clustered index ix_sno on fruit(sno)
CREATE INDEX ix_frtname on fruites(frtname)
-- Create a procedute to fetch fruit detail.
CREATE Proc usp_fetchfruits
SELECT * FROM fruit WHERE frtname=@frtname
EXECUTE usp_fetchfruit @frtname='Apple'
The above query prepares a demo table fruit and inserts 10000 rows for Apple and 10 rows for Mango. The script also creates a procedure to return fruit details. Let’s examine the cached execution plan of procedure when executed with a value of ‘Apple’. To see how to get cached execution plan of specific object please check my earlier blog http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-class-2-working-with-stored-procedures-part-1.
In the above snapshot, the parameter values can be seen under the ParameterList element in the xml representation of the cached execution plan; also total number of rows returned is 10000 which are equal to the count in fruit table for Apple. This explains that the resource estimation i.e. memory grant, IO estimation, etc. for the plan is being done on the basis of the parameter value and may not apply to other parameter values returning different count.
The above plan will be used for next executions of the stored procedure. When executed for value Mango it will return only 10 rows however the plan is optimized for 10000 rows. The performance won’t suffer in this case as number of rows returned are less than the rows saved in plan cache. Suppose, the same procedure is first compiled with @frtName=”Mango”. It would then have been optimized for 10 rows and performance would have been degraded for @frtname=”Apple”.
This is the parameter sniffing problem where in an execution plan, cached for a particular parameter value is used to process all other parameter values.
There are several solutions to this problem, which will be discussed in next class.