Tuesday, March 27, 2012

AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

No comments:

Post a Comment