Problem
In my previous tips SQL Server 2016 extended events detailed backup information and SQL Server restore steps with extended events we have seen how useful Extended Events are in SQL Server 2016. While exploring more on Extended Events I observed some new interesting events and in this tip we will explore these and see how they can be helpful.
Solution
Extended Events play an important role for troubleshooting issues and will replace Profiler probably in an upcoming version of SQL Server. There are new Extended Events called telemetry_xevents in the Sessions folder in the Extended Events folder and it is enabled by default in SQL Server 2016.
As per Microsoft, most applications will include diagnostic features that generate custom monitoring and debugging information, especially when an error occurs. This is referred to as instrumentation and is usually implemented by adding event and error handling code to the application. The process of gathering remote information that is collected by instrumentation is usually referred to as telemetry.
So let’s explore these Extended Events.
SQL Server telemetry_xevents Extended Events
Microsoft has provided this new Extended Events Session to capture details around important events, errors that occurred and could help us to view what’s happening with SQL Server at a high level in case of an issue.
So to see which events are being captured in this telemetry_xevents session, right click on the session and select Script Session As > Create To > New Query Editor Window. The following are the events that are part of this session:
CREATE EVENT SESSION [telemetry_xevents] ON SERVER
ADD EVENT qds.query_store_db_diagnostics,
ADD EVENT sqlserver.always_encrypted_query_count,
ADD EVENT sqlserver.auto_stats,
ADD EVENT sqlserver.column_store_index_build_low_memory,
ADD EVENT sqlserver.column_store_index_build_throttle,
ADD EVENT sqlserver.columnstore_delete_buffer_flush_failed,
ADD EVENT sqlserver.columnstore_delta_rowgroup_closed,
ADD EVENT sqlserver.columnstore_index_reorg_failed,
ADD EVENT sqlserver.columnstore_log_exception,
ADD EVENT sqlserver.columnstore_rowgroup_merge_failed,
ADD EVENT sqlserver.columnstore_tuple_mover_delete_buffer_truncate_timed_out,
ADD EVENT sqlserver.columnstore_tuple_mover_end_compress,
ADD EVENT sqlserver.data_masking_ddl_column_definition,
ADD EVENT sqlserver.data_masking_traffic,
ADD EVENT sqlserver.data_masking_traffic_masked_only,
ADD EVENT sqlserver.database_cmptlevel_change,
ADD EVENT sqlserver.database_created,
ADD EVENT sqlserver.database_dropped,
ADD EVENT sqlserver.error_reported(
WHERE ([severity]>=(20) OR ([error_number]=(18456) OR [error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902) OR [error_number]=(41354) OR [error_number]=(41355) OR [error_number]=(41367) OR [error_number]=(41384) OR [error_number]=(41336) OR [error_number]=(41309) OR [error_number]=(41312) OR [error_number]=(41313)))),
ADD EVENT sqlserver.json_function_compiled(
ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.missing_column_statistics,
ADD EVENT sqlserver.missing_join_predicate,
ADD EVENT sqlserver.query_memory_grant_blocking,
ADD EVENT sqlserver.rls_query_count,
ADD EVENT sqlserver.sequence_function_used(
ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.server_memory_change,
ADD EVENT sqlserver.server_start_stop,
ADD EVENT sqlserver.stretch_database_disable_completed,
ADD EVENT sqlserver.stretch_database_enable_completed,
ADD EVENT sqlserver.stretch_database_reauthorize_completed,
ADD EVENT sqlserver.stretch_index_reconciliation_codegen_completed,
ADD EVENT sqlserver.stretch_remote_column_execution_completed,
ADD EVENT sqlserver.stretch_remote_column_reconciliation_codegen_completed,
ADD EVENT sqlserver.stretch_remote_index_execution_completed,
ADD EVENT sqlserver.stretch_table_alter_ddl,
ADD EVENT sqlserver.stretch_table_codegen_completed,
ADD EVENT sqlserver.stretch_table_create_ddl,
ADD EVENT sqlserver.stretch_table_data_reconciliation_results_event,
ADD EVENT sqlserver.stretch_table_hinted_admin_delete_event,
ADD EVENT sqlserver.stretch_table_hinted_admin_update_event,
ADD EVENT sqlserver.stretch_table_predicate_not_specified,
ADD EVENT sqlserver.stretch_table_predicate_specified,
ADD EVENT sqlserver.stretch_table_query_error,
ADD EVENT sqlserver.stretch_table_remote_creation_completed,
ADD EVENT sqlserver.stretch_table_row_migration_results_event,
ADD EVENT sqlserver.stretch_table_row_unmigration_results_event,
ADD EVENT sqlserver.stretch_table_unprovision_completed,
ADD EVENT sqlserver.stretch_table_validation_error,
ADD EVENT sqlserver.string_escape_compiled(
ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.temporal_ddl_period_add,
ADD EVENT sqlserver.temporal_ddl_period_drop,
ADD EVENT sqlserver.temporal_ddl_schema_check_fail,
ADD EVENT sqlserver.temporal_ddl_system_versioning,
ADD EVENT sqlserver.temporal_dml_transaction_fail,
ADD EVENT sqlserver.window_function_used(
ACTION(sqlserver.database_id))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
As we can see this Extended Event Session has START_STATE= ON that means even if you restart SQL Server, this Extended Event Session will automatically start up.
This contains a lot of Extended Events, so let’s see what these are:
- sqlserver.auto_stats: Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.
- column_store_index_build_low_memory: Storage Engine detected a low memory condition and the rowgroup size was reduced.
- column_store_index_build_throttle: Shows the statistics of columnstore index build parallelism throttling.
- columnstore_delete_buffer_flush_failed: Columnstore delete buffer flush failed.
- columnstore_delta_rowgroup_closed: A delta rowgroup was closed.
- columnstore_index_reorg_failed: An Index Reorganize operation failed.
- columnstore_log_exception: Occurs when an exception occurs during processing for a columnstore operation.
- columnstore_rowgroup_merge_failed: A MERGE operation started merging columnstore rowgroups but failed in the process.
- columnstore_tuple_mover_delete_buffer_truncate_timed_out: Columnstore tuple mover truncate operation timed out due to an active snapshot transaction.
- columnstore_tuple_mover_end_compress: Columnstore tuple mover completed compressing a deltastore.
- data_masking_ddl_column_definition: Occurs when a data masking function is modified on a table column definition (ADD, CHANGE or DROP)
- data_masking_traffic: Occurs when data masking is applied to a query.
- data_masking_traffic_masked_only: Occurs when data masking is applied to a query and the query is actually masked.
- database_cmptlevel_change: Database compatibility level is changed.
- database_created
- database_dropped
- error_reported: Occurs when an error is reported.
- json_function_compiled: Indicates that JSON function is compiled.
- missing_column_statistics: Occurs when a query accesses a column that does not have statistics available that could have been useful for query optimization. Use this event to identify queries that may be using sub-optimal plans. This event is only generated when the Auto Create Statistics option is turned off.
- missing_join_predicate: Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row.
- query_memory_grant_blocking: Occurs when a query is blocking other queries while waiting for memory grant
- query_store_db_diagnostics: Periodically fired with Query Store diagnostics on database level.
- rls_query_count: Query using RLS.
- sequence_function_used: Occurs when a sequence function is used.
- server_memory_change: Occurs when the server memory usage increases or decreases by more than either 1 megabyte (MB) or 5% of the maximum server memory, whichever is larger. Use this event to identify periods of increased memory usage on the server.
- server_start_stop: Occurs when the Server has been started or stopped.
- stretch_database_disable_completed: Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command.
- stretch_database_enable_completed: Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command.
- stretch_database_reauthorize_completed: Reports the completion of a sp_rda_reauthorize_db spec proc.
- stretch_index_reconciliation_codegen_completed: Reports the completion of code generation for stretch remote index operation.
- stretch_remote_column_execution_completed: Reports the completion of remote execution for the generated code for a stretched column.
- stretch_remote_column_reconciliation_codegen_completed: Reports the completion of code generation for stretch remote column reconciliation.
- stretch_remote_index_execution_completed: Reports the completion of remote execution for the generated code for a stretched index.
- stretch_table_alter_ddl: Occurs when the stretch is enabled from ALTER TABLE DDL.
- stretch_table_codegen_completed: Reports the completion of code generation for a stretched table.
- stretch_table_create_ddl: Occurs when the stretch is enabled from CREATE TABLE DDL.
- stretch_table_data_reconciliation_results_event: Reports an error or completion of a successful data reconciliation of a number of batches of rows.
- stretch_table_hinted_admin_delete_event: Reports the execution of a Stretch delete DML operation that uses an admin hint.
- stretch_table_hinted_admin_update_event: Reports the execution of a Stretch update DML operation that uses an admin hint.
- stretch_table_predicate_not_specified: Occurs when predicate is not specified during stretching of table.
- stretch_table_predicate_specified: Occurs when predicate is specified during stretching of table.
- stretch_table_query_error: Reports an error thrown during Stretch query rewrite.
- stretch_table_remote_creation_completed: Reports the completion of remote execution for the generated code for a stretched table.
- stretch_table_row_migration_results_event: Reports an error or completion of a successful migration of a number of batches of rows.
- stretch_table_row_unmigration_results_event: Reports an error or completion of a successful unmigration of a number of batches of rows
- stretch_table_unprovision_completed: Reports the completion removal of local resources for a table that was unstretched.
- stretch_table_validation_error: Reports the completion of validation for a table when the user enables stretch.
- string_escape_compiled: Indicates that STRING_ESCAPE function is compiled.
- temporal_ddl_period_add: Occurs when temporal period is being added.
- temporal_ddl_period_drop: Occurs when temporal period is being dropped.
- temporal_ddl_schema_check_fail: Occurs when temporal ddl data consistency check fails.
- temporal_ddl_system_versioning: Occurs when system versioning option for a table is being changed.
- temporal_dml_transaction_fail: Occurs when start time of the record being inserted into history table is greater than its end time.
- window_function_used: Occurs when a window function is used.
Now let’s see how this works. I performed some operations like database creation, compatibility level change, database drop, etc. to see how this is getting captured with this Extended Events Session using Watch Live Data. To do this go to Management > Extended Events > Sessions and right click on telemetry_xevents and select Watch Live Data.
As we can see it captures detailed information about the events like a database create, compatibility level change, missing column statistics, etc.
If we look at current active sessions while the Extended Events data is being collected we can see this session is using a table valued function (TVF) sys.fn_MSxe_read_event_stream to pull back the data. It also shows wait type XE_LIVE_TARGET_TVF. This wait type occurs when a thread is waiting for an event session to have events fire while watching the session output in the live data viewer, but this doesn’t seem to be causing any issues, however it would be good to monitor if it is a significant wait type in your environment.
This is the query that is being executed:
SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)
More specifically it is running the below code to capture the data:
DECLARE @source NVARCHAR(256) = 'telemetry_xevents'
,@sourceopt INT = 0; -- bit 0 specifies live data, if 1 it will take source as .xel file.
SELECT type, data FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt);
Stop or Start a SQL Server Extended Events Session
To stop or start the data collection we can run the following commands:
--Command to stop the xevent session
ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = stop;
--Command to start the xevent session
ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = start;
It is a good idea to explore this telemetry_xevents session and benefit from the new features in SQL Server 2016.
Next Steps
- Download and explore SQL Server 2016
- Check out SQL Server 2016 Tips
- Check out SQL Server Extended Events Tips
- Read more about Extended events
- Read more about Extended events sessions
