SQL Server Performance

View a cached query plan?

Discussion in 'Performance Tuning for DBAs' started by kpayne, Oct 18, 2005.

  1. kpayne New Member

    Hi all.

    Is there a way to view the compiled query plan of a stored procedure and/or table-valued function?

    The reason that I am asking is that I have spent a good deal of time performance tuning a few different multi-statement table-valued functions by executing the DML statements directly in QA and examining the query plan and IO statistics.

    Unfortunately, the functions accept 8 parameters that can greatly affect the execution time - and probably the query plan. For instance, the first two parameters define a date and a date range indicator (e.g. "One Month", "YTD", "Rolling 12 months"). These and other parameters can affect the joins. One combination of parameters may pull nearly all rows from a table while other combinations may only use a small number of rows. I imagine that the optimal execution plan could change from a merge join to a hash or nested loop in the latter case.

    Additionally, there is an unknown factor when the code is migrated to production. Our production environment hardware is different from development and staging.

    I would like to view the compiled plans to make sure that they matche the plans from QA.

    TIA

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  2. FrankKalis Moderator

    The only way I can think of right now, would be to trace this, import the trace into a table and then analyze the data in the table.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. FrankKalis Moderator

  4. satya Moderator

    May also use the Query analyzer to check estimated execution plan.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. kpayne New Member

    Thank you for the ideas. I'm going to have the production DBA run a trace after it migrates and I will learn to use the text query plan.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  6. thomas New Member

    Do you have a performance problem? If not, stop worrying!

    Tom Pullen
    DBA, Oxfam GB
  7. kpayne New Member

    quote:Originally posted by thomas

    Do you have a performance problem? If not, stop worrying!

    Tom Pullen
    DBA, Oxfam GB

    Yes, performance is a big issue with these objects. The functions are combined in a stored procedure that can take 25 minutes with the wrong execution plans - or 2.5 minutes with efficient plans.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  8. FrankKalis Moderator

  9. kpayne New Member

    Frank,<br /><br />Thank you for the offer of looking through the code. I spent all day yesterday reworking the joins and indexes and finally hit on a configuration where SQL Server consistently chose the query plan that resulted in the shortest execution time. I made sure to execute the procs and functions with a parameter combination that pulls the largest row counts from each table.<br /><br />I've got consistent execution times of under 2 minutes. The only outstanding issue is to make sure that the production DBA executes the procs and functions with the same parameters immediately after they migrate [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><b>Keith Payne</b><br />Technical Marketing Solutions<br />www.tms-us.com
  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />The only outstanding issue is to make sure that the production DBA executes the procs and functions with the same parameters immediately after they migrate <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Sometimes standing in their back with a big sledgehammer works wonder and can help you tremendously in your argumentation. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Good to hear you found a solution!<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page