Clustered Index Scan
Introduction
The Clustered Index Scan operator is used to read all or most data from a clustered index. In combination with a Top operator, it can also be used to read the first few rows according to the innate order of a clustered index, or to read just a few rows from a table when data order is irrelevant and there is no nonclustered index that covers all required columns.
The behavior of the Clustered Index Scan operator is in fact exactly the same as the behavior of the Index Scan operator, with only a very few differences as noted below. Though these two operators do have different names, not only in the graphical execution plan but also in the underlying XML, I suspect that in reality they are both using the same internal logic and not a copy of it.
One of the differences between Clustered Index Scan and Index Scan is the range of index types that can be used, as denoted by the Storage subproperty of the Object property. Where an Index Scan supports RowStore, ColumnStore, and MemoryOptimized indexes, a Clustered Index Scan operator can only target RowStore and ColumnStore indexes. The simple reason for this difference is that neither the current version of SQL Server (2017) nor any of the older versions have support for memory-optimized clustered indexes, and it is not possible to scan an index that cannot be created.
A Clustered Index Scan operator with Storage equal to “ColumnStore” is represented in SSMS (and many other tools) by a different icon and, with a different name. Only the underlying XML reveals that this is merely a presentation difference, and that the same operator is actually used. For this reason, this page describes the behavior of ALL types of Clustered Index Scan, including those represented as Columnstore Index Scan in the graphical execution plan.
Visual appearance in execution plans
Depending on the tool being used, a Clustered Index Scan operator with the Storage property not equal to ColumnStore is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
 |
