Don’t get me wrong, there’s almost no one who enjoys a good SQL Profiler diagnostic trace more than I do. But, starting in the early CTPs of SQL Server 2008, I’ve become impressed with the diagnostic enablement afforded through Extended Events. Enough to hack through that first beta (sans docs, with only metadata) and blog about it for a week. XEvents’ biggest weakness was that there was no GUI, and a guy named Jonathan Kehayias took care of that with his SSMS add-in.
In SQL Server 2012, it was announced that the old trace would be deprecated in favor of XEvents. The sheer number of events (618 in SQL Server 2012 RTM, with 9 more in SQL Server 2012 SP1) dwarfed the profiler events, as well as a nice GUI built into SSMS and programmatic interfaces for session definition, metadata, and trace-reading (including realtime) with a component called LINQ to XEvents, things were sort of sealed. That’s not even mentioning the actions, the powerful predicate system, and the series of targets.
Still, XEvents seems daunting for some, mostly (I think) because there’s too much XML involved. But c’mon, XPath is, for the most part, like navigating through a file system. And basic XPath is all you need, for the most part, to reformat the event data. Goodness, the XEvent data doesn’t even use XML Namespaces, to keep the querying simple.
After looking at the SQL Server 2014 CTP1 metadata, the results are in; things are settled. The score is:
— 763 XEvents total (that’s 145 new ones) vs. 0 new trace events (although SQL Profiler/event system is still around)
— 4 new packages, including 3 related to Hekaton (internals, compiler, and runtime).
— 68 events in the “sqlserver” packages, including about 10 more xtp (Hekaton) events in these packages, events to trace interactions with Windows Azure storage, for the new cardinality estimating code, and new columnstore functionality.
In a release where there are actually 3 new Event Notification event types (for CREATE/DROP AUDIT and DDL_DATABASE_AUDIT_EVENTS, remember Event Notifications?) the fact that there’s no new trace capability means YOU need to start learning/experimenting with Extended Events now. if you haven’t already. Go to a class, or a presentation, or read blogs or something… And you may want to brush up on your XPath…
A good place to start would be with Jonathan’s “An XEvent A Day” blog series. Or his two courses on Pluralsight about them. But start NOW, before you find yourself in SQL 2012 or 2014 with your diagnostic hands tied behind your back. How else can you look at wait stats for a specific workload or work with the T-SQL stack?
And BTW, Here’s a list of all the new events ([package name].[event name]) in 2014 CTP1 (since 2012 SP1):
hkenginexepkg.after_changestatetx_event – Fires after transaction changes state
hkenginexepkg.alloctx_event –
hkenginexepkg.attempt_committx_event – Is raised when a transaction is asked to commit
hkenginexepkg.before_changestatetx_event – Fires before transaction changes state
hkenginexepkg.compute_oldest_active_tx_hint – Indicates that the oldest active transaction hint has been recalculated
hkenginexepkg.dependency_acquiredtx_event – Raised after transaction takes a dependency on another transaction
hkenginexepkg.endts_acquiredtx_event – Fires after transaction acquires an end timestamp
hkenginexepkg.gc_cycle_completed – Indicates that a GC notification has been enqueued.
hkenginexepkg.gc_notification_enqueued – Indicates that a GC notification has been enqueued.
hkenginexepkg.redo_single_hk_record – Redo on a HK log record
hkenginexepkg.update_oldest_active_tx_stats – Indicates that an attempt is being made to recalculate the GC base generation.
hkenginexepkg.waiting_for_dependenciestx_event – Raised when we have explicitly waited for dependencies to clear
hkenginexepkg.xtp_checkpoint_file_flush – Indicates the point at which a given file has been flushed to disk.
hkenginexepkg.xtp_checkpoint_file_flush_complete – Indicates the point at which all in-flight buffers have been flushed to disk.
hkenginexepkg.xtp_checkpoint_file_read – Indicates reading of a file in XTP checkpoint recovery.
hkenginexepkg.xtp_checkpoint_write_io – Indicates that the checkpointing subsystem has issued or completed a write IO.
hkenginexepkg.xtp_create_log_record – Fires when the XTP engine creates a log record.
hkenginexepkg.xtp_root_file_write – Indicates that the write of the checkpoint root file is complete.
qds.query_store_async_shutdown_failed – Fired when query store encounters an error during async query store shutdown
qds.query_store_background_task_creation_failed – Fired if the background task for QDS data persistence could not be created
qds.query_store_background_task_initialization_failed – Fired if the background task for QDS data persistence could not be initialized
qds.query_store_background_task_persist_failed – Fired if the background task for QDS data persistence is not completed successfully
qds.query_store_begin_persist_runtime_stat – Fired immediately before current runtime statistics for a query plan is persisted to the database.
qds.query_store_bloom_filter_false_positive – Fired if the Bloom filter for QDS statement texts gives a false positive result
qds.query_store_check_consistency_init_failed – Fired when check consistency task fails during initialization
qds.query_store_database_initialization_failed – Fired if initialization of the Query Store for database has failed. The Query Store will be disabled for this database
qds.query_store_disk_size_check_failed – Fired when a check against Query Store on-disk size limit fails
qds.query_store_disk_size_info – Fired when a check against QDS on-disk size is performed
qds.query_store_execution_runtime_info – Fired when runtime information is sent to the query store.
qds.query_store_execution_runtime_info_discarded – Fired when runtime information sent to the query store is discarded.
qds.query_store_execution_runtime_info_evicted – Fired when runtime information sent to the query store is evicted.
qds.query_store_flush_failed – Fired when query store failed to flush dirty data
qds.query_store_loaded – Fired when query store is loaded
qds.query_store_notify_dirty_shutdown_on_partition_startup – Fired when previous instance of query store for the partition is shutdown by force in order to allow creation of a new instance
qds.query_store_notify_force_failure_failed – Fired when query store failed to notify force failure
qds.query_store_persist_task_init_failed – Fired when persist task fails during initialization
qds.query_store_plan_forcing_failed – Occurs when forcing of plan from qds fail
qds.query_store_plan_persistence_failure – Fired if there’s a failure to persist plan
qds.query_store_plan_removal – Fired when plan is removed
qds.query_store_query_persistence_failure – Fired if there’s a failure to persist query
qds.query_store_read_write_failed – Fired if the read/write to Query Store internal tables failed
qds.query_store_statement_not_found – Fired in case when statement couldn’t be found due to race condition or ambiguous user request.
qds.query_store_unloaded – Fired when query store is unloaded from memory
sqlos.nonpreemptive_long_syncio – record long sync io operation in nonpreemptive worker
sqlos.stuck_dispatcher_callback_executed – Stuck dispatcher callback executed
sqlos.wait_completed – Occurs when there is a wait completed on a SQLOS controlled resource. Use this event to track wait completion.
sqlserver.after_natively_compiled_proc_entry_removal_on_drop – Fired after the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver.before_natively_compiled_proc_entry_removal_on_drop – Fired before the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver.before_redo_lsn_update – Occurs just prior to the update of the EOL LSN
sqlserver.buffer_pool_eviction_thresholds_recalculated – Lazywriter and/or worker clock has wrapped the BUF array and thresholds are re-calculated.
sqlserver.buffer_pool_extension_pages_evicted – Page is evicted from the buffer pool extension cache.
sqlserver.buffer_pool_extension_pages_read – Page is read from the buffer pool extension cache.
sqlserver.buffer_pool_extension_pages_written – Page or contiguous set of pages evicted into the buffer pool extension cache.
sqlserver.check_phase_tracing – Occurs when DBCC CHECK enters a new phase of the checking. Use this event to trace the phases of DBCC CHECK process.
sqlserver.check_thread_message_statistics – Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of messages a DBCC CHECK thread has sent or received.
sqlserver.check_thread_page_io_statistics – Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of logical, physical, and read-ahead IOs a DBCC CHECK thread has performed.
sqlserver.check_thread_page_latch_statistics – Occurs when a phase of DBCC CHECK is finished. Use This event to collect the number and time of page latch and IO latch waits.
sqlserver.clustered_columnstore_index_rebuild – Occurs when clustered index on the table was rebuilt. This event is raised three times for ALTER index rebuild operation on CCSI. The event is raised when the operation takes lock on index rebuild resource, when lock is taken on the table and when S lock on the table is upgraded to SCH_M lock to switch indexes in metadata.
sqlserver.column_store_index_build_low_memory – Occurs when Storage Engine detects low memory condition and the rowgroup size is reduced.
sqlserver.column_store_index_build_process_segment – Occurs when a segment is processed
sqlserver.column_store_index_build_throttle – Shows the statistics of columnstore index build parallelism throttling
sqlserver.columnstore_tuple_mover_begin_compress – Occurs when column store tuple mover starts compressing a deltastore.
sqlserver.columnstore_tuple_mover_end_compress – Occurs when column store tuple mover is done compressing a deltastore.
sqlserver.database_xml_deadlock_report – Produces a deadlock report for a victim, with information scoped to the victim’s database.
sqlserver.db_lock_acquired_from_cache – Occurs when a DB lock is acquired from the XactWorkspace DB lock cache.
sqlserver.db_lock_released_from_cache – Occurs when a DB lock is released from the XactWorkspace DB lock cache.
sqlserver.ddl_with_wait_at_low_priority – A DDL statement was executed using the WAIT_AT_LOW_PRIORITY options
sqlserver.diag_quantum_end – Occurs when the diag is notified of quantum end.
sqlserver.dyn_throttle_checkpoint – Occurs when checkpointing has been dynamically throttled with a new Checkpoint rate.
sqlserver.feature_extension – Occurs when received and parses data for a feature in feature extension.
sqlserver.file_read_enqueued – File read enqueued
sqlserver.file_read_throttled – File read throttled
sqlserver.file_write_enqueued – File write enqueued
sqlserver.file_write_throttled – File write throttled
sqlserver.hadr_tds_synchronizer_payload_skip – Hadron Tds Listener Synchronizer skipped a listener payload because there were no changes since the previous payload.
sqlserver.lock_request_priority_state – The priority state of a lock request
sqlserver.log_cache_write_block – Writing a log block to the log cache
sqlserver.logreader_start_scan – Outputs the replnextlsn value from the database table that the first replcmds scan uses as a starting point.
sqlserver.metadata_ddl_add_column – Occurs when an ALTER TABLE ADD column operation is updating base index.
sqlserver.metadata_ddl_alter_column – Occurs when an ALTER TABLE ALTER column operation is updating base index.
sqlserver.metadata_ddl_drop_column – Occurs when an ALTER TABLE DROP column operation is updating base index.
sqlserver.mixed_extent_activation – Track mixed extent activation and deactivation operations.
sqlserver.mixed_extent_allocation – Track mixed extent allocation operations
sqlserver.mixed_extent_deallocation – Track mixed extent deallocation operations.
sqlserver.mixed_page_allocation – Track mixed page allocation operations
sqlserver.mixed_page_deallocation – Track mixed page allocation operations
sqlserver.mixed_page_scan_file – Track the activity of SGAM scans for mixed page allocation
sqlserver.mixed_page_scan_page – Track the activity of SGAM scans for mixed page allocation
sqlserver.mixed_page_skipextent – Track the activity of SGAM scans for mixed page allocation
sqlserver.natively_compiled_proc_execution_started – Fired before a natively compiled procedure is executed.
sqlserver.optimizer_timeout – Occurs when the optimizer times out either due to spending too much time or hitting a memory limit. Use this event to look at all the queries that are impacted by the optimizer timeout in a particular workload. This can be very useful when tuning a particular workload.
sqlserver.plan_affecting_convert – Occurs when a type convert issue affects the plan. The expression value indicates the conversion that can cause inaccurate Cardinality Estimation or prevent from using a seek based plan in the query plan choice. If performance is affected, rewriting the query could help.
sqlserver.process_killed_by_abort_blockers – A process is killed by an ABORT = BLOCKERS DDL statement
sqlserver.query_execution_batch_hash_aggregation_finished – Occurs at the end of batch hash aggregation.
sqlserver.query_execution_batch_hash_children_reversed – Occurs each time when hash join reverses build and probe side while processing data spilled to disk.
sqlserver.query_execution_batch_hash_join_spilled – Occurs each time when hash join spills some data to disk in batch processing.
sqlserver.query_optimizer_estimate_cardinality – Occurs when the query optimizer estimates cardinality on a relational expression.
sqlserver.query_optimizer_force_both_cardinality_estimation_behaviors – Both traceflags 2312 and 9481 were enabled, attempting to force both old and new cardinality estimation behaviors at the same time. The traceflags were ignored. Disable one or both of the traceflags.
sqlserver.query_store_failed_to_capture_query – Fired if the Query Store failed to capture query. The Query Store will not track statistics for this query
sqlserver.query_store_failed_to_load_forced_plan – Fired if the query failed to load forced plan from QDS. Forcing policy will not be applied
sqlserver.query_store_persist_on_shutdown_failed – Occurs when SQL Server fails to store dirty entries in Query Store on database shutdown.
sqlserver.selective_xml_index_no_compatible_sql_type – Occurs when a value() method is used in a query, specifying a sql type that differs from the type specified during selective XML index creation for that path.
sqlserver.selective_xml_index_no_compatible_xsd_types – Occurs when the user specifies a different resulting XSD type in a query, than the one that was used to promote the path during selective XML index creation.
sqlserver.selective_xml_index_path_not_indexed – Occurs when the path given in the user query is not promoted in the selective XML index, and thus the index is not used.
sqlserver.selective_xml_index_path_not_supported – Occurs when selective XML index is not used for a XML query due to the user using an unsupported path type.
sqlserver.session_recoverable_state_change – Occurs when the server determines a state change in term of session recovery on a connectionresilency-enabled connection.
sqlserver.spatial_guess – Occurs when the optimizer cannot get enough join selectivity information. Use this event to look at the queries where spatial index could be used and the optimizer guesses the spatial selectivity.
sqlserver.unmatched_filtered_indexes – Occurs when the optimizer cannot use a filtered index due to parameterization. For each such index an event is fired. The unmatched_database_name, unmatched_schema_name, unmatched_table_name, unmatched_index_name fields give details of the filtered index that could not be used.
sqlserver.xfcb_blob_properties_obtained – Windows Azure Storage blob property is obtained from response header.
sqlserver.xfcb_failed_request – Failed to complete a request to Windows Azure Storage.
sqlserver.xfcb_header_obtained – Response header is obtained from request to Windows Azure Storage.
sqlserver.xfcb_read_complete – Read complete from Windows Azure Storage response.
sqlserver.xfcb_request_opened – A request is opened to Windows Azure Storage.
sqlserver.xfcb_send_complete – Request send to Windows Azure Storage is complete.
sqlserver.xfcb_write_complete – Request send to Windows Azure Storage is complete.
sqlserver.xtp_create_procedure – Occurs at start of XTP procedure creation.
sqlserver.xtp_create_table – Occurs at start of XTP table creation.
sqlserver.xtp_deploy_done – Occurs at completion of XTP object deployment.
sqlserver.xtp_matgen – Occurs at start of MAT generation.
sqlserver.xtp_offline_checkpoint_scan_start – Fired by XTP offline checkpoint when the checkpoint thread begins.
sqlserver.xtp_offline_checkpoint_scan_stop – Fired by XTP offline checkpoint when the checkpoint thread stops.
sqlserver.xtp_recover_done – Occurs at completion of log recovery of XTP table.
sqlserver.xtp_recover_table – Occurs at start of log recovery of XTP table.
sqlserver.xtp_storage_table_create – Occurs at just before the XTP storage table is created.
ucs.ucs_connection_rejected_by_proxy_whitelist – After a connection attempt to the UCS proxy endpoint is rejected by whitelist check
ucs.ucs_proxy_connect_next_hop – UCS proxy next hop connection
ucs.ucs_proxy_receive_proxy_connect_message – UCS proxy receive proxy connect message
ucs.ucs_proxy_route_add – UCS proxy route added
ucs.ucs_proxy_route_disable – UCS proxy route disabled
ucs.ucs_proxy_route_refresh – UCS proxy route refreshed
ucs.ucs_proxy_send_proxy_connect_message – UCS proxy send proxy connect message
XeXtpCompilePkg.cgen – Occurs at start of C code generation.
XeXtpCompilePkg.invoke_cl – Occurs prior to the invocation of the C compiler.
XeXtpCompilePkg.mat_export – Occurs at start of MAT export.
XeXtpCompilePkg.pit_export – Occurs at start of PIT export.
XeXtpCompilePkg.pitgen_procs – Occurs at start of PIT generation for procedures.
XeXtpCompilePkg.pitgen_tables – Occurs at start of PIT generation for tables.
XeXtpRuntimePkg.bind_md – Occurs prior to binding metadata for a memory optimized table.
XeXtpRuntimePkg.bind_tables – Occurs prior to binding tables for a natively compiled procedure.
XeXtpRuntimePkg.create_table – Occurs prior to creating memory optimized table.
XeXtpRuntimePkg.deserialize_md – Occurs prior to deserializing metadata.
XeXtpRuntimePkg.load_dll – Occurs prior to loading the generated DLL.
XeXtpRuntimePkg.recover_done – Occurs at completion of checkpoint recovery of a memory optimized table.
XeXtpRuntimePkg.recover_table – Occurs at start of checkpoint recovery of a memory optimized table.
XeXtpRuntimePkg.serialize_md – Occurs prior to serializing metadata.
Enjoy.
Cheers, Bob