Continuation from the previous 81 parts, the whole series can be found at http://www.nikoport.com/columnstore/.

Previously, I have blogged about about the Extended Events in SQL Server 2014 3 times: Columnstore Indexes – part 44 (“Monitoring with Extended Events”) served as an introduction post, the Columnstore Indexes – part 47 (“Practical Monitoring with Extended Events”) lead into the usage details of those, and once the development team has added a new Extended Event for SQL Server 2014 Service Pack1 in Columnstore Indexes – part 52 (“What’s new for Columnstore XE in SQL Server 2014 SP1”).

This blog post is dedicated to the state of the Extended Events in the upcoming SQL Server 2016. While at the moment of this blog post writing the latest version available is the Release Candidate 1, I do not expect a lot of changes right now before the RTM.

In SQL Server 2014 we have had 18 Extended Events and with Service Pack 1 we have received 1 more to be a total of 19 Extended Events for studying the Columnstore Indexes and the Batch Mode processing.

In SQL Server 2016 that number has been greatly increased – there are whooping 61 Extended Events, that will give us an important insight into the Columnstore Indexes.

Even more important, Sunil & his team have given an own category inside the Extended Events – a category that is named Columnstore, which will ease the search for the basic columnstore events. Be aware though not all Extended Events related to Columnstore Indexes are included in that category – even including all channels will give you 41 Extended Events, while hiding the other 20 Extended Events, which are sometimes not categorised at all and at other times are stored under different categories, such as Execution or Error, for example. I believe the reason behind not changing the old Extended Events category is quite simple – Microsoft always looks for avoiding breaking existing applications.

To see all the extended events at once, the best way is to use a T-SQL query that is listed below:

select p.name AS package_name,
       o.name AS source_name,
       o.description
from sys.dm_xe_packages AS p
inner join sys.dm_xe_objects AS o 
     on p.guid = o.package_guid
where (p.capabilities IS NULL OR p.capabilities & 1 = 0)
   and (o.capabilities IS NULL OR o.capabilities & 1 = 0)
   and o.object_type = 'event' 
   and
   (
      p.description like '%column_store%' or p.description like '%columnstore%' or
  o.description like '%column_store%' or o.description like '%columnstore%' or
  o.name like '%column_store%' or o.name like '%columnstore%' 
  or o.name like '%batch%' or o.description like '%batch%' or p.description like '%batch%'
  or o.name like '%dictionary%' or o.description like '%dictionary%' or p.description like '%dictionary%'  
   )
   and o.name not in ('sql_batch_completed','sql_batch_starting','sql_statement_recompile','plan_guide_successful','plan_guide_unsuccessful','uncached_sql_batch_statistics')
   and o.name not in ('stretch_sp_migration_get_batch_id','stretch_table_row_migration_event','stretch_table_row_unmigration_event','stretch_table_data_reconciliation_event','stretch_table_row_migration_results_event','stretch_table_row_unmigration_results_event','stretch_table_data_reconciliation_results_event')
   and o.name not in ('sql_batch_starting_aggregate','sql_batch_starting_aggregate_xdb')
   and o.name not in ('query_execution_wait_syncpoint')
   and o.name not in ('check_batch_contents','check_batch_execution_complete')
order by source_name;

After looking at them for some time, I have decided to group them for better understanding and visibility. As for the 2014 Edition of SQL Server, there are good old 3 categories that existed before with new events (Tuple Mover, Columnstore Processing, Batch Mode), but there are definitely new categories which will allow to observer the new functionality (Columnstore Internal Object Operations & Errors), plus an own category for the obsolete & removed Extended Events (Removed Exteded Events).

Let’s go over the each of the categories, before diving into the details:

1. Columnstore Index Creation – in SQL Server 2016 there were no new extended events for this category directly, but the additions for the Columnstore Internal Object Operations, Columnstore Processing & Tuple Mover categories will provide better insight on the Index Creation process.

(clustered_columnstore_index_rebuild,

column_store_index_build_low_memory,

column_store_index_build_throttle,

column_store_index_build_process_segment)

2. Batch Mode – the category for analysing Batch Execution Mode, the real speed behind the Columnstore Indexes. This category contains 8 Extended Events with 2 new ones that were not available in SQL Server 2014.

(batch_hash_join_separate_hash_column,

batch_hash_table_build_bailout,

expression_compile_stop_batch_processing,

query_execution_batch_filter — NEW,

query_execution_batch_hash_aggregation_finished,

query_execution_batch_hash_children_reversed,

query_execution_batch_hash_join_spilled,

query_execution_batch_spill_started — NEW)

