Displaying Execution Plans

  • ckempste


    Points: 17983

    Hi Randy

    So whats the combo of commands to "get a text based execution plan after the execution of the statement", as showplan_all and showplan_text dont physical execute the parsed statement but return the text output. Just missing in the article.

    Nice summary btw, beats hunting around the docs. May be worth detailing the output though (where applicable) and gotchas to watchout for, esp the time and IO stats.



    Chris Kempster


    Author of "SQL Server 2k for the Oracle DBA"

    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Randy_Dyess

    SSC Eights!

    Points: 974

    Review the section over SET STATISTICS PROFILE. This command will allow the query to execute as well as return the execution plan.

    I also plan on detailing the output over several articles as I feel that the explanation would be too long to fit in a single article. I will start working on a side article that explains the commands in more details and go over some of the "gotchas" you asked for.


  • Randy_Dyess

    SSC Eights!

    Points: 974

    Comments posted to this topic are about the item Displaying Execution Plans

  • russella0

    SSC Enthusiast

    Points: 135

    Thanks for the tip on SET STATISTICS PROFILE ON, I'd given up on thinking that you could get the execution plan back in text from Query Analyzer when the SQL was actually executing - I though it would only show the estimated plan if you wanted a text based Execution Plan this is going to help me a lot in my job.

    Maybe, and I know I might be jumping ahead as you have other articals on the way, you could answer my other burning question.

    When you SET STATISTICS TIME ON and run several SQL statement is the CPU time and Elapsed time a cumalative of all statements run in the batch or just the statement it is reporting back on - I've never been able to find a definitive answer and had kind of given up.



  • isddarms

    Old Hand

    Points: 352

    Is Query Analyzer different from SQL Server Management Studio?

  • breadcrumb

    SSC Veteran

    Points: 205

    isddarms wrote:

    Is Query Analyzer different from SQL Server Management Studio?

    Next you'll be telling us to break out a copy of Enterprise Manager 🙂

  • Chris Harshman


    Points: 42104

    isddarms wrote:

    Is Query Analyzer different from SQL Server Management Studio?

    Note: this article was first published in 2003.

    Back in the SQL Server 2000 days, the GUI tools for SQL Server were Query Analyzer and Enterprise Manager.  In SQL Server 2005 and onward, SQL Server Management Studio became the default GUI tool.  Today you can easily get the execution plan with icons in Management Studio, or use free tools such as SentryOne Plan Explorer:



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

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