Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010

    Unanswered: Strange SP Inconsistent Performance Issue

    I have a very strange problem with a stored procedure. After a few days of use, the SP gets slower and slower every time it's called till it eventually it takes too long and times out. If I go into SQL Server Management Studio and 'Modify' the SP and simply click Execute to simply re-save the same SP code, the SP will go back to executing in under a second again every time it's called and the cycle repeats with it slowing down over the coming days.

    I've run SQL Server Profiler & Database Engine Tuning Advisor and use the Activity Monitor to track the performance. When the SP is running slowly, it maxes out the CPU and the activity monitor shows the spike for the whole time. When it's running fast, you don't even see a blip in the Activity Monitor graphs and the query shows low demand on the Expensive Queries stats.

    So my question is 'what does clicking Modify and hitting Execute do that would make such a dramatic difference in performance as no changes are being made to the SP when I do it.' I obviously don't understand the inner workings enough of SQL server and how it manages memory etc but I'm really stumped on this and it's a hard performance issue to research as it's so inconsistent and cyclical.

    I haven't included the SP code or DDL as I don't think it's specifically relevant (but yes it's rather large and complex.)

    Thanks so much for any help/direction you can provide.

    MS SQL 2008 Web Edition 64
    Windows Server Web Edition SP2

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Posting the code, actually, will help in giving you specifics, rather than simply telling you that it is "parameter sniffing".
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2010
    Thank you for the response and directing me towards the concept of parameter sniffing. That looks like the area I need to research more and understand. Thank you very much for your help.
    Last edited by xcgeek; 12-22-10 at 15:21.

Posting Permissions

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