Blog - Brent Ozar

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

February 7, 2019

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:

sp_WhoIsActivelyRunningTelemetryQueries (click to see full size)

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

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]

The trick is blocking the mind control waves while still letting the headphones get a signal

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

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

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

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…

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

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

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)

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 was kinda hoping AdventureWorks would be shown as “economy class”

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

Most of the 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

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

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

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

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

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.

Stopped the trace, back to rocking out

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

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

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

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

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

Update 2019/02/08 from Conor Cunningham

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

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

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

The Next Mastering Series is About to Start. Wanna Save $2,000?

February 6, 2019

Brent Ozar

Company News, Conferences and Classes

No Comments

Ever wanted to take my Mastering series of classes? I recommend that you take ’em in a specific order because each class builds on the next. If you try to parachute in out of order, you’re gonna have a bad time.

The next rotation is about to start:

It’s not a cheap investment, I know: the Mastering classes are $2,995 each. That’s why a lot of folks choose the Live Class Season Pass which lets you attend all of ’em for a year straight – so you can re-take classes when work gets in the way, or when you wanna take your game up a notch.

There’s a new less expensive option:
save $2,000 by skipping the lab VMs.

During the Mastering classes, you get your own private lab VM in the cloud to follow along during the labs. You’re tasked with a series of tough assignments, and they help you reinforce the concepts that you’ve been seeing during the lectures. You get a lunchtime lab, and an afternoon/overnight lab as homework.

However, some students don’t use their lab VMs – they have a pesky day job that interferes. During the lunch & afternoon breaks, they work on their day job stuff instead, answering emails and doing help desk tickets.

Since the lab VMs are pretty expensive, I’d rather pass the savings on to you. So starting today, you can pick up a Live Class Season Pass for just $3,995. Enjoy, and see you in class!

Registration Open for My Upcoming Pre-Cons: Belgium, Dallas, Iceland, Israel, Miami, Orlando!

February 5, 2019

Brent Ozar

Company News

No Comments

I’m hitting the road this spring. Here’s where to find me in person:

SQLSaturday Iceland – Your First SQL Server Performance Tuning Class – March 16 – You’re stuck with a database server that’s not going fast enough. You have a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns. Learn more and register. – Update: sold out! I pre-announce these to our mailing list subscribers, and they snapped up the all seats over the weekend.

SQLSaturday Israel – Performance Tuning in 21 Demos – April 10 – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you want to jump right into the code and see what happens. You’re the kind of person who loves watching YouTube videos that start with, “Hold my beer and watch this.” Learn more and register. (24 seats left as of this writing.)

Techorama Belgium – SQL Server Performance Tuning in a Day – May 20 – You’re stuck with a database server that’s not going fast enough. You’ve got a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns. You’ll get your developers, sysadmins, and DBAs all on the same page, talking about the same metrics, and understanding whether the right fix is code, indexes, or hardware. Learn more and register.

SQLSaturday Dallas – Performance Tuning in 21 Demos – May 31 – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you want to jump right into the code and see what happens. You’re the kind of person who loves watching YouTube videos that start with, “Hold my beer and watch this.”Attendees will receive a year’s access to their choice: the Recorded Class Season Pass or the Consultant Toolkit. Learn more and register.

Techorama – now that’s a projector

SQLSaturday South Florida – Performance Tuning in 21 Demos – June 7 – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you want to jump right into the code and see what happens. You’re the kind of person who loves watching YouTube videos that start with, “Hold my beer and watch this.” Attendees will receive a year’s access to their choice: the Recorded Class Season Pass or the Consultant Toolkit. Learn more and register.

SQL Intersection Orlando – Developer’s SQL Server Recipe Book – June 9 – You and your team have to build a lot of queries: transactional insert/update/deletes, batch jobs, and reports. You’re comfortable writing queries to get data in and out, but they’re just not as fast as you’d like. You’re looking for a set of examples that you can follow – plus a list of anti-patterns that seem good until they just don’t scale. I’ll share my favorite T-SQL starting points for different business requirements. We’ll implement them on the Stack Overflow database, and you can follow along on your laptop.Attendees will receive a year’s access to their choice: the Recorded Class Season Pass or the Consultant Toolkit. Learn more and register.

If you’re organizing an event this fall/winter, and you’d like me to present a pre-con, I’d love to help. Email me at Help@BrentOzar.com – the earlier, the better. I tend to book several months in advance. See you out there!

The “Guess the Next Release Date” contest continues…

February 4, 2019

Brent Ozar

SQL Server 2019

Back in December 2017, when I posted the Guess the Next SQL Server Release Date Contest, you could leave your guess in the comments for a chance to win a Live Class Season Pass and a Recorded Class Season Pass.

Here’s how the guesses so far have gone – and for this quick analysis, I’m only including people who followed the rules, not people who put a bunch of text in their comment. When it comes time to pick a winner, I’ll go through the text ones too.

Looks like most folks expected it to be around the end of last year, 2018. I talked to a few folks about where their guesses came from, and they’d taken hints from the 2017 post from the Release Services team about the new Modern Servicing Model. At that time, Microsoft wrote:

CUs will be delivered more often at first and then less frequently. Every month for the first 12 months, and every quarter for the remainder 4 years of the full 5-year mainstream lifecycle.

Between that, and the quick cadence between SQL Server 2016 and 2017, they suspected that Microsoft would be shipping a new version of SQL Server every year. Made sense at the time. (Later, Microsoft revisited that support policy.)

Now, looking back:

SQL Server 2012: 2012-04-01
SQL Server 2014: 2014-04-21 (~25 months later)
SQL Server 2016: 2016-06-01 (~25 months later)
SQL Server 2017: 2017-10-02 (~16 months later)
Today, 2019-02-04: 16 months later

The SQL Server 2019 release cadence is actually slower than the 2017 pace. Interesting. The 2019 release is definitely more ambitious – Kubernetes, Big Data Clusters, batch mode for rowstore queries – there’s a lot of huuuuge stuff in here.

Something to think about as you place your guess. Good luck!

Update 2019/03/01 – closed comments here since folks got confused as to where the contest is happening.

[Video] Getting Started with the Consultant Toolkit

February 1, 2019

Brent Ozar

Consultant Toolkit, Videos

9

You’re a consultant who needs to figure out why your clients’ SQL Servers are slow and unreliable.

You’ve collected a bunch of diagnostic queries over the years, but they have a lot of holes, and you don’t really have the time to maintain them – let alone make ’em better. You waste time connecting to the server, running one query at a time, copy/pasting the results into Excel or taking screenshots. You’re burning the client’s money doing manual labor, and neither of you are happy with the results.

That’s where our Consultant Toolkit comes in, and today I did a live webcast talking about how it works:

Get a free trial and a big discount during our launch sale – ends Saturday.

10 Database Tasks That Sound Easy, But Aren’t

January 31, 2019

Brent Ozar

Processes and Practices

32

All of these are doable with scripts, tools, and/or elbow grease, mind you – they’re just not as easy as they sound at first glance until you know about the existence of those scripts, tools, and/or free people waiting around with spare elbow grease.

“Go through the log and find out who ran this query.”
“Apps sometimes time out when connecting to database server. Why?”
“Build a new server with the same configuration as the old server.”
“I just ran an UPDATE without a WHERE clause. Undo just that one query.”
“Show me everyone who can read from this table, including groups and app users.”
“Run a load test on these two servers and compare them.”
“The reports server needs a synced copy of the accounting server’s customer table.”
“What were the slowest running queries yesterday?”
“Why is this query slow in production, but fast in development?”
“Apply the latest updates for the cluster.”

As you read through that list and say to yourself, “Hey, I know how to do some of those really easily,” stop and congratulate yourself. You’ve learned things over the years, and you’re better at databases than when you started.

