Friday, June 5, 2009

Stored Procedure taking forever.

Today, I came across an issue where from Sql Server Management Studio, a stored procedure was taking forever to run.  But from the application it was running just fine, and if I executed the sql from within the proc on it’s own, it ran just fine. 

Luckily, a friend, Chris Brandsma had stumbled across this about a year ago, and posted it and a solution on Elegant Code.

It’s called SQL Parameter sniffing, and MS (as well as Oracle and the others) use it to improve performance of stored procs.  However, if you have a large or sudden variance in the number of rows returned, the proc will seem to “hang up.”

At least I know what the fix is, and what to look for if it happens again on one of those “random” timeout bugs.

No comments: