Telemetry_xevents, NT SERVICE\SQLTELEMETRY, and XE_LIVE_TARGET_TVF in SQL Server 2016
Update, Feb 2017: I forgot that I’d written this post, so I didn’t think to update it when I learned more about this topic.
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:
So just because the telemetry_xevents session doesn’t have a target doesn’t mean that the data can’t be consumed.
Meet the NT SERVICE\SQLTELEMETRY Login
When observing the instance using Adam Machanic’s free sp_WhoIsActive procedure, I can see the SQLTELEMETRY login collecting data. It looks like this:
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.
Will telemetry_xevents Ship in 2016 RTM?
Stay tuned, we’ll find out later.