Announcing the Consultant Toolkit: Now Available in the European Union, too

January 30, 2019

Brent Ozar

Consultant Toolkit

14

Yesterday, I got a barrage of emails and LinkedIn messages that went like this:

Hey Brent, about your new consultant toolkit: I really love that idea and would want to get my hands on it. Unfortunately i originate in the EU. I know about your reasoning around not selling to EU members and I don’t want to start a discussion around that. Do you see ANY way how I can purchase it? I’ve written plenty of wrappers in the past, but I’d rather spend my time making servers faster than maintaining custom code bases whenever procedures change.

Alright, alright, alright.

You can now buy the Consultant Toolkit through an EU-friendly distributor. (That launch sale pricing is good for this week only.)

And I know what your next question’s going to be: “So, when can we buy the training classes and SQL ConstantCare® through them?” Those are quite a bit harder since the training classes involve a lot of streaming videos, and SQL ConstantCare® involves a lot of data. It’s on my radar, and it’s something that we’re working on for later this year, but I don’t have anything to announce yet.

Announcing the Official Release of the Consultant Toolkit

January 29, 2019

Brent Ozar

Consultant Toolkit

13

You’re a consultant, and you need to get diagnostic data from your clients’ SQL Servers.

You’ve been copy/pasting queries and results from SSMS, trying to save execution plans one by one, wasting time doing manual labor.

That stops right now.

For years, we’ve had our own slick in-house utility that makes the process way easier. Here’s how it works:

There’s no per-client pricing: use this on as many servers as you want, at as many clients as you want. This is all about helping you tackle more work, in less time, and make more money. I’m even including the same email templates and help instructions that I’ve used with clients for years.

The regular pricing will be:

$495 for one user (consultant)
$895 for teams of 2-4 people
$1,795 for teams of 5 or more

But right now, we’re running launch pricing, and your first month is free. Let’s do this!

Update: European Union folks can buy the Consultant Toolkit through an EU-friendly distributor. (There’s no free trial there, unfortunately – that distributor doesn’t have those same kinds of options.)

Updated First Responder Kit with Your January Code Resolutions

January 28, 2019

Brent Ozar

First Responder Kit Updates

4

I have a sneaking suspicion that a bunch of you made New Year’s Resolutions to contribute to open source, because y’all got started with a bang this year. We’ve even got improvements from Microsoft folks for Azure Managed Instances!

You can download the updated FirstResponderKit.zip here.

sp_Blitz Changes

Improvements: skipping checks on Azure SQL DB Managed Instance that aren’t relevant, like system databases on C, Hekaton enabled, and TDE enabled. (#1919, thanks Jovan Popovic.)
Fix: if you leave your Amazon EC2 VM name as the default (c’mon, seriously people), we won’t flag it as an RDS server. (#1925, thanks Randy Knight.)
Fix: when alerting on backups written to the same drive as databases, ignore tempdb. (#1916, thanks Henrik Staun Poulsen.)

We also added a top-row result warning if checks were skipped because it detected AWS RDS, Express Edition, or Managed Instances – or combinations, like here:

sp_Blitz running on AWS RDS Express Edition

sp_BlitzBackups Changes

Improvement: now raises a 16 severity error if it fails due to a nonexistent linked server, making it easier to troubleshoot failures. (#1904, thanks M-Ron.)

sp_BlitzFirst Changes

Fix: when sp_BlitzFirst is set up in an Agent job to call sp_BlitzCache, it now checks the SQL Server version before trying to sort by memory grants (which isn’t possible on older versions/patch-levels.) (#1910, thanks M-Ron.)

sp_BlitzIndex Changes

Improvement: when you pass in @TableName to do table-level analysis, we added a new result set for statistics histograms at the bottom. (#1900)
Improvement: in @Mode = 2 (listing all the existing indexes), we added columns at the far right with the drop and create TSQL. (#1921, thanks Joshua Higginbotham.)
Improvement: when @Mode = 4 doesn’t find any results, it now links to FirstResponderKit.org instead of BrentOzar.com. (Trying to remove unnecessary links to us to make it easier for consultants to copy/paste the results as-is for clients.) (#1905)
Fix: if you don’t set @BringThePain = 1 and we find >100 partitions, we now raise a level 0 severity error instead of 16. We don’t need to pop you over to the messages tab since there’s a line about the partitions at the top of the result set anyway. (#1914)
Fix: @GetAllDatabases = 1 was failing on Amazon RDS when it hit the rdsadmin database because they’re doing something fancy with the resource database. (#1927)

sp_DatabaseRestore Changes

Improvement: validate the @Database parameter against the full backup file. (#1894, thanks ShawnCrocker.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

You can download the updated FirstResponderKit.zip here.

How to Think Like the Engine – 2019 Edition

January 25, 2019

Brent Ozar

Indexing, SQL Server, Videos

6

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.

In 90 minutes of videos, you’ll learn:

The differences between clustered and nonclustered indexes
How (and when) to make a covering index
The basics of execution plans
What determines sargability
What SQL Server uses to estimate the memory your query needs

And more. To follow along, print out this 5-page PDF built from Stack Overflow’s database pages. Here’s the first module:

Wanna keep watching?

To see the rest and get the slides & scripts, head on over to How to Think Like the Engine. Enjoy!

Watch Brent Tune Queries: Fixing Nested Functions

January 24, 2019

Brent Ozar

T-SQL

15

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch.

However, there are some cases where I just can’t work around a perfect storm of anti-patterns. To understand why, let’s take an example. I’ve kept the general idea the same, but I’ve rewritten the entire example in the Stack Overflow database to protect the innocent.

The most resource-intensive query was a stored procedure – let’s say it looked for interesting users who live in the same location as you, who you might like to meet, because they’ve commented on the same answers that you have:

CREATE OR ALTER PROC dbo.usp_FindUsersToMeet @UserId INT AS
BEGIN
SELECT u.DisplayName, u.Location, u.Reputation, u.Id, u.AboutMe
  FROM dbo.ufn_UsersYouMightLike(@UserId) ul
    INNER JOIN dbo.Users u ON ul.UserId = u.Id
END
GO

1

2

3

4

5

6

7

CREATE OR ALTER PROC dbo.usp_FindUsersToMeet @UserId INT AS

BEGIN

SELECT u.DisplayName, u.Location, u.Reputation, u.Id, u.AboutMe

FROM dbo.ufn_UsersYouMightLike(@UserId) ul

INNER JOIN dbo.Users u ON ul.UserId = u.Id

END

GO

Uh oh – it called a table-valued function, ufn_UsersYouMightLike. What’s in there?

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)
RETURNS @Users TABLE   
(  
    UserID INT      
)  
AS  
BEGIN  
/* Build a list of other users in the same city */
DECLARE @OtherUsersInYourLocation TABLE (Id INT);
INSERT INTO @OtherUsersInYourLocation(Id)
  SELECT them.Id
    FROM dbo.Users me
    INNER JOIN dbo.Users them ON me.Location = them.Location
    WHERE me.Id = @UserId;

/* Find questions they've commented on, that you've also commented on */
INSERT INTO @Users (UserId)
SELECT DISTINCT o.Id
  FROM @OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId
  WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1; /* You also commented on the same post*/  
RETURN
END
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)

RETURNS @Users TABLE

(

UserID INT

)

AS

BEGIN

/* Build a list of other users in the same city */

DECLARE @OtherUsersInYourLocation TABLE (Id INT);

INSERT INTO @OtherUsersInYourLocation(Id)

SELECT them.Id

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

WHERE me.Id = @UserId;

/* Find questions they've commented on, that you've also commented on */

INSERT INTO @Users (UserId)

SELECT DISTINCT o.Id

FROM @OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1; /* You also commented on the same post*/

RETURN

END

GO

Uh oh – it’s getting worse. It populates a table variable, and we know those have massive problems. Then, it queries that table variable, and it has a scalar function in the where clause. What’s in that fn_HasCommentedOnAnAnswer?

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)  
RETURNS INT
AS   
BEGIN  
    /* Get the current PostTypeId for an answer */
    DECLARE @PostTypeId INT;
    SET @PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer');

    /* Find out if they've left a comment on an answer to this @PostId */
    DECLARE @HasCommented int = 0;  
    IF EXISTS (SELECT *
                    FROM dbo.Posts q
                    INNER JOIN dbo.Posts a ON q.Id = a.ParentId
                                          AND a.PostTypeId = @PostTypeId
                    INNER JOIN dbo.Comments c ON a.Id = c.PostId
                    WHERE q.Id = @PostId
                      AND c.UserId = @UserId)
        SET @HasCommented = 1;
    RETURN @HasCommented;  
END; 
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)

RETURNS INT

AS

BEGIN

/* Get the current PostTypeId for an answer */

DECLARE @PostTypeId INT;

SET @PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer');

/* Find out if they've left a comment on an answer to this @PostId */

DECLARE @HasCommented int = 0;

IF EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a ON q.Id = a.ParentId

AND a.PostTypeId = @PostTypeId

INNER JOIN dbo.Comments c ON a.Id = c.PostId

WHERE q.Id = @PostId

AND c.UserId = @UserId)

SET @HasCommented = 1;

RETURN @HasCommented;

END;

GO

Hoo boy. We’ve got some work to do. We have a few problems to fix:

A table variable – producing bad row estimates, underestimating costs, and probably stopping us from getting parallelism
A multi-statement table-valued function – which makes performance tuning way harder for my developers, who couldn’t see what was really happening inside the stored procedure
A scalar function – which the above function is going to run row by row, and it’s going to inhibit parallelism too

I’m going to unpack all of ’em, and I could probably fix ’em in any order. Let’s start with the scalar.

Fixing the scalar function by inlining it

My general advice on scalars is to take their logic out and try to inline them wherever they’re called. In our case, the calling query looks like this:

SELECT DISTINCT o.Id AS UserId
  FROM OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId
  WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1; 
/* You also commented on the same post*/

1

2

3

4

5

SELECT DISTINCT o.Id AS UserId

FROM OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1;

/* You also commented on the same post*/

So in a perfect world, I’d open up fn_HasCommentedOnAnAnswer, copy the logic out, and paste it directly into the WHERE clause here. Let’s open it up and see if I can do it:

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)  
RETURNS INT
AS   
BEGIN  
    /* Get the current PostTypeId for an answer */
    DECLARE @PostTypeId INT;
    SET @PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer');

    /* Find out if they've left a comment on an answer to this @PostId */
    DECLARE @HasCommented int = 0;  
    IF EXISTS (SELECT *
                    FROM dbo.Posts q
                    INNER JOIN dbo.Posts a ON q.Id = a.ParentId
                                          AND a.PostTypeId = @PostTypeId
                    INNER JOIN dbo.Comments c ON a.Id = c.PostId
                    WHERE q.Id = @PostId
                      AND c.UserId = @UserId)
        SET @HasCommented = 1;
    RETURN @HasCommented;  
END; 
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)

RETURNS INT

AS

BEGIN

/* Get the current PostTypeId for an answer */

DECLARE @PostTypeId INT;

SET @PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer');

/* Find out if they've left a comment on an answer to this @PostId */

DECLARE @HasCommented int = 0;

IF EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a ON q.Id = a.ParentId

AND a.PostTypeId = @PostTypeId

INNER JOIN dbo.Comments c ON a.Id = c.PostId

WHERE q.Id = @PostId

AND c.UserId = @UserId)

SET @HasCommented = 1;

RETURN @HasCommented;

END;

GO

Drat – they’ve got multiple statements in here. They’re setting a configuration variable, and then using that variable in a subsequent query. The more queries you find in the scalar, the harder the rewrite is going to be.

In this case, I’m going to do a very fast and dirty rewrite. I bet the @PostTypeId is a config variable that doesn’t even change very often, and I could probably hard-code it, but I’m not going to go quite that dirty. Here, I’m just going to dump the config variable into a subquery.

Instead of this:

INNER JOIN dbo.Posts a 
  ON q.Id = a.ParentId
  AND a.PostTypeId = @PostTypeId

1

2

3

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = @PostTypeId

I’m going to do this:

INNER JOIN dbo.Posts a 
  ON q.Id = a.ParentId
  AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

1

2

3

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

I know – not elegant – but it does get my scalar function down to just one query:

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)  
RETURNS INT
AS   
BEGIN  
    /* Find out if they've left a comment on an answer to this @PostId */
    DECLARE @HasCommented int = 0;  
    IF EXISTS (SELECT *
                    FROM dbo.Posts q
                    INNER JOIN dbo.Posts a 
                      ON q.Id = a.ParentId
                      AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')
                    INNER JOIN dbo.Comments c ON a.Id = c.PostId
                    WHERE q.Id = @PostId
                      AND c.UserId = @UserId)
        SET @HasCommented = 1;
    RETURN @HasCommented;  
END; 
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

CREATE OR ALTER FUNCTION dbo.fn_HasCommentedOnAnAnswer(@UserId INT, @PostId INT)

RETURNS INT

AS

BEGIN

/* Find out if they've left a comment on an answer to this @PostId */

DECLARE @HasCommented int = 0;

IF EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

INNER JOIN dbo.Comments c ON a.Id = c.PostId

WHERE q.Id = @PostId

AND c.UserId = @UserId)

SET @HasCommented = 1;

RETURN @HasCommented;

END;

GO

Which means that now I can take that one SELECT query, copy it out, and paste it into the query where it was being called, ufn_UsersYouMightLike. Here’s the part of that function that was calling the scalar:

SELECT DISTINCT o.Id
  FROM @OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId
  WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1; /* You also commented on the same post*/

1

2

3

4

SELECT DISTINCT o.Id

FROM @OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

WHERE dbo.fn_HasCommentedOnAnAnswer(@UserId, c.PostId) = 1; /* You also commented on the same post*/

If I copy out the scalar’s logic and embed it into the WHERE clause, it’ll look like this:

SELECT DISTINCT o.Id
  FROM @OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId
  WHERE EXISTS (SELECT *
        FROM dbo.Posts q
        INNER JOIN dbo.Posts a 
            ON q.Id = a.ParentId
            AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')
        INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId
        WHERE q.Id = c.PostId
            AND cInner.UserId = @UserId); /* You also commented on the same post*/

1

2

3

4

5

6

7

8

9

10

11

SELECT DISTINCT o.Id

FROM @OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

WHERE EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId

WHERE q.Id = c.PostId

AND cInner.UserId = @UserId); /* You also commented on the same post*/

Ugh. I’m not proud of that, but at least the scalar’s out of the way for now. Next up…

Fixing the multi-statement TVF by inlining it

After inlining the scalar, I’m dealing with an outer function that looks like this:

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)
RETURNS @Users TABLE   
(  
    UserID INT      
)  
AS  
BEGIN  
/* Build a list of other users in the same city */
DECLARE @OtherUsersInYourLocation TABLE (Id INT);
INSERT INTO @OtherUsersInYourLocation(Id)
  SELECT them.Id
    FROM dbo.Users me
    INNER JOIN dbo.Users them ON me.Location = them.Location
    WHERE me.Id = @UserId;

/* Find questions they've commented on, that you've also commented on */
INSERT INTO @Users (UserId)
SELECT DISTINCT o.Id
  FROM @OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId
  WHERE EXISTS (SELECT *
        FROM dbo.Posts q
        INNER JOIN dbo.Posts a 
            ON q.Id = a.ParentId
            AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')
        INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId
        WHERE q.Id = c.PostId
            AND cInner.UserId = @UserId); /* You also commented on the same post*/  
RETURN
END
GO

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

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)

RETURNS @Users TABLE

(

UserID INT

)

AS

BEGIN

/* Build a list of other users in the same city */

DECLARE @OtherUsersInYourLocation TABLE (Id INT);

INSERT INTO @OtherUsersInYourLocation(Id)

SELECT them.Id

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

WHERE me.Id = @UserId;

/* Find questions they've commented on, that you've also commented on */

INSERT INTO @Users (UserId)

SELECT DISTINCT o.Id

FROM @OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

WHERE EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId

WHERE q.Id = c.PostId

AND cInner.UserId = @UserId); /* You also commented on the same post*/

RETURN

END

GO

If you’ve been through the functions module in Fundamentals of Query Tuning or in Mastering Query Tuning, you’ll recognize the problem: this function has multiple queries in it, meaning it’s a perilous multi-statement TVF. Its true cost and work won’t show up in the calling query (the proc), so our developers had no idea how bad it really was.

Like with scalars, my general advice here is to try to turn them into inline (single-statement) functions. Again, the more statements you have, the harder this is – but here, it’s not too bad. Let’s just take the statement that was populating the table variable:

INSERT INTO @OtherUsersInYourLocation(Id)
  SELECT them.Id
    FROM dbo.Users me
    INNER JOIN dbo.Users them ON me.Location = them.Location
    WHERE me.Id = @UserId;

1

2

3

4

5

INSERT INTO @OtherUsersInYourLocation(Id)

SELECT them.Id

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

WHERE me.Id = @UserId;

And shove that into the FROM clause, which used to be:

INSERT INTO @Users (UserId)
SELECT DISTINCT o.Id
  FROM @OtherUsersInYourLocation o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId

1

2

3

4

INSERT INTO @Users (UserId)

SELECT DISTINCT o.Id

FROM @OtherUsersInYourLocation o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

The fast duct-tape way of doing it would be to simply paste it in:

INSERT INTO @Users (UserId)
SELECT DISTINCT o.Id
  FROM (SELECT them.Id
            FROM dbo.Users me
            INNER JOIN dbo.Users them ON me.Location = them.Location
            WHERE me.Id = @UserId) o
  INNER JOIN dbo.Comments c ON o.Id = c.UserId

1

2

3

4

5

6

7

INSERT INTO @Users (UserId)

SELECT DISTINCT o.Id

FROM (SELECT them.Id

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

WHERE me.Id = @UserId) o

INNER JOIN dbo.Comments c ON o.Id = c.UserId

But let’s clean that up a little:

INSERT INTO @Users (UserId)
SELECT DISTINCT them.Id
  FROM dbo.Users me
  INNER JOIN dbo.Users them ON me.Location = them.Location
  INNER JOIN dbo.Comments c ON them.Id = c.UserId
  WHERE me.Id = @UserId

1

2

3

4

5

6

INSERT INTO @Users (UserId)

SELECT DISTINCT them.Id

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

INNER JOIN dbo.Comments c ON them.Id = c.UserId

WHERE me.Id = @UserId

Now I can set aside the table variable, and this whole thing turns into one statement, a magical inline table-valued function:

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)
RETURNS TABLE AS
RETURN
SELECT DISTINCT them.Id AS UserId
  FROM dbo.Users me
  INNER JOIN dbo.Users them ON me.Location = them.Location
  INNER JOIN dbo.Comments c ON them.Id = c.UserId
  WHERE me.Id = @UserId
    AND EXISTS (SELECT *
        FROM dbo.Posts q
        INNER JOIN dbo.Posts a 
            ON q.Id = a.ParentId
            AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')
        INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId
        WHERE q.Id = c.PostId
            AND cInner.UserId = @UserId); /* You also commented on the same post*/  
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE OR ALTER FUNCTION dbo.ufn_UsersYouMightLike (@UserId INT)

RETURNS TABLE AS

RETURN

SELECT DISTINCT them.Id AS UserId

FROM dbo.Users me

INNER JOIN dbo.Users them ON me.Location = them.Location

INNER JOIN dbo.Comments c ON them.Id = c.UserId

WHERE me.Id = @UserId

AND EXISTS (SELECT *

FROM dbo.Posts q

INNER JOIN dbo.Posts a

ON q.Id = a.ParentId

AND a.PostTypeId = (SELECT TOP 1 Id FROM dbo.PostTypes WHERE Type = 'Answer')

INNER JOIN dbo.Comments cInner ON a.Id = cInner.PostId

WHERE q.Id = c.PostId

AND cInner.UserId = @UserId); /* You also commented on the same post*/

GO

We still have the stored proc calling a function, mind you – but I’m trying to change the bare minimum of things I can to suddenly get the client across the finish line.

So how does it perform?

I really wanted to show you before-and-after numbers, dear reader, but the “before” query was still going after 45 minutes – and I’m only using the small 10GB StackOverflow2010 database here!

The improved version runs in under a second! Granted, the execution plan isn’t pretty:

And there are obvious improvements we could make to the code and indexes, but…who cares? IT RUNS IN UNDER A SECOND! Rather than chasing further improvements to that one, I’d hand that to my developers, walk them through the changes we made, and then talk about the next terribly long query to go tune. In most cases, the big bang for the buck will be taking those 45-minute-long queries and turning them into just 1 second, rather than turning 1 second queries into 200ms queries.

Recap and lessons learned

When I show this to a developer, there are usually hugs and sobbing involved. They believed – rightfully – that if a feature ships in SQL Server, then it should perform well. That just isn’t the reality, sadly: table variables, scalar UDFs, multi-statement TVFs – these are all things that the documentation don’t warn you about. Sure, they compile – but they sure don’t scale.

These two function examples were simple on purpose because this is a blog post, not a doctoral thesis. When you get into functions that have several statements, each building on the next, building tables and fetching configuration data, this just gets more laborious. It’s not mentally hard, mind you, it’s just hard work, rolling up your sleeves to combine queries into one big ugly monster – or rewriting it from row-by-row functions to a set-based approach inside a stored procedure.

The earlier a senior database developer can get involved in the design and code review process, the less work gets wasted by other developers who don’t know that SQL Server just can’t handle those, nor implicit conversions, dynamic WHERE clauses with OR/ISNULL/COALESCE, etc, things that compile – but don’t scale.

To learn more about why – and how to work around ’em – check out my free Watch Brent Tune Queries videos, and then graduate to the Fundamentals of Query Tuning course.

Tuning Dynamic SQL by Hand with Short Circuits

January 23, 2019

Brent Ozar

T-SQL

7

When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example:

CREATE OR ALTER PROC dbo.usp_SearchUsers
	@SearchDisplayName NVARCHAR(40) = NULL,
	@SearchLocation NVARCHAR(100) = NULL,
	@SearchReputation INT = NULL AS
BEGIN
	DECLARE @StringToExecute NVARCHAR(4000);
	SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';
 
	IF @SearchDisplayName IS NOT NULL
		SET @StringToExecute = @StringToExecute + N' AND DisplayName LIKE @SearchDisplayName ';
 
	IF @SearchLocation IS NOT NULL
		SET @StringToExecute = @StringToExecute + N' AND Location LIKE @SearchLocation ';
 
	IF @SearchReputation IS NOT NULL
		SET @StringToExecute = @StringToExecute + N' AND Reputation = @SearchReputation ';
 
	EXEC sp_executesql @StringToExecute, 
		N'@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100), @SearchReputation INT', 
		@SearchDisplayName, @SearchLocation, @SearchReputation;
END
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

CREATE OR ALTER PROC dbo.usp_SearchUsers

@SearchDisplayName NVARCHAR(40) = NULL,

@SearchLocation NVARCHAR(100) = NULL,

@SearchReputation INT = NULL AS

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

