Thursday, August 21, 2008

Stored Procedure - Parameter sniffing

I spent a fair amount of time yesterday on a stored procedure which was performing very badly. Once I had done all the usual checks I decided to run the SQL that was contained in the sp and was very confused to see that it was a lot quicker. The sp was taking over 3 minutes to execute while the SQL query was taking 2 seconds.

I ended up finding out why from this link and this one.

Lets hope this parameter sniffing issue doesn't catch you out for as long as it did me.

Happy deving!

2 comments:

BJS said...

Nice work dude but thats odd - having to create local variables and assign them to your parameter values...eish

Anonymous said...

it was helpful. thanks.