Post reply

Query execution plans and dbcc pintable

  • December 8, 2005 at 4:02 am

    #112833

    Hi 

    I'm having trouble optimizing one of our most frequently used stored procedures which is the cause of 80% of our users' complaints about slow responses.

    I execute the procedure once and it takes 60 - 90 seconds. I  execute it immediately afterwards and it takes 5 seconds, execute again, 5 seconds and so on, even if I open another query analyzer window and change the parameters. If I execute it again after, say, 1 hour then sometimes it takes a few seconds, sometimes over a minute.

    I've looked at the code which is pretty complicated - I don't think I can improve it. I've also pinned the main table involved which has made a slight difference (I think).

    I think the answer lies in the query execution plan which is build by the 1st query and remains in cache until it's flushed in the normal course of events. It seems a dramatic difference though (90 seconds down to 5 seconds), is there another explanation? Is there any way of pinning the execution plan in cache?

    A bit of a long and obscure post but any suggestions would be gratefully received.

     

  • December 8, 2005 at 4:27 am

    #608366

    It's an interesting problem which may have a number of possible solutions.

    • Complex stored procs may work better if split into smaller less complex procs.
    • Adding a recompile to the proc will avoid the "wrong plan" scenario but probably at a cost to performance ( I personally try avoid the with recompile )
    • Your proc may be recompiling anyway - if it's complex - run it through profiler to check what happens and to isolate / identify the parts which are problematic.
    • The underlying tables may be changing causing seeks to change to scans
    • Params may change sufficiently to do the same

    You'll really need to profile in great depth to find the problem - I've been there and it took me some considerable time < grin >

    It may be that the design of the proc will always actually do this - there's only so much a DBA can do and if the code is the issue then the code has to change - this was how many of my suimilar problems were fixed.

    Watch the set options - they can cause differences in performance. If the proc is called from an app the set options may be different from your QA settings which completely obscures the issue.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • December 8, 2005 at 5:08 am

    #608374

    Thanks for this - I created the procedure with recompile but it always took ages which reinforces the idea that its to do with the execution plan.

    Pinning execution plans is a bit of a non starter anyway as they're constantly changing, I think we are lokking at rewriting it and splitting it into several smaller procedures.

  • December 9, 2005 at 3:30 am

    #608601

    "Parameter sniffing" may also be at play.

    Search (here and elsewhere) for an explaination of the term and a simple remedy.

  • December 9, 2005 at 8:17 am

    #608679

    can you post the code. I think its probably due to recompilation.

    have a look here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp and see if your code contains the elements identified as causes of recompilation.

    www.sql-library.com[/url]

  • December 12, 2005 at 8:45 am

    #609038

    Thanks Jules & Andrew. I've had a busy couple of days but I'll look at the MSDN article & parameter sniffing later today.

  • December 12, 2005 at 8:50 am

    #609039

    PS I can't post the code, my employer will have my guts for garters if I do.

    The main select involves 14 (FOURTEEN) inner and outer joins and several of the join clauses inculde NOT EXISTS statements. In fact, looking at it, it's a mircale it works at all!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply