SQL Server 2016 Telemetry Extended Event Session

By:   |   Comments (1)   |   Related: > Extended Events


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.

SQL Server Extended Events telemetry_xevents in Management Studio

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.

Watch Live Data interface for Extended Events in Management Studio

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.

SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, May 8, 2017 - 11:00:32 PM - Hiram Back To Top (55594)

The xevent is actually set to autostart at sql service startup, so if you want to really stop it then do this.

 

ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = STOP;

ALTER EVENT SESSION [telemetry_xevents] ON SERVER WITH (STARTUP_STATE=OFF);

 

To put back to default run this:

ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = STOP;

GO

ALTER EVENT SESSION [telemetry_xevents] ON SERVER WITH (STARTUP_STATE=ON);

ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = START;














get free sql tips
agree to terms