SQL SERVER – Extended Events to watch for using ColumnStore Index

In the recent past, I have written a number of posts around ColumnStore Index and how they function. Some of the nuances of working with ColumnStore Indexes are available in this blog for reference. I have also written a few posts around Extended Events. One of my DBA friends pinged me to check if there were any way to use Profiler to see how ColumnStore Indexes worked. Obviously, there was nothing much of help I could offer because there were actually none in reality.

I recommended him to start looking into Extended Events – immediately he jumped to ask what are some of the events he can look out for when working with ColumnStore indexes. I was taken aback and was trying to search for the same in this blog but couldn’t find one. I spent some 15 mins on the Extended Events website to check on the same and I wrote back to him the following. I personally felt these were good notes to have when working with ColumnStore Indexes and hence thought to put the same here too.

Query Execution – batch_hash_table_build_bailout: Occurs when the batch operation to build a hash table encounters a low memory condition and switches to row mode operation. Use this event to identify performance issues due to low memory condition.

For Storage Engine related events:

We will not talk about how the storage engine behaves with ColumnStore indexes, but we will try to look at the events this time. I will surely write about the storage part in a later blog post.

• column_store_object_pool_hit: Occurs when a look up in the column store object pool, finds the object in the pool.
• column_store_object_pool_miss: Occurs when a look up into the column store object pool cannot find the object in the pool.
• column_store_segment_eliminate: Occurs when a filter eliminates column store segment during scan.
• column_store_rowgroup_readahead_issued: Occurs when we issue read-ahead for a rowgroup during a scan on a column store.
• column_store_rowgroup_read_issued: Occurs when we issue read for a rowgroup during a scan on a column store.

Expression Services – expression_compile_stop_batch_processing: Occurs when an expression is not natively supported in batch processing mode and a wrapper of row-by-row evaluation is used.

I personally felt this was a good start to working with Extended Events and ColumnStore indexes to start with. I have not written any of the memory related components. But that will be for a future post. Do let me know if you ever used any of these and what was the use case for the same. Let me know via your comments, please.

Meanwhile, please go to this blog post where I explain how extended events, help with finding long running queries.

Reference: Pinal Dave (https://blog.sqlauthority.com)