Algorithm
For a full description of the algorithm of the Clustered Index Scan, please refer to the description of the algorithm of the Index Scan operator. As stated above, the two operators are so similar that I suspect that they actually are just two names for the same code units in the SQL Server engine.
Specific behavior, by index type
Though a Clustered Index Scan does essentially the same as an Index Scan, there can be some subtle behavior differences, depending on the index type. These are called out in the paragraphs below.
RowStore
For RowStore indexes, clustered and nonclustered indexes are built according to the same basic structure, known as a B-tree. There are some subtle differences regarding how exactly the bits are laid out on the page, but these are far beyond the scope of this website.
The basic structure of one root page, zero or more levels of intermediate index pages, and lots of leaf pages is equal for clustered and nonclustered indexes. The only difference that has some significance for understanding execution plans is that a clustered rowstore index includes all the columns of a table in the leaf pages, whereas a nonclustered rowstore index only includes the indexed columns, the columns that are indexed for the table’s clustered index (if any), and any included columns. (Both types of index may also include hidden internal columns, such as a uniqueifier or the RID; additional details of this are not in scope for this website).
Because of this difference, a clustered index will almost always include more data per row in its leaf pages. This results in less rows per page, and hence more pages. Sometimes this in turn can even result in extra levels in the B-tree structure. The result of this is that when a Clustered Index Scan and an Index Scan read the same number of rows from (an index on) the same table, the Clustered Index Scan will usually result in more logical pages being read, and hence a higher cost.
The flip side to this is that a Clustered Index can always provide all the data that the optimizer needs in the rest of the execution plan. An Index Scan can only provide all the data if the set of columns needed for the query is a subset of the set of columns included on the leaf pages of the nonclustered index being scanned. If the optimizer needs to read a set of columns that are not all included in any available nonclustered, it often uses a Clustered Index Scan instead, because the alternative (which would typically be to add a Nested Loops operator and either a Key Lookup or an RID Lookup operator) adds too much overhead.
ColumnStore
The differences between clustered and nonclustered columnstore indexes are even less than they are for clustered and nonclustered rowstore indexes. The only actual differences are in the syntax to create them, and in what other indexes are allowed on the table.
A clustered columnstore index always includes all columns of the table, whereas a nonclustered columnstore index only includes the specified columns – though typically these will be all or most of the table’s columns anyway. However, since a columnstore index allows data to be accessed by individual columns, this does not affect a clustered or nonclustered columnstore index scan – both of these would still only access the segments created for the columns in the Output List property and ignore the rest. The only situation where scanning a clustered columnstore index can be slightly more expensive than scanning a nonclustered columnstore index is when processing the deltastore rowgroups. These are still in B-tree format, so here the clustered index might include more columns (and hence less rows) on each page then the nonclustered version.
MemoryOptimized
At the time of writing, none of the existing SQL Server versions (2019 and below) have support for clustered memory-optimized indexes. It is theoretically possible that the Clustered Index Scan operator is already able to scan memory-optimized clustered indexes, but since those indexes cannot be created, it is impossible to verify this without source code access.
And since the optimizer will never generate an execution plan that scans an index that cannot exist, it is also immaterial whether or not the theoretical scenario is already supported or not. It simply never happens.
Operator properties
The properties below are specific to the Clustered Index Scan operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Clustered Index Scan operator are marked with a *.
(Note that most of these properties are exactly the same as for the Index Scan operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| Actual Partition Count | This is the number of partitions within a partitioned table that were accessed at least once during execution. Only available in execution plan plus run-time statistics and only when the Partitioned property is included and set to True. |
| Actual Partitions Accessed | This property lists all the partitions within a partitioned table that were accessed at least once during execution. In the execution plan XML, this is actually stored as one or more PartitionRange elements, each with a Start and End property. In tools such as SSMS, this is displayed as a semicolon separated list of ranges, where every single-partition range is displayed as a number instead. So for instance, “2-4;7;10-11” represents that partitions 2, 3, 4, 7, 10, and 11 were accessed at least once. Only available in execution plan plus run-time statistics and only when the Partitioned property is included and set to True. |
| Defined Values * | For a Clustered Index Scan, this property lists the columns read from the index and returned to the calling operator. It is therefore the same as the Output List property. |
| Estimated Number of Rows to be Read | This is an estimate of the number of rows that will be read by the operator as it is scanning the index. In most cases this will be equal to the estimated total number of rows in the index, except when there are other operators in the execution plan that can result in this operator not being called anymore before the end of the data is reached. The difference between this property and the Estimated Number of Rows property represents the number of rows that is estimated to be read but not returned due to the Predicate property. Note that this property is computed without taking the effect of a row goal (if any) into account. If the EstimateRowsWithoutRowGoal property is present, then you need to compare that, instead of the Estimated Number of Rows, to the Estimated Number of Rows to be Read property for a proper assessment of the estimated selectivity of the Predicate. |
| Forced Index | This property is set to true if the use of this index was forced through an index hint in the query. |
| ForceScan | This property is set to true if the query used a FORCESCAN hint to force the use of a scan operator even if the optimizer would rather use a seek operator. |
| NoExpandHint | This property is set to true if the NOEXPAND hint was used in the query to force the optimizer to use indexes on an indexed view. |
| Number of Rows Read | This is the number of rows that was read by the operator as it was scanning the index. In most cases this will be equal to the total number of rows in the index, except when there were other operators in the execution plan that resulted in this operator not being called anymore before the end of the data was reached. In a parallel execution plan, this property shows a breakdown of rows on each individual thread. The difference between this property and the Actual Number of Rows property represents the number of rows that was read but not returned due to the Predicate property. Note that this property may report incorrect values when the Predicate property contains a PROBE. Only available in execution plan plus run-time statistics. When the actual number of rows read is equal to zero, this property is omitted. |
| Object | The Object property lists the index that is being scanned by the Clustered Index Scan operator, using four part naming (database, schema, table, index) and optionally followed by a table alias. The subproperties of the Object property represent the alias and the four name parts separately, but also include two additional properties:
|
| Ordered | This property is set to True by the optimizer when other operators in the execution plan require that the data is returned in an order that matches the index’s key columns. When set to False, the optimizer doesn’t care about the order of rows returned, and the Clustered Index Scan is free to determine, sometimes at run time, whether or not to use an access method that follows this order. See the main text for more details on this. |
| Partitioned | This property is present and set to True when the index read by the Clustered Index Scan is a partitioned index. |
| Predicate | When present, this property defines a logical test to be applied to all rows that are read by the Clustered Index Scan. Only rows for which the predicate evaluates to True are returned. When possible, the Clustered Index Scan operator will push this predicate into the storage engine to prevent extra roundtrips between the operator and the storage engine. Note that, except for columnstore indexes when rowgroup elimination can apply, a Predicate on a Clustered Index Scan does not reduce the amount of rows that is touched by the operator. The difference between the Actual Number of Rows and the Number of Rows Read property (or their estimated counterparts) shows how many rows were read but not returned. This can be used to gauge how useful an index would be that supports the predicate. |
| Seek Predicates | The Seek Predicates property can only appear on a Clustered Index Scan operator if the Object scanned is a partitioned index, and it only supports equality or inequality conditions on an internal column called PtnIdnnnn, where nnnn is a 4-digit number that is unique within the execution plan. This implements partition elimination, where only the indicated partition(s) of the index are scanned. |
| Table Cardinality | This property shows the number of rows in the index’s table when the plan was last compiled. Note that this is true even for a filtered index, so it cannot be used as an indication of how many rows are read if the Index Scan is allowed to finish. For this, use the Estimated Number of Rows to be Read property instead. |
Implicit properties
This table below lists the behavior of the implicit properties for the Clustered Index Scan operator.
(Note that most of these properties are exactly the same as for the Index Scan operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| Batch Mode enabled | The Clustered Index Scan operator supports both row mode and batch mode execution for both RowStore and ColumnStore indexes. |
| Blocking | The Clustered Index Scan operator is non-blocking. |
| Memory requirement | The Clustered Index Scan operator does not have any special memory requirement. |
| Order-preserving | The Clustered Index Scan operator imposes an order if the Ordered property is set to true. If Ordered is set to false, then the operator is assumed to return data in an unreliable order, even though in some cases the actual access methods happen to always return data ordered. |
| Parallelism aware | When the Clustered Index Scan operator is running in a parallel section of an execution plan, it uses the “Parallel Page Supplier” method described here. |
| Segment aware | The Clustered Index Scan operator is not segment aware. |