Skip to content

Commit bfa3bdf

Browse files
ktaranovktaranov
committed
Update awesome Glenn Berry Diagnostic Information Queries
1 parent e0f8fc0 commit bfa3bdf

7 files changed

Lines changed: 121 additions & 81 deletions

‎Scripts/SQL Server 2005 Diagnostic Information Queries.sql‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
-- SQL Server 2005 Diagnostic Information Queries
33
-- Glenn Berry
44
-- CY 2017
5-
-- Last Modified: June 19, 2017
5+
-- Last Modified: August 7, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -1162,3 +1162,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
11621162
-- http://bit.ly/2443SAd
11631163

11641164

1165+
-- August 2017 blog series about upgrading and migrating SQL Server
1166+
-- https://www.sqlskills.com/blogs/glenn/category/upgrading-sql-server/
1167+
1168+

‎Scripts/SQL Server 2008 Diagnostic Information Queries.sql‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
-- SQL Server 2008 Diagnostic Information Queries
33
-- Glenn Berry
44
-- CY 2017
5-
-- Last Modified: June 19, 2017
5+
-- Last Modified: JAugust 7, 2017
66
-- https://sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -1290,3 +1290,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
12901290

12911291
-- Microsoft IT Pro Cloud Essentials
12921292
-- http://bit.ly/2443SAd
1293+
1294+
1295+
-- August 2017 blog series about upgrading and migrating SQL Server
1296+
-- https://www.sqlskills.com/blogs/glenn/category/upgrading-sql-server/

‎Scripts/SQL Server 2008 R2 Diagnostic Information Queries.sql‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
-- SQL Server 2008 R2 Diagnostic Information Queries
33
-- Glenn Berry
44
-- CY 2017
5-
-- Last Modified: June 19, 2017
5+
-- Last Modified: August 7, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -1379,3 +1379,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
13791379

13801380
-- Microsoft IT Pro Cloud Essentials
13811381
-- http://bit.ly/2443SAd
1382+
1383+
1384+
-- August 2017 blog series about upgrading and migrating SQL Server
1385+
-- https://www.sqlskills.com/blogs/glenn/category/upgrading-sql-server/

‎Scripts/SQL Server 2012 Diagnostic Information Queries.sql‎

Lines changed: 24 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
11

22
-- SQL Server 2012 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- July 2017
5-
-- Last Modified: July 7, 2017
4+
-- August 2017
5+
-- Last Modified: August 7, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -87,8 +87,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
8787
-- 11.0.6594 SP3 CU8 3/20/2017
8888
-- 11.0.6598 SP3 CU9 5/15/2017
8989
--
90-
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
91-
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
90+
9291

9392
-- How to determine the version, edition and update level of SQL Server and its components
9493
-- https://support.microsoft.com/en-us/kb/321185
@@ -123,7 +122,11 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
123122
-- Performance Related Fixes in Post-SQL Server 2012 RTM Builds
124123
-- https://www.sqlskills.com/blogs/glenn/performance-related-fixes-in-post-sql-server-2012-rtm-builds/
125124

125+
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
126+
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
126127

128+
-- Download SQL Server Management Studio (SSMS)
129+
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
127130

128131

129132
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
@@ -212,7 +215,7 @@ DBCC TRACESTATUS (-1);
212215
-- http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
213216

214217
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
215-
-- http://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
218+
-- https://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
216219

217220
-- TF 6533 - Spatial performance improvements in SQL Server 2012 and 2014
218221
-- https://support.microsoft.com/en-us/kb/3107399
@@ -276,7 +279,7 @@ ORDER BY sj.name OPTION (RECOMPILE);
276279
-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
277280
--
278281
-- MSDN sysjobs documentation
279-
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx
282+
-- https://msdn.microsoft.com/en-us/library/ms189817.aspx
280283

281284
-- SQL Server Maintenance Solution
282285
-- https://ola.hallengren.com/
@@ -327,7 +330,7 @@ FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);
327330

328331
-- SQL Server NUMA Node information (Query 11) (SQL Server NUMA Info)
329332
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
330-
active_worker_count, avg_load_balance, resource_monitor_state
333+
idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
331334
FROM sys.dm_os_nodes WITH (NOLOCK)
332335
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
333336
------
@@ -362,7 +365,6 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
362365
-- Physical memory state is transitioning
363366

364367

365-
366368
-- You can skip the next two queries if you know you don't have a clustered instance
367369

368370

@@ -423,7 +425,6 @@ EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\Syste
423425
-- Not as useful for virtualization
424426

425427

426-
427428
-- Get processor description from Windows Registry (Query 18) (Processor Description)
428429
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
429430
------
@@ -551,7 +552,9 @@ ORDER BY [Overall Latency] OPTION (RECOMPILE);
551552
------
552553

