Database Reference
In-Depth Information
Figure 25-9.
Execution plan without blocking operators
There are three ways that you can analyze execution plans in SQL Server and Management Studio. The most
common method is graphical execution plan representation, which can be enabled through the “Include Actual
Execution Plan” menu item in the
Query
menu. A graphical execution plan represents an execution plan tree rotated
90 degrees counter-clockwise. The top root element of the tree is the leftmost icon on the plan, with children nodes on
the right side of the parents.
When you select an operator in the plan, a small pop-up window shows some of the properties of the operator.
However, you can get a more comprehensive picture by opening the operator's Properties window in Management
Studio.
■
SQL Sentry “plan explorer” is a great freeware tool, which simplifies execution plan analysis and addresses a
few of the shortcomings of SQL Server management Studio. you can download it from
http://sqlsentry.net
.
Tip
In addition to a graphical representation of the execution plan, SQL Server can display it as text or as XML.
A text representation places each operator into individual lines displaying parent/child relationships with indents
and | symbols. A text execution plan may be useful when you need to share a compact and easy-to-understand
representation of the execution plan without worrying about image size and the scale of the graphical execution plan.
An XML execution plan represents operators as XML nodes with child operator nodes nested in parent nodes.
It is the most complete representation of the execution plan, which includes a large set of attributes, which are
omitted in other modes. However, an XML execution plan is complex and it requires some time and practice before it
can become easy to understand.
In addition to the actual execution plan, SQL Server can provide an
estimated execution plan
without running
the actual query. This allows you to obtain the execution plan shape quickly for analysis. However, it does not include
actual row and execution counts, which are very helpful during query performance tuning.
Table
25-1
shows different commands that generate estimated and actual execution plans in graphical, text, and
XML modes.
