Skip to content

Commit 6a7fd73

Browse files
ktaranovktaranov
committed
Update Glen Berry Diagnostic Information Queries February 9 2017
1 parent 6a7f5b7 commit 6a7fd73

7 files changed

Lines changed: 82 additions & 59 deletions

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

Lines changed: 5 additions & 2 deletions
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: January 8, 2017
5+
-- Last Modified: January 19, 2017
66
-- http://sqlserverperformance.wordpress.com/
77
-- http://sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -351,7 +351,7 @@ db.[compatibility_level] AS [DB Compatibility Level],
351351
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
352352
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
353353
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
354-
db.is_auto_close_on, db.is_auto_shrink_on
354+
db.is_auto_close_on, db.is_auto_shrink_on, db.is_published
355355
FROM sys.databases AS db WITH (NOLOCK)
356356
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
357357
ON db.name = lu.instance_name
@@ -1146,5 +1146,8 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
11461146
-- http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part3
11471147

11481148

1149+
-- Sign up for Microsoft Visual Studio Dev Essentials and get a free 3 month pass to Pluralsight
11491150

1151+
-- Microsoft Visual Studio Dev Essentials
1152+
-- https://www.visualstudio.com/dev-essentials/
11501153

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

Lines changed: 7 additions & 2 deletions
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: January 8, 2017
5+
-- Last Modified: January 19, 2017
66
-- http://sqlserverperformance.wordpress.com/
77
-- http://sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -398,7 +398,7 @@ db.[compatibility_level] AS [DB Compatibility Level],
398398
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
399399
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
400400
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
401-
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
401+
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled, db.is_published
402402
FROM sys.databases AS db WITH (NOLOCK)
403403
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
404404
ON db.name = lu.instance_name
@@ -1275,3 +1275,8 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
12751275
-- SQL Server 2014 DMV Diagnostic Queries – Part 3
12761276
-- http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part3
12771277

1278+
1279+
-- Sign up for Microsoft Visual Studio Dev Essentials and get a free 3 month pass to Pluralsight
1280+
1281+
-- Microsoft Visual Studio Dev Essentials
1282+
-- https://www.visualstudio.com/dev-essentials/

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

Lines changed: 2 additions & 2 deletions
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: January 1, 2017
5+
-- Last Modified: January 19, 2017
66
-- http://sqlserverperformance.wordpress.com/
77
-- http://sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -460,7 +460,7 @@ db.[compatibility_level] AS [DB Compatibility Level],
460460
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
461461
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
462462
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
463-
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
463+
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled, db.is_published
464464
FROM sys.databases AS db WITH (NOLOCK)
465465
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
466466
ON db.name = lu.instance_name

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

Lines changed: 18 additions & 14 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-
-- January 2017
5-
-- Last Modified: January 8, 2017
4+
-- February 2017
5+
-- Last Modified: February 9, 2017
66
-- http://sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -80,7 +80,8 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
8080
-- 11.0.5649 SP2 CU12 5/16/2016 ----> 11.0.6537 SP3 CU3 5/16/2016
8181
-- 11.0.5655 SP2 CU13 7/18/2016 ----> 11.0.6540 SP3 CU4 7/18/2016
8282
-- 11.0.5657 SP2 CU14 9/19/2016 ----> 11.0.6544 SP3 CU5 9/20/2016
83-
-- 11.0.5676 SP2 CU15 11/16/2016 ----> 11.0.6567 SP3 CU6 11/16/2016
83+
-- 11.0.5676 SP2 CU15 11/16/2016 ----> 11.0.6567 SP3 CU6 11/16/2016
84+
-- 11.0.5678 SP2 CU16 1/17/2017 ----> 11.0.6579 SP3 CU7 1/17/2017
8485
--
8586
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
8687
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
@@ -352,17 +353,11 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
352353
-- Physical memory state is transitioning
353354

354355

355-
-- Get SQL Server Error Log count and sizes (Query 13) (Error Log Count)
356-
EXEC sp_enumerrorlogs;
357-
------
358-
359-
-- This gives you the count of your SQL Server Error Logs, and shows you how large each one is
360-
361356

362357
-- You can skip the next two queries if you know you don't have a clustered instance
363358

364359

365-
-- Get information about your cluster nodes and their status (Query 14) (Cluster Node Properties)
360+
-- Get information about your cluster nodes and their status (Query 13) (Cluster Node Properties)
366361
-- (if your database server is in a failover cluster)
367362
SELECT NodeName, status_description, is_current_owner
368363
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
@@ -376,7 +371,7 @@ FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
376371
-- http://support.microsoft.com/kb/2920151
377372

378373

379-
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
374+
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 14) (AlwaysOn AG Cluster)
380375
SELECT cluster_name, quorum_type_desc, quorum_state_desc
381376
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
382377
------
@@ -385,7 +380,7 @@ FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
385380

386381

387382

388-
-- Hardware information from SQL Server 2012 (Query 16) (Hardware Info)
383+
-- Hardware information from SQL Server 2012 (Query 15) (Hardware Info)
389384
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
390385
cpu_count/hyperthread_ratio AS [Physical CPU Count],
391386
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
@@ -401,7 +396,7 @@ FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
401396
-- It merely indicates that you have a hypervisor running on your host
402397

403398

404-
-- Get System Manufacturer and model number from SQL Server Error log (Query 17) (System Manufacturer)
399+
-- Get System Manufacturer and model number from SQL Server Error log (Query 16) (System Manufacturer)
405400
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
406401
------
407402

@@ -411,6 +406,15 @@ EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
411406
-- This query will return no results if your error log has been recycled since the instance was started
412407

413408

409+
-- Get BIOS date from Windows Registry (Query 17) (BIOS Date)
410+
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
411+
------
412+
413+
-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
414+
-- Not as useful for virtualization
415+
416+
417+
414418
-- Get processor description from Windows Registry (Query 18) (Processor Description)
415419
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
416420
------
@@ -1498,4 +1502,4 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
14981502
-- Sign up for Microsoft Visual Studio Dev Essentials and get a free 3 month pass to Pluralsight
14991503

15001504
-- Microsoft Visual Studio Dev Essentials
1501-
-- https://www.visualstudio.com/products/visual-studio-dev-essentials-vs?wt.mc_id=WW_CE_BD_OO_SCL_TW_DESQLBenefitAnnouncement_SQL
1505+
-- https://www.visualstudio.com/dev-essentials/

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

Lines changed: 16 additions & 13 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-
-- January 2017
5-
-- Last Modified: January 8, 2017
4+
-- February 2017
5+
-- Last Modified: February 9, 2017
66
-- http://sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -338,17 +338,12 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
338338
-- Physical memory state is transitioning
339339

340340

341-
-- Get SQL Server Error Log count and sizes (Query 14) (Error Log Count)
342-
EXEC sp_enumerrorlogs;
343-
------
344-
345-
-- This gives you the count of your SQL Server Error Logs, and shows you how large each one is
346341

347342

348343
-- You can skip the next two queries if you know you don't have a clustered instance
349344

350345

351-
-- Get information about your cluster nodes and their status (Query 15) (Cluster Node Properties)
346+
-- Get information about your cluster nodes and their status (Query 14) (Cluster Node Properties)
352347
-- (if your database server is in a failover cluster)
353348
SELECT NodeName, status_description, is_current_owner
354349
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
@@ -362,15 +357,15 @@ FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
362357
-- http://support.microsoft.com/kb/2920151
363358

364359

365-
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 16) (AlwaysOn AG Cluster)
360+
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
366361
SELECT cluster_name, quorum_type_desc, quorum_state_desc
367362
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
368363
------
369364

370365
-- You will see no results if your instance is not using AlwaysOn AGs
371366

372367

373-
-- Good overview of AG health and status (Query 17) (AlwaysOn AG Status)
368+
-- Good overview of AG health and status (Query 16) (AlwaysOn AG Status)
374369
SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
375370
drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
376371
drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
@@ -392,7 +387,7 @@ ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE)
392387
-- You will see no results if your instance is not using AlwaysOn AGs
393388

394389

395-
-- Hardware information from SQL Server 2014 (Query 18) (Hardware Info)
390+
-- Hardware information from SQL Server 2014 (Query 17) (Hardware Info)
396391
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
397392
cpu_count/hyperthread_ratio AS [Physical CPU Count],
398393
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
@@ -408,7 +403,7 @@ FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
408403
-- It merely indicates that you have a hypervisor running on your host
409404

410405

411-
-- Get System Manufacturer and model number from SQL Server Error log (Query 19) (System Manufacturer)
406+
-- Get System Manufacturer and model number from SQL Server Error log (Query 18) (System Manufacturer)
412407
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
413408
------
414409

@@ -418,6 +413,14 @@ EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
418413
-- This query will return no results if your error log has been recycled since the instance was started
419414

420415

