Over 1000 XEvents in SQL Server 2016 CTP2. Here are the new ones.

Extended events has firmly established itself as the premier diagnostic feature in SQL Server and SQL Server 2016 brings along more events to correspond to new features and fill in some diagnostic gaps. I always like to investigate a new release by seeing what’s new to trace. So here’s a “diff” of SQL Server 2016 CTP2 vs SQL Server 2014 (RTM) with respect to extended events. In SQL Server 2016 CTP2, the team has provided OVER 1000 different events, 1034 to be precise. 165 of them are new.

First, a couple of caveats:

The SQL Server team occasionally introduces events before the feature is available. An example of this is the query store, where a number of events were included in the metadata for SQL Server 2014, before the feature was available.

Because SQL Server and Azure SQL Database (ASD) share a common codebase, some events may be more useful (or only useful) in an ASD environment. An example of this may be the sqlserver.fulltextlog_written event. Full-text search was just introduced in ASD V1.2 and in ASD, you can’t read the full-text logs, a common diagnostic. Maybe this event will fill the gap.

Some of the events may have actually been introduced in SQL Server 2014 SP1. I remember a blog post that mentioned columnstore events in 2014 SP1, or for ASD V1.2. Figured that the 2016CTP2 vs 2014 comparison was more useful.

If you’re looking for these events in the XEvent GUI, don’t forget to turn on the debug channel events, which are off by default in the GUI. There appears to be some refactoring going on the the GUI categories, too, so use the GUI’s “event name completion” instead.

And of course, my observations:

The columnstore feature, including batch mode in queries, which had little to no coverage in 2014 extended events, now has rich coverage.

The new stretch tables and query store features are covered. Even JSON production has some diagnostics.

There are new series of events for availability groups (HADR) and in-memory tables (XTP), as well as Azure IO.

I’m especially happy with the new SQLCLR events. These will be helpful for folks who use built-in SQLCLR features (like spatial data) and those who work on the edges of user SQLCLR (i.e. unsafe assemblies).

Only 3 events were “discontinued” in SQL Server 2016 CTP2 vs. 2014, two refactored query store events, and one likely internal one, for columnstore code coverage.

Here’s the list…enjoy.


