Capture execution plans

May include but is not limited to: graphical execution plans, SHOWPLAN

I’m a bit confused about this one being a separate objective. As all of us know, SQL Server produces graphical (and textual) execution plans for your queries. You can watch these plans any time you’d like by pressing Ctrl+M.

A few things to know about execution plans:

  • They are read from left to right, and from top to bottom.
  • The thicker an arrow, the more data it returns.

I think that’s good enough. Another way to access graphical execution plans is the SHOWPLAN clause. When specifying it, SQL Server returns the execution plan in an XML format, without executing the commands. The syntax to use:

SET SHOWPLAN_XML ON

If you prefer plain text, use the SHOWPLAN_TEXT clause instead. But make sure you turn off these features, because you only get execution plans as a result.

You can get additional information about your queries when using the STATISTICS clause. The difference is that when you specify STATISTICS, you get a result after your query is executed. There are four things to query for: XML, PROFILE, IO, TIME. The syntax is:

SET STATISTICS TIME ON

Then you can view the results in the  Messages pane in SSMS.

Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: