New Extended Events for 2016

As we begin to get a grasp of all that is available with SQL Server 2016 and all of the new features, it is a great idea to see what else has been made available to us in order to help support and troubleshoot these new features. With that, it is well worth a look at the new changes to Extended Events to support all the shiny new stuff.

Here is a list of what I could find as of CTP 2 (I have yet to look at CTP 2.1). This list alone has some really cool stuff in it.  Browse through it and you will be able to find new stuff to help support and administer things like Stretch Database as well as JSON. You won’t find these events within Trace or profiler – only XE.

The presence of these new events (even some new ones for Query Store) is quite enough to get me excited for that next release of SQL Server. There is some great stuff coming down the pipe.

I have randomly highlighted some of the new events to support some of these new features. (And since this is only CTP 2.0, one can be sure there will be changes made to this list with the actual release of SQL Server 2016.)

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

There you have it, currently 165 new events available to help monitor and troubleshoot SQL Server.

Database Drops in SQL 2012

In the previous article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.

Enjoy!

Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.

xe_results

In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.

SQL Cruise Mediterranean+

Comments: 2 Comments
Published on: June 24, 2015

This is a post that is looooong overdue. I have intended to write some of this at least since the first SQL Cruise I attended back in 2013 (in the Caribbean). Now with three Cruises under the belt and plenty of awesome experiences, it is high time to follow through on those intentions.

Official_SQLCruise_2015For those that have not heard, SQL Cruise is a mix of training and vacation. You get a little (or a lot) of SQL and then you get a little (or a lot) of vacation mixed with the opportunity to see places you may not have otherwise visited. Last but certainly not least is the opportunity afforded to all attendees to meet other people they may have never otherwise gotten a chance to meet. This last benefit may be the most important facet of SQL Cruise in that the networking done during the cruise will last a long time and can certainly open a few doors if/when necessary.

One great example of this networking occurred on the first cruise I attended. In the meetings outside of the scheduled training, one of the other cruisers (he is now an MCM and MVP) asked a pretty important question about an issue within his work environment. A performance monitoring package they ran for all of their clients was causing some serious problems. The problems affected about 30% of all of the servers which numbered over 1500. The application on the affected servers would stop responding and they would no longer receive metrics or alerts to conditions being raised.

This problem was significant enough that they engaged Microsoft and Microsoft engineers had been collecting metrics and logs for over six months (at the time). There was a series of try this and try that and all of it resulted in no change whatsoever. So this fellow cruiser brought the issue to the cruise with him. In chatting with the cruiser (oh, and he had to fly to the US from Europe in order to attend), I discovered that the symptoms he was enduring were quite similar to some things I had seen within SSIS packages for example. We discussed a quick fix which he took back to his employer.

Through a series of tests and deployments, this Cruiser and his employer rolled out the fix to all of the servers in the environment. This fix ended up saving them so much money in labor and other costs, that his employer sent him and his family (eight people in total) back to SQL Cruise in the Caribbean the following year. The ROI for this Cruiser and his employer was huge! Additionally, he and I have become friends thanks to the Cruise. This is the sort of stuff that defines SQL Cruise.

This year, we had the chance to repeat the Cruise by attending the Mediterranean version. This installment was a whirlwind of touring Europe. Some started in London before proceeding to Barcelona and then on to such places as Pompeii, Rome, Pisa, Cannes, Monaco, and Mallorica before returning to Barcelona to continue on to Berlin or London before returning home. For me, we chose to start in Paris and then taking the bullet train on to Barcelona.

Med Sunset

The tourism was fast and furious. The training was faster and more furious. And in the end, the European / Mediterranean trip was gone before we knew it. All who attended surely walked away with great memories and with having learned something (whether it be cultural, historic, or SQL in nature).

If you have the chance to attend a SQL Cruise, I would say do it. The training comes from the technical leads as well as the attendees in the rooms. Often, the tech lead will even defer to any of the other professionals in attendance. Especially in the case of Trace Flags as we learned this past week – in every session and in every office hours meeting. Just ask Grant Fritchey (blog | twitter) about it some time – he loves talking about Trace Flags.

Reading Extended Event File Session Data