IF @SearchDisplayName IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND DisplayName LIKE @SearchDisplayName ';

IF @SearchLocation IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND Location LIKE @SearchLocation ';

IF @SearchReputation IS NOT NULL

SET @StringToExecute = @StringToExecute + N' AND Reputation = @SearchReputation ';

EXEC sp_executesql @StringToExecute,

N'@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100), @SearchReputation INT',

@SearchDisplayName, @SearchLocation, @SearchReputation;

END

GO

The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all the parameter combinations.

But what if a lot of the queries look similar?

For example, what if your search page has a set of defaults, and the vast, vast majority of searches just use the defaults?

In our example above, what if the vast majority of the time, people always searched for users in London whose Reputation = 1? There’s no reason we can’t put a short-circuit at the top of our stored procedure:

CREATE OR ALTER PROC dbo.usp_SearchUsers
	@SearchDisplayName NVARCHAR(40) = NULL,
	@SearchLocation NVARCHAR(100) = NULL,
	@SearchReputation INT = NULL AS
BEGIN

    IF @SearchLocation = 'London' 
       AND @SearchReputation = 1 
       AND @SearchDisplayName IS NULL
        EXEC usp_SearchUsers_WelcomeToLondon;

	DECLARE @StringToExecute NVARCHAR(4000);
	SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

(...the rest continues normally)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

CREATE OR ALTER PROC dbo.usp_SearchUsers

@SearchDisplayName NVARCHAR(40) = NULL,

@SearchLocation NVARCHAR(100) = NULL,

@SearchReputation INT = NULL AS

BEGIN

IF @SearchLocation = 'London'

AND @SearchReputation = 1

AND @SearchDisplayName IS NULL

EXEC usp_SearchUsers_WelcomeToLondon;

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 ';

(...the rest continues normally)

In complex stored procedures, this short circuit buys me a few cool advantages:

You can build a hand-crafted query. You can restructure the T-SQL to be more performant, add index hints, query hints, trace flags, you name it, things you wouldn’t normally want to add to dynamic SQL on the fly.

You can get the data from somewhere different. For example, at one client, 99% of product searches were looking for the “Item of the Day” on sale – and that product list only changed once per day. We simply built a new table with the necessary product info, populated it once per day on a schedule, and changed the search procedure to something like this (obviously with a different table name):

IF @SearchFeaturedProduct = 1
  SELECT * FROM dbo.FeaturedProductsTableUpdatedOnceDaily;

1 2	IF @SearchFeaturedProduct = 1 SELECT * FROM dbo.FeaturedProductsTableUpdatedOnceDaily;

For that client, it made a phenomenal difference in eliminating joins, memory grants, permissions checks, and sorts. (The table only had one row in it.)

You skip the overhead of building a dynamic string. I know it sounds trivial, but when you’re dealing with tens of thousands of queries per second, this can be a big deal.

How to know if this tactic makes sense

Warning: run-on sentence incoming.

If you run sp_BlitzCache and your top most resource-intensive queries use dynamic SQL,
And a little further down in the results, you see a dynamic string that was built by one of these procs,
And when you compare the outer proc’s resource utilization numbers (CPU, reads, duration, etc) number to the dynamic SQL’s resource utilization, and it looks like that one dynamic SQL line is a big chunk,
And you think you could do better tuning that particular branch of dynamic SQL by hand,
And the other branches wouldn’t benefit from you just tuning the main string itself,
Then it’s a good fit.

New Fundamentals of Index Tuning Course

January 23, 2019

Brent Ozar

Indexing

No Comments

You’re a developer who needs to make your application go faster, and you don’t want to (or can’t!) change the queries. You’ve never been to an official training class, and your manager isn’t about to spring for travel.

In my new Fundamentals of Index Tuning course, you’ll learn:

How to design indexes for a query without looking at its execution plan
How to pick the order of keys in an index
How query parameter values can change which index is better
Where to find index recommendations using query plans, DMVs, and sp_BlitzIndex
How SQL Server’s index recommendations are built, and why they’re often wrong

To make the concepts more real, download the Stack Overflow database (get the smallest size so your index creations go as quickly as possible.) I’ll give you the exact same queries I’m running onscreen so you can follow along, and then after each module, you’ll have a series of assignments to complete. After you work on it, I’ll show you my answers, and you get those to download too.

Watch the recorded version for ~~$195~~ $97.50 – half off this week thanks to our sponsor, Sentry One Essentials.
Or join me live for ~~$595~~ $297.50 – half off this week, and when you buy your ticket, you immediately get access to the recorded version, too. That way, you can start learning now, and then reinforce the concepts and ask live questions during your live class.
Already a member? If you’re in my Recorded Class Season Pass, you can start watching the recordings right now. If you’ve got a Live Class Season Pass or a Team Membership, go ahead and sign up – it’s free with your unlimited membership.

Sign up now, and let’s get started!

Free Downloads for Powerful SQL Server Management

January 22, 2019

Brent Ozar

Production Database Administration, Videos

11

You’re responsible for managing and troubleshooting SQL Server.

Good news: you’re not alone. The SQL Server community has banded together and built a bunch of cool stuff to help you do your job easier. In a one-hour session today for the PASS DBA Fundamentals online user group (free to join, of course), I showed some off.

Tools I demoed live during the webcast

How to run a load tests: SQLQueryStress – an open source app originally written by Adam Machanic, and now maintained by Erik Ejlskov Jensen on Github. In the webcast, I’ll be using a set of free random workload scripts which run against the…

How to run demos: Stack Overflow database – the popular Q&A site StackOverflow.com gives away your questions, answers, comments, etc in an open format, and I import it into a SQL Server database you can use for learning T-SQL and indexing. I publish small (10GB), medium (50GB), and large (312GB) versions.

How to see what queries are running: sp_WhoIsActive – during the webcast, I’ll want to see what queries are causing me problems right now, and I won’t be firing up Activity Monitor. That thing is a hot mess. Instead, I’ll run @AdamMachanic‘s amazing tool, sp_WhoIsActive, and I’ll show you my favorite parameters.

How to see what queries have run recently: First Responder Kit – after the load test passes, let’s say someone asks me, “Hey, what queries were causing a problem earlier this morning?” I’ll show you how the open source sp_BlitzCache gives you the answers.

How to analyze their query plans: SentryOne Plan Explorer – when you’re struggling to understand why a query is slow, does it ever feel like SQL Server is fighting you back? Almost like it’s purposely trying to hide known anti-patterns and problems in the query plans? Plan Explorer surfaces them for you.

How to protect your databases: Ola Hallengren’s maintenance scripts – the built-in maintenance plans are a good first step at getting backups, corruption, and index maintenance. Over time, though, you’re going to graduate to needing something more powerful, and then it’s time to say hola to Ola.

And another dozen cool freebies

How to learn with free books – Redgate Books – you can download the PDF versions of their books absolutely free, including material on execution plans, source control, troubleshooting, and more. The site is just a little confusing – there are more books than you see at first. Click on the tabs under “Our books by topic” and you can see more books on SQL DBA topics, .NET, SysAdmin, etc.

How to manage more SQL Servers in less time: DBAtools.io – a collection of PowerShell cmdlets and a vibrant community of contributors. If you’re managing 50 or more SQL Servers by yourself, you need to drop what you’re doing and leverage these tools.

How to check for corruption at scale: Minion CheckDB – as your database volume grows, it can be hard to run CHECKDB frequently enough. This free tool helps you break the work into chunks and offload corruption checking easier. If your databases are 1TB or larger, you should check this out.

Monitor your servers for free: Quest Spotlight Basic – if your boss won’t spring for monitoring software, this is a free way to get a lot of useful analysis.

Watch tons of past conference videos: PASS.org, SQLbits.com, GroupBy.org – these conferences record all their sessions and distribute older ones for free. (You’ll need to create an account to watch the PASS ones, but it’s free, and worth it.)

Manage your columnstore indexes: Columnstore Indexes Script Library – written by the industry expert on columnstore indexes, @NikoNeugebauer, who’s written over a hundred CS blog posts too.

Stay up to date with newsletters: SQLServerCentral and MSSQLTips – these come out daily, so it’s a lot of email, but just don’t feel guilty about deleting ’em when you don’t have the time to keep up. When you’ve got a few minutes, though, skim the headlines on these and you’ll almost always find something of interest inside. If you want to drink from the firehose, you can follow the same blogs I read (also available as an OPML file.)

Learn SQL Server’s DMVs with queries: Glenn Berry’s scripts – these queries help you understand what’s happening inside your SQL Server, but more importantly, they show you many system tables & metrics. The amount of diagnostic goodies we have inside the engine is just astounding.

Format your T-SQL online: format-sql.com – a shortcut to Redgate’s in-browser code formatter. Not as powerful as their paid SQL Prompt, but when you’re in a pinch and you need a quick format, this is perfect.

Where to find more

I’m only scratching the surface! Check out sqlserver-kit.org, an incredible directory maintained by Konstantin Taranov in Github. He’s got everything from trace flags to downloads to lists of blogs.

Which Version of SQL Server Should You Use?

January 21, 2019

Brent Ozar

SQL Server, SQL Server 2016, SQL Server 2017, SQL Server 2019

55

Wait! Before you install that next SQL Server, hold up. Are you sure you’re using the right version?

I know, management wants you to stay on an older build, and the vendor says they’ll only support older versions, but now’s your chance to make your case for a newer version – and I’m gonna help you do it.

I’m going to go from the dark ages forward, making a sales pitch for each newer version.

You should probably install SQL Server 2008 if…

You’re dealing with an ancient application that’s no longer under support, like a third party app written by a vendor who has folded up and closed down
You’re only licensed for SQL Server 2008 because you didn’t buy Software Assurance, which gets you the ability to run newer versions for free
You only have Standard Edition, and you want support for unlimited memory (which goes away in 2008R2)
You don’t know that both SQL 2008 and SQL Server 2008 R2 (which came out about two years later) both have the same end-of-support date (and, uh, now you know that, so, that rules out that one)
You don’t know that Extended support ends July 9, 2019. Yes, if you host it in an Azure VM, Microsoft will give you extended support until 2022, but that’s it.

With that end-of-support date, I’m just not a fan of new 2008 installs today. So let’s move on.

You should consider SQL Server 2008 R2 if…

But I got a really good deal on this CD at a garage sale

You need these new features added since SQL 2008:

PowerPivot for Excel (which has been replaced)
Utility Control Point (which nobody uses)
Master Data Services
StreamInsight

Uh…yeah. No. Let’s skip this version and move on.

You should consider SQL Server 2012 if…

You want an extremely well-known, well-documented product
You want to write modern T-SQL (paging, better windowing functions, conversion and parsing)
You want a lot of diagnostic capabilities (because a lot of goodies got added in the DMVs & XE while it was being actively updated, although those days have come to an end)
You are absolutely averse to installing patches (because SP4 came out in Oct 2017, and there’s only been one security fix since, and that’s it)
You’re comfortable building another SQL Server in 3 years (because support ends in July 2022)
You either don’t need robust encryption for your backups, or you’re willing to buy a third party tool to get it

2012 introduced a few other features – Availability Groups, columnstore indexes, contained databases, Data Quality Services – but they were so limited that it’s hard to consider this a good starting point for those features today.

You should consider SQL Server 2014 if…

You want an extremely well-known, well-documented product – like 2012, it’s pretty easy to find material off the shelf and hire people who know how to use the tools in this version
You want to use Always On Availability Groups – but I’m even hesitant to put that here, because they continue to get dramatically better in newer versions. I’d just consider this a minimum starting point for even considering AGs (forget 2012) because starting with 2014, the secondary is readable even when the primary is down.
You need to encrypt your backups, and you’re not willing to buy a third party backup tool
You use log shipping as a reporting tool, and you have tricky permissions requirements (because they added new server-level roles that make this easier)
You need faster performance without changing the code, and you have lots of time to put into testing – 2014’s Cardinality Estimator (CE) changes made for different execution plans, but they’re not across-the-board better. You still have to put in time to find the queries that are gonna get slower, and figure out how to mitigate those.

2014 also introduced a few other features that don’t sound like assets today: In-Memory OLTP, which wasn’t production-quality at the time, Buffer Pool Extensions, data files in Azure blobs, backing up to a URL, and Delayed Durability.

You should consider SQL Server 2016 if…

You’re an independent software vendor (ISV) – because 2016 Service Pack 1 gave you a lot of Enterprise features in Standard Edition. This meant you could write one version of your application that worked at both your small clients on Standard, and your big clients on Enterprise.
You use Standard Edition – because it supports 128GB RAM (and can even go beyond that for some internal stuff like query plans.)
You want to stay here until 2025-2026 – this version has more years left in its support life than SQL Server 2012/2014, so you can install it once and stick around longer.
You’re comfortable learning via experimentation, not documentation – because as you get to these cutting edge features below, your experimentation & learning time goes up, because there are WAY less established industry best practices around the below stuff.
You have compliance needs for a new application – And I’m specifically calling out new apps here, but 2016 adds Always Encrypted, Dynamic Data Masking, Row Level Security, and temporal tables, features which make it easier for you to build things to protect and track your valuable data. It’s still not easy, it’s just easier.
You want to use columnstore indexes – I’m going to call this the minimum version I’d start with because they were finally updatable and could have both columnstore and rowstore indexes on the same table. This grid has a great comparison of what changed with columnstore over the years.
You need query plan monitoring, and you can’t afford a third party tool – because Query Store gives you some pretty cool capabilities. People aren’t using it as much as I’d like. If I took a full time DBA job again tomorrow, this (and PowerShell) would be the two skills I’d probably pick up.

Yeah, I know, I didn’t put “you want an extremely well-known, well-documented product” here in 2016, but it’s not because the product is bad. It’s just relatively new compared to 2012/2014, and it’s way, way harder to find great off-the-shelf training on things like Always Encrypted or columnstore indexes, or hire people who know how to use those.

They’re not bad features. They’re great features – they’re just earlier on the adoption curve right now, so getting great best practices is harder, that’s all. Not impossible, just harder.

You should consider SQL Server 2017 if…

You’re willing to apply patches every 30-60 days – because on newer releases like this, the patches are coming fast and furious, and they fix some pretty significant issues, and it’s going to be a while before 2019 comes out and 2017’s patches slow down. (Remember, there are no more Service Packs, just Cumulative Updates.)
You have a zero-RPO goal and financial risks – because 2017 added a new minimum commit replica setting on AGs that will let you guarantee commits were received by multiple replicas
You want easier future upgrades – because starting with 2017, you can have a Distributed Availability Group with different versions of SQL Server in it. DAGs aren’t too robust or well-documented today, but I like the idea of this as a down payment on easier upgrades when you upgrade down the road. (Prior to this, AG version upgrades are absolutely terrible, and you’re often better off building a new cluster and migrating over to it.)
You need high performance columnstore queries – because we got a lot of cool stuff for batch mode execution plans.
You’re dead-set on running SQL Server on Linux – but seriously, go through the release notes and click on every Cumulative Update to read the bugs that were fixed. Some of the clustering bugs have really made my eyebrows raise.
You’re dead-set on doing machine learning & R in SQL Server – I know it’s trendy for data folks to do this, but remember, you’re spending $2,000 to $7,000 per core for SQL Server licensing to do this.

You should consider waiting for SQL Server 2019 if…

