Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80

    Unanswered: 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. Do you have any explanation?
    Last edited by manowar; 08-28-04 at 12:25.
    Davide Mauri
    http://www.davidemauri.it

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    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
    Davide Mauri
    http://www.davidemauri.it

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by manowar
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    52
    See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html

  6. #6
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80

    Thumbs up

    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!
    Last edited by manowar; 08-30-04 at 08:16.
    Davide Mauri
    http://www.davidemauri.it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •