When SET SHOWPLAN_ALL is set on, detailed information is displayed pertaining to the query execution plan used. The query is not executed. This statement must be the only statement in the query batch.
Suppose we execute the following query when SET SHOWPLAN_ALL ON has been executed:
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no
WHERE balance = 100
The output will be returned in the form of a rowset that can be accessed by programs. There is too much information returned to display it across the page, so we will break it down into its constituent parts. Rather than use the previous SQL statement, we will use a slightly more complex one involving an inner join of the Customers and Accounts tables. We are not too concerned with the reason a particular plan was chosen here—the goal of this example is merely to show the output from this SET statement. Ultimately, I find the graphical query execution plan much easier to use and I will focus on that shortly.
StmtText
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no WHERE balance = 100
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([BankingDB].[dbo].[customers] AS [C]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([A].[customer_no]) WITH PREFETCH)
|--Table Scan(OBJECT:([BankingDB].[dbo].[accounts] AS [A]), WHERE:([A].[balance]=100.00))
|--Index Seek(OBJECT:([BankingDB].[dbo].[customers].[nciCustomerNo] AS [C]), SEEK:([C].[customer_no]=
[A].[customer_no]) ORDERED FORWARD)
I have wrapped the output so this StmtText column can be read completely. This is how it looks with no wrap, so it can easily be matched with the other columns I will discuss. I have had to truncate the text to fit it on the page.
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no WHERE balance = 100
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([BankingDB].[dbo].[customers] AS [C]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([A].[customer_no]) WITH PREFETCH)
|--Table Scan(OBJECT:([BankingDB].[dbo].[accounts] AS [A]), WHERE:([A].[balance]=100.00))
|--Index Seek(OBJECT:([BankingDB].[dbo].[customers].[nciCustomerNo] AS [C]), SEEK:([C].[customer_no]=
This StmtText column repeats the SQL statement in the first row of the column. Subsequent rows in the display, known as PLAN_ROWS, contain a description of the operation taking place. This column contains the physical operator and may or may not also contain the logical operator. So what are physical and logical operators? The physical operator describes the physical mechanism by which the operation was performed. In our example we can see physical operators such as Nested Loops, Table Scan, and Index Seek. Logical operators describe the relational operation being performed—in our example, an Inner Join. Often, there is no separate logical operator, since the logical operation results in a number of steps—each representing physical operations. In our example, there is no logical operator mentioned in the line that represents the Table Scan physical operation.
Other information is also often present in the StmtText column. In our example, we can see that the row containing the Index Seek physical operator also names the index in question— nciCustomerNo—and the column used in the predicate—customer_no—as well as the table name. The row containing the Nested Loops physical operator also specifies WITH
PREFETCH, which means that asynchronous read ahead is being utilized (see Chapter 5). The information in the StmtText column is also repeated in other columns, as we shall now see.
Note that the output is in the form of a hierarchical tree with the SQL statement itself at the top of the tree. I find that decoding the hierarchy can sometimes be confusing, but, again, as we shall see, the graphical query execution plan will help us here. It is often best, however, to start looking at the deepest level in the hierarchy. This represents the basic operations against tables and indexes, which together form the basic building blocks of the query execution plan. Other steps will utilize these basic steps until the result of the query is returned. To assist in understanding the hierarchy, the next set of columns lend a helping hand.
StmtId NodeId Parent PhysicalOp LogicalOp --- --- --- --- --- 17 1 0 NULL NULL
17 3 1 Bookmark Lookup Bookmark Lookup 17 5 3 Nested Loops Inner Join 17 7 5 Table Scan Table Scan
17 8 5 Index Seek Index Seek
The StmtId is a number that identifies the statement in the batch of SQL statements if there is more than one SQL statement in the batch. This groups all the steps together for the one statement. The NodeId is a number that identifies the step in the query execution plan, and the Parent is the node ID of the parent step. Using these numbers, the position of a step in the hierarchical tree can be ascertained. The PhysicalOp and LogicalOp columns contain the physical and logical operators as described above.
Argument --- 1
BOOKMARK:([Bmk1000]), OBJECT:([BankingDB].[dbo].[customers] AS [C]) OUTER REFERENCES:([A].[customer_no]) WITH PREFETCH
OBJECT:([BankingDB].[dbo].[accounts] AS [A]), WHERE:([A].[balance]=100.00) OBJECT:([BankingDB].[dbo].[customers].[nciCustomerNo] AS [C]),
OBJECT:([BankingDB].[dbo].[customers].[nciCustomerNo] AS [C]), SEEK:([C].[customer_no]=[A].[customer_no])
ORDERED FORWARD
This column displays extra information concerning the operation, as described previously. The next set of columns includes the values used by the operator; they are typically columns from a SELECT list or WHERE clause. Internal values may also be represented here. In our example, the * has been expanded to the actual list of columns.
DefinedValues --- NULL
[C].[customer_no], [C].[customer_fname], [C].[customer_lname], [C].[customer_notes]
NULL
[A].[account_no], [A].[customer_no], [A].[branch_no], [A].[balance], [A].[account_notes]
[Bmk1000]
Next we see columns that are concerned with the estimated cost of the query.
EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost --- --- --- --- --- 1988.1769 NULL NULL NULL 1.4232613
1988.1769 6.2500001E-3 2.1869945E-3 886 1.4230624 1988.1769 0.0 8.3105788E-3 445 1.4146254 1988.1769 0.60027075 0.01378925 435 1.22812 1.0 6.3284999E-3 7.9603E-5 19 0.1661949 The EstimateRows column contains the number of rows the query optimizer expects the operator to return. In our example, we are looking at 1,988 rows estimated for all the operators except the Index Seek. The 1,988 estimate comes from the fact that the query optimizer estimates that this number of Account table rows will have a balance of 100. The value of 1 from the index seek indicates that the query optimizer knows that for each row from the Accounts table a maximum of one row can be returned from the Customers table (it has a unique index on the customer_no column).
How many rows are actually returned? How many customer accounts have a balance of exactly 100? The answer in our database is, in fact, zero! The query optimizer estimate is very inaccurate. Why? We shall see shortly!
The EstimateIO column contains the estimated I/O cost for the operator. In our example, the cost estimates are small numbers, so what do the values represent? The numbers are weighted by some undocumented weighting factor. Microsoft does not publish the weighting factor, since they want the ability to adjust it to their heart's desire. This means that it is practically impossible to translate the EstimateIO value into logical reads. However, it is possible to compare these numbers with one another, and we know the lower the number the lower the cost.
The EstimateCPU column contains the estimated CPU cost for the operator. In our example, the cost estimates are again small numbers, and, again, the numbers are weighted by some undocumented weighting factor. This means that it is not possible to translate the
EstimateCPU value into CPU milliseconds. Again, it is possible to compare these numbers with one another, and, again, the lower the number the lower the cost. Using these two estimates we can easily see the most expensive operation in terms of I/O and CPU in a query. The AvgRowSize is the estimated average row size (in bytes) passing through the operator. In our example, rows from the Accounts table are estimated to be 435 bytes in length. The output of the Index Seek operator is an index entry (key plus pointer) of 19 bytes. Once the Customers table row has been retrieved from the data page (the Index Lookup) and joined with the Accounts table row, the combined size is estimated at 886 bytes.
The TotalSubtreeCost column contains the estimated total cost of the operator and the cost of all its children. This is derived from the EstimateIO and EstimateCPU columns, and, again, some mystery weighting factor is used. This number, though, represents a cost value that combines the I/O and CPU cost and is very useful when looking for the operation in a query that is using the lion's share of the query resource. The OutputList column represents a list of the columns that will be displayed by the query.
OutputList --- NULL
[C].[customer_no], [C].[customer_fname], [C].[customer_lname], [C].[customer_notes], [A].[account_no],
[A].[customer_no], [A].[branch_no], [A].[balance], [A].[account_notes] [Bmk1000], [A].[account_no], [A].[customer_no], [A].[branch_no],
[A].[balance], [A].[account_notes]
[A].[account_no], [A].[customer_no], [A].[branch_no], [A].[balance], [A].[account_notes]
[Bmk1000]
Warnings Type Parallel EstimateExecutions
--- ---- --- --- ---
NULL SELECT 0 NULL NULL PLAN_ROW 0 1.0 NULL PLAN_ROW 0 1.0 NO STATS:([accounts] [customer_no], PLAN_ROW 0 1.0 [accounts].[balance])
The Warnings column contains any warning messages issued by the query optimizer for the operation. In our example, the only operation to be associated with a warning is the Table Scan operation, where the Accounts table is being scanned looking for rows with a balance of 100. We shall look at this warning in the graphical query execution plan shortly, but for now let us just say that the query optimizer is telling us why the estimate of the number of rows returned is so inaccurate—can you guess what the warning means?
The Type column merely flags a row as being the parent row for the query—a SELECT, INSERT, UPDATE, or DELETE, for example, or a row representing an element of the query execution plan—PLAN_ROW.
The Parallel column contains a value of 0 or 1 specifying whether the operator can execute in parallel (1) or not (0).
The EstimateExecutions column is the estimated number of times the operator will execute during the query. In our example, the Table Scan operator will execute once. However, for each row in the Accounts table being scanned, the Customer table will be accessed (it is the inner table in a nested loops join). For this reason, the EstimateExecutions column for the Index Seek operator contains the value 1988.1769.
So, as we have seen, the SET SHOWPLAN_ALL statement produces a large amount of information concerning the query execution plan. As I've hinted at a number of times now, I feel this information is best displayed through the graphical query execution plan. Before we take a look at this there are more SET statements that are useful—so let's have a look at them.