Telemetry_xevents, NT SERVICE\SQLTELEMETRY, and XE_LIVE_TARGET

on November 19, 2015

Update, Feb 2017:

Here’s Microsoft’s overview of what the telemetry collects: https://www.microsoft.com/EN-US/privacystatement/SQLServer/Default.aspx

Click “learn more” at the bottom of “Specific Features in SQL Server 2016” to see the details on telemetry. It talks a bit about what data is collected. Below that there are two options to opt-out, depending on your type of installation.
I tested the registry option, and it doesn’t stop or remove the Extended Events Session. If you don’t want it running, that would be a separate step. I don’t know of any Microsoft documentation forbidding you from turning it off, if you don’t want it running.

If it seems like a big problem that your SQL Server might “phone home” with information over the internet, I think the best question to ask is, “Why does your SQL Server have the ability to send information out over the internet?" In many environments, this is not allowed at all on database servers by the network and firewall configurations for security reasons.

OK, back to the original post…

When kicking the tires of SQL Server 2016 CTP3, I was interested to find a new session defined in the Extended Events folder. Hello, telemetry_xevents!

Telemetry_xevents Extended Events Session Definition

Scripting it out, this session contains the following events:

CREATE EVENT SESSION [telemetry_xevents] ON SERVER 
ADD EVENT sqlserver.data_masking_ddl_column_definition,
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.missing_column_statistics,
ADD EVENT sqlserver.missing_join_predicate,
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_events_submitted,
ADD EVENT sqlserver.stretch_table_codegen_completed,
ADD EVENT sqlserver.stretch_table_remote_creation_completed,
ADD EVENT sqlserver.stretch_table_row_migration_results_event,
ADD EVENT sqlserver.stretch_table_unprovision_completed,
ADD EVENT sqlserver.stretch_table_validation_error,
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
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

There’s something quite odd about this session. It has no target! The data isn’t being written to memory in the ring buffer or to a file or even a counter.

So I did a little testing. I right clicked the session and selected ‘Watch Live Data’ to see if I could consume the data flowing through in SQL Server Management studio even though it didn’t have a target. And then I ran this in another session:

RAISERROR ('HALLO!',20,1) WITH LOG;
GO

Sure enough, after a little while, my error appeared:

telemetry_xevents_error

So just because the telemetry_xevents session doesn’t have a target doesn’t mean that the data can’t be consumed.

When observing the instance using Adam Machanic’s free sp_WhoIsActive procedure, I can see the SQLTELEMETRY login collecting data. It looks like this:

Not exactly a big cpu user.

SQLTELEMETRY is querying sys.fn_MSxe_read_event_stream

Here’s what the query it’s running looks like. This is a documented function, but is intended for internal use.

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

Should I Worry About the XE_LIVE_TARGET_TVF wait type?

It’s early days, but based on what I’ve seen so far, this wait type looks ignorable. As you can see from the screenshot, this wait is accruing, but that session is using very little resources and is just accessing the documented sys.fn_MSxe_read_event_stream function.

If you are concerned about this query or wait type, it is possible to stop the telemetry_xevents session– but it’s unclear what impact that has at this point since it’s not documented.