The last time when I wrote about ColumnsStore FAQ, one of the readers had an interesting question. The question was around how to identify the traits of using a column store inside an execution plan. The very thought got me thinking. I was thinking where to start. I remembered that ShowPlan XML was a great way to see some of these attributes. The very next question that came up, what should one look forward to inside a ShowPlan XML.
Showplan can be used to determine whether a columnstore index was used to process the query and whether batch processing was used. Three additional properties will be displayed when viewing Showplan XML, when viewing the tooltip displayed when hovering over the Index Scan icon in the graphical showplan, or when right clicking the graphical showplan and selecting properties.
Three Properties to Look Out
The first property is “Storage,” which will have two possible values: restore and column store. It is self-explanatory what the values mean. The value will be column store when the data is being accessed from the column store and will be restored when the data is accessible from the row-based table.
The other two properties are EstimatedExecutionMode and ActualExecutionMode. Both properties can have two values, either batch or row. The value row indicates the traditional processing, while batch indicates that values for multiple rows are being processed. The values for both determine how the SQL Server engine went about executing the actual query. Below table explains the same in simple words. In addition, the number of batches or rows passing through a given operator will be in statistics XML.
Estimated Vs Actual – Interpretation
| EstimatedExecutionMode | ActualExecutionMode | Interpretation |
| Row | Row | Same as current SQL Server behavior |
| Row | Batch | Not available in SQL Server 2012 |
| Batch | Batch | Query Engine successfully executed in batch mode |
| Batch | Row | Query Engine was unable to process in batch mode and fell back to row mode execution |
I am sure this will be a great start for folks who want to understand the nuances of working with SQL Server and ColumnStore index. The query processing engine is quite intelligent and am sure will evolve and more will get added to this. I have tried to overly simplify a complex topic which will help one and all.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
