Columnstore Index Scan

Introduction

The Columnstore Index Scan is not really an actual operator. You can encounter it in graphical execution plans in SSMS (and other tools), but if you look at the underlying XML of the execution plan, you will see that it is either an Index Scan or a Clustered Index Scan operator.

SQL Server currently supports three types of index storage: rowstore, columnstore, and memory-optimized. Indexes of each of those types can be the target of an Index Scan or Clustered Index Scan, as indicated by the Storage property. When the Storage property is RowStore or MemoryOptimized, then the normal icon for (clustered) index scan is use, but when Storage is ColumnStore than SSMS (and other tools) choose to show a different icon instead.

While clustered and nonclustered indexes each have their own operator for rowstore storage, these types are lumped together as a single icon and operator name in SSMS. Whether the targeted columnstore index is a clustered or a nonclustered index is only visible in parentheses behind the operator name (Clustered vs Nonclustered), or in the Logical Operation property (Clustered Index Scan vs Index Scan).

Visual appearance in execution plans

Depending on the tool being used, an Index Scan or Clustered Index Scan operator with the Storage property 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

The Columnstore Index Scan is not a real operator but merely a visual indicator that an Index Scan or Clustered Index Scan operator is targeting a columnstore index. Please check the appropriate pages for a full description of the algorithms of those operators.

Operator properties

The properties below are specific to the Columnstore 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 Columnstore 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.
ForceSeekThis 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. On a Columnstore Index Scan operator, it is therefore always false.
IndexKindThis represents the type of clustered index being scanned; for a Columnstore Index Scan this can be either NonClustered or Clustered.
Note that this property is not exposed in the property list of SSMS, though the type of index is showed in parentheses after the operator. The property can be accessed through the execution plan XML.
Logical OperationThis is Index Scan when scanning a nonclustered columnstore index, or Clustered Index Scan when scanning a clustered columnstore index.
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 Columnstore Index Scan operator, using four part naming (database, schema, table, index) and optionally followed by a table alias.
OrderedFor Columnstore Index Scan, this is always False.
Physical OperationThough the Physical Operation property is either Index Scan or Clustered Index Scan (as to be expected) in the actual XML of the execution plan, it is displayed as Columnstore Index Scan by SSMS.
Azure Data Studio and SentryOne Plan Explorer do expose the Physical Operation as it is stored in the XML.
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 Columnstore Index Scan will push this predicate into the storage engine to enable rowgroup elimination, which can reduce the amount of IO done, and to reduce roundtrips between the operator and the storage engine.
If the Predicate does not enable any rowgroups to be eliminated, then it will 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.
StorageThis property determines the type of index being scanned. For a Columnstore Index Scan, this is always equal to 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 Columnstore 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 Columnstore Index Scan operator supports both row mode and batch mode execution.
BlockingThe Columnstore Index Scan operator is non-blocking.
Memory requirementThe Columnstore Index Scan operator does not have any special memory requirement.
Order-preservingThe Columnstore Index Scan operator returns data in an unreliable order.
Parallelism awareWhen the Columnstore Index Scan operator is running in a parallel section of an execution plan, it uses the “Parallel Page Supplier” method described here.
Segment awareThe Columnstore Index Scan operator is not segment aware.

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