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.
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
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:
Post a Comment