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

I was working in my lab and I was having some odd blocking waits that I wasn’t expecting. I started firing up sp_WhoIsActive to see what queries were running, and I got a rather odd surprise:

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]

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

Some of the interesting queries include…

Microsoft is getting 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

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

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

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

They’re looking 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)

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

They’re checking 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

Are you using encryption? They know.

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

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

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

Summary

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.