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)
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 fruites(frtname)
GO
-- Create a procedute to fetch fruit detail.
CREATE Proc usp_fetchfruits
@frtname varchar(20)
AS
BEGIN
SELECT * FROM fruit WHERE frtname=@frtname
END
GO
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 https://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.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Hello,
I got this example yesterday when I was reading about Query Execution Plan.
use adventureWorks
go
SELECT *
FROM [Person].[Address]
WHERE [City] = ‘Newark’
SELECT *
FROM [Person].[Address]
WHERE [City] = ‘London’
are two same queries but with different parameter.
They returns two different query Execution plans.
Is this the Example of parameter sniffing?
Hi Vivek – I would suggest you to check query plan and verify that this is an example of parameter sniffing or not by using the explaination in this blog.
Hi Vivek
Parameter sniffing is when you have in the same parameter difference between the resultset was returned and the sql server use the same plan, but for a large or small resultset you in theory you need a different plan, because the way is different. Example when you bring a large resultset maybe the scan is better than seek and de sql server use the plan cache with seek, or use scan instead seek.