The statement execution plan shows how long each node took to evaluate, how
many rows were retrieved, whether an index was used, and so on. If an index was
used, it shows the start and stop positions for the matching index scan. Looking
at the plan can help you determine whether to add an index or to rewrite the
query.
A statement execution plan is composed of a tree of result set nodes. A
result set node represents the evaluation of one portion of the statement; it
returns rows to a calling (or parent) node and can receive rows from a child
node. A node can have one or more children. Starting from the top, if a node has
children, it requests rows from the children. Usually only the execution plans
of DML statements (queries, inserts, updates, and deletes, not dictionary
object creation) are composed of more than one node.
For example, consider the following query:
SELECT * FROM Countries
This simple query involves one node only: reading all the data out of the
Countries table. It involves a single node with no children.
This result set node is called a Table Scan ResultSet. RUNTIMESTATISTICS
text for this node looks something like this:
Statement Name:
null
Statement Text:
select * from countries
Parse Time: 20
Bind Time: 10
Optimize Time: 50
Generate Time: 20
Compile Time: 100
Execute Time: 10
Begin Compilation Timestamp : 2005-05-25 09:16:21.24
End Compilation Timestamp : 2005-05-25 09:16:21.34
Begin Execution Timestamp : 2005-05-25 09:16:21.35
End Execution Timestamp : 2005-05-25 09:16:21.4
Statement Execution Plan Text:
Table Scan ResultSet for COUNTRIES at read committed isolation
level using instntaneous share row
locking chosen by the optimizer
Number of opens = 1
Rows seen = 114
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 10
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=3
Number of rows qualified=114
Number of rows visited=114
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 119.00
optimizer estimated cost: 69.35
Consider this second, more complex query:
SELECT Country
FROM Countries
WHERE Region = 'Central America'
When executed, this query involves two nodes: one to retrieve qualifying rows
(the restriction is done at this node) and one to project the requested columns.
So, at bottom, there is a Table Scan ResultSet for scanning the table.
The qualifier (Region = 'Central America') is evaluated in this
node. These data are passed up to the parent node, called a
Project-Restrict ResultSet, in which the rows are projected; only the
country column is needed (the first column in the table).
RUNTIMESTATISTICS text for these two nodes looks something like this:
Statement Name:
null
Statement Text:
SELECT Country FROM Countries WHERE Region = 'Central America'
Parse Time: 10
Bind Time: 0
Optimize Time: 370
Generate Time: 10
Compile Time: 390
Execute Time: 0
Begin Compilation Timestamp : 2005-05-25 09:20:41.274
End Compilation Timestamp : 2005-05-25 09:20:41.664
Begin Execution Timestamp : 2005-05-25 09:20:41.674
End Execution Timestamp : 2005-05-25 09:20:41.674
Statement Execution Plan Text:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 11.90
optimizer estimated cost: 69.35
Source result set:
Table Scan ResultSet for COUNTRIES at read committed isolation level
using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 6
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 10
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 2}
Number of columns fetched=2
Number of pages visited=3
Number of rows qualified=6
Number of rows visited=114
Scan type=heap
start position:
null stop position:
null qualifiers:
Column[0][0] Id: 2
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 11.90
optimizer estimated cost: 69.35
Other, more complex queries such as joins and unions have other types of
result set nodes.
For inserts, updates, and deletes, rows flow out of the top, where they are
inserted, updated, or deleted. For selects (queries), rows flow out of the top
into a result set that is returned to the user.
The shows the many possible
ResultSet nodes that might appear in an execution plan.
In addition, read for more information
about some of the ways in which
executes
statements.