You’re on 2012 or newer – so you have support for a while
Your environment is stable – so you don’t have a pressing need to change things
Your environment is performing well enough to get by
You only want to do one upgrade or new build in the year 2019
You heavily rely on user-defined functions – because 2019 may dramatically speed those up
You rely heavily on table variables, and you can change the code – those are getting better too

Microsoft is bringing some new technology bets to the table: Big Data Clusters, high availability in containers, and Java support. However, I just can’t make an argument for holding off on a new SQL Server deployment today (Jan 2019) for those technologies. If your database environment requires (not desires) those features, then you should already be running the 2019 previews in your test labs – but you’re probably already running other versions of SQL Server in production for your other database needs.

Is it supposed to smell this bad?

If you only desire (not require) those features, then don’t wait around for 2019. Continue your production deployments today. Don’t gamble on v1.0 features that might or might not ship this year, and may not be at the quality and completion level you really want. Don’t test in prod.

So what’s the right answer?

When I look at that list today, SQL Server 2016 makes a pretty compelling case for most folks. It’s a good balance of new features, stability, and long shelf life. In most shops, where folks are overworked and can’t upgrade every server every year, I can see installing 2016 today, and then seeing how 2019’s release goes, and planning for my 2019 deployments in the year 2020.

I’m sure I’m going to hear back from y’all though – what are your compelling reasons to pick a particular version?

Wanna see Erik Darling as Freddie Mercury at #SQLbits?

January 18, 2019

Brent Ozar

SQLBits

7

Erik Darling is raising money for the Mercury Phoenix Trust, fighting AIDS worldwide to help save lives.

If we raise $5,000, he’ll present his SQLbits indexing session dressed like Freddie Mercury:

If I hit my goal on this, I'm gonna deliver my @SQLBits indexes session dressed like a proper Freddie. Working hard on my AY-YO.
https://t.co/zwEutkWD8M pic.twitter.com/QqY1E5vzS2

— Erik Darling Data (@erikdarlingdata) January 18, 2019

I would absolutely love to see this (well, I won’t be there, so I’ll have to settle for photos) so I’ll match the first $1,000 of donations.

Let’s make this happen, party people: make your donations and let’s make this world the fun, helpful place we want it to be.

Upcoming Free Training Webcasts

January 17, 2019

Brent Ozar

Videos

2

Hey, wanna learn about SQL Server for free?

Feb 7 – PASS Data Expert Series: PASS is taking the top-rated Summit 2018 recordings and replaying them live, for free, on a webcast. I’ll be in the chat room too, taking your questions. Register here, and the lineup includes:

SSMS & T-SQL Tricks with Bob Pusateri – 11AM Eastern
Fix My Functions with Kendra Little – 12:20PM
Getting Better Query Plans with me – 1:40PM
Performance Tips and Tricks with Pinal Dave – 3:00PM
SQL Server with Containers with Bob Ward – 4:20PM
Building Trust in Teams with Richard Campbell – 5:40PM

Jan 25 – How to Think Like the Engine: You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this 90-minute class, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.

Feb 1 – Getting Started with the Consulting Toolkit: SQL Server consultants: you need an easy way to run an app, gather lots of diagnostic data, save it to a portable spreadsheet that you can analyze later. Even better, you want an app that your clients can run when their server is having problems but you’re not around – and then send that spreadsheet to you so you can diagnose problems after they’ve happened. That’s my new Consultant Toolkit. In this webcast, I’ll show you how to get it for free, start using it, and teach your clients how to use it.

Feb 8 – An Introduction to GitHub for DBAs: Distributed source control is really intimidating: branches, pull requests, merges – will somebody just take my code, for crying out loud? Why does it have to be so complicated and involved? I’m with you: I’m Brent Ozar, and I hated GitHub. For years, I struggled with it, but I’ve come to a gradual truce. I’m not a GitHub pro by any means, but in the span of one session, I can explain the most important terms to you in a way that’ll make sense for non-developers. I’ll show you how to contribute to someone else’s open source project, and how to get started putting your own scripts under source control.

Feb 15 – How to Capture Baselines with sp_BlitzFirst: You’re responsible for performance tuning SQL Server, and you’ve heard the same advice a bunch of times: “You should set a performance baseline.” But what does that even mean? And how do you do it? What metrics should you gather? How often should you gather them, and how much history should you keep? I’m a performance tuning DBA with a couple decades of experience, and I’ll show you how to capture baselines by setting up the free, open source sp_BlitzFirst to run in an Agent job every 15 minutes. You’ll also learn how to use the free Power BI Dashboard to read the baseline metrics.

Register now. If you can’t make it live, no worries – the videos will be on our YouTube channel too.

Email*
Name*
First Last
Things I want*
- First Responder Kit (scripts & guides)
- Subscribe to email updates (3-4 per week)
Marketing permissions*
I consent to receive email marketing from Brent Ozar Unlimited.
Our emails contain marketing stuff, so we need to give you some fine quality fine print: Brent Ozar Unlimited® will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at Help@BrentOzar.com. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.
Phone
This field is for validation purposes and should be left unchanged.

SQL Server Perfmon counters that are still interesting in 2019

January 16, 2019

Brent Ozar

Monitoring

9

“Page Life Expectancy? That belongs in a museum!“

(Cross-posted from the PASS Blog.)

Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I’ve moved on – wait stats are a much better starting point – but there are still 3 good Perfmon counters that I rely on.

No, not Page Life Expectancy, Buffer Cache Hit Ratio, and Disk Queue Length. Those are still hot garbage.

1. Forwarded Fetches per Second

When your tables don’t have clustered indexes, you’ve got a heap: a table organized in random order. Heaps are notoriously bad ideas if you do updates on variable-length fields because as SQL Server runs out of space on a page, it’ll move a row to another page where there’s available space for your new update. The more of these you have, the higher penalty you pay when scanning a table.

The Perfmon counter SQLServer:Access Methods: Forwarded Records/sec tracks how many of times SQL Server had to jump around while reading. (Think of these as extra logical reads to deliver query results.) The higher this number is, the more extra logical reads you’re doing. I don’t gauge the number by itself – I look at it as a percentage of the reads we’re doing overall. If I can cut the logical reads we’re doing by, say, 10% just by rebuilding the heap, great! That’s an easy win.

ENHANCE

With a name like “Forwarded Records/sec” you would reasonably assume that querying this number would get you the, uh, forwarded records that occurred in any given second. Nope. It’s a cumulative number from when SQL Server last restarted. That means in order to get actionable data, you need to measure this over time, and do differentials between measurements.

2. Memory Grants Pending

When your query starts executing, it usually needs a memory grant to do things like sorts, joins, and parallelism. I find this topic really intriguing, and you can probably tell – we’ve got a whole category of memory grant posts.

SQL Server:Memory Manager: Memory Grants Pending tells you the number of queries that are waiting on a memory grant before they can even START executing. When this number > 0, your apps are probably getting query timeouts, and the sysadmins are confused because CPU time looks low, but SQL Server is slow.

3. Compilations per Second

When you execute your beautifully hand-crafted query, SQL Server has probably never seen anything like it before – well, at least not since it restarted – and has to build a query execution plan.

With a regular transactional app, the same queries probably come in over and over, just with different parameters. (Data warehouses and reporting systems with a lot of truly unique queries are different – we’ll set those aside for now.)

“WHY AM I NEXT TO A HELMET?”

The SQLServer: SQL Statistics: Compilations/sec counter measures the number of times ~~per second~~ (hahaha, you wish) that SQL Server has had to compile a plan for a query it hasn’t seen before. The best way to evaluate this is to frame it in terms of the SQLServer:SQL Statistics: Batch Requests/sec counter, which, as you can probably guess, is also not measured per second. Totally up to you to do a differential, or to use the open source sp_BlitzFirst to do the differentials for you.

In a transactional system, I expect to see 90% (or higher) query plan reuse – so Compilations/sec should be 10% (or less) of the Batch Request/sec measure.

These 3 Perfmon counters shouldn’t be the first place you look – wait stats are still a better starting point – but sooner or later, you’re going to hit one of these issues that require Perfmon counters to solve. Ol’ trusty Perfmon is still there for you.

How to Load Test a Database Application

January 15, 2019

Brent Ozar

Development

9

I stay in the nicest hotels

It sounds really simple:

Capture a bunch of production queries
Replay those same queries over in a load test environment

So what’s the big deal? Why is it so hard to find good guidance on how to pull this off? How hard can it be?

Problem 1: many queries aren’t repeatable.

When you try to replay this query:

DELETE ProcessingQueue
  WHERE JobID = 12345;

1 2	DELETE ProcessingQueue WHERE JobID = 12345;

The rows have to actually be there in the table, or else your delete isn’t doing any work. That means you can’t replay that same query several times in a row. You’re going to have to restore the database after each load test, starting it back at the same point.

Deletes aren’t the only commands with this problem, either: for any query with a WHERE clause, we need the data to exist in the state we want:

SELECT *
  FROM ProcessingQueue
  WHERE IsProcessed = 0 AND JobID = 12345;

1

2

3

SELECT *

FROM ProcessingQueue

WHERE IsProcessed = 0 AND JobID = 12345;

In both cases, the rows for JobID 12345 have to already exist, so for a successful load test, our setup steps at the beginning of the post need to look more like:

Take a backup of the production database(s)
Capture a bunch of production queries
Restore that same backup to the load test environment
Replay those same queries over in a load test environment

(I can already hear the security crowd groaning, saying, “No no no, you’re never allowed to restore production data somewhere else.” Put a sock in it: we’re already talking about capturing queries in production, and as you know, those have personally identifiable data already. Read through til the end of the post before you get the flamethrower out.)

Problem 2: amplification introduces false blocking.

Managers think that to simulate more load, they can just take the production queries and replay them multiple times, simultaneously, from the replay tool. We’ve already talked about how you can’t reliably replay deletes, but even inserts and updates cause a problem.

Say we’re load testing Stack Overflow queries, and our app does this:

UPDATE dbo.Users
  SET Reputation = Reputation + 1
  WHERE Id = 22656;

1

2

3

UPDATE dbo.Users

SET Reputation = Reputation + 1

WHERE Id = 22656;

If try to simulate more load by running that exact same query from 100 different sessions simultaneously, we’re just going to end up with lock contention on that particular user’s row. We’ll be troubleshooting a blocking problem, not the problem we really have when 100 different users run that same query.

We really need to simulate activity across lots of DIFFERENT users, so you end up writing code to randomize parts of the load test. Now our load test routine looks like:

Take a backup of the production database(s)
Capture a bunch of production queries
Analyze the production queries to see which ones need to be randomized, and write that code (or require it as part of the development process)
Restore that same backup to the load test environment
Replay those same queries over in a load test environment using a tool that can randomize the parameter calls

It’s hard work, but it’ll pay off because we can reuse our load test over time, right? Well, wait..

Problem 3: production is running yesterday’s code.

Load testing is (theoretically) useful to know that our new code changes are going to perform well when we deploy ’em. However, if we’re capturing production queries…they’re not running the new code yet, nor does the production database have our new columns and tables.

There goes your week

So now our testing routine looks like:

Take a backup of the production database(s)
Capture a bunch of production queries
Analyze the production queries to see which ones need to be randomized, and write that code (or require it as part of the development process)
Restore that same backup to the load test environment
Apply the deployment scripts for our new code
Replay ~~those same queries~~ a mix of old code and our new production code over in a load test environment using a tool that can randomize the parameter calls

That’s why it’s so hard to load test databases.

Everybody starts down the analysis path, gets about an hour into the planning, and realizes it’s still a big challenge in 2019. When you take all of these problems together, plus the risks of using production data in development, the capture-and-replay technique just flat out doesn’t make financial sense for application load tests.

But I was careful there to say application load tests.

Capture-and-replay load tests can still make sense when you want to test new hardware, different compatibility levels, or new indexing strategies. Just make sure to restore the database to the right point in time, and don’t try to amplify your workloads lest you introduce blocking problems.

How Should We Show Statistics Histograms in sp_BlitzIndex?

January 10, 2019

Brent Ozar

Indexing, sp_BlitzIndex

4

If you’re a graduate of my free How to Think Like the SQL Server Engine course – and you’d better be, dear reader – then you’re vaguely familiar with DBCC SHOW_STATISTICS. It’s a command that shows you the contents of a statistics histogram.

When I’m doing the first two parts of the D.E.A.T.H. Method – (D)eduplicating identical indexes and (E)liminating unused indexes – I often wanna check the selectivity of the first field in the index. However, running DBCC SHOW_STATISTICS was painful because it’s not set-based – I couldn’t easily dump out the contents of lots of indexes at once.

That’s where sys.dm_db_stats_histogram comes in really handy. I’ve added a new section to the bottom of sp_BlitzIndex to show its contents when you’re examining a single table with the @TableName parameter:

sp_BlitzIndex’s new statistics histograms at the bottom

In that screenshot, I’m looking at the indexes on the Stack Overflow Users table, in particular, an index on DisplayName. The bottom section shows the histogram for that index, and if you keep scrolling down – in the real output, not the screenshot, silly – you can see the histograms for the rest of the indexes.

Now, this is a brand spankin’ new feature – it’s only in the dev branch of the First Responder Kit, and sys.dm_db_stats_histogram is only available on SQL Server 2016 SP1 CU2, 2017, and newer.

Right now, the stats on user-created indexes show up first, followed by the user-created stats, followed by the auto-created stats, all in alphabetical order. This feature also only works when you’re looking at a specific table with the @TableName parameter – it doesn’t really make sense to dump out all histograms across an entire database.

My question for you, dear reader, is: are there any changes you would want to this feature before I roll it out as part of the main branch? If you want to show other data, don’t just give me the wish list – give me the problem you’re trying to solve with the additional data, because there might be a better way to render it than what I’m showing.

You can leave a comment with any changes you’d like to see, or, if you’d like to edit the code and contribute changes yourself, check out the contribution guide.

Microsoft is getting your top 30 user database names.

They’re looking for SharePoint, Dynamics, and…AdventureWorks?

Most of the queries are uncommented, but the security ones seem to have comments.

They’re checking your storage speed.

Are you using encryption? They know.

Summary

Update 2019/02/08 from Conor Cunningham

There’s a new less expensive option: save $2,000 by skipping the lab VMs.

sp_Blitz Changes

sp_BlitzBackups Changes

sp_BlitzFirst Changes

sp_BlitzIndex Changes

sp_DatabaseRestore Changes

For Support

Wanna keep watching?

Fixing the scalar function by inlining it

Fixing the multi-statement TVF by inlining it

So how does it perform?

Recap and lessons learned

But what if a lot of the queries look similar?

How to know if this tactic makes sense

Tools I demoed live during the webcast

And another dozen cool freebies

Where to find more

You should probably install SQL Server 2008 if…

You should consider SQL Server 2008 R2 if…

You should consider SQL Server 2012 if…

You should consider SQL Server 2014 if…

You should consider SQL Server 2016 if…

You should consider SQL Server 2017 if…

You should consider waiting for SQL Server 2019 if…

So what’s the right answer?

1. Forwarded Fetches per Second

2. Memory Grants Pending

3. Compilations per Second

Problem 1: many queries aren’t repeatable.

Problem 2: amplification introduces false blocking.

Problem 3: production is running yesterday’s code.

That’s why it’s so hard to load test databases.

Most of the queries are uncommented,
but the security ones seem to have comments.

There’s a new less expensive option:
save $2,000 by skipping the lab VMs.