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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT db_id() AS database_id, o.[type] AS object_type, i.[type] AS index_type, p.[data_compression], COUNT_BIG(DISTINCT p.[object_id]) AS NumTables, COUNT_BIG(DISTINCT CAST(p.[object_id] AS VARCHAR(30)) + '|' + CAST(p.[index_id] AS VARCHAR(10))) AS NumIndexes, ISNULL(px.[IsPartitioned], 0) AS IsPartitioned, IIF(px.[IsPartitioned] = 1, COUNT_BIG(1), 0) NumPartitions, SUM(p.[rows]) NumRows FROM sys.partitions p INNER JOIN sys.objects o ON o.[object_id] = p.[object_id] INNER JOIN sys.indexes i ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id] OUTER APPLY (SELECT x.[object_id], 1 AS [IsPartitioned] FROM sys.partitions x WHERE x.[object_id] = p.[object_id] GROUP by x.[object_id] HAVING MAX(x.partition_number) > 1) px WHERE o.[type] NOT IN ('S', 'IT') GROUP BY o.[type] ,i.[type] ,p.[data_compression] ,px.[IsPartitioned] |
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:
|
1 2 3 4 |
SELECT TOP 100 Query = CAST((N'<?ClickToSeeQuery -- ' + NCHAR(13) + NCHAR(10) + CAST(TextData AS NVARCHAR(MAX)) + NCHAR(13) + NCHAR(10) + N'-- ?>') AS XML), * FROM dbo.Trace ORDER BY LEN(CAST(TextData AS VARCHAR(8000))) DESC; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT TOP 30 d.[name] FROM sys.databases d WITH(nolock) INNER JOIN sys.master_files mf WITH(nolock) ON mf.database_id = d.database_id LEFT OUTER JOIN sys.database_mirroring dm WITH(nolock) ON dm.database_id = d.database_id LEFT OUTER JOIN sys.dm_hadr_database_replica_states hadr WITH(nolock) ON hadr.[database_id] = d.[database_id] WHERE d.[state] NOT BETWEEN 1 AND 6 -- online only AND d.[user_access] NOT IN (1, 2) -- multi user AND d.[source_database_id] IS NULL -- not snapshot AND d.[name] NOT IN ('master', 'tempdb', 'model', 'msdb') -- not system AND ISNULL(dm.[mirroring_role], 1) = 1 -- either principal or not mirrored AND d.[is_in_standby] = 0 -- not standby AND DATABASEPROPERTYEX(d.[name], 'Collation') IS NOT NULL -- started AND ISNULL(hadr.[database_state], 0) = 0 -- online in HADR GROUP BY d.[name] ORDER BY SUM(mf.size) DESC -- sampling using size |
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:
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:
|
1 2 3 4 5 6 7 |
select db_id() AS database_id, name, desired_state, actual_state, reason from sys.database_automatic_tuning_options where desired_state > 0 |
Most of them work more like this, just returning database ids:
|
1 2 3 |
SELECT DB_ID() AS database_id, predicate_type, operation, COUNT_BIG(*) AS Count FROM sys.security_predicates WITH(nolock) GROUP BY predicate_type, operation |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
SELECT database_id, database_guid, CASE WHEN db_name(database_id) LIKE '%WideWorldImportersDW%' THEN 'WideWorldImportersDW' WHEN db_name(database_id) LIKE '%WideWorldImporters%' THEN 'WideWorldImporters' WHEN db_name(database_id) LIKE '%AdventureWorks2016CTP3%' THEN 'AdventureWorks2016CTP3' WHEN db_name(database_id) LIKE '%AdventureWorksDW2016CTP3%' THEN 'AdventureWorksDW2016CTP3' WHEN db_name(database_id) LIKE '%AdventureWorks2014%' THEN 'AdventureWorks2014' WHEN db_name(database_id) LIKE '%AdventureWorksDW2014%' THEN 'AdventureWorksDW2014' WHEN db_name(database_id) LIKE '%AdventureWorks2012%' THEN 'AdventureWorks2012' WHEN db_name(database_id) LIKE '%AdventureWorksDW2012%' THEN 'AdventureWorksDW2012' WHEN db_name(database_id) LIKE '%AdventureWorks2008%' THEN 'AdventureWorks2008' WHEN db_name(database_id) LIKE '%AdventureWorksDW2008%' THEN 'AdventureWorksDW2008' WHEN db_name(database_id) LIKE '%AdventureWorksDW%' THEN 'AdventureWorksDW' WHEN db_name(database_id) LIKE '%AdventureWorks%' THEN 'AdventureWorks' WHEN db_name(database_id) LIKE '%ReportServerTempDB%' THEN 'ReportServerTempDB' WHEN db_name(database_id) LIKE '%ReportServer%' THEN 'ReportServer' WHEN db_name(database_id) LIKE '%WSS_Content%' THEN 'WSS_Content' WHEN db_name(database_id) LIKE '%MDW%' THEN 'MDW' WHEN db_name(database_id) LIKE '%MicrosoftDynamicsAX_baseline%' THEN 'MicrosoftDynamicsAX_baseline' WHEN db_name(database_id) LIKE '%MicrosoftDynamicsAX%' THEN 'MicrosoftDynamicsAX' WHEN db_name(database_id) = 'master' THEN 'master' WHEN db_name(database_id) = 'msdb' THEN 'msdb' WHEN db_name(database_id) = 'tempdb' THEN 'tempdb' WHEN db_name(database_id) = 'model' THEN 'model' WHEN db_name(database_id) LIKE '%SharePoint_Config%' THEN 'SharePoint_Config' WHEN db_name(database_id) LIKE '%SharePoint_AdminContent%' THEN 'SharePoint_AdminContent' WHEN db_name(database_id) LIKE '%AppManagement%' THEN 'AppManagement' WHEN db_name(database_id) LIKE '%Bdc_Service_DB%' THEN 'Bdc_Service_DB' WHEN db_name(database_id) LIKE '%Search_Service_Application_DB%' THEN 'Search_Service_Application_DB' WHEN db_name(database_id) LIKE '%Search_Service_Application_AnalyticsReportingStoreDB%' THEN 'Search_Service_Application_AnalyticsReportingStoreDB' WHEN db_name(database_id) LIKE '%Search_Service_Application_CrawlStoreDB%' THEN 'Search_Service_Application_CrawlStoreDB' WHEN db_name(database_id) LIKE '%Search_Service_Application_LinkStoreDB%' THEN 'Search_Service_Application_LinkStoreDB' WHEN db_name(database_id) LIKE '%Secure_Store_Service_DB%' THEN 'Secure_Store_Service_DB' WHEN db_name(database_id) LIKE '%SharePoint_Logging%' THEN 'SharePoint_Logging' WHEN db_name(database_id) LIKE '%SettingsServiceDB%' THEN 'SettingsServiceDB' WHEN db_name(database_id) LIKE '%User Profile Service Application_ProfileDB%' THEN 'User Profile Service Application_ProfileDB' WHEN db_name(database_id) LIKE '%User Profile Service Application_SyncDB%' THEN 'User Profile Service Application_SyncDB' WHEN db_name(database_id) LIKE '%User Profile Service Application_SocialDB%' THEN 'User Profile Service Application_SocialDB' WHEN db_name(database_id) LIKE '%WordAutomationServices%' THEN 'WordAutomationServices' WHEN db_name(database_id) LIKE '%Managed Metadata Service Application_Metadata%' THEN 'Managed Metadata Service Application_Metadata' WHEN db_name(database_id) LIKE '%SharePoint Translation Services%' THEN 'SharePoint Translation Services' WHEN db_name(database_id) LIKE '%ProjectWebApp%' THEN 'ProjectWebApp' WHEN db_name(database_id) LIKE '%DefaultPowerPivotServiceApplicationDB%' THEN 'DefaultPowerPivotServiceApplicationDB' WHEN db_name(database_id) LIKE '%PerformancePoint Service%' THEN 'PerformancePoint Service' WHEN db_name(database_id) LIKE '%SessionStateService%' THEN 'SessionStateService' WHEN db_name(database_id) = 'SSISDB' THEN 'SSISDB' WHEN db_name(database_id) = 'DQS_MAIN' THEN 'DQS_MAIN' ELSE 'Other' END AS default_database_class FROM sys.database_recovery_status WITH(nolock) |
The output looks like this:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/*The following query helps to understand how the Microsoft SQL Server Connector for Azure Key Vault (EKM Provider) is being used for those configuring Azure Key Vault for Transparent Data Encryption (TDE), Cell Level Encryption (CLE), and/or Backup encryption. The count of cryptographic EKM providers and the version number of SQL Server Connector on the SQL Server instance are collected. For non-Microsoft EKM providers, no specific information other than the count of non-Microsoft providers is being queried. Note: 'A16BA7DE-26E0-43C4-871C-4ED750C65597' is the provider guid for the Microsoft SQL Server Connector, and is used only to help identify its use by the instance.*/ SELECT 'Other' as ProviderType, null as ProviderVersion, count_big(*) as ProviderCount FROM sys.dm_cryptographic_provider_properties WHERE guid != 'A16BA7DE-26E0-43C4-871C-4ED750C65597' UNION ALL SELECT 'SQLServerConnector' as ProviderType, provider_version as ProviderVersion, 1 as ProviderCount FROM sys.dm_cryptographic_provider_properties WHERE guid = 'A16BA7DE-26E0-43C4-871C-4ED750C65597' ORDER BY ProviderType, ProviderVersion |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
select 1 as name, -- Temp DB case mf.type_desc when 'ROWS' then 1 when 'LOG' then 2 end as 'type', CAST(SUM(num_of_reads) as nvarchar(128)) as total_num_of_reads, CAST(SUM(num_of_bytes_read) as nvarchar(128)) as total_num_of_bytes_read, CAST(SUM(io_stall_read_ms) as nvarchar(128)) as total_io_stall_read_ms, CAST(SUM(num_of_writes) as nvarchar(128)) as total_num_of_writes, CAST(SUM(num_of_bytes_written) as nvarchar(128)) as total_num_of_bytes_written, CAST(SUM(io_stall_write_ms) as nvarchar(128)) as total_io_stall_write_ms, COUNT_BIG(*) as total from sys.dm_io_virtual_file_stats(NULL, NULL) fs, sys.master_files mf where fs.database_id = mf.database_id and fs.file_id = mf.file_id and fs.database_id = 2 and (fs.num_of_reads != 0 OR fs.num_of_writes != 0) group by mf.name, mf.type, mf.type_desc UNION ALL select 2 as name, -- User DBs case mf.type_desc when 'ROWS' then 1 when 'LOG' then 2 end as 'type', CAST(SUM(num_of_reads) as nvarchar(128)) as total_num_of_reads, CAST(SUM(num_of_bytes_read) as nvarchar(128)) as total_num_of_bytes_read, CAST(SUM(io_stall_read_ms) as nvarchar(128)) as total_io_stall_read_ms, CAST(SUM(num_of_writes) as nvarchar(128)) as total_num_of_writes, CAST(SUM(num_of_bytes_written) as nvarchar(128)) as total_num_of_bytes_written, CAST(SUM(io_stall_write_ms) as nvarchar(128)) as total_io_stall_write_ms, COUNT_BIG(*) as total from sys.dm_io_virtual_file_stats(NULL, NULL) fs, sys.master_files mf where fs.database_id = mf.database_id and fs.file_id = mf.file_id and fs.database_id > 4 and (fs.num_of_reads != 0 OR fs.num_of_writes != 0) group by mf.type, mf.type_desc UNION ALL select 3 as name, -- System DBs case mf.type_desc when 'ROWS' then 1 when 'LOG' then 2 end as 'type', CAST(SUM(num_of_reads) as nvarchar(128)) as total_num_of_reads, CAST(SUM(num_of_bytes_read) as nvarchar(128)) as total_num_of_bytes_read, CAST(SUM(io_stall_read_ms) as nvarchar(128)) as total_io_stall_read_ms, CAST(SUM(num_of_writes) as nvarchar(128)) as total_num_of_writes, CAST(SUM(num_of_bytes_written) as nvarchar(128)) as total_num_of_bytes_written, CAST(SUM(io_stall_write_ms) as nvarchar(128)) as total_io_stall_write_ms, COUNT_BIG(*) as total from sys.dm_io_virtual_file_stats(NULL, NULL) fs, sys.master_files mf WITH(nolock) where fs.database_id = mf.database_id and fs.file_id = mf.file_id and (fs.database_id = 1 OR fs.database_id = 3 OR fs.database_id = 4) and (fs.num_of_reads != 0 OR fs.num_of_writes != 0) group by mf.type, mf.type_desc |
SQLCEIP checks to see if your databases are encrypted.
In each database, they’re checking for encrypted columns:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT db_id() as database_id, object_id, column_id, user_type_id, max_length, precision, scale, collation_name, is_nullable, CASE WHEN encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256' THEN encryption_algorithm_name ELSE 'CustomAlgorithm' END AS encryption_algorithm_name, encryption_type_desc FROM sys.columns WITH(nolock) WHERE column_encryption_key_id IS NOT NULL |
And if you’re using SSMS’s Data Classification feature to classify data as personally identifiable, they’re inventorying that:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT DB_ID() AS database_id, COUNT_BIG(*) AS TotalNumberOfClassifiedColumns, COUNT_BIG(DISTINCT Totals.major_id) AS TotalNumberOfClassifiedTables, COUNT_BIG(DISTINCT Totals.information_type_name) AS TotalNumberOfUniqueInformationTypes, COUNT_BIG(DISTINCT Totals.sensitivity_label_name) AS TotalNumberOfUniqueSenstivityLabels FROM (SELECT C1.major_id, C1.minor_id, C1.information_type_name, C2.sensitivity_label_name FROM (SELECT major_id, minor_id, value AS information_type_name FROM sys.extended_properties WHERE NAME = 'sys_information_type_name') C1 FULL OUTER JOIN (SELECT major_id, minor_id, value AS sensitivity_label_name FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name') C2 ON (C1.major_id = C2.major_id AND C1.minor_id = C2.minor_id)) AS Totals |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
SELECT b1.device_type, ISNULL(b1.[type],'NULL') AS backup_type, b1.is_copy_only, b1.time_bucket, b1.compressed_backup_size_bucket, b1.compression_percent_bucket, b1.backup_throughput_bucket, COUNT_BIG(b1.device_type) AS 'count', b1.encryption FROM ( SELECT mf.device_type, bset.[type], CAST(bset.[is_copy_only] AS INT) AS [is_copy_only], CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 0 WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 1 AND 30 THEN 1 WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 30 AND 60 THEN 2 WHEN DATEDIFF(MINUTE, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 1 AND 5 THEN 3 WHEN DATEDIFF(MINUTE, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 5 AND 10 THEN 4 WHEN DATEDIFF(MINUTE, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 10 AND 30 THEN 5 WHEN DATEDIFF(MINUTE, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 30 AND 60 THEN 6 WHEN DATEDIFF(HOUR, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 1 AND 2 THEN 7 WHEN DATEDIFF(HOUR, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 2 AND 6 THEN 8 WHEN DATEDIFF(HOUR, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 6 AND 12 THEN 9 WHEN DATEDIFF(HOUR, bset.[backup_start_date], bset.[backup_finish_date]) BETWEEN 12 AND 24 THEN 10 ELSE 99 END AS 'time_bucket', CASE WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 0 AND 1 THEN 0 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 1 AND 5 THEN 1 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 5 AND 25 THEN 2 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 25 AND 100 THEN 3 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 100 AND 500 THEN 4 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 500 AND 1000 THEN 5 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 1000 AND 5000 THEN 6 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 5000 AND 10000 THEN 7 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 10000 AND 50000 THEN 8 WHEN CAST(ISNULL(bset.compressed_backup_size, 0) / 1048576 AS FLOAT) BETWEEN 50000 AND 100000 THEN 9 ELSE 99 END AS compressed_backup_size_bucket, CASE WHEN CAST(((ISNULL(bset.backup_size,0) - ISNULL(bset.compressed_backup_size,0)) / CASE WHEN bset.backup_size <= 0 THEN 1 ELSE ISNULL(bset.backup_size, 1) END ) * 100 AS FLOAT) = 0.0 THEN 0 ELSE (CAST(((ISNULL(bset.backup_size,0) - ISNULL(bset.compressed_backup_size,0)) / CASE WHEN bset.backup_size <= 0 THEN 1 ELSE ISNULL(bset.backup_size, 1) END ) * 100 AS INT)/10) + 1 END AS compression_percent_bucket, CASE WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) = 0.0 THEN 0 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 0 AND 100 THEN 1 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 100 AND 500 THEN 2 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 500 AND 1000 THEN 3 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 1000 AND 5000 THEN 4 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 5000 AND 10000 THEN 5 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 10000 AND 20000 THEN 6 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 20000 AND 30000 THEN 7 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 30000 AND 50000 THEN 8 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 50000 AND 100000 THEN 9 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 100000 AND 500000 THEN 10 WHEN CAST(CAST(ISNULL(bset.compressed_backup_size, 0) / 1024 AS FLOAT) / (CASE WHEN DATEDIFF(SECOND, bset.[backup_start_date], bset.[backup_finish_date]) = 0 THEN 1 END) AS FLOAT) BETWEEN 500000 AND 1000000 THEN 11 ELSE 99 END AS backup_throughput_bucket, -- in KB CASE WHEN bset.encryptor_type like '%CERTIFICATE%' THEN 1 WHEN bset.encryptor_type like '%ASYMMETRIC KEY%' THEN 2 ELSE 0 END AS encryption FROM [msdb].[dbo].[backupset] bset WITH (READUNCOMMITTED) JOIN [msdb].[dbo].[backupmediafamily] mf WITH (READUNCOMMITTED) ON mf.media_set_id = bset.media_set_id WHERE backup_finish_date <= GETDATE() and backup_finish_date > DATEADD(hh, -24, GETDATE() ) ) AS b1 GROUP BY b1.device_type, b1.[type], b1.is_copy_only, b1.time_bucket, b1.compressed_backup_size_bucket, b1.compression_percent_bucket, b1.backup_throughput_bucket, b1.encryption |
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.
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?
88 Comments. Leave new
WOAH. I have this disabled in the paid versions, but we have a lot of Developer Edition instances.
WOAH.
It reminded me of the Russinovich Sony rootkit: https://blogs.technet.microsoft.com/markrussinovich/2005/10/31/sony-rootkits-and-digital-rights-management-gone-too-far/
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?
(cough) Express Edition (/cough)
LOL
Sorry
Fascinating read. Thanks for the SQL research!
You bet, my pleasure.
Do you find that many people block outbound connections from their database servers, which would be one imperfect way to stopping this collection?
imperfect way *of
James – no, I don’t find that, especially on dev/test/QA/evaluation/Express environments.
If I knowingly disable this kind of traffic (regarding telemetry services). Would that be a violation of the terms of use for non-paid versions of SQL Server?
Chin. On. Floor. Wow.
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?
Where there’s is a will, there’s a way. Now, if gets legal, then the will might take on a different meaning.
So its legal for them to take our data, not legal for us to disable. Wtf?
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?
Brandon – yeah, you can start here: https://stackoverflow.com/questions/43548794/how-to-turn-off-telemetry-for-sql-2016
Thanks for the link!
So… you can name your prod databases like AdventureWorks1, AdventureWorks2… and so on, to shield up a little bit
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.
Uh-oh! You may have triggered some people with those words! Good thing you didn’t say WallDB. Ooops!
And the truly huge financial and legal question – Does this violate the EU GDPR opt-in rules?
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
Thanks,
Brent, we like you. If your gonna keep this up, you might want to hire a food taster.
HAHAHA, that’s true. Lord knows I have enough enemies already.
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!
Microsoft will no doubt agree. It should be against the law…to disable their telemetry. However, just in case it is illegal to require telemetry, the purchase contract will have a “You should have bought the squirrel” clause where a “SQL Health Care Telemetry” deduction does not apply if it’s disabled.
PS https://www.youtube.com/watch?v=zfmZSiFlgKU (funny stuff)
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
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?
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?
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:
https://docs.microsoft.com/en-us/sql/sql-server/sql-server-privacy?view=sql-server-2017
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:
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.
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.)
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:
However, that simply doesn’t match what this page says:
https://docs.microsoft.com/en-us/sql/sql-server/sql-server-privacy?view=sql-server-2017
It says, and I quote:
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.
He went a bit quiet after that, didn’t he?
Sure did.
I have to tell you, Brent… thank you for this thread and the research you and other respondents have done. I knew there was an issue (and is a reason I stopped using the MAP tool for migrations long ago) but had no idea it was this bad on SQLCEIP.
You’re welcome, Jeff! My pleasure.
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?
Yep, exactly.
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.
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.
PS By “telemetry log”, I don’t mean that stuff in the link that hacks telemetry configuration.
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.
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.
+ 1 BILLION!!!
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.
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.
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.
Brandon – you’ll want to confer with your security team and Microsoft.
Roger that. Thanks.
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.
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?
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
WITH
(
nolock
)
JOIN sys.dm_xe_sessions xes
WITH
(
nolock
)
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) */
That’s outside of the scope of this post.
Gotcha, no prob. Is this something that you cover elsewhere, like in a class?
No.
Alright, no problem. The post was helpful in at least explaining what CEIP is though.