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 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:

SQL Server Management Studio

Azure Data Studio

Plan Explorer

(until version 17.3)

(version 17.4 and up)

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 (2017 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.

(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 nameDescription
Defined ValuesFor 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 ReadThis 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.
Forced IndexThis property is set to true if the use of this index was forced through an index hint in the query.
ForceScanThis 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.
IndexKindThis represents the type of clustered index being scanned; this is always equal to Clustered for a Clustered Index Scan.
Note that this property is not exposed in the property list of SSMS, though the type of index is showed in parentheses below the operator. The property can be accessed through the execution plan XML.
Logical OperationAlways equal to Clustered Index Scan.
NoExpandHintThis 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 ReadThis 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.
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.
Actual execution plans only.
ObjectThe 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.
OrderedThis 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.
PredicateWhen 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.
StorageThis property determines the type of index being scanned. (Note that this could also be determined by using the DMVs based on the Object property). Possible values are RowStore and ColumnStore.
Table CardinalityThis 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 nameDescription
Batch Mode enabledThe Clustered Index Scan operator supports both row mode and batch mode execution for both RowStore and ColumnStore indexes.
BlockingThe Clustered Index Scan operator is non-blocking.
Memory requirementThe Clustered Index Scan operator does not have any special memory requirement.
Order-preservingThe 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 awareWhen 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 awareThe Clustered Index Scan operator is not segment aware.
Menu

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close