Skip to content

How to read ToolTips of Execution Plan

May 30, 2011

There you have it.  Our initial dive into reading the wealth of information presented in the tooltips for the graphical query execution plans in Microsoft SQL Server.  These tooltips provide us with substantially more information than the graphic portion of the execution plan.  From costing information to amount of actual data affected at each step of the process, the tooltips give us a greater insight into why and how the Query Optimizer responds to our T/SQL code.

Each of the icons and the arrows has, associated with it, a pop-up window called a ToolTip, which you can access by hovering your mouse pointer over the icon. Each icon on a Execution Plan is an Operation.
Physical Operation – represent what actually occurred (such as joins, seeks, scans…). The logical and physical operators are usually the same, but not always.
Logical Operation – the results of the optimizer’s calculations for what should happen when the query executes.
Estimated I/O Cost – these are relative values used for presenting whether a given operation is I/O intensive. The Query Optimizer assigns these values during parsing and they serve only as a comparative tool to aid in determining where the costs of a given operation lie. The larger the value, the more cost-intensive the process.
Estimated CPU Cost – these are relative values used for presenting whether a given operation is CPU intensive. The Query Optimizer assigns these values during parsing and they serve only as a comparative tool to aid in determining where the costs of a given operation lie. The larger the value, the more cost-intensive the process.
Estimated Operator Cost – This is the summation of the I/O and CPU estimated costs. This is the value that is also presented under each operation icon in the graphical execution plan.
Estimated Subtree Cost – This is the total of this operation’s cost as well as all other operations that preceded it (on its left and bottom) in the query to this point.
Estimated Number of Rows – This value is derived based upon the statistics available to the Query Optimizer at the time the execution plan is drafted. The more current (and the larger the sampling size of the statistics) the more accurate this metric and others will be when compared to the actual data.
Estimated Row Size – Also based upon the statistics available at the time of parsing, this value corresponds to how wide the Query Optimizer believes the affected rows to be. The same rule applies to statistics here as well as with the Estimated Number of Rows – the more current and descriptive the data set used to generate the statistics – the more accurate this value will be in comparison to the actual data. Good stats also lead to better (more accurate) decisions made by the Query Optimizer when actually processing your queries.
Ordered – is a Boolean value signifying whether the rows are ordered in the operation.
NodeID – Is the ordinal value associated with this particular operation in the query execution plan, which simply means numbered in order, of the node itself, interestingly enough numbered left (0) to right (n)!
The Forced Index value would be True when a query hint is used to put a specific index to use within a query.
NoExpandHint this is roughly the same concept as Forced Index, but applied to indexed views.
Actual Number of Rows – The actual number of rows if the query was run.
Predicate – is the term used to describe the portion of a query used to filter, describe, or compare sets of data. For example, the query that filters the results for just those rows WHERE country=’Germany’.
Object – The object used as a tool to accomplish this task, which can be a table object (in table scan operation), an index object (in index seed/scan).
Output List – the list of columns that are output from the process being described: which is the columns that are to be returned in our SELECT statement or the input columns of the next operation.
Advertisements

From → DBA, Execution Plan

Leave a Comment

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 )

w

Connecting to %s

%d bloggers like this: