Skip to content

Commit e485466

Browse files
ktaranovktaranov
committed
Update SQL Server Diagnostic Information Queries to 2017-12
1 parent 347b1e9 commit e485466

7 files changed

Lines changed: 374 additions & 299 deletions

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

Lines changed: 6 additions & 5 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: October 5, 2017
5+
-- Last Modified: December 6, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -521,14 +521,15 @@ AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
521521
AND waiting_tasks_count > 0)
522522
SELECT
523523
MAX (W1.wait_type) AS [WaitType],
524+
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
525+
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
526+
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
527+
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
524528
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
525529
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
526530
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
527531
MAX (W1.WaitCount) AS [Wait Count],
528-
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
529-
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
530-
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
531-
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
532+
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
532533
FROM Waits AS W1
533534
INNER JOIN Waits AS W2
534535
ON W2.RowNum <= W1.RowNum

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

Lines changed: 6 additions & 5 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: October 5, 2017
5+
-- Last Modified: December 6, 2017
66
-- https://sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -571,14 +571,15 @@ AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
571571
AND waiting_tasks_count > 0)
572572
SELECT
573573
MAX (W1.wait_type) AS [WaitType],
574+
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
575+
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
576+
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
577+
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
574578
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
575579
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
576580
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
577581
MAX (W1.WaitCount) AS [Wait Count],
578-
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
579-
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
580-
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
581-
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
582+
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
582583
FROM Waits AS W1
583584
INNER JOIN Waits AS W2
584585
ON W2.RowNum <= W1.RowNum

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

Lines changed: 6 additions & 5 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: October 5, 2017
5+
-- Last Modified: December 6, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -632,14 +632,15 @@ AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
632632
AND waiting_tasks_count > 0)
633633
SELECT
634634
MAX (W1.wait_type) AS [WaitType],
635+
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
636+
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
637+
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
638+
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
635639
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
636640
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
637641
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
638642
MAX (W1.WaitCount) AS [Wait Count],
639-
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
640-
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
641-
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
642-
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
643+
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
643644
FROM Waits AS W1
644645
INNER JOIN Waits AS W2
645646
ON W2.RowNum <= W1.RowNum

0 commit comments

Comments
 (0)