This blog deals with below:
· Understanding Parameter Sniffing.
· Example
· Possible Solutions with application for each one of them.
The query fired against SQL Server, is first compiled before execution. Subsequent executes of stored procedure reuse already stored execution plans. This is plan reuse helps saving CPU cycles in compilation thus increasing throughput. The reuse of plan is enhanced using stored procedures. In most of the cases using saved execution plan is good but in some cases the plan reuse may lead to inconsistent performance problems specially in Stored procedures. Parameter sniffing occurring typically when data is skewed (that is values of one type are more frequent then other values).
When a query is executed, based on statistical information about underlying tables accessed by query, SQL Server will generate execution plans.. Execution plan is set of logical steps that sql server will follow to generate results. The parameters being passed to stored procedure are compared against statistics present for tables / indexes (Statistics indicate distribution of data).
Say for example in Employee table we have column Gender where there are 1000 Males but 10 Females that is data being skewed. Below is a very sample script to create understand parameter sniffing.
use tempdb
go
if exists(select 1 from sysobjects where name like 'Employee')
begin
drop table Employee
end
create table Employee(id int, name varchar(100) , Gender Char(1))
go
declare @i int
set @i = 1
while @i < 100000
begin
insert into Employee values(@i,'Guru','M')
set @i = @i +1
end
go
declare @i int
set @i = 100000
while @i < 100056
begin
insert into Employee values(@i,'Saru','F')
set @i = @i +1
end
go
go
create clustered index ind on Employee(id)
go
create index ind1 on Employee(Gender)
go
create procedure USP_Parameter_Sniffing
@Gender char(1)
as
begin
select * from Employee where Gender = @Gender
end
go
set statistics profile on
set statistics IO on
set statistics time on
go
USP_Parameter_Sniffing 'F'
go
set statistics profile on
set statistics IO on
set statistics time on
go
For Above query with ‘F’ as parameter, apart from query results below execution plan is used to produce results. The execution plan contains Non clustered indexes that point to Clustered index keys and use Bookmark lookup operator for selecting rows from base tables.
Rows Executes StmtText
----------- ----------- ----------------------------------------------------------------------------------------------------------------------
56 1 select * from Employee where Gender = @Gender
56 1 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[Employee]) WITH PREFETCH)
56 1 |--Index Seek(OBJECT:([tempdb].[dbo].[Employee].[ind1]), SEEK:([Employee].[Gender]=[@Gender]) ORDERED FORWARD)
Now if we run the same query with ‘M’ Parameter..
set statistics profile on
set statistics IO on
set statistics time on
go
USP_Parameter_Sniffing 'M'
go
set statistics profile on
set statistics IO on
set statistics time on
go
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 25000 ms.
Rows Executes StmtText
----------- ----------- ----------------------------------------------------------------------------------------------------------------------
99999 1 select * from Employee where Gender = @Gender
99999 1 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[Employee]) WITH PREFETCH)
99999 1 |--Index Seek(OBJECT:([tempdb].[dbo].[Employee].[ind1]), SEEK:([Employee].[Gender]=[@Gender]) ORDERED FORWARD)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 25000 ms.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 25000 ms.
But if you run below statement you would get results faster and you would see it would go for clustered index scan instead of Non clustered index scan followed by book mark lookup
dbcc freeproccache
set statistics profile on
set statistics IO on
set statistics time on
go
USP_Parameter_Sniffing 'M'
go
set statistics profile on
set statistics IO on
set statistics time on
go
To understand more about Parameter sniffing:
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
How to find out if this phenomena is occurring on server?
Typical symptoms would include:
· Assuming blocking and other resources are ruled out , if procedure execution times varying depending on parameters passed but execution plan remains same.
· Data is skewed (more data of one type is present when compared to other like Male/Female in our example).
· If procedure is executed with different parameters by running DBCC Freeproccache and each execute produces different plan then may be parameter sniffing is occurring.
o Exec <procecure name> Parameter
o DBCC FREEPROCCACHE
o Exec <procecure name> Parameter1
Solutions would be:
· If compilation cost of query is low, then sp_recompile could be used (If used incorrectly this could lead to HIGH CPU , so be careful about it).
· Declare a second set of parameters inside stored procedure and use them in Where conditions.
· DBCC FREEPROCCACHE would also work (But this will make all queries compile again and is not recommended)
In case you have questions please post them for reply.
Thanks & Regards
Guru Charan B
