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!

Why, I don't remember creating this myself.

Why, I don’t remember creating this myself.

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

HALLOOOO!

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:

Not exactly a big cpu user.

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.

Will telemetry_xevents Ship in 2016 RTM?

Stay tuned, we’ll find out later.

Previous Post
Did My Query Eliminate Table Partitions in SQL Server?
Next Post
My SQLPASS 2015 Session Evaluations

Related Posts

No results found

6 Comments. Leave new

  • […] Kendra Little is sleuthing: […]

    Reply
  • Hi Kendra
    any updates on this? Seems nobody lies awake of this event session but… “telemetry”, doesn’t that mean “we’ll be sending your data to MS”? I don’t understand why for example they would be interested in error 18456 though.
    Also it seems the session does have a target now (ADD TARGET package0.ring_buffer).
    Did you figure out yet what happens if we would stop the session?
    Thx
    Thierry

    Reply
    • Oh, thanks for asking– I forgot that I’d written a post on this, actually! I did end up learning more. I’ll edit the post to include this information as well.

      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.

      Reply
      • Thanks a lot for the quick reply! That link is really valuable. I think I’ll have to stop being paranoid and start dealing to live with the telemetry stuff. It’s everywhere nowadays…
        Thx again!

        Reply
  • Stephen Anslow
    December 12, 2017 5:27 pm

    I hope it isn’t just me, but the link within the “learn More” that goes to https://support.microsoft.com/en-us/kb/3153756 fails with FireFox, fails with Chrome, but works fine with IE! Many, many, microsoft links seem to be coded in such a way as to fail miserably on anything BUT IE. Am I alone…? (Not authorized on FireFox, ERR_EMPTY_RESPONSE on Chrome – both are the latest browser versions.

    But a huge thanks, Kendra, we’re tired of Event Viewer filling up with failed TELEMETRY login attempts.

    Reply
    • Hey Stephen, I think it’s just you 🙂 Tried it in Chrome and it work fine. At the top of the document, it DOES tell me to try Edge however 🙂

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu