Displaying Execution Plans

  • 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"

  • 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.


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

  • 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.



  • Is Query Analyzer different from SQL Server Management Studio?

  • 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 🙂

  • 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 6 (of 6 total)

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