416+
-- Get BIOS date from Windows Registry (Query 19) (BIOS Date)
417+
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
418+
------
419+
420+
-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
421+
-- Not as useful for virtualization
422+
423+
421424
-- Get processor description from Windows Registry (Query 20) (Processor Description)
422425
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
423426
------
@@ -1602,7 +1605,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
16021605
-- Sign up for Microsoft Visual Studio Dev Essentials and get a free 3 month pass to Pluralsight
16031606

16041607
-- Microsoft Visual Studio Dev Essentials
1605-
-- https://www.visualstudio.com/products/visual-studio-dev-essentials-vs?wt.mc_id=WW_CE_BD_OO_SCL_TW_DESQLBenefitAnnouncement_SQL
1608+
-- https://www.visualstudio.com/dev-essentials/
16061609

16071610

16081611

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

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

22
-- SQL Server 2016 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- January 2017
5-
-- Last Modified: January 8, 2017
4+
-- February 2017
5+
-- Last Modified: February 9, 2017
66
-- http://sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -67,6 +67,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
6767
-- 13.0.2149.0 RTM CU1 7/25/2016
6868
-- 13.0.2164.0 RTM CU2 9/22/2016
6969
-- 13.0.2186.0 RTM CU3 11/16/2016 ----> 13.0.4001.0 SP1 RTM 11/16/2016
70+
-- 13.0.2193.0 RTM CU4 1/18/2017 ----> 13.0.4411.0 SP1 CU1 1/18/2017
7071

7172

7273
-- How to obtain the latest Service Pack for SQL Server 2016
@@ -349,17 +350,14 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
349350
-- Physical memory state is transitioning
350351

351352

352-
-- Get SQL Server Error Log count and sizes (Query 14) (Error Log Count)
353-
EXEC sp_enumerrorlogs;
354-
------
355353

356354
-- This gives you the count of your SQL Server Error Logs, and shows you how large each one is
357355

358356

359357
-- You can skip the next two queries if you know you don't have a clustered instance
360358

361359

362-
-- Get information about your cluster nodes and their status (Query 15) (Cluster Node Properties)
360+
-- Get information about your cluster nodes and their status (Query 14) (Cluster Node Properties)
363361
-- (if your database server is in a failover cluster)
364362
SELECT NodeName, status_description, is_current_owner
365363
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
@@ -373,15 +371,15 @@ FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
373371
-- http://support.microsoft.com/kb/2920151
374372

375373

376-
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 16) (AlwaysOn AG Cluster)
374+
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
377375
SELECT cluster_name, quorum_type_desc, quorum_state_desc
378376
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
379377
------
380378

381379
-- You will see no results if your instance is not using AlwaysOn AGs
382380

383381

384-
-- Good overview of AG health and status (Query 17) (AlwaysOn AG Status)
382+
-- Good overview of AG health and status (Query 16) (AlwaysOn AG Status)
385383
SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
386384
drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
387385
drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
@@ -406,7 +404,7 @@ ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE)
406404
-- https://blogs.msdn.microsoft.com/bobsql/2016/09/26/sql-server-2016-it-just-runs-faster-always-on-availability-groups-turbocharged/
407405

408406

409-
-- Hardware information from SQL Server 2016 (Query 18) (Hardware Info)
407+
-- Hardware information from SQL Server 2016 (Query 17) (Hardware Info)
410408
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
411409
cpu_count/hyperthread_ratio AS [Physical CPU Count],
412410
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
@@ -437,7 +435,7 @@ FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
437435
-- LARGE_PAGES
438436

439437

440-
-- Get System Manufacturer and model number from SQL Server Error log (Query 19) (System Manufacturer)
438+
-- Get System Manufacturer and model number from SQL Server Error log (Query 18) (System Manufacturer)
441439
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
442440
------
443441

@@ -447,6 +445,14 @@ EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
447445
-- This query will return no results if your error log has been recycled since the instance was started
448446

449447

448+
-- Get BIOS date from Windows Registry (Query 19) (BIOS Date)
449+
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
450+
------
451+
452+
-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
453+
-- Not as useful for virtualization
454+
455+
450456
-- Get processor description from Windows Registry (Query 20) (Processor Description)
451457
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
452458
------
@@ -1788,7 +1794,7 @@ ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
17881794
-- Sign up for Microsoft Visual Studio Dev Essentials and get a free 3 month pass to Pluralsight
17891795

17901796
-- Microsoft Visual Studio Dev Essentials
1791-
-- https://www.visualstudio.com/products/visual-studio-dev-essentials-vs?wt.mc_id=WW_CE_BD_OO_SCL_TW_DESQLBenefitAnnouncement_SQL
1797+
-- https://www.visualstudio.com/dev-essentials/
17921798

17931799

17941800

0 commit comments

Comments
 (0)