3. Tuple Mover – the category that is serving for observing the Tuple Mover behaviour. Tuple Mover is a process that converts uncompressed Row Groups into compressed ones. It is being executed automatically on 5 minutes basis or it can be invoked through the ALTER INDEX … REORGANIZE command.

In SQL Server 2014 there were just 2 Extended events, but in SQL Server 2016 this category has expanded with 14 additional Extended events, making the total number equal to 16!

(columnstore_compression_delay_disqualified_rowgroup — NEW,

columnstore_delete_buffer_flush_failed — NEW,

columnstore_delete_buffer_state_transition — NEW,

columnstore_delete_buffer_closed_rowgroup_with_generationid_found — NEW,

columnstore_delta_rowgroup_closed — NEW,

columnstore_index_reorg_failed — NEW,

columnstore_migration_commit — NEW,

columnstore_no_rowgroup_qualified_for_merge — NEW,

columnstore_rowgroup_cleanup — NEW,

columnstore_rowgroup_compressed — NEW,

columnstore_rowgroup_merge_complete — NEW,

columnstore_rowgroup_merge_failed — NEW,

columnstore_rowgroup_merge_start — NEW,

columnstore_skip_removing_tombtsone_rowgroup — NEW,

columnstore_tuple_mover_begin_compress,

columnstore_tuple_mover_begin_delete_buffer_flush — NEW,

columnstore_tuple_mover_compression_stats — NEW,

columnstore_tuple_mover_delete_buffer_flush_requirements_not_met — NEW,

columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met — NEW,

columnstore_tuple_mover_delete_buffer_truncate_timed_out — NEW,

columnstore_tuple_mover_delete_buffer_truncated — NEW,

columnstore_tuple_mover_delete_buffers_swapped — NEW,

columnstore_tuple_mover_end_compress,

columnstore_tuple_mover_end_delete_buffer_flush — NEW,

columnstore_tuple_mover_met_requirements_for_delete_buffer_flush — NEW,

columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate — NEW,

columnstore_x_dbfl_acquired — NEW )

4. Columnstore Internal Object Operations – this brand new category focuses on the internal operations for the Row Groups (Index Merging, operations with Tombstone Row Groups, and of course Delta-Stores), Segments and Dictionaries. This category contains just 7 Extended Event but these new events are something that will blow your mind. In a lot of cases you will find using these new Extended Events – because diving into Tuple Mover & Index build processes, has never been so easy until now. As for the specifics for the Dictionaries – we are able to see the exact operation taken place on the Columnstore Dictionaries, the area that was closed in both previous SQL Server versions.

(columnstore_rowgroup_operation — NEW,

columnstore_column_segment_operation – NEW,

columnstore_create_dictionary — NEW,

columnstore_create_segment — NEW,

columnstore_delete_dictionary — NEW,

columnstore_delete_segment — NEW,

columnstore_dictionary_operation — NEW)

5. Columnstore Processing – from a simple category with just 3 events in SQL Server 2014, in SQL Server 2016 I see that this was category has been bumped up with additional 6 new events, making the total number equal to 9 Extended Events. The principal focus of this query is on the query processing with Segment & Deleted Buffer eliminations, plus 2 new exciting events, allowing to dive into the filters and their applications.

(column_store_segment_eliminate,

column_store_rowgroup_readahead_issued,

column_store_rowgroup_read_issued,

column_store_expression_filter_apply — NEW,

column_store_expression_filter_bitmap_set — NEW,

column_store_rowgroup_skip_delete_buffer — NEW,

query_execution_column_store_rowgroup_scan_finished — NEW,

query_execution_column_store_segment_scan_finished — NEW,

query_execution_column_store_segment_scan_started — NEW )

6. Columnstore Object Pool – this category suffered no visible changes in SQL Server 2016. We are still able to investigate which Columnstore Objects are in the Columnstore Object Pool and which are not. There was an addition for the information on the new structures, related to all 3 Columnstore Indexes Types (Index Mapping, Deleted Buffer & Deleted Table), but there is nothing too much exciting to report on.

(column_store_object_pool_miss,

column_store_object_pool_hit)

6. Errors – This new Category which is catching all possible exceptions for the Columnstore Indexes and can be used for forwarding to Microsoft Support. This Extended Event can be interesting for anyone looking deeper into the engine.

columnstore_log_exception — NEW

