Victim Of Parameter Sniffing?
Have you come across a time when a stored procedure takes a lot longer to run than normal but the T-SQL itself runs a lot faster in Query Analyzer? This might be due to “parameter sniffing”. During compilation of a stored procedure the query optimizer attempts to “sniff” or “seek out” the values being passed in… in order to create an optimized query plan. The problem occurs when values that are passed in does not match a plan in the procedure cache and SQL Server attempts to create a new plan in the cache. This will cause the procedure to take much longer to execute and performance decreases.
The “fix” for parameter sniffing is to create local variables within the stored procedure that match the parameters being passed in. So for example,
Create Procedure myProc @name varchar(30), @age int
As
Declare @name_local varchar(30), @age_local int
Set @name_local = @name
Set @age_local = @age
Then use @name_local and @age_local throughout your stored procedure and you will get the same execution times as if you were running the straight T-SQL in query analyzer. Let me know if this helps!
8 Comments
Eric
August 11th, 2008
at 5:48am
This helped enormously. Thanks!
Bill Delaune
September 24th, 2008
at 5:54am
Can you clarify conditions that produce the parameter sniffing issue? It seems the rebuilding indexes and recompiling the stored procedure would produce a valid execution plan for the store proc compiliation. Does the issue occur after the immediately after the sproc has been compiled, or does it appear after time has passed and the indexes may have become fragmented, etc. I’ve seen this suggestion posted before and it seems like the process of updating the sproc will force a recompile that might be the real source of the performance improvement.
sqlsquirrel
October 1st, 2008
at 10:03pm
Thanks Bill for your response. This issue occurred for me when upgrading from SQL Server 2000 to SQL Server 2005. Several procedures had this issue. When the upgrade was completed all user databases had the DBCC CHECKDB and their statistics updated.
I had tried to recompile and rebuild indexes in the upgraded databases from SQL Server 2000. Their compatibility was set to 2005 compatibility. I still had the same issue. But once I assigned the variables to local variables it fixed the issue. I am curious to see if this will be fixed in upcoming service packs for SQL Server 2005.
Aidan Hodges
October 21st, 2008
at 7:12pm
I had a similar issue to this. I had a stored procedure that accepted a take param and added one day to it to find the end date:
CREATE PROCECURE dbo.test
(
@Date SMALLDATETIME
)
DECLARE @EndDate
SELECT @EndDate = DATEADD(DD, 1, @Date)
I fixed this issue by accepting bother the start and end Date as parameters:
CREATE PROCEDURE dbo.test
(
@StartDate SMALLDATETIME
,@EndDate SMALLDATETIME
)
I assume this issue here was that it couldn’t determine the value of @EndDate before hand – a bit like using a function call within a Query.
sp runs much slower than same query ran in QA | keyongtech
January 18th, 2009
at 8:33am
[...] [...]
Michael Culley
March 16th, 2009
at 2:10pm
The correct way to fix this for sql 2005 is to use the OPTIMIZE option, eg
SELECT * FROM WHATEVER
WHERE X = @SomeParam
OPTION (OPTIMIZE FOR (@SomeParam = ‘ABC’))
sqlsquirrel
March 17th, 2009
at 6:57pm
Thanks for the tip Michael!… I did not think to use the OPTION(OPTIMIZE FOR) to fix it in SQL Server 2005!… That is very cool!
-Squirrel
Sridhar
September 10th, 2009
at 5:41am
It really help me. i was just about to bang my head to desk, googling for solution about hours. Thanks a lot.