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.

Extended Events, Birkenstocks and SQL Server

TSQL Tuesday

I bring you yet another installment in the monthly meme called T-SQL Tuesday.  This is the 67th edition, and this time we have been given the opportunity to talk about something I really enjoy – Extended Events.

Props to Jes Borland (blog | twitter) for picking such an awesome topic. There is so much to cover with extended events, it’s like a green canvas ready for anything you can imagine.

Birkenstocks?

birkentstocks

I will save the explanation here for later when hopefully it all ties together for you (well, at least buckles up).

treehugger

While that is all fun and playful, let’s get down to the serious side now. One of my favorite quick fixes as a consultant is to come in and find that the server is set to “environment friendly” / “green” / “treehugger” mode. You can read more about power saving cpus from my friend Wayne Sheffield here.

That poor old cpu thing has been beat up pretty good. But how can we tell if the server is running in that mode if the only thing we can do is look in SQL Server (can’t install cpu-z, or don’t have adequate permissions on the server to see windows settings – just play along)? Lucky for us there is this cool thing called Extended Events.

In SQL Server we have this cool event called perfobject_processor. This particular event has some really cool metrics that it captures.  One such metric is the frequency. The frequency is an indicator to us whether the server has the cpu set to balanced, high performance, or power saver. Having that in mind, let’s create a session to trap this data and experiment a little with the cpu settings.

Well, that looks amazingly easy and straight forward. I am telling the session to trap the additional CPU information such as numa_node_id and cpu_id. You can eliminate those if you wish. They may be beneficial when trying to identify if there is an issue on a specific processor though.

To experiment, I will break out the age old argument provoker – xp_cmdshell. I will use that to cycle through each of the power saving settings and look at the results. Here is the bulk of the script all together.

And now for the XE Parser.

If I parse through the extended event after each change of the power scheme, I would be able to see the effect of each scheme change in the event session as well as in a tool such as Resource Monitor. Here is what I was able to see with each of the changes.

Balanced Saver

From Resource Monitor:

balanced_cpu

And the XE data:

balanced_results

This is my default power scheme. On my laptop, this is ok. For a production SQL server, this will cause problems.

High Performance

fullpower fullpower_results

Quickly, you should be able to spot that the blue line in the graph, and the numeric values from the XE session correlate to the processor giving you everything it has. This is good for SQL Server.

Power Saver

powersaver_cpu

See how that blue line falls off sharply?

 

powersaver_results

 

Supporting that steep fall in the graph, we can see that the XE trapped the percent of max frequency as 36%. You might be lucky and attain 36%. Don’t be surprised if you see something even lower. Please don’t use this setting on a production box – unless you want to go bald.

We can see that we have great tools via Extended Events to help troubleshoot various problems. As I said, this is one of my favorites because it is a very common problem and a very easy fix.

SQL Server is not GREEN! Do not put birkenstocks on the server and try to turn the server into a tree hugger. It just won’t work out that well. Set your fileservers or your print servers to be more power conscientious, but this is something that will not work well on SQL Server.

Final thought. If you have not figured out the birkenstocks, well it is a common stereotype with environmentalists in some areas that they may wear woolly socks and birkenstocks.

No wool socks were harmed in the making of this blog post!

Monitoring SQL Server

TSQL2sDay150x150Welcome to the fabulous world of blog parties, SQL Server and what has been the longest running SQL Server related meme in the blogosphere – TSQLTuesday.

This month we are hosted by Catherine Wilhemsen (blog | twitter) from Norway. And interestingly, Catherine has asked for us to talk about monitoring SQL Server.  Wow! Talk about a HUGE topic to cover in such a short space. Well, let’s give it a go.

I am going to try and take this in a bit of a different direction, and we shall see if I have any success with it or not.

Direction the First

Monitoring is a pretty important piece of the database puzzle. Why? Well, because you want to try and find out before the end-users that something is happening. Or do you? It is a well established practice at many shops to allow the end-users to be the monitoring solution. How does this work, you ask?

It works, by waiting for an end-user to experience an error or some unexpected slowness. Then the user will either call you (the DBA), your manager, the company CEO, or (if you are lucky) the helpdesk. Then, the user will impatiently wait for you to try and figure out what the problem is.

The pros to this solution involve a much lower cost to implementation.  The cons, well we won’t talk about that because I am trying to sell you on this idea. No, in all seriousness, the con to this approach could involve a lot of dissatisfaction, job loss, outages, delays in processing, delays in paychecks, dizziness, fainting, shortness of breath, brain tumors, and rectal bleeding.  Oh wait, those last few are more closely related to trial medications for <insert ailment here>.

If you are inclined to pursue this type of monitoring – may all the hope, prayers, faith and luck be on your side that problems do not occur.

New Direction

This methodology is also rather cheap to implementation.  The risk is relatively high as well and I have indeed seen this implementation. In this new approach, we will require that the DBA eyeball monitor the databases all day and all night.

At the DBA’s disposal is whatever is currently available in SQL Server to perform the monitoring.  It is preferred that only Activity Monitor and Profiler be used to perform these duties. However, the use of sp_who2 and the DMVs is acceptable for this type of duty.

The upside to this is that you do not incur any additional cost for monitoring over what has been allocated for the salary of the DBA. This an easy and quick implementation and requires little knowledge transfer or ability.

The downside here is – well – look at the problems from the last section and then add the glassed over stoner look of the 80s from staring at the monitor all day.

If you have not had the opportunity to use this type of monitoring – consider how lucky you are.  This has been mandated by several companies (yes I have witnessed that mandate).

Pick your Poison

Now we come to a multi-forked path.  Every path at this level leads to a different tool set.  All of these tools bare different costs and different levels of knowledge.

The pro here is that these come with lower risk to those suspicious symptoms from the previous two options. The con is that it will require a little bit more grey matter to configure and implement.

You can do anything you would like at this level so long as it involves automation.  You should configure alerts, you should establish baselines, you should establish some level of history for what has been monitored and discovered. My recommendation here is to know your data and your environment and then to create scripts to cover your bases.

One last thought, no matter what solution you decide to implement, you should also monitor the monitor. If the DBA collapses from long hours of eyeball monitoring, who will be there to pick him/her up to resume the monitoring?

If you opt to not implement any of these options, or if you opt to implement either of the first two options, I hope you have dusted off your resume!

Audit Schema Change Report

Comments: 4 Comments
Published on: March 11, 2015

In a recent article on SSG, I discussed how to use Extended Events to function in a Profiler like fashion.  You can read about that here.  I recommend reading that article first because it helps to lay some of the groundwork for this article.

Within Management Studio, from a righ-click context menu, there is an option to run a report called “Schema Changes History”.  The name of this report might indicate that there is some means to find when a change occurred in the schema and potentially what the change was as well as who made the change.  It does say “Schema Changes History” after-all.

If you run the report, you would be able to see a report similar to the following.

Change_Report

 

This looks like it could be a pretty useful report.  If I drill into the collapsed items, I can get better information.  But, how exactly is this report producing this kind of data?  I don’t have a database audit running to trap this information.  I also don’t have SSDT-BI installed, so I can’t export the report and check the report definition.

I could run a trace and refresh the report data and see what pops there.  I would really hate to use Profiler though, and I really don’t want to go through the hassle of creating a server side trace to figure out the source data definitions.  Then the lightbulb moment occurs.  I already have an XE session that can trace the SQL queries just like Profiler.  Why not use a better tool to do the job and satisfy my curiosity at the same time?

So, go read that article, I wrote for SSG, to get the XE session definition in order to follow along a little better.  With that XEvent session running, I can refresh the report data and start to evaluate what is happening in order to produce the Audit report.

By this point, you have probably thought to yourself that the source of the data must be coming from the default trace.  If you thought that, then you are correct.  It is a good presumption based on a knowledge of what kind of data is being tracked in the default trace.  But I really wanted to know for certain what the source of the data was.  As I perused the XEvent session data, I noticed several queries similar to the following in the results.

XE_Capture

Yes, I am using the GUI (*shrug*) in this image to explore the data from the session.  That is just for display friendliness.  But, as you look through those queries you start to see a few patterns and some obvious signs that the source of the data is the default trace.  Here is a cleaner look at the code being used to get the data-set.

Now, I must confess that I altered it a bit to make sure it was doing what I thought.  Just a little thing like swapping a missing definition for the table variable for a quick insert into a temp table.  But the reality is, it is pretty much the source code of the report.  It is not the prettiest of code for the source, but it works.

Due to the lack of prettiness to the code and the seemingly overly complex means to get to the end result, I decided I wanted to “tune” it up a little bit.

Now, I have something that is easier to read and maintain (my opinion) and works well.  It can also be used easily enough in an RDL should you wish to create an additional report different from the canned report in SSMS.  Or just continue to use it from within Management Studio and check the results without the overhead of the report presentation.

Last Execution of a Proc

SQL Server is full of good stuff.  There are plenty of features to be used.  Plenty of applications to help it.  And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run.

It just so happens that a couple of clients requested some information on this additional metadata.  Both of the clients wanted something just a little different from the other.  After a little bit of thought, it came pretty clearly that what they wanted was definitely available within SQL Server.  The first client simply wanted to know the last time a procedure had been executed.  That is relatively easy enough – granted the procedure has been executed and granted the execution statistics have not been evicted.

The second client also wanted to know some information about the execution of a stored procedure.  But this time, they wanted to get the execution plan.  There are a few ways to trap an execution plan.  You could either run a trace, an XE session, or you could execute the query and grab the plan.  But if you didn’t already have an XE session running or a trace running and the client does not want you to execute the query to trap that plan?

Well, that is not a problem because SQL Server stores this stuff.  As long as the query and plan have not been evicted from the plan cache then you can get the metadata you desire.

Metadata to the Rescue

The metadata that we seek happens to be accessible through the use of several dynamic management views.  These are sometimes called dynamic management objects and are great to use to get to know your data and environment.  This time around, I am mostly interested in the following dynamic management objects: sys.dm_exec_query_statssys.dm_exec_cached_planssys.dm_exec_sql_text , and sys.dm_exec_query_plan.  I am using these objects because I want to trap execution stats along with the query SQL statement as well as the cached plan for that query.

So this is what I came up with to aid each of the clients in getting the data they sought.

Pretty simple and straight forward.  Now, I have this quick query to find the last time a proc was run, as well as a means to trap the execution plan for that query.  If I run that query, I would be able to see something like the following.

Capture

I hope this is useful to you and hope you enjoy.

Security as a Fleeting Thought

Comments: 6 Comments
Published on: February 10, 2015

Today we have another installment in what is known as TSQL Tuesday.  This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter).

TSQL2sDay150x150Today, the invitation is for us to share our stories on how we like to manage security.  Or at least that is the request that was made by Kenneth.  I am going to take a bit of a twist on that request.  Instead of sharing how I like to manage security, I am going to share some interesting stories on how I have seen security managed.

Let’s just call this a short series on various case studies in how to manage your security in a very peculiar way.  Or as the blog title suggests, how to manage your security as an afterthought.

Case Study #1

dbsecurityWe have all dealt with the vendor that insists on the user account that will be used for their database and application be one of two things.  Either it needs to be sa or needs to be a member of the sysadmin fixed server role.  The ensuing discussion with those vendors is always a gem.  They insist the application will break, you as the diligent DBA prove otherwise, and then the senior manager sponsoring the application comes around with a mandate that you must provide the access the vendor is requesting.

Those are particularly fun times.  Sometimes, there is a mutual agreement in the middle on what security can be used and sometimes the DBA just loses.

But what about when it is not a vendor application that mandates such relaxed security for their application and database?  What if it happens to be the development group?  What if it happens to be a developer driven shop and you are the consultant coming in to help get things in order?

I have had the distinct pleasure of working in all of those scenarios.  My favorite was a client that hosted ~700 clients, each with their own database.  There were several thousand connections coming into the server and every single connection was coming in as ‘sa’.  Yes, that is correct.  There were no user logins other than the domain admins group on the server – which was also added to the sysadmin security role.  That is always a fun discussion to start and finish.  The look of color disappearing from the clients’ eyes as the realize the severity of the problem.

Please do not attempt this stunt at home.

Case Study #2

In a similar vain, another one that I have seen far too often is the desire to grant users dbo access within a database.  While this is less heinous than granting everybody sysadmin access – it is only a tad better.  Think about it in this way – does Joe from financing really need to be able to create and drop tables within the accounting database?  Does Marie from human resources need to be able to create or drop stored procedures from the HR database?  The answer to both should be ‘NO’.

In another environment, I was given the opportunity to perform a security audit.  Upon looking over things, it became very clear what the security was.  Somebody felt it necessary to add [Domain Users] to the dbo role on every database.  Yes, you read that correctly.  In addition to that, the same [Domain Users] group was added to the sysadmin server fixed security role.  HOLY COW!

In this particular case, they were constantly trying to figure out why permissions and objects were changing for all sorts of things within the database environment.  The answer was easy.  The fix is also easy – but not terribly easy to accept.

Please do not attempt this stunt at home.

Case Study #3

I have encountered vendor after vendor that has always insisted that they MUST have local admin and sysadmin rights on the box and instance (respectively).  For many this is a grey area because of the contracts derived between the client and the vendor.

For me, I have to ask why they need that level of access.  Does the vendor really need to be able to backup your databases and investigate system performance on your server?  Does that vendor need, or are they even engaged, to troubleshoot your system as a whole?  Or, do they just randomly sign in and apply application updates without your knowledge or perform other “routine” tasks unknown to you?

I have seen vendors change permissions and add back door accounts far too often.  They seldom if ever are capable of providing the level of support necessary when you are stuck with deadlocks by the second or blocking chains that tie up the entire server.  In addition, they are generally unavailable for immediate support when a production halting issue arises in their application – or at least not for a few hours.

This is specifically in regards to application vendors.  They are not your sysadmin and they are not your DBA.  If they must have RDP access or access to the database – put it under tight control.  Disable the account until they request access.  Then a request can be made and a note documented about why the access is needed.  Then the account can be enabled, monitored and disabled after a specified amount of time.

Please do not attempt this stunt at home.

This also changes when that vendor happens to be providing you IT functionality and is not specifically tied to an application.  Those relationships are a bit different and do require a little more trust to the person who is acting on your behalf as your IT staff.

Conclusion

I have shared three very dangerous stunts that are sometimes portrayed to be done by professionals.  Do not try this in your environment or at home.  It is dangerous to treat security with so little concern.  Security is not some stunt, and should be treated with a little more care and attention.

If you find yourself in any of these situations, an audit is your friend.  Create some audit process within SQL Server or on the Local server to track changes and accesses.  Find out what is going on and be prepared to act while you build your case and a plan for implementing tighter security.

«page 1 of 18






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