5. Removed Exteded Events – The name of this category is quite clear: these are the events that were totally removed from the SQL Server 2016:
column_store_code_coverage. That event was most probably kept inside the SQL Server by a mistake, it allowed seeing things which are mostly making no sense for anyone that is not developing or debugging on a very deep level the Columnstore Indexes.

Descriptions for the new Extended Events

query_execution_batch_filter – Occurs when batch processing filters one batch using expression services. This is a very internal event, providing you with information on sub-segment elimination, more precisely on every batch that has managed to eliminate the output rows. For a regular user, this is rather less-amusing event, but for anyone interested in the internals of the batch mode – this provide detailed information for every thread and every batch, allowing to discover a little bit more about the engine.

query_execution_batch_spill_started – Occurs when batch operator runs out of granted memory and initiates spilling to disk of another partition of in-memory data. A great event that will provide you with the information on the batch spill, once it has started. Very useful to discover the lack of memory for the executed queries.

columnstore_delete_buffer_flush_failed – Occurs when Columnstore delete buffer flush failed. This one is rather a diagnostic event, useful for troubleshooting Deleted Buffer Flush errors.

columnstore_delete_buffer_state_transition – Occurs when closed delete buffer state changes. This allows you to monitor the situation when the data from the Deleted buffer is being synchronised with the Deleted Bitmap and the swap of the Deleted Buffer takes place.

columnstore_migration_commit – “Occurs when ‘prepareCallback’ has been called for a data migration transaction, and the transaction has successfully committed.” In English this means that this Extended Event kicks off once the InMemory Columnstore migration process – either inwards to Compressed Row Groups or to Tail Row Group is being started. For compression of the rows this is an equivalent process of the Tuple Mover work.

columnstore_tuple_mover_begin_delete_buffer_flush – Occurs when Columnstore tuple mover started flushing a delete buffer. Useful for migration of the Tuple Mover (Alter Index Reorganize) operations.

columnstore_tuple_mover_compression_stats – Provides statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc. Very interesting event, very useful for the monitoring of the Columnstore Indexes behaviour.

columnstore_tuple_mover_delete_buffer_flush_requirements_not_met – Occurs when column store tuple mover was not able to acquire required locks for flushing a delete buffer.

columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met – Occurs when column store tuple mover was not able to acquire required locks for truncating a delete buffer.

columnstore_tuple_mover_delete_buffer_truncate_timed_out – Columnstore tuple mover truncate operation timed out due to an active snapshot transaction.

columnstore_tuple_mover_delete_buffer_truncated – Columnstore tuple mover truncated delete buffer.

columnstore_tuple_mover_delete_buffers_swapped – Columnstore tuple mover swapped delete buffers.

columnstore_tuple_mover_end_delete_buffer_flush – Columnstore tuple mover completed flushing a delete buffer.

columnstore_tuple_mover_met_requirements_for_delete_buffer_flush – Occurs when column store tuple mover has acquired required locks and is ready to start flushing a delete buffer.

columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate – Occurs when column store tuple mover has acquired required locks and is ready to start truncating a delete buffer.

columnstore_x_dbfl_acquired – Occurs when an X (Exclusive) Delete Buffer Flush Lock is acquired.

columnstore_delete_buffer_closed_rowgroup_with_generationid_found – This event occurs when Deleted Buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.

columnstore_compression_delay_disqualified_rowgroup – Occurs when a rowgroup is not compressed because it does not satisfy the compression delay condition. Delay compression is described in Columnstore Indexes – part 76 (“Compression Delay”)

columnstore_delta_rowgroup_closed – Occurs when a Delta Row Group was closed. After that moment this Row Group is ready to be compressed by the Tuple Mover.

columnstore_index_reorg_failed – Occurs when an Index Reorganize operation has failed. Interesting here are the reasons for the failure, so this event is to be used in conjunction with other Extended Events, such as columnstore_no_rowgroup_qualified_for_merge & columnstore_log_exception between others.

columnstore_no_rowgroup_qualified_for_merge – Occurs when a user invoked a ALTER INDEX REORGANIZE command but based on the policy, no rowgroup qualified. An important event to monitor to see if the Tuple Mover works as expected and to see if we are trying to reorganise to often without a need for it.

columnstore_rowgroup_cleanup – Occurs when a rowgroup has been cleaned up and the corresponding entry has been removed from the row group info table. This happens if we have deleted the data in the Row Group completely.

columnstore_rowgroup_compressed – Occurs when a compressed rowgroup was created. This an more limited alternative event for the likes of columnstore_rowgroup_operation

columnstore_rowgroup_merge_start – Occurs when MERGE operation started merging columnstore rowgroups together.

columnstore_rowgroup_merge_complete – Occurs when a MERGE operation (ALTER INDEX REORGANIZE, Tuple Mover) completed merging columnstore rowgroups together. This is a good event to monitor for understanding the effectiveness of the reorganisation policies. This is a subset of the Extended Event columnstore_rowgroup_operation.

columnstore_rowgroup_merge_failed – Occurs when a MERGE operation started merging columnstore rowgroups but failed in the process. This can happen when in parallel to the Merge process, we are deleting data from the merged Row Groups, for example.

columnstore_skip_removing_tombtsone_rowgroup – Occurs when engine skips removing tombstone rowgroup as active scaner might need it. This can happen on busier system that are getting a lot of parallel queries, and serves as a good explanation for the Tombstone Row Groups existence.

columnstore_rowgroup_operation – Occurs when a metadata operation occurred on a columnstore Row Group. This is an absolutely fantastic event which is for me the best addition to the Extended Events in SQL Server 2016. With information on operation status with likes such as ROWGROUP_ADD, ROWGROUP_UPDATE, ROWGROUP_DELETE, ROWGROUP_MERGE_START, ROWGROUP_MERGE_END and respective state of the Row Group such as OPEN, CLOSED, COMPRESSED, TOMBSTONE & INVISIBLE – you know that you can have a complete of what’s going on for your Columnstore Index on the Row Groups level.

Love it !

columnstore_column_segment_operation – Occurs when a metadata operation occurred on a columnstore column segment. Very similar to the previous extended event for the Row Groups (columnstore_rowgroup_operation), this one gives you info on the Segment level.

columnstore_dictionary_operation – A metadata operation occurred on a columnstore dictionary. The third of the great additions for SQL Server 2016. Another great operation overview Extended Event, this time focused on the Columnstore Dictionaries.

columnstore_create_dictionary – Occurs when a segment has been created and an entry has been added to the segments table. Very useful in understanding the type of data you are compressing (if it can have dictionaries, a good sign that this even takes place).

With event fields pointing to the involved Database, Table, Column, Compression, Dictionary Type details if its an InMemory or Disk-Based Columnstore, number of entries (as seen in the DMV sys.column_store_dictionaries), there is also some additional information on the dictionary flags for those who are interested in the internals.

columnstore_create_segment – Occurs when a segment has been created and an entry has been added to the segments table. A good way to monitor progress of the big Columnstore Index creation and problem determination. This is a great new Extended Event with all the information hat you will have in the sys.column_store_segments DMV, plus details if the object is in memory.

columnstore_delete_dictionary – Occurs when a dictionary has been removed and the corresponding entry has been removed from the dictionaries table.

columnstore_delete_segment – Occurs when a segment has been removed and the corresponding entry has been removed from the segments table.

column_store_expression_filter_apply – Occurs when an expression bitmap filter was applied on a rowgroup column batch.

column_store_expression_filter_bitmap_set – Occurs when an expression bitmap filter was set on a rowgroup column at rowgroup compile time.

column_store_rowgroup_skip_delete_buffer – Occurs when column store row group skips delete buffer check during a scan. This condition applies only to the Nonclustered Columnstore Index, since it is the only Columnstore Index with such a structure.

query_execution_column_store_rowgroup_scan_finished – Occurs when row bucket processor finishes column store row group scan. Good usage is for monitoring big fat tables processes.

query_execution_column_store_segment_scan_finished – Occurs when row bucket processor finishes column store segment scan.

query_execution_column_store_segment_scan_started – Occurs when column segment scan starts.

columnstore_log_exception – Occurs when an exception occurs during processing for a columnstore operation. If you are getting error messages while working with Columnstore Index, this is one of the first extended events you should be running on your system.

Final Thoughts

Absolutely amazing expansion of the Extended Events in SQL Server 2016, with my favourites so far are the %_operation ones providing great & easy overview on what is going on with the Row Groups, Segments & Dictionaries.

I love the idea behind the new columnstore_log_exception Extended Event – this one will be very useful in the after-RTM lifecycle, as SQL Server 2014 have shown.

In one of the next blog posts I will show practical examples of applications for each of this Extended Events.

to be continued with Columnstore Indexes – part 83 (“Columnstore Replication in SQL Server 2016”)