Full Name    description
qds.query_store_background_task_persist_finished    Fired if the background task for Query Store data persistence is completed successfully
qds.query_store_background_task_persist_started    Fired if the background task for Query Store data persistence started execution
qds.query_store_capture_policy_abort_capture    Fired when an UNDECIDED query failed to transition to CAPTURED.
qds.query_store_capture_policy_evaluate    Fired when the capture policy is evaluated for a query.
qds.query_store_capture_policy_start_capture    Fired when an UNDECIDED query is transitioning to CAPTURED.
qds.query_store_db_data_structs_not_released    Fired if Query Store data structures are not released when feature is turned OFF.
qds.query_store_db_diagnostics    Periodically fired with Query Store diagnostics on database level.
qds.query_store_db_settings_changed    Fired when Query Store settings are changed.
qds.query_store_db_whitelisting_changed    Fired when Query Store database whitelisting state is changed.
qds.query_store_generate_showplan_failure    Fired when Query Store failed to store a query plan because the showplan generation failed.
qds.query_store_global_mem_obj_size_kb    Periodically fired with Query Store global memory object size.
qds.query_store_load_started    Fired when query store load is started
qds.query_store_schema_consistency_check_failure    Fired when the QDS schema consistency check failed.
qds.query_store_size_retention_cleanup_finished    Fired when size retention policy clean-up task is finished.
qds.query_store_size_retention_cleanup_skipped    Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
qds.query_store_size_retention_cleanup_started    Fired when size retention policy clean-up task is started.
qds.query_store_size_retention_plan_cost    Fired when eviction cost is calculated for the plan.
qds.query_store_size_retention_query_cost    Fired when query eviction cost is calculated for the query.
qds.query_store_size_retention_query_deleted    Fired when size based retention policy deletes a query from Query Store.
sqlclr.clr_context_dump    ClrContextDump triggered.
sqlclr.notify_on_clr_disabled    Event_ClrDisabled has been triggered in ClrHost.
sqlclr.on_app_domain_failure    AppDomain hit a failure.
sqlclr.on_app_domain_unloading    AppDomain is unloading.
sqlclr.on_host_policy_callback    IHostPolicyManager received an event.
sqlclr.on_host_policy_failure    IHostPolicyManager received an event.
sqlos.ex_raise2    Raised exception
sqlos.premature_systemthread_wakeup    system thread is woken up prematurely
sqlos.recalculate_mem_target    New Memory Targets which are set after RecalculateTarget is executed
sqlserver.availability_replica_database_fault_reporting    Occurs when a database reports a fault to the availability replica manager which will trigger a replica restart if the database is critical
sqlserver.backup_restore_progress_trace    Prints backup/restore progress trace messages with details
sqlserver.batchmode_sort_spill_file    Record the spill file read/write information for batch mode sort
sqlserver.batchmode_sort_status    Record batch mode sort status
sqlserver.column_store_expression_filter_apply    An expression bitmap filter was applied on a rowgroup column batch.
sqlserver.column_store_expression_filter_bitmap_set    An expression bitmap filter was set on a rowgroup column at rowgroup compile time.
sqlserver.columnstore_delete_buffer_closed_rowgroup_with_generationid_found    Delete buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.
sqlserver.columnstore_delete_buffer_flush_failed    Columnstore delete buffer flush failed.
sqlserver.columnstore_delete_buffer_state_transition    Occurs when closed delete buffer state changes.
sqlserver.columnstore_delta_rowgroup_closed    A delta rowgroup was closed.
sqlserver.columnstore_no_rowgroup_qualified_for_merge    A user invoked a REORG command but based on the policy, no rowgroup qualified.
sqlserver.columnstore_rowgroup_compressed    A compressed rowgroup was created.
sqlserver.columnstore_rowgroup_merge_complete    A MERGE operation completed merging columnstore rowgroups together.
sqlserver.columnstore_rowgroup_merge_start    A MERGE operation started merging columnstore rowgroups together.
sqlserver.columnstore_tuple_mover_begin_delete_buffer_flush    Columnstore tuple mover started flushing a delete buffer.
sqlserver.columnstore_tuple_mover_compression_stats    Statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc.
sqlserver.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.
sqlserver.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.
sqlserver.columnstore_tuple_mover_delete_buffer_truncated    Columnstore tuple mover truncated delete buffer.
sqlserver.columnstore_tuple_mover_delete_buffers_swapped    Columnstore tuple mover swapped delete buffers.
sqlserver.columnstore_tuple_mover_end_delete_buffer_flush    Columnstore tuple mover completed flushing a delete buffer.
sqlserver.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.
sqlserver.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.
sqlserver.columnstore_x_dbfl_acquired    Occurs when X Delete Buffer Flush Lock is acquired.
sqlserver.compressed_alter_column_is_md_only    Occurs during an alter column operation. Indicates whether the alter column is metadata only or not.
sqlserver.connection_accept    Occurs when a new connection is accepted by (or duplicated into) the server. This event serves to log all connection attempts.
sqlserver.connection_duplication_failure    Occurs when connection duplication fails
sqlserver.data_purity_checks_for_dbcompat_130    Occurs when an operation that may require a data purity check for dbcompat level 130 occurs.
sqlserver.database_recovery_times    Database recovery times
sqlserver.database_tde_encryption_scan_duration    Database TDE Encryption Scan
sqlserver.database_transaction_yield    Occurs when a database transaction yields execution due to TDS buffer being full.
sqlserver.fulltextlog_written    Errorlog written
sqlserver.global_transaction    Occurs when global transaction is started.
sqlserver.hadr_db_log_throttle    Occurs when DB log generation throttle changes.
sqlserver.hadr_db_log_throttle_configuration_parameters    Occurs when DB log generation throttle configuration parameters are read.
sqlserver.hadr_db_log_throttle_input    Occurs when HADR Fabric log management component updates log throttle.
sqlserver.hadr_db_marked_for_reseed    Occurs when a HADR secondary DB falls too far behind primary and is marked for reseed.
sqlserver.hadr_db_remote_harden_failure    A harden request as part of a commit or prepare failed due to remote failure.
sqlserver.hadr_log_block_send_complete    Occurs after a log block message has been sent. This event is only used for failpoints.
sqlserver.hadr_partner_log_send_transition    Log send transition between log writer and log capture.
sqlserver.hadr_partner_restart_scan    Restart partner scans for this partner.
sqlserver.hadr_physical_seeding_backup_state_change    Physical Seeding Backup Side State Change.
sqlserver.hadr_physical_seeding_failure    Physical Seeding Failure Event.
sqlserver.hadr_physical_seeding_forwarder_state_change    Physical Seeding Forwarder Side State Change.
sqlserver.hadr_physical_seeding_forwarder_target_state_change    Physical Seeding Forwarder Target Side State Change.
sqlserver.hadr_physical_seeding_progress    Physical Seeding Progress Event.
sqlserver.hadr_physical_seeding_restore_state_change    Physical Seeding Restore Side State Change.
sqlserver.hadr_physical_seeding_submit_callback    Physical Seeding Submit Callback Event.
sqlserver.hadr_send_harden_lsn_message    Occurs when we’re crafting a message to send containing a new hardened LSN on a secondary. Test only.
sqlserver.hadr_transport_configuration_state    Indicates session state changes
sqlserver.hadr_transport_dump_dropped_message    Use this event to trace dropped HADR transport messages throughout the system.
sqlserver.hadr_transport_dump_failure_message    Use this event to help trace HADR failure messages.
sqlserver.hadr_transport_dump_preconfig_message    Use this event to help trace HADR preconfig messages.
sqlserver.hadr_transport_sync_send_failure    Synchronous send failure in hadr transport.
sqlserver.hadr_transport_ucs_registration    Reports UCS registration state changes
sqlserver.json_depth_error    Occurs when depth of json text being parsed is bigger than 128.
sqlserver.json_parsing_error    Indicates json parser error. Occurs when json format is not valid.
sqlserver.json_stackoverflow_error    Json parser stack overflow.
sqlserver.json_unescaped_character    Jsonparser hitted unescaped character in json string.
sqlserver.log_pool_cache_miss    Occurs when a log consumer attempts to lookup a block from the log pool but fails to find it.
sqlserver.log_pool_push_no_free_buffer    Occurs when log pool push fails to get a free buffer and bails out.
sqlserver.login_event    This is an abbreviated version of process_login_finish, containing only the columns required by external monitoring telemetry pipeline.
sqlserver.page_cache_trace    Modification of the page cache.
sqlserver.private_login_accept    TDS connection accept event that is logged to private MDS table.
sqlserver.private_login_finish    TDS login finish event that is logged to private MDS table.
sqlserver.process_login_finish    This event is generated when server is done processing a login (success or failure).
sqlserver.query_execution_batch_filter    Occurs when batch processing filters one batch using expression services.
sqlserver.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.
sqlserver.query_execution_column_store_rowgroup_scan_finished    Occurs when row bucket processor finishes column store row group scan.
sqlserver.query_execution_column_store_segment_scan_finished    Occurs when row bucket processor finishes column store segment scan.
sqlserver.query_execution_column_store_segment_scan_started    Occurs when column segment scan starts.
sqlserver.query_memory_grant_blocking    Occurs when a query is blocking other queries while waiting for memory grant
sqlserver.query_memory_grant_usage    Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies
sqlserver.query_trace_column_values    Trace output column values of each row on each query plan operator
sqlserver.remote_data_archive_db_ddl    Occurs when the database T-SQL ddl for stretching data is processed.
sqlserver.remote_data_archive_provision_operation    Occurs when a provisioning operation starts or ends.
sqlserver.remote_data_archive_query_rewrite    Occurs when RelOp_Get is replaced during query rewrite for Stretch.
sqlserver.remote_data_archive_table_ddl    Occurs when the table T-SQL ddl for stretching data is processed.
sqlserver.remote_data_archive_telemetry    Occurs whenever an on premise system transmits a telemetry event to Azure DB.
sqlserver.remote_data_archive_telemetry_rejected    Occurs whenever an AzureDB Stretch telemetry event is rejected
sqlserver.report_login_failure    This event is generated for a TDS login failure.
sqlserver.rpc_starting_aggregate    Occurs periodically, aggregating all occasions an rpc call is started.
sqlserver.rpc_starting_aggregate_xdb    Occurs periodically, aggregating all occasions an rpc call is started.
sqlserver.sql_batch_starting_aggregate    Occurs periodically, aggregating all occasions a sql batch is started.
sqlserver.sql_batch_starting_aggregate_xdb    Occurs periodically, aggregating all occasions a sql batch is started.
sqlserver.startup_dependency_completed    Occurs on the completion of a startup dependency in the SQL Server startup sequence
sqlserver.stretch_codegen_errorlog    Reports the output from the code generator
sqlserver.stretch_codegen_start    Reports the start of stretch code generation
sqlserver.stretch_create_migration_proc_start    Reports the start of migration procedure creation
sqlserver.stretch_create_remote_table_start    Reports the start of remote table creation
sqlserver.stretch_create_update_trigger_start    Reports the start of create update trigger for remote data archive table
sqlserver.stretch_database_disable_completed    Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command
sqlserver.stretch_database_enable_completed    Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command
sqlserver.stretch_database_events_submitted    Reports the completion telemetry transfer
sqlserver.stretch_migration_debug_trace    Debug trace of stretch migration actions.
sqlserver.stretch_migration_queue_migration    Queue a packet for starting migration of the database and object.
sqlserver.stretch_migration_sp_stretch_get_batch_id    Call sp_stretch_get_batch_id
sqlserver.stretch_migration_start_migration    Start migration of the database and object.
sqlserver.stretch_sync_metadata_start    Reports the start of metadata checks during the migration task.
sqlserver.stretch_table_codegen_completed    Reports the completion of code generation for a stretched table
sqlserver.stretch_table_provisioning_step_duration    Reports the duration of a stretched table provisioning operation
sqlserver.stretch_table_remote_creation_completed    Reports the completion of remote execution for the generated code for a stretched table
sqlserver.stretch_table_row_migration_event    Reports the completion of the migration of a batch of rows
sqlserver.stretch_table_row_migration_results_event    Reports an error or completion of a successful migration of a number of batches of rows
sqlserver.stretch_table_unprovision_completed    Reports the completion removal of local resources for a table that was unstretched
sqlserver.stretch_table_validation_error    Reports the completion of validation for a table when the user enables stretch
sqlserver.stretch_unprovision_table_start    Reports the start of stretch table un-provisioning
sqlserver.trust_verification_failed    Occurs when a SQL Server binary fails Authenticode signature verification.
sqlserver.unable_to_verify_trust    Occurs when SQL Server is unable to perform Authenticode signature verification on binaries.
sqlserver.xio_blob_properties_obtained    Windows Azure Storage blob property is obtained from response header.
sqlserver.xio_failed_request    Failed to complete a request to Windows Azure Storage.
sqlserver.xio_header_obtained    Response header is obtained from request to Windows Azure Storage.
sqlserver.xio_read_complete    Read complete from Windows Azure Storage response.
sqlserver.xio_request_opened    A request is opened to Windows Azure Storage.
sqlserver.xio_send_complete    Request send to Windows Azure Storage is complete.
sqlserver.xio_write_complete    Request send to Windows Azure Storage is complete.
sqlserver.xstore_acquire_lease    The properties of the lease acquire reques.
sqlserver.xstore_create_file    Creating an XStore file has been attempted with the options below.
sqlserver.xstore_debug_trace    Telemetry tracing event has occurred.
sqlserver.xstore_lease_renewal_request    Attempt to renew blob lease
sqlserver.xtp_alter_table    Occurs at start of XTP table altering.
sqlserver.xtp_drop_table    Occurs after an XTP table has been dropped.
ucs.ucs_negotiation_completion    UCS transport connection negotiation completed
XtpCompile.cl_duration    Reports the duration of the C compilation.
XtpEngine.trace_dump_deleted_object_table_row    Dump deleted object table row
XtpEngine.xtp_ckptctrl_abort_checkpoint    Indicates that the checkpoint close thread aborted a checkpoint.
XtpEngine.xtp_ckptctrl_close_checkpoint    Indicates that the checkpoint close thread hardened a checkpoint.
XtpEngine.xtp_ckptctrl_close_install_merge    Indicates that the checkpoint close thread installed a merge.
XtpEngine.xtp_ckptctrl_new_segment_definition    Indicates that the checkpoint controller processed a new segment definition.
XtpEngine.xtp_ckptctrl_storage_array_grow    Indicates the XTP storage array has grown in size.
XtpEngine.xtp_merge_request_start    Indicates that an XTP storage merge was requested.
XtpEngine.xtp_merge_request_stop    Indicates that an XTP storage merge request ended.
XtpEngine.xtp_merge_start    Indicates that an XTP storage merge range is starting.
XtpEngine.xtp_merge_stop    Indicates that an XTP storage merge range completed.
XtpEngine.xtp_redo_log_corruption    Indicates that the redo process failed due to log corruption.
XtpEngine.xtp_root_file_deserialized    Indicates that the load of a checkpoint root file is complete.
XtpEngine.xtp_root_file_serialized    Indicates that the write of the checkpoint root file is complete.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.