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:

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] 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:

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; 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:

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 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:

select db_id() AS database_id, name, desired_state, actual_state, reason from sys.database_automatic_tuning_options where desired_state > 0 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:

SELECT DB_ID() AS database_id, predicate_type, operation, COUNT_BIG(*) AS Count FROM sys.security_predicates WITH(nolock) GROUP BY predicate_type, operation 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:

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) 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.

/*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 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.

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 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:

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 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:

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 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:

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 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?