What Queries Does Microsoft’s CEIP Service Run On Your SQL Server?


You’ve seen the CEIP Service on your SQL Server, and you’re wondering what queries it runs and how it sends that information back to Microsoft. I was wondering too, because I started seeing queries running that I didn’t expect:

sp_WhoIsActivelyRunningTelemetryQueries (click to see full size)

Ah-ha, the telemetry service, also known as SQLCEIP! Starting with SQL Server 2016, your database server phones home to Microsoft by default. I clicked on the sql_text to see what query was running:

Brent Ozar in a tin foil hat
The trick is blocking the mind control waves while still letting the headphones get a signal

Well, whaddya know: that’s where my lock wait times were coming from. The SQL Server telemetry service was trying to query system objects using the default isolation level, which means they would get blocked. (We avoid that problem in sp_BlitzIndex with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.)

That got me to thinking – why not run a trace on SQLCEIP?

I captured a trace for a few hours after SQL Server started up. Here’s the 1MB trace file (trc) output readable with Profiler, and here’s a 1MB SQL Server 2017 database backup with the trace output stored in table. If you use the table version, this will help you analyze the queries involved:

Some of the interesting queries include…

The CEIP Service gets your top 30 user database names.

They don’t return the size, but they do pull the database names with this query:

That query really surprised me because I hadn’t expected Microsoft to bring back database names – the rest of the queries seemed to take extra steps to only get database IDs or group data together by databases, but not this one:

Databases by size

Huh. I gotta think that’s an oversight. I have clients who consider database names to be confidential data since they have their client names or legal matters as part of the database name. (Not that that was a great design, but that ship has sailed.)

Few – but very few – of the other queries also return database names, like this one:

Most of them work more like this, just returning database ids:

I wouldn’t be surprised if, after this gets published, somebody goes through the telemetry queries looking for database names, and changes those queries to use something like the database-class approach used in other queries below. (Not to mention fixing the default read-committed isolation level bug that started me on this whole hunt – some of the telemetry queries use read uncommitted, and some don’t.)

CEIP searches for SharePoint, Dynamics, and…AdventureWorks?

They’re categorizing databases by name:

The output looks like this:

I was kinda hoping AdventureWorks would be shown as “economy class”

I love this – if you’re going to analyze which customers are using SQL Server features, you want to avoid false positives. The sample databases like AdventureWorks use all kinds of features, so you wouldn’t want to count those as customers actually leveraging, say, spatial data. These database names are also specific enough that they’re going to avoid most false positives.

Most of the CEIP Service’s queries are uncommented,
but the security ones seem to have comments.

The queries that return data about encryption, certificates, and the like usually seem to have comments. I’m guessing that was done on purpose, trying to assuage folks’ fears. If I told a manager, “Microsoft’s telemetry is sending back what kind of encryption you’re using, how many columns are encrypted, whether your backup is encrypted, etc.,” they would probably sit up a little straighter in their chairs. I’m guessing the comments were left in to make people feel a little better about what encryption config data is leaving the building.

The CEIP Service checks your storage speed.

Storage speed is one of the classic database admin complaints, especially around 15-second IO warnings. They’re aggregating it by TempDB (interesting that they used spaces and called it “Temp DB”), user databases, and system databases.

SQLCEIP checks to see if your databases are encrypted.

In each database, they’re checking for encrypted columns:

And if you’re using SSMS’s Data Classification feature to classify data as personally identifiable, they’re inventorying that:

They’re also analyzing the backups you took in the last 24 hours, how long they’re taking, how big they are, and what kind of encryption type you’re using:

Can You Disable the CEIP Service?

I was disappointed when Microsoft first announced that you can’t turn off telemetry in Developer and Express Editions. I understood the value of having telemetry is on by default, but not allowing developers to turn it off struck me as ridiculous because that’s probably the least valuable telemetry you could gather.

Stopped the CEIP trace, back to rocking out

Now that I see these queries, I wonder even more.

Is Microsoft really making decisions based on how fast a developer’s laptop performs with encrypted backups, or how columnstore deltas are keeping up on her laptop? Or do they segment out the Developer Edition data?

