Parameter Sniffing

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