Using Extended Events to trap/trace information allows the trapping of that information to various targets. One of the targets I will frequently tell people to use is the file target. The reasoning for this is the opportunity this affords to be able to review the output  at a later time whether the event session is running or not.

Along  with that recommendation, I also show some quick code to pull the data back from the session target (the file on disk). This code is written to dynamically pull the file information out of the XE Dynamic Management Views to make it a little easier on the user (and a bit less likely to have a typo). That code looks a bit like the following:

This works really well when the session is running. It pulls the desired file path and name back from the DMVs and one is able to parse the session data. But what if that session is stopped?

Well, if the session is stopped, we have a problem. With a stopped session, the session above will not produce any results. The short of this is that SQL Server removes the entries from the sys.dm_xe_session_targets and sys.dm_xe_sessions DMVs. Due to that, there is a bit of a tweak to be able to query those files for the sessions that have been stopped.

To query those files, one could rewrite the previous query to something like this:

Now, I bet you may be asking why stop an Extended Event session from running. Afterall, extended events is extremely light weight and has little impact on the server, right? True that may be, there is still a cost for traces. If you can just run a trace for a targeted time period, then why not limit the amount of tracing?

Looking at the code, you can see that I reverted to a less dynamic approach to read those event files. Instead of trying to get that info direct from the database, I am just telling SQL Server where to find the files on disk (note the filepath that is passed).

There you have it. A means to query these trace files whether the XE trace is running or stopped.

Execution Plans in Extended Events

Extended Events is a wonderful tool. Execution Plans are also some wonderful things – or are a wonderful tool as well. Both of these tools are fantastic for troubleshooting. Combined, they can potentially be even better.

Or, they could just cause some hair loss and be a fairly complicated pain in your butt. Hopefully the combination of the two will be more useful than painful for you. And today, I will discuss one of the pains that could be an issue when combining these two tools. I will also discuss some options for getting around this pain point between XE and Execution Plans.

Let’s start by taking a quick look at two Extended Events events that can expose execution plans for you. These two events are query_pre_execution_showplan and query_post_execution_showplan. Here are a couple sample sessions set to trap these events.

And…

With these sample sessions ready, we need a quick description of what is happening. I have built the sessions (at least the TrapEstExecPlans session) to go to both a file and the ringbuffer targets. This was done to test the behavior on both targets to ensure consistency in behavior between the different targets.

Also, I added a bunch of actions that are available to query, but the sample queries shown later will not use the data for those actions. This is only due to the examples I have chosen to share.

With those sessions created, go ahead and start them, then run a query like the following:

After  having executed this query, I can start taking a look at the data captured. I will only query the TrapEstExecPlans session in this post because the behavior between the two sessions and events is consistent.

Keep in mind that I talked about having both a ringbuffer and a file target for this session. I am only going to share the query from the ringbuffer target because the behavior from the memory target and the filetarget is the same.

If I take a look at the execution plans shared from the results of that query, I might see something like this:

Plan from XE

 

If I look at the execution plan at runtime for that same query, I would see something like this:

Exec Plan

Between the two plans, I have color coded the various attributes of the plan to pair the matching attributes and make it easier to spot the differences. In this case, the differences are in the first two nodes. The plan from Extended Events does not have the connection properties, but is also missing a few other important things like the StatementType and StatementText. Looking at the plan from XE in the gui, you would see something like this:

XE-plan

Instead of this:

Execplan_graphic

Those minor differences in the XML of the execution plans can lead to somewhat of a pain. Alas, there is a fix for that. And the fix comes down to doing one of a few things already available to us in the XE session data. We can either lookup the plan_handle that was trapped by the action, or we can lookup the QueryPlanHash that is available in the XML from the execution plan data that has been trapped. With that information, one can likely retrieve the stored execution plan and catch the rest of the missing components of that execution plan.

There is one other option and that is the action that traps the sql_text. The sql_text that is captured can lead us to understand what kind of plan (select, update, delete, etc) we are looking at from the XE session data.

So, while it is a bit of a nuisance that the two sources of execution plans does not produce the same plan, it is not the end of the world. There is still adequate information available from the XE session data to figure out the missing pieces of the execution plan.

«page 1 of 87






Calendar
July 2015
M T W T F S S
« Jun    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, July 7, 2015