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.