553554
-- Shows you the drive-level latency for reads and writes, in milliseconds
554-
-- Latency above 20-25ms is usually a problem
555+
-- Latency above 30-40ms is usually a problem
556+
-- These latency numbers include all file activity against all SQL Server
557+
-- database files on each drive since SQL Server was last started
555558

556559

557560
-- Calculates average stalls per read, per write, and per total input/output for each database file (Query 24) (IO Stalls by File)
@@ -614,12 +617,10 @@ SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owne
614617
db.state_desc, db.containment_desc, db.log_reuse_wait_desc AS [Log Reuse Wait Description],
615618
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
616619
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
617-
db.[compatibility_level] AS [DB Compatibility Level],
618-
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
619-
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
620-
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
621-
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled,
622-
db.is_published, db.group_database_id, db.replica_id,
620+
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify Option],
621+
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced,
622+
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, db.is_auto_close_on, db.is_auto_shrink_on,
623+
db.target_recovery_time_in_seconds, db.is_cdc_enabled, db.is_published, db.group_database_id, db.replica_id,
623624
db.is_encrypted, de.encryption_state, de.percent_complete, de.key_algorithm, de.key_length
624625
FROM sys.databases AS db WITH (NOLOCK)
625626
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
@@ -898,7 +899,6 @@ ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
898899

899900

900901
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 36) (CPU Utilization History)
901-
-- This version works with SQL Server 2012
902902
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
903903

904904
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
@@ -989,6 +989,7 @@ ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
989989
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
990990
-- Watch out for high values for CACHESTORE_SQLCP
991991
-- Enabling 'optimize for ad hoc workloads' at the instance level can help reduce this
992+
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
992993

993994
-- CACHESTORE_OBJCP Object Plans
994995
-- These are compiled plans for stored procedures, functions and triggers
@@ -1074,8 +1075,7 @@ SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
10741075
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
10751076
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
10761077
AS [Available Space In MB], f.[file_id], fg.name AS [Filegroup Name],
1077-
f.is_percent_growth, f.growth,
1078-
fg.is_default, fg.is_read_only
1078+
f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only
10791079
FROM sys.database_files AS f WITH (NOLOCK)
10801080
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
10811081
ON f.data_space_id = fg.data_space_id
@@ -1127,7 +1127,7 @@ ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
11271127
------
11281128

11291129

1130-
-- Queries 47 through 52 are the "Bad Man List"
1130+
-- Queries 47 through 52 are the "Bad Man List" for stored procedures
11311131
-- Top Cached SPs By Execution Count (Query 47) (SP Execution Counts)
11321132
SELECT TOP(100) p.name AS [SP Name], qs.execution_count,
11331133
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
@@ -1548,3 +1548,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
15481548

15491549
-- Microsoft IT Pro Cloud Essentials
15501550
-- http://bit.ly/2443SAd
1551+
1552+
1553+
-- August 2017 blog series about upgrading and migrating SQL Server
1554+
-- https://www.sqlskills.com/blogs/glenn/category/upgrading-sql-server/

‎Scripts/SQL Server 2014 Diagnostic Information Queries.sql‎

Lines changed: 29 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
11

22
-- SQL Server 2014 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- July 2017
5-
-- Last Modified: July 6, 2017
4+
-- August 2017
5+
-- Last Modified: August 7, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -13,7 +13,7 @@
1313
-- If you want to find all of our SQLskills SQL101 blog posts, check out https://www.sqlskills.com/help/sql101/
1414

1515

16-
-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server
16+
-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server!
1717

1818

1919
--******************************************************************************
@@ -79,8 +79,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
7979
-- 12.0.4511 SP1 CU12 4/17/2017 12.0.5546 SP2 CU5 4/17/2017
8080

8181

82-
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
83-
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
82+
8483

8584
-- How to determine the version, edition and update level of SQL Server and its components
8685
-- https://support.microsoft.com/en-us/kb/321185
@@ -106,6 +105,9 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
106105
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
107106
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
108107

108+
-- Download SQL Server Management Studio (SSMS)
109+
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
110+
109111

110112
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
111113
-- This query might take a few seconds if you have not recycled your error log recently
@@ -197,7 +199,6 @@ DBCC TRACESTATUS (-1);
197199
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
198200
-- https://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
199201

200-
201202
-- TF 6533 - Spatial performance improvements in SQL Server 2012 and 2014
202203
-- https://support.microsoft.com/en-us/kb/3107399
203204

@@ -213,6 +214,16 @@ DBCC TRACESTATUS (-1);
213214
EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';
214215
------
215216

