Clustered Index Seek
Introduction
The Clustered Index Seek operator uses the structure of a clustered index to efficiently find either single rows (singleton seek) or specific subsets of rows (range seek). Because a clustered index always contains all columns in a table, a Clustered Index Seek is one of the most efficient ways for SQL Server to find single rows or small ranges, provided there is a filter that can be used efficiently.
The behavior of the Clustered Index Seek operator is in fact exactly the same as the behavior of the Index Seek 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 Seek and Index Seek is the range of index types that can be used, as denoted by the Storage subproperty of the Object property. Where an Index Seek supports both RowStore and MemoryOptimized indexes, a Clustered Index Seek operator can only target RowStore indexes. The simple reason for this difference is that neither the current version of SQL Server (2019) nor any of the older versions have support for memory-optimized clustered indexes, and it is not possible to seek in an index that cannot be created.
Visual appearance in execution plans
Depending on the tool being used, a Clustered Index Seek operator 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 Seek, please refer to the description of the algorithm of the Index Seek 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
Though a Clustered Index Seek does essentially the same as an Index Seek, 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 can always provide all the data that the optimizer needs in the rest of the execution plan. An Index Seek 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, so it may be needed to then also include a Nested Loops operator into either a Key Lookup or an RID Lookup operator to get all data. For a Clustered Index Seek, no additional lookup is ever needed.
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 Seek operator is already able to access 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 navigates an index that cannot exist, it is also immaterial whether or not the theoretical scenario is already supported or not. It simply never happens.
Singleton seek vs range seek
Because SQL Server by default creates a clustered index when a PRIMARY KEY is created, most clustered indexes are declared as unique. That results in a common misconception that clustered indexes are always created as UNIQUE indexes. This is not true, when explicitly creating a clustered index, the index can be declared to be not UNIQUE. In that case, a hidden extra column, called the uniqueifier, is added internally. This uniqueifier cannot be used in queries but can be used internally by SQL Server.
For a Clustered Index Seek, a Seek Keys specification is considered a singleton seek if both of the following conditions are met:
- The Seek Keys must not have a Range specification (which implies that it must have a Prefix specification);
- The Prefix specification needs to provide values for all columns in the index specification, so if the index is on e.g. three columns, all three must be included in the Prefix specification of the Seek Keys. If the clustered index is not declared as UNIQUE, then the Prefix specification also has to include the uniqueifier column.
Operator properties
The properties below are specific to the Clustered Index Seek 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 Seek operator are marked with a *.
(Note that most of these properties are exactly the same as for the Index Seek 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 Seek, 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 navigating the index. The higher the difference between this number and the Table Cardinality, the more effective the use of this Clustered Index Seek (as opposed to a Clustered Index Scan) is estimated to be. 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. On a Clustered Index Seek operator, it is therefore always false. |
| ForceSeek | This property is set to true if the query used a FORCESEEK hint to force the use of a seek operator even if the optimizer would rather use a scan 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 navigating the index. 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. Only available in execution plan plus run-time statistics. When the number of rows read is equal to zero, this property is omitted. |
| Object | This property lists the index that is being navigated by the Clustered Index Seek 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 always equal to True for a Clustered Index Seek. |
| Partitioned | This property is present and set to True when the index read by the Clustered Index Seek 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 Seek. Only rows for which the predicate evaluates to True are returned. When possible, the Clustered Index Seek operator will push this predicate into the storage engine to prevent extra roundtrips between the operator and the storage engine. Note that a Predicate on a Clustered Index Seek 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 better supports the search predicates. |
| Scan Direction | For range seeks, this property specifies whether the data has to be returned in normal index order (“FORWARD”) or reverse index order (“BACKWARD”). The BACKWARD option is not available for memory-optimized indexes. |
| Seek Predicates | A collection of one or more Seek Keys that are used to navigate the index and locate the rows to be read. See the main text for more information. |
| Table Cardinality | This property shows the number of rows in the index’s table when the plan was last compiled. |
Implicit properties
This table below lists the behavior of the implicit properties for the Clustered Index Seek operator.
(Note that most of these properties are exactly the same as for the Index Seek operator; they are repeated here for ease of use.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Clustered Index Seek operator supports row mode execution only. |
| Blocking | The Clustered Index Seek operator is non-blocking. |
| Memory requirement | The Clustered Index Seek operator does not have any special memory requirement. |
| Order-preserving | The Clustered Index Seek operator imposes an order, as specified in the Scan Direction property. If the Seek Predicates property specifies more than one Seek Keys specification, the optimizer always ensures that these are in the correct corresponding order. |
| Parallelism aware | When the Clustered Index Seek operator is used for range scans in a parallel section of an execution plan, it uses the “Parallel Page Supplier”. See Index Scan for more details. |
| Segment aware | The Clustered Index Seek operator is not segment aware. |