Query Plan(04) – Basic Properties

Every operator has its own properties. Reading those properties can help understand the logics behind them. Let’s have a glance on a query plan of a simple query

create table #t(Value int) 
insert into #t (Value) values(1)
select * from #t

This is what you get from the last statement of previous batch. Data in the temp table is scanned and sent over to cient.Move your mouse over Table Scan operator. A yellow box will show up telling you some properties of the operator, like Estimated Cost, etc. Click on that operator then press F4, you will see a list of properties of this operator at your right in SSMS application. It tells you the details of the operator.
Actual Number of Rows: It’s the actual number rows returned by this operator. It will not be shown in the property list if the execution plan is an estimated plan. A query can use one thread or multiple threads to execute the operator where you can see the sub-property underneath. In this case, it only uses one thread. If there are multiple threads, you will see different number of rows being processed by each thread.
Actual Rebinds: Binding is basically connecting outputs from one operator as parameters of another operator. When the first set of paramters are no longer used and next set of values from the source operator comes an play, an Actual Rebinds take place. This property is not reliably populated all the time. If there are multiple threads, it will show number of Actual Rebinds for each thread.
Actual Rewinds: Records are processed one by one until the end. Some time, system needs to go back to the first record and re-go through the records. This is called rewind. Actual Rewinds means actual number of rewind happened for the query execution.
Defined Values: Values defined in this operator. Those are the definitions of the outputs.
Description: description of the operator.
Estimated CPU Cost: Estimated CPU time in second.
Estimated IO Cost: Estimated IO time in second.
Estimated Number of Execution: Number of times the operator is executed.
Estimated Operator Cost: Estimated time for this operator: CPU + IO.
Estimated Rebinds: Estimated rebinds.
Estimated Rewinds: Estimated rewinds.
Estimated Rowsize: Estimated size of a row. This is useful while SQL Server pre-allocate memory for the query.
Estimated Subtree Cost: Estimated cost for this operator plus all precedent operators.
ForcedIndex: Use indexes specified in the query by user. For example below, SQL Server will use IX_SalesOrderHeader_CustomerID index to lookup rows in the base table. select * from Sales.SalesOrderHeader with(index = [IX_SalesOrderHeader_CustomerID])
ForcedScan: When forcescan hint is specified. This hint is an undocumented hint. Please use it with caution. It will scan the clustered index or heap regardless of indexes.select * from Sales.SalesOrderHeader with(forcescan) where CustomerID = 11008
ForcedSeek: When forceseek hint is specified. This property is not shown on the picture right.
Logical Operation: The name of the logical operation.
NodeID: ID of the node, no real meaning.
NoExpandHint: NoExpand hint specified or not. NoExpand hint can be used when an index view is referenced in the query. It will tell the query optimizer to use the index of the view rather than expand the view and merge the logic of the view with the query.
Number of Execution: number of time the operator executed. If table A nested loop join table B where table A has 10 records, operator reading data in table B will be executed 10 times.
Object: The object operator accesses. There are few properties under this property.
Ordered: This indicates whether the set of the data output from this operator is ordered or not. The value will be true when desired output order same of operator output order, otherwise it will be false. select * from Sales.SalesOrderHeader , Ordered for this operator will be fase since there is no desired output order. select * from Sales.SalesOrderHeader order by SalesOrderID, Ordered for this query will be true because the order or clustered index scan is same as the order in ORDER BY.
Output List: list of output columns
Parallel: whether or not it’s a parallel operation.
Physical Operation: Name of the physical operation.
Table Cardinality: Cardinality of the table.

Leave a Comment

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.