217+
-- Lets you determine whether Instant File Initialization (IFI) is enabled for the instance
218+
-- This should be enabled in the vast majority of cases
219+
-- SQL Server 2016 lets you enable this during the SQL server installation process
220+
221+
-- Database Instant File Initialization
222+
-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization
223+
224+
-- Misconceptions around instant file initialization
225+
-- https://www.sqlskills.com/blogs/paul/misconceptions-around-instant-file-initialization/
226+
216227

217228
-- SQL Server Process Address space info (Query 7) (Process Memory)
218229
-- (shows whether locked pages is enabled, among other things)
@@ -265,7 +276,7 @@ ORDER BY sj.name OPTION (RECOMPILE);
265276
-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
266277
--
267278
-- MSDN sysjobs documentation
268-
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx
279+
-- https://msdn.microsoft.com/en-us/library/ms189817.aspx
269280

270281
-- SQL Server Maintenance Solution
271282
-- https://ola.hallengren.com/
@@ -316,7 +327,7 @@ FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);
316327

317328
-- SQL Server NUMA Node information (Query 12) (SQL Server NUMA Info)
318329
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
319-
active_worker_count, avg_load_balance, resource_monitor_state
330+
idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
320331
FROM sys.dm_os_nodes WITH (NOLOCK)
321332
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
322333
------
@@ -351,8 +362,6 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
351362
-- Physical memory state is transitioning
352363

353364

354-
355-
356365
-- You can skip the next two queries if you know you don't have a clustered instance
357366

358367

@@ -516,7 +525,6 @@ ORDER BY database_id OPTION (RECOMPILE);
516525
-- If you do get results here, you should do further investigation to determine the root cause
517526

518527

519-
520528
-- Get number of data files in tempdb database (Query 25) (Tempdb Data Files)
521529
EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';
522530
------
@@ -601,7 +609,9 @@ ORDER BY [Overall Latency] OPTION (RECOMPILE);
601609
------
602610

603611
-- Shows you the drive-level latency for reads and writes, in milliseconds
604-
-- Latency above 20-25ms is usually a problem
612+
-- Latency above 30-40ms is usually a problem
613+
-- These latency numbers include all file activity against all SQL Server
614+
-- database files on each drive since SQL Server was last started
605615

606616

607617
-- Calculates average stalls per read, per write, and per total input/output for each database file (Query 29) (IO Stalls by File)
@@ -953,7 +963,6 @@ ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
953963

954964

955965
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 41) (CPU Utilization History)
956-
-- This version works with SQL Server 2014
957966
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
958967

959968
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
@@ -1044,6 +1053,7 @@ ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
10441053
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
10451054
-- Watch out for high values for CACHESTORE_SQLCP
10461055
-- Enabling 'optimize for ad hoc workloads' at the instance level can help reduce this
1056+
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
10471057

10481058
-- CACHESTORE_OBJCP Object Plans
10491059
-- These are compiled plans for stored procedures, functions and triggers
@@ -1066,7 +1076,7 @@ ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
10661076
-- Enabling forced parameterization for the database can help, but test first!
10671077

10681078
-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
1069-
-- http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
1079+
-- https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
10701080

10711081

10721082
-- Get top total logical reads queries for entire instance (Query 47) (Top Logical Reads Queries)
@@ -1129,8 +1139,7 @@ SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
11291139
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
11301140
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
11311141
AS [Available Space In MB], f.[file_id], fg.name AS [Filegroup Name],
1132-
f.is_percent_growth, f.growth,
1133-
fg.is_default, fg.is_read_only
1142+
f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only
11341143
FROM sys.database_files AS f WITH (NOLOCK)
11351144
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
11361145
ON f.data_space_id = fg.data_space_id
@@ -1198,7 +1207,7 @@ ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
11981207
------
11991208

12001209

1201-
-- Queries 52 through 57 are the "Bad Man List"
1210+
-- Queries 53 through 57 are the "Bad Man List" for stored procedures
12021211
-- Top Cached SPs By Execution Count (Query 53) (SP Execution Counts)
12031212
SELECT TOP(100) p.name AS [SP Name], qs.execution_count,
12041213
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
@@ -1216,7 +1225,7 @@ ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
12161225
-- Tells you which cached stored procedures are called the most often
12171226
-- This helps you characterize and baseline your workload
12181227

1219-
-- Queries 54 through 59 are the "Bad Man List"
1228+
12201229
-- Top Cached SPs By Avg Elapsed Time (Query 54) (SP Avg Elapsed Time)
12211230
SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
12221231
qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,
@@ -1660,4 +1669,5 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
16601669
-- http://bit.ly/2443SAd
16611670

16621671

1663-
1672+
-- August 2017 blog series about upgrading and migrating SQL Server
1673+
-- https://www.sqlskills.com/blogs/glenn/category/upgrading-sql-server/

0 commit comments

Comments
 (0)