Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: Stored Procedure Timing Out Until Recompile

    Hey all,

    We have a problem with one of our MS SQL 2000 databases and some stored procedures.

    I'm not sure exactly what the problem is, but these are the symptons....

    The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.

    Calling it through the query analyzer it runs within a second.

    Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.

    We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.

    I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.

    The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.

    The code for the second results set is...

    Code:
    Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate
    	From Pricing p
    		Inner Join Security s
    		On p.SecurityID = s.SecurityID
    		Left Outer Join Issuer i
    		On s.IssuerID = i.IssuerID
    	WHERE p.PricingTypeID = @PricingType
    	And p.TenorTypeID = @TenorType
    	And p.CaptureDate Between @DateFrom And @DateTo
    	AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
    Does anyone have any idea what might be going on here?

    Regards
    Shaun

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I would guess parameter sniffing (you can google this on t'web).

    The reason it works from one application and not another is that SQL Server generates different execution plans depending on some settings. ODBC by default sets some of these settings. For example, if you execute a statement with ANSI NULLS OFF and then with ANSI NULLS ON then there will be two compiled plans for obvious reasons - the most efficient plan is very much dependent on whether or not NULL = NULL.

    To see this empirically you can recompile the proc, run it once in QA and execute the below:
    Code:
    SELECT TOP 100 *
    FROM dbo.syscacheobjects
    WHERE objid = OBJECT_ID('p_analyse_msg_log')
    You will see one row. Execute it from your app and execute the above again. You will see two rows. TYhe key field is setopts - a bitmask. Check out syscacheobjects in help. I can't find it now but there is a function on the web that will split out the setopts field into the various connection settings

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - did I say parameter sniffing? I meant the plan aging and becoming sub optimal for the data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie, so the correct execution plan gets generated, then something happens that tells MS SQL to generate a new execution plan.

    The new execution plan that gets generated is (for whatever reason) sub-optimal.

    We then log in to Query Analyzer and recompile the stored procedure that creates a new execution plan which is optimal.

    So a couple of questions from this...

    1. What causes a new plan to be generated? Are they only generated when the stored procedure is called and the current plan is out of date?

    2. Apart from using the "WITH RECOMPILE" statement in the stored procedure can we control when the procedure gets recompiled and generates a new plan?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rokslide
    Okie, so the correct execution plan gets generated, then something happens that tells MS SQL to generate a new execution plan.

    The new execution plan that gets generated is (for whatever reason) sub-optimal.
    No - the other way round. An optimal plan is created for the ODBC settings. This plan eventually becomes sub optimal because the data changes over time. You go in via QA to see what is going on and a new, optimal plan reflecting the current data is created because QA uses different settings.

    So - you get one plan per setting configuration. This plan sticks around until you specifically say you want it recompiled (it can also be aged out but wer'll ignore that for now).

    Q2 - yes if you are really concerned you could schedule a regulr running of sp_recompile for the proc every few weeks or so.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie, assuming that is what is happening, wouldn't we see a gradual degredation of performance instead of an instant drop out?

    Does that also mean the flow is something more like....

    1. Stored proc is added to database and compiled (with optimal plan for Query Analyzer)

    2. Stored proc is called from web application and re-compiles (with optimal plan for ODBC/OLEDB)

    3. Execution plan becomes sub-optimal due to changes in data.

    4. Stored proc is recompiled in Query Analyzer

    5. See step 2 and repeat.....

    Is there a way to see the existing execution plan for ODBC/OLEDB (I know you can see what it is for QA, but not other things).

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The only missing step is that you will not have called the sproc from QA for a long period. As such the plan is eventually removed from the cache (you can read up on stuff like the lazy writer and procedure\ data cache for more info). This is why during step 4 a new plan is created which is optimal.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie, if we assume the above is happening (eg. an execution plan built by MS SQL is optimal where a query plan built from the stored proc being executed via the web application is sub-optimal) then if I add a "WITH RECOMPILE" statement to the stored procedure and execute it from the web page it should start timing out all the time,... would that be correct?

    Also, the procedure starting timing out again today at 11am. I had checked it at 9 & 10am and it was working. No data was loaded or updated between the period that it was working and the period that it stopped working.

    Is it possible that a particular execution of the procedure (eg. certain parameters etc) could trigger a recompile with a sub optimal plan that fails for the majority of executions?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rokslide
    if I add a "WITH RECOMPILE" statement to the stored procedure and execute it from the web page it should start timing out all the time,... would that be correct?
    No - exactly the opposite. If a procuedure is executed with recompile then it will always execute with an optimal plan (inadequacies of the optimiser not withstanding). Remember that the problem we are looking at is the stored plan (which has in the past been compiled) is not correct for the current execution. If you recompile the plan for every execution it will always be optimal for that execution.

    Now - I will admit to an ASSumption. I thought you were describing a suddent drop off that does not improve over time. I also ASSumed you were describing running the procedure with the same parameters on every run, finding that the only difference waws that the execution times were terrible via one app and execllent via another that you subsequently tested with.

    Quote Originally Posted by rokslide
    Is it possible that a particular execution of the procedure (eg. certain parameters etc) could trigger a recompile with a sub optimal plan that fails for the majority of executions?
    You have probably hit on the problem (although there is a still a misunderstanding about the recompiling bit ). Look up parameter sniffing on the web - check out at least a couple of articles - maybe start with sql-server-performance.com. Once you have done that check out this article:
    http://www.sqljunkies.com/WebLog/ama...reCaching.aspx
    I am not suggesting you necessarily want to use this guys solution however if you follow the steps he uses you will see many of the things we have discussed (parameter sniffing, multiple execution plans, using with recompile etc). Hopefully it will resonate a little and make a bit more sense.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Quote Originally Posted by pootle flump
    Now - I will admit to an ASSumption. I thought you were describing a suddent drop off that does not improve over time. I also ASSumed you were describing running the procedure with the same parameters on every run, finding that the only difference was that the execution times were terrible via one app and execllent via another that you subsequently tested with.
    Just to clarify.

    We were/are getting a sudden drop off that does not improve until recompilation.

    When I am executing the stored procedure (after the sudden drop off) I am using the same parameters on every run and the execution times are different between two applications (query analyzer and our web application).

    There are other people running the query however and I do not know the parameters that they are using.

    Quote Originally Posted by pootle flump
    You have probably hit on the problem (although there is a still a misunderstanding about the recompiling bit ). Look up parameter sniffing on the web - check out at least a couple of articles - maybe start with sql-server-performance.com. Once you have done that check out this article:
    http://www.sqljunkies.com/WebLog/ama...reCaching.aspx
    I am not suggesting you necessarily want to use this guys solution however if you follow the steps he uses you will see many of the things we have discussed (parameter sniffing, multiple execution plans, using with recompile etc). Hopefully it will resonate a little and make a bit more sense.
    Thanks for that. This article makes some sense.... but I am still slightly confused.

    The key thing that is confusing me I think is the difference in the executions plans after the "sudden drop off". I still don't understand why query analyzer runs well but the web application does not. I suspect that there are actually two execution plans. One for QA and one for the web app, is this correct?

    I am trying to come up with a scenario of events to explain what I am seeing... at the moment I have something like this....

    1. Stored proc is compiled.
    2. Stored proc is executed from the web app and execution plan 1 is generated (this is out optimal plan)
    3. Execution plan 1 is removed from the cache for whatever reason
    4. Stored proc is executed from the web app with different parameters then step 2 and excution plan 2 is generated (this is sub optimal).
    5. Users yell and scream and we start trying to find the problem
    6. We test the stored proc from QA and generate execution plan 3 (we now have execution plan 2 and 3 in the cache - one for QA and one for the web app)
    7. Execution plan 2 and 3 continue to exist until we recompile.
    8. Return to step 2.

    Does that work? If not would it be possible for you to correct the flow of events above?

    Thanks for all your help on this. It is very much appreciated.

  11. #11
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by rokslide
    The key thing that is confusing me I think is the difference in the executions plans after the "sudden drop off". I still don't understand why query analyzer runs well but the web application does not. I suspect that there are actually two execution plans. One for QA and one for the web app, is this correct?
    No, this is not correct. When SQL Server generates an execution plan for an sproc, it places it into procedure cache. When the same proc is called again (and again and again), it first looks to see if the proc execution plan has been cached ... if so, that is the plan it will use to again retrieve results for that proc with the new parameters provided by the call. If the proc has been aged out of cache (or forced to recompile), it will then pull the proc, create a new execution plan based on the statistics of the parameters and data in the desired result set, and cache that plan. Only one execution plan will be cached for a give sproc, not one for web users and one for QA users.

    -- This is all just a Figment of my Imagination --

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tomh53
    No, this is not correct. When SQL Server generates an execution plan for an sproc, it places it into procedure cache. When the same proc is called again (and again and again), it first looks to see if the proc execution plan has been cached ... if so, that is the plan it will use to again retrieve results for that proc with the new parameters provided by the call. If the proc has been aged out of cache (or forced to recompile), it will then pull the proc, create a new execution plan based on the statistics of the parameters and data in the desired result set, and cache that plan. Only one execution plan will be cached for a give sproc, not one for web users and one for QA users.
    Hi tom

    Reluctantly I have to contradict you there. A stored procedure can have many plans. If you check the setopts column in syscacheobjects (a bitmap indicating the options set for that particular plan) you will see that there can be up to 256 plans per procedure. I did have a "proof" script for this but I don't have it available now.... in fact I'm not sure where it is If you like I can whip up another for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Not a problem Poots ...I can always learn something new.

    It makes me a better DBA

    Of course, then I have to wonder which execution plan the engine knows to use to satisfy a request ... back to the books!

    -- This is all just a Figment of my Imagination --

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tomh53
    Not a problem Poots ...I can always learn something new.

    It makes me a better DBA

    Of course, then I have to wonder which execution plan the engine knows to use to satisfy a request ... back to the books!
    It checks the settings for the connection and retrieves the approriate plan (based on the setopts contents again) for it. If no appropriate plan exists (for example the cached plan is for ANSI_NULLS ON and the connection has ANSI_NULLS OFF) then a new one is created.

    This isn't the one I have bookmarked (lost the bookmark of course ) but a similar function to help you read the setopts column:
    http://www.sqldev.net/misc/fn_setopts.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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