And if they segment it out, why not let people turn off telemetry? Why gather so much ill will from SQL Server’s mandatory phoning-home of that near-useless data?

Normally I’d end this post with a link to the feedback item requesting it, but Microsoft has closed it and erased all votes on it, saying:

Microsoft has no current plans to change the model for how usage data is emitted.

Update 2019/02/08 from Conor Cunningham

Microsoft’s Conor Cunningham pointed out a few things in the comments:

  • These are indeed queries that SQLCEIP is running
  • However, that data may not actually be going back to Microsoft
  • In order to figure out if it’s going back to Microsoft, customers are expected to follow these steps
  • Even when customers do follow those steps, you have to repeat that process continuously because “Microsoft can and does adjust the queries we evaluate over time” – meaning, you might think they’re not gathering database names today, and they can turn right around and gather them in the next Cumulative Update.

I’m stunned that Microsoft still won’t just publish a list of data that they’re gathering from customers’ SQL Servers. What are they so afraid of? Surely they’ve got a written list of data they’re gathering, right?

Previous Post
The Next Mastering Series is About to Start. Wanna Save $2,000?
Next Post
[Video] An Introduction to GitHub for DBAs

88 Comments. Leave new

  • WOAH. I have this disabled in the paid versions, but we have a lot of Developer Edition instances.


  • Robert Sterbal
    February 7, 2019 9:12 am
  • Hi,

    Surely no-one is ‘unpaid’ versions to host sensitive data? My understanding is that this can be turned off in the ‘paid’ versions which you’d expect to be used in production environments?

  • Fascinating read. Thanks for the SQL research!

  • Do you find that many people block outbound connections from their database servers, which would be one imperfect way to stopping this collection?

  • Marcy Ashley-Selleck
    February 7, 2019 9:21 am

    Chin. On. Floor. Wow.

  • Randy in Marin
    February 7, 2019 9:30 am

    Does the data queue until it gets uploaded? My machine at home takes an extra long time to shutdown sometimes. I now have to wonder if it’s waiting on telemetry to be sent. What to whom or where….

  • If it strikes your fancy, maybe a good follow-up blog post would be “How to disable SQL Server telemetry in all editions”. I know they only formally allow it in the paid editions, but there’s always a way, right?

  • Is there a guide on how to turn this off completely? I have unchecked the two boxes in the ‘Error and Usage Reporting Settings’ dialogue. I have disabled the ‘SQL Server CEIP service’ in services manager, and I have disabled the SQL login that was created in my instance ‘NT SERVICE\SQLTELEMETRY$InstanceName’ …. is there more I have to do to disable everything that has to do with Telemetry?

  • Eduardo Pivaral
    February 7, 2019 9:47 am

    So… you can name your prod databases like AdventureWorks1, AdventureWorks2… and so on, to shield up a little bit

    • Randy in Marin
      February 7, 2019 10:04 am

      Or have some fun. NuclearCodes, TrumpLegalDocuments, MicrosoftEmpFamilyInfo, BlackmailPictures, PleaseDontSpyOnMe,…. Sorry, now that I have entered the word “Trump”, you’ll probably have to turn over all your cloud data to the feds for their investigation, as Apple has done.

  • And the truly huge financial and legal question – Does this violate the EU GDPR opt-in rules?

  • Stephen Mandeville
    February 7, 2019 10:57 am

    Just disable it all :

    Get-Service |
    Where-Object { $_.Name -like ‘*telemetry*’ -or $_.DisplayName -like ‘*CEIP*’ } |
    ForEach-Object {
    $servicename = $_.Name;
    $displayname = $_.DisplayName;
    Set-Service -Name $servicename -StartupType Disabled
    $serviceinfo = Get-Service -Name $servicename
    $startup = $serviceinfo.StartType
    Write-Host “$servicename : $startup : $displayname”;

    Set-Location “HKLM:\”
    $sqlentries = @( “\Software\Microsoft\Microsoft SQL Server\”, “\Software\Wow6432Node\Microsoft\Microsoft SQL Server\” )
    Get-ChildItem -Path $sqlentries -Recurse |
    ForEach-Object {
    $keypath = $_.Name
    (Get-ItemProperty -Path $keypath).PSObject.Properties |
    Where-Object { $_.Name -eq “CustomerFeedback” -or $_.Name -eq “EnableErrorReporting” } |
    ForEach-Object {
    $itemporpertyname = $_.Name
    $olditemporpertyvalue = Get-ItemPropertyValue -Path $keypath -Name $itemporpertyname
    Set-ItemProperty -Path $keypath -Name $itemporpertyname -Value 0
    $newitemporpertyvalue = Get-ItemPropertyValue -Path $keypath -Name $itemporpertyname
    Write-Host “$keypath.$itemporpertyname = $olditemporpertyvalue –> $newitemporpertyvalue”

    • That’s cute… do you realise that disabling a running service doesn’t stop it? It won’t start on next boot, but it doesn’t stop it now.

  • Thanks Brent,

    Last week, I did database migration (2016) on AWS and went live. I was monitoring the incoming requests and found out the same thing, started wondering. And today I got your email.. 🙂

    I have stopped and disabled both the services:

    1) CEIP service for Sql server
    2) SQL Server Integration Services CEIP service 13.0

    Disabled the login as well.

    1) NT Service\SQLTELEMETRY$xxxxxxx


  • Brent, we like you. If your gonna keep this up, you might want to hire a food taster.

  • They don’t know it but “Santa” is watching and he’s a wee bit ticked off right now! Thanks for the tip, Brent. EULA or not, this should be against the law!

  • That reminds me… I’ve gotta pull the fuse on my OnStar system.

  • I Use this one to deactivate CEIP telemetry over PowerShell

    # Disable all CEIP services

    Get-Service |? name -Like “*TELEMETRY*” | select -property name,starttype,status
    Get-Service -name “*TELEMETRY*” | Stop-Service -passthru | Set-Service -startmode disabled
    Get-Service |? name -Like “*TELEMETRY*” | select -property name,starttype,status

    # Deactivate all CEIP registry keys, Set all CEIP registry keys to 0

    $Sys32Key = “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server”
    $Sys32Items = Get-ChildItem $Sys32Key -Recurse | Where-Object -Property Property -eq ‘EnableErrorReporting’
    Get-ChildItem $Sys32Key -Recurse | Where-Object -Property Property -eq ‘EnableErrorReporting’| select -property name
    foreach ($Item in $Sys32Items)
    { $Item | Set-ItemProperty -Name ‘EnableErrorReporting’ -Value 0
    $Item | Set-ItemProperty -Name ‘CustomerFeedback’ -Value 0 }

    $Wow64Key = “HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server”
    $Wow64Items = Get-ChildItem $Wow64Key -Recurse | Where-Object -Property Property -eq ‘EnableErrorReporting’
    Get-ChildItem $Wow64Key -Recurse | Where-Object -Property Property -eq ‘EnableErrorReporting’| select -property name
    foreach ($Item in $Wow64Items)
    { $Item | Set-ItemProperty -Name EnableErrorReporting -Value 0
    $Item | Set-ItemProperty -Name CustomerFeedback -Value 0 }

    CEIP und SQL Server Data Tools in Visual Studio 2017
    $SSDTKey = “HKLM:\Software\Policies\Microsoft\VisualStudio”
    $SSDTtems = Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’
    Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’ | select -property name
    foreach ($Item in $SSDTtems) { $Item | Set-ItemProperty -Name OptIn -Value 0 }
    $SSDTKey = “HKLM:\Software\Wow6432Node\Microsoft\VSCommon”
    $SSDTtems = Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’
    Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’ | select -property name
    foreach ($Item in $SSDTtems) { $Item | Set-ItemProperty -Name OptIn -Value 0 }
    $SSDTKey = “HKLM:\Software\Microsoft\VSCommon ”
    $SSDTtems = Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’
    Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’ | select -property name
    foreach ($Item in $SSDTtems) { $Item | Set-ItemProperty -Name OptIn -Value 0 }
    CEIP und SQL Server Data Tools in Visual Studio 2015
    $SSDTKey = “HKCU:\Software\Microsoft\VSCommon”
    $SSDTtems = Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’
    Get-ChildItem $SSDTKey -Recurse | Where-Object -Property Property -eq ‘OptIn’ | select -property name
    foreach ($Item in $SSDTtems) { $Item | Set-ItemProperty -Name OptIn -Value 0 }

    $BINDKey = “HKCU:\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server”
    $BINDtems = Get-ChildItem $BINDKey -Recurse | Where-Object -Property Property -eq ‘CustomerFeedback’
    Get-ChildItem $BINDKey -Recurse | Where-Object -Property Property -eq ‘CustomerFeedback’ | select -property name
    foreach ($Item in $BINDtems) { $Item | Set-ItemProperty -Name CustomerFeedback -Value 0 }

    It is inspired by other ho scripted how to disable this Microsoft Device/Product tracking

  • Conor Cunningham, MSFT
    February 8, 2019 6:22 am

    Brent, I will encourage you to review the data sent through the documented interface for auditing what data actually gets sent to Microsoft. You can read that here: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/local-audit-for-sql-server-usage-feedback-collection?view=sql-server-2017 . Specifically, the queries you quote here are run by SQLCEIP but the data collected by Microsoft does not include database names as you insinuate in your post. The query in question is just used by SQLCEIP to go run per-database commands from within SQLCEIP. Microsoft does not care what the name of your databases are. We do care that the features we build get used and work for our customers and we use the telemetry to help focus our engineering efforts on features that customers need.

    • Conor – yep, I’ve gone through that, but here’s the problem: without a clear, documented list of what gets sent, I have nothing. SQLCEIP could (and indeed appears to) run different queries at different times. Knowing that, how am I supposed to make a definitive list? Does it gather different data depending on load? Which features are enabled? Which build I’m on?

    • Gerald Versluis
      April 11, 2019 6:31 am

      Conor, I’m confused… “help focus our engineering efforts on features that customers need”? Surely, that is not what SQLCEIP is doing. SQLCEIP could never collect information on features that do not exist. How does Microsoft determine that what happens, is or is not a workaround because of lacking features?

      Also, can yo explain how Microsoft will – as it is legally obliged to do under the EU GDPR – allow for the removal all data that in some way relates to any invidual, stored either on the SQL server or at Microsoft – and *prove* this removal?

  • Conor Cunningham, MSFT
    February 8, 2019 6:47 am

    You are missing the point. You can see for yourself that the database names you quote here from the HADR query are not being sent to Microsoft for the case in question if you inspect the actual telemetry – just grep for it. As written, your post is misleading customers about what actually gets collected by Microsoft and this can be confusing to customers. Whether we adjust the queries over time is orthogonal to that point.

    As for queries changing over time – Microsoft can and does adjust the queries we evaluate over time. If we get customer reports of a problem that appears common, we may go look to see how common that problem is so that we can prioritize fixes in later updates for an in-market release. As for what kinds of data we collect/don’t collect, you can see the privacy supplement here:
    This should give you a good idea of the kinds of data that would ever be collected or not collected. If there are cases where it is unclear, please feel free to let us know.

    • To make sure I’m getting the point, let me sum up your position:

      • It’s the customer’s responsibility to research what data Microsoft collects
      • That research is done by waiting and wondering when collection will happen, and how collection changes over time, because Microsoft doesn’t publish what triggers a collection or how collections vary
      • Microsoft can change that data at any time without warning
      • “a good idea of the kinds of data that would ever be collected” is supposed to be enough for compliance

      Did I miss anything or get anything incorrect there?

    • You’re also missing the point, Conor… A really serious point. While I understand the well intended reason to collect the data, Microsoft hasn’t given us a choice to opt out regardless of how innocent they claim the collection to be. This may queer any chances of DOD, other government related companies, health, and financial institutions of using SQL Server and may, in fact, cause them to drop existing SQL Server installations like a hot potato.

      Microsoft needs to add an “opt out” feature that actually and verifiably works.

  • Conor Cunningham, MSFT
    February 8, 2019 7:03 am

    I think you missed the part about how your original post was inaccurate and you should potentially go fix that, yes. You can try to deflect on that by changing the subject, but ultimately I hope your readers will see that slight of hand.

    Microsoft takes the compliance of our products very seriously. We have various compliance certifications that attest to our data handling practices, and the privacy supplement describes what we will not collect quite explicitly. We will work with customers who have compliance requirements for various industry verticals to make sure that they can use SQL to build compliant solutions. If there are areas where our privacy statements are unclear, we are very happy to work to make them clearer.

    I’m not here to argue with you Brent – just to point out when you post things that are not accurate/misleading to Microsoft’s customers. I ask that you correct your original post now that we’ve shown you the error.

    • Conor – certainly, I’ll go correct it to make it clear that these are queries that SQLCEIP is running against servers, and that I don’t personally have the time to verify what data gets sent to Microsoft, and that Microsoft is basically giving us the finger when it comes to listing what data they send in. You got it, coming right up.

    • Connor.

      I think Brent clearly identified the queries source, and you confirmed that they are indeed the queries. And you missed that there are two queries that you confirmed that collect the actual database name. Now, if there is some additional obfuscation of those names outside of the queries, maybe you’d like to post the source code that does just that? Otherwise, I’ve got a finger for you.

      • Hahaha, easy now with the fingers, let’s keep it civil, heh.

        To be fair, what Conor’s saying is that they run the query, but they don’t send that data up to Microsoft. (The privacy supplement is very dodgy around this – they can’t say that they don’t gather database names & table names & queries because they do gather crash dumps, and crash dumps contain exactly that.)

  • Conor Cunningham, MSFT
    February 8, 2019 7:34 am

    Jason, I will explain how SQLCEIP works in the hopes of clarifying for you. SQLCEIP connects to SQL Server and periodically runs queries. It also has XEvent session(s) that it starts to collect data. Some of those queries are used to set up context for other queries, and not all of the data from those queries are sent to Microsoft. Microsoft actually publishes a way to look at everything sent to Microsoft (both from queries and from XEvents) and that is documented in the link I posted earlier (https://docs.microsoft.com/en-us/sql/database-engine/install-windows/local-audit-for-sql-server-usage-feedback-collection?view=sql-server-2017). You can go see for yourself what is collected there.

    In the case of the query in question (which I have asked Brent to clarify in his post), the query does contain the database name. However, that query is used by SQLCEIP to subsequently connect to that database and run other queries but is not to return database names to Microsoft. If you follow the process we documented to see the data returned to Microsoft, you’d see that. Brent looked at the queries being run instead, and there is a possibility for confusion there as a result. I hope that gives you a path to validate that we are not trying to go collect database names as the original post suggests. We don’t collect that.

    (I did not miss that the database names were returned from the query that Brent posted – that is why I am here correcting the mistaken conclusion. In any event, we will go see if we can adjust those queries to avoid further possibility of confusion for those not following the documented process to audit what SQLCEIP sends)

    • Conor – you wrote:

      I hope that gives you a path to validate that we are not trying to go collect database names as the original post suggests. We don’t collect that.

      However, that simply doesn’t match what this page says:


      It says, and I quote:

      Customer content is defined as data stored within user tables, directly or indirectly. The data includes statistics or user literals within query texts that might be stored within user tables.

      Examples of customer content

      • Data values stored within the rows of any user table.
      • Statistics objects containing copies of values within the rows of any user table.
      • Query texts containing literal values.

      Crash Dumps may contain Customer Content and be emitted to Microsoft.

      Are you trying to say that crash dumps are only being collected on accident? If that’s a bug, what is Microsoft doing to fix that bug? Or are you collecting crash dumps on purpose, knowing that they contain access control data and personally identifiable data?

      You can’t have it both ways: either you’re knowingly gathering personally identifiable data on purpose, or you’re not.

    • Conor,

      I have read the article you linked. However, that details how to configure a custom path for the audit files. If I have no desire to go mucking about my SQL Server’s registry, can you link to a document that shows the default folder for these files? That way I can review what might have been gathered without ad-hoc changing settings on my production system?

      • Conor, you also state…

        “It also has XEvent session(s) that it starts to collect data. Some of those queries are used to set up context for other queries, and not all of the data from those queries are sent to Microsoft.”

        Looking on a SQL 2016 instance I had readily available, I see the events
        – database_created
        – database_dropped

        When I watch the live data from that extended_event, I clearly see a Field of “database name” and a Value of the database that is created or dropped. Can you post the source code where this data is obfuscated or dropped out before the data is transmitted off my premises? Forgoing that, can you post the default file and folder location where said extended_event “telemetry_xevents” is placed in plain text for me to review?

        name timestamp
        database_created 2019-02-09 08:29:50.0799223

        Field Value
        database_name CONOR_EXPLAIN_YOURSELF

        • Oh, and I found it in the live stream… via DBCC OUTPUTBUFFERS

          00000090 00 00 00 2c 00 00 00 43 00 4f 00 4e 00 4f 00 52 …,…C.O.N.O.R
          000000a0 00 5f 00 45 00 58 00 50 00 4c 00 41 00 49 00 4e ._.E.X.P.L.A.I.N
          000000b0 00 5f 00 59 00 4f 00 55 00 52 00 53 00 45 00 4c ._.Y.O.U.R.S.E.L
          000000c0 00 46 00 00 00 1d 00 00 00 13 00 00 00 00 00 00 .F…………..

        • Nothing from the guy who wants to tell us how MS is proud of their compliance and the teams of people they have to ensure legal compliance? Sorry Brent but I have a finger that’s wiggling again.

          • HAHAHA, I understand. It’s frustrating. Conor’s heart is in the right place, and he’s utterly brilliant, but he’s in the wrong here, and the MS docs on crash dumps even prove it – but he’s not going to budge, and he’s gone radio silent.

    • Thanks for the link, Conor. The trouble is that it appears to only show how to turn the local audit on or off. It doesn’t appear to show how to turn off telemetry. While I appreciate the value of the information our systems may be sending to MS, it should be up to us whether you get anything from our systems or not and we shouldn’t have to jump through a hoop to disable it.

      Is there an easy way to disable it?

      • Jeff – bad news, and Conor keeps dodging that: you CAN’T turn it off on the free versions of SQL Server (Express, Developer, and Evaluation.)

        To disable it on Standard & Enterprise, the easiest thing to do is read the comments here – a lot of other folks have come up with quicker/easier ways using PowerShell.

        • Yep… I get that. That’s all a part of why I think it’s just a wrong thing to do. It doesn’t actually give you the option at installation time for the paid editions to have your new instance to come up with telemetry disabled, correct?

  • There’s a high likelihood that Microsoft is using this data to pad their marketing numbers. Similar to how they were allegedly misinterpreting telemetry to pad Windows 10 enterprise deployment numbers.

  • Randy in Marin
    February 8, 2019 9:57 am

    I think Conor has a point. I can see how telemetry can be valuable to analyze usage and analyze issues, including adjusting the data collected to target a specific problem. The intentions are good…at least until a corrupt individual gets ahold of it. But for security, perhaps there can be – or is – an optional telemetry log that would hold all the information sent (including where it is sent) so that security officers can review it if required? If a hacker got control of this, they could mine for gold. In the bigger scheme of things, Microsoft telemetry is not what I worry most about. And if telemetry helps Microsoft makes products better, that’s a good thing as long as it is secure.

  • Chris Camilleri
    February 8, 2019 12:50 pm

    Could put in a rule at the firewall level to disable the phoning home for the telemetry service? It should be easy to figure out where the traffic is going.

  • “I was working in my lab…”

    At approximately what time, would you say? Did anything unusual occur?

  • DYNAMICS is the system database for the Microsoft Dynamics GP (formerly Great Plains) accounting system. It is quite interesting to me that is in there. It stores a lot of next number sort of things and system settings for the accounting package. One of the things it stores is the names of the various company databases that you have setup in the system, for your various legal entities for example

  • […] More information: https://www.brentozar.com/archive/2019/02/what-queries-does-microsofts-telemetry-service-run-on-your… […]

    • Just to clarify, it appears the query looks for Dynamics AX (Axapta) database, not GP. I’m not surprised that MS wants to see where the Dynamics installs are in the wild. There is a lot of money in the ERP ecosystem and they have invested a lot of money into the Dynamics line. But… querying for database names wouldn’t work with GP, as each GP database is named differently (you choose it on install).

  • Ah, if it’s AX, then my point is moot. But querying for database names would work with GP. They are all in the SY01500 table in the Dynamics database.

  • Stephen Falken
    February 13, 2019 2:05 pm

    If Microsoft REALLY wanted to know what features people wanted in SQL Server, they’d just dig through the old Connect database items and look at features and fixes people have been asking for for years.

    Just a thought.

  • Wow. Remarkable write up. Thank you!! Have been experiencing unexplained performance degradation on a new v2017 instance — checked server events today and found a very large amount of CEIP activity. Stopped/disabled it, and ran the tests again — duration reduced by more than 50%. Sometimes I really question their approach.

  • Xiaogang Zheng
    July 23, 2020 12:17 pm

    Great article. Thanks

  • This appears to have originally been written back in Feb 2019… Brent, has anything changed on this since then? He Microsoft released more information or changed their stance at all?

    • Cralis – for changes about Microsoft products over time, your best bet would be contacting Microsoft. I wish I could keep a running tab on everything they do without publishing documentation about it, but, yeah. You can kinda guess how that would go.

  • Brandon Forest
    December 16, 2020 9:28 am

    Brent, Thanks for your detailed analysis.

    I just started in a new position as Senior SQL DBA for a small bank. I’ve used your Consultants Toolkit and done a –deepdive into the one SQL 2016 production server. Noticing but not understanding what the CEIP service was, spurred me on to dig deeper. This post and similar SQL Server Central posts made me sit-up straight and pay attention. My boss just informed us that we are being audited by the FDIC this summer, and that he is relying on me to get the SQL side of the house in order.

    My inclination to to disable, but leave in place CEIP service.

    What negative effects can I expect and look to mitigate by doing this?

    As always, thanks and keep up the good work.

  • Randy in Marin
    December 16, 2020 9:34 am

    I get the feeling that the only way to be secure is to look for network adapters in your system. If any are found, remove them. This is probably not enough. Might need to disconnect the power cord too.

  • I guess I don’t understand the comments about not being able to disable CEIP on the Developer Edition because, under “Services” on my laptop, I’ve clearly been able to set those to “Disabled”. Are these not actually disabled?

  • We just started seeing this process starting with “SELECT target_data
    FROM sys.dm_xe_session_targets xet ” in our SolarWinds DPA tool showing significant more wait time (as in from less than 1 second within a 10 minute timeframe to 30 to 40 seconds) with mostly PREEMPTIVE_XE_GETTARGETSTATE wait starting on 12/16 and continuing after. Any ideas what could cause this process to suddenly have significantly more PREEMPTIVE_XE_GETTARGETSTATE wait time?

  • In my default trace, I tracked back an issue with my recovery model randomly changing to be from the SQLTELEMETRY user, with SqlServerCEIP as the application. Sql Server 13.0.5103.6, not sure if that makes a difference, but am astonished that the CEIP is actually updating the recovery model on our database.

  • Refused to Sink
    July 1, 2022 8:10 pm

    The service must be deleted, its login must be disabled, and all associated XE events must be stopped if CEIP requests are to be eliminated. May Microsoft always be reminded to act clearly against its customers?

  • Robert Sterbal
    July 5, 2022 7:43 pm

    A thoughtful thread. I wonder how much different it would be today.

    Is the situation different on Azure?

  • I might have missed it in the post, but what is, if any, the downside of disabling this service? Is it just that Microsoft doesn’t get the telemetry data they want from your server? We have a server in production running Enterprise 2017 and I noticed in Solarwinds DPA that waits for a query associated with this service shot up significantly over the weekend. Should I be concerned or can I just disable the service? Is this something that is covered in one of your classes, or can you direct me to where I should begin my investigation? Below is the query in question. Thanks in advance.

    /* BEGIN ACTIVE SECTION (comment inserted by DPA) */
    SELECT target_data
    FROM sys.dm_xe_session_targets xet
    JOIN sys.dm_xe_sessions xes
    ON xes.address = xet.event_session_address
    WHERE xes.name = ‘telemetry_xevents’
    AND xet.target_name = ‘ring_buffer’
    /* END ACTIVE SECTION (comment inserted by DPA) */


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.