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 subproperty of the Object 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:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
(shown as Index Scan or Clustered Index Scan) |
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. Properties that are included on the Common properties page but are also included below for their specific meaning for the Columnstore 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 I/O Statistics * | For a Columnstore Index Scan, only the Actual Scans subproperty of the Actual I/O Statistics is correct. All other metrics always show zero. Please refer to the SET STATISTICS IO output to see the actual amount of I/O done by the Columnstore Index Scan operator. |
| Actual Number of Rows * | For a Columnstore Index Scan on a nonclustered columnstore index, the Actual Number of Rows may be shown incorrectly. Recently deleted rows may be in a “delete buffer”, and not yet in the deleted bitmap. These rows are then incorrectly included in the Actual Number of Rows. Read this post and the comments below if for more information. (Note that the post specifically uses a filtered nonclustered columnstore index, but unfiltered nonclustered columnstore indexes expose the same behavior). |
| Actual Number of Rows for All Executions * | For a Columnstore Index Scan on a nonclustered columnstore index, the Actual Number of Rows for All Executions may be shown incorrectly. Recently deleted rows may be in a “delete buffer”, and not yet in the deleted bitmap. These rows are then incorrectly included in the Actual Number of Rows for All Executions . Read this post and the comments below if for more information. (Note that the post specifically uses a filtered nonclustered columnstore index, but unfiltered nonclustered columnstore indexes expose the same behavior). |
| 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. However, due to a bug (?), this value is reported as 0 (zero) when all partitions of the columnstore index were accessed. |
| 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. However, due to a bug (?), this property is not shown when all partitions of the columnstore index were accessed. |
| Defined Values * | For 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 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. |
| 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. On a Columnstore Index Scan operator, it is therefore always false. |
| Logical Operation * | This is Index Scan when scanning a nonclustered columnstore index, or Clustered Index Scan when scanning a clustered columnstore index. |
| 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. |
| Object | The 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. The subproperties of the Object property represent the alias and the four name parts separately, but also include two additional properties:
|
| Ordered | For Columnstore Index Scan, this is always False. |
| Partitioned | This property is present and set to True when the index read by the Columnstore Index Scan is a partitioned index. |
| Physical Operation | Though 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. |
| Predicate | When present, this property defines a logical test to be applied to all rows that are read by the Columnstore 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 Estimated Number of Rows and the Estimated Number of Rows to be Read property shows how many rows are expected to be read, but not returned. Unlike other scan operators, a Columnstore Index Scan does not return the actual Number of Rows Read property, so even in an execution plan plus run-time statistics, only the estimated values are available. |
| Seek Predicates | The Seek Predicates property can only appear on a Columnstore Index Scan operator if the Object scanned is a partitioned columnstore 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 columnstore 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 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 name | Description |
|---|---|
| Batch Mode enabled | The Columnstore Index Scan operator supports both row mode and batch mode execution. |
| Blocking | The Columnstore Index Scan operator is non-blocking. |
| Memory requirement | The Columnstore Index Scan operator does not have any special memory requirement. |
| Order-preserving | The Columnstore Index Scan operator returns data in an unreliable order. |
| Parallelism aware | When 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 aware | The Columnstore Index Scan operator is not segment aware. |