SQL Parameter Sniffing

There are several articles out there on parameter sniffing. I had never come across this issue until this week. Here are some of my notes. Here is a good article for definitions and examples. http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Lessons learned

  1. In certain cases you will need to utilize optimizer hints. The Optimizer Engine is real good, but its not perfect
  2. Use the WITH RECOMPILE in a STORED PROCEDURE Create statement with caution. The system overhead involved in recompiling the plan every execution may eliminate the perofrmance gains of picking the correct plan.
  3. Make sure you check your index fragmentation and statistics. Just to eliminate the obvious.
  4. Learned some other techniques to reduce parameter sniffing (using local variables).
select COUNT(*) from dbo.table1

 

select * from table1

--Hardcoded Date
select * from table1 with (nolock) where CollectionDate >= '4/24/2012 4:04PM'

--Date Variable
DECLARE @FromDate datetime
set @FromDate = '4/24/2012 4:04PM'
select @FromDate select * from table1 with (nolock) where CollectionDate >= @FromDate

Using an Index HINT
sourcecode language="sql"]
--Date Variable with INDEX HINT
DECLARE @FromDate datetime
set @FromDate = '4/24/2012 4:04PM'
select @FromDate
select * from table1 with (nolock  INDEX(IX_table1_CollectionDate)) where CollectionDate >= @FromDate
[/sourcecode]

CREATE PROCEDURE testSearch2
@FromDate
datetime
AS
select * from table1 with (nolock)
where CollectionDate >= @FromDate

--NO Recompile exec testSearch2 '4/24/2012 4:04PM'


Then I dropped the procedure and recreated it with the RECOMPILE Option:

DROP PROCEDURE testSearch2
GO
CREATE PROCEDURE testSearch2
@FromDate datetime
WITH RECOMPILE
AS select *
from table1 with (nolock)
where CollectionDate >= @FromDate

--AFTER RECOMPILE
exec testSearch2 '4/24/2012 4:04PM' 

Advertisements