Displaying Execution Plans

Question

Displaying Execution Plans

Randy_Dyess

SSC Eights!

Points: 974
More actions
August 19, 2003 at 12:00 am

#105849

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

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

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

ckempste SSCoach Points: 17983 More actions · Answer 1

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.

Cheers

Ck

Chris Kempster

http://www.chriskempster.com

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

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

Randy_Dyess SSC Eights! Points: 974 More actions · Answer 2

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

russella0 SSC Enthusiast Points: 135 More actions · Answer 3

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.

Thanks,

Andy

isddarms Old Hand Points: 352 More actions · Answer 4

Is Query Analyzer different from SQL Server Management Studio?

breadcrumb SSC Veteran Points: 205 More actions · Answer 5

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 SSC-Forever Points: 42192 More actions · Answer 6

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:

https://www.sentryone.com/plan-explorer