Skip to content

Commit e8dbd1b

Browse files
ktaranovktaranov
committed
Update awesome Glen Berry SQL Server Diagnostic Information Queries
1 parent f7ae166 commit e8dbd1b

7 files changed

Lines changed: 212 additions & 153 deletions

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

Lines changed: 4 additions & 3 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: August 7, 2017
5+
-- Last Modified: October 5, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -86,7 +86,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
8686

8787
-- SQL Server 2005 fell out of Mainsteam Support on April 12, 2011
8888
-- This means no more Service Packs or Cumulative Updates
89-
-- SQL Server 2005 will end Extended Support on April 12, 2016
89+
-- SQL Server 2005 ended Extended Support on April 12, 2016
9090

9191
-- SQL Server 2005 Service Pack 4
9292
-- http://www.microsoft.com/en-us/download/details.aspx?id=7218
@@ -938,7 +938,8 @@ INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
938938
ON mig.index_handle = mid.index_handle
939939
INNER JOIN sys.partitions AS p WITH (NOLOCK)
940940
ON p.[object_id] = mid.[object_id]
941-
WHERE mid.database_id = DB_ID()
941+
WHERE mid.database_id = DB_ID()
942+
AND p.index_id < 2
942943
ORDER BY index_advantage DESC OPTION (RECOMPILE);
943944
------
944945

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

Lines changed: 6 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: JAugust 7, 2017
5+
-- Last Modified: October 5, 2017
66
-- https://sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -111,6 +111,9 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
111111
-- The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released
112112
-- http://support.microsoft.com/kb/2629969
113113

114+
-- Download SQL Server Management Studio (SSMS)
115+
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
116+
114117

115118

116119
-- When was SQL Server installed (Query 2) (SQL Server Install Date)
@@ -1047,7 +1050,8 @@ INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
10471050
ON mig.index_handle = mid.index_handle
10481051
INNER JOIN sys.partitions AS p WITH (NOLOCK)
10491052
ON p.[object_id] = mid.[object_id]
1050-
WHERE mid.database_id = DB_ID()
1053+
WHERE mid.database_id = DB_ID()
1054+
AND p.index_id < 2
10511055
ORDER BY index_advantage DESC OPTION (RECOMPILE);
10521056
------
10531057

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

Lines changed: 11 additions & 9 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: August 7, 2017
5+
-- Last Modified: October 5, 2017
66
-- https://www.sqlserverperformance.wordpress.com/
77
-- https://www.sqlskills.com/blogs/glenn/
88
-- Twitter: GlennAlanBerry
@@ -86,12 +86,10 @@ SELECT SERVERPROPERTY ('MachineName') AS [Server Name], @@VERSION AS [SQL Server
8686
-- 10.50.4305 SP2 CU12 4/21/2014
8787
-- 10.50.4319 SP2 CU13 6/30/2014
8888
-- 10.50.6000 SP3 RTM 9/26/2014
89-
-- 10.50.6525 SP3 + HF 2/9/2015
90-
-- http://support.microsoft.com/kb/3033860
91-
-- Any build older than 10.50.4000 is on an "unsupported service pack"
92-
-- SQL Server 2008 R2 SP3 RTM (Build 10.50.6000) is the final public build of SQL Server 2008 R2, barring any later security fixes.
89+
-- 10.50.6525 SP3 + HF 2/9/2015 http://support.microsoft.com/kb/3033860
9390

9491

92+
-- SQL Server 2008 R2 SP3 RTM plus an on-demand hotfix (Build 10.50.6525) is the final public build of SQL Server 2008 R2, barring any later security fixes.
9593

9694
-- SQL Server 2008 R2 RTM was considered an "unsupported service pack" as of July 12, 2012
9795
-- SQL Server 2008 R2 SP1 was considered an "unsupported service pack" as of August 8, 2013
@@ -102,16 +100,19 @@ SELECT SERVERPROPERTY ('MachineName') AS [Server Name], @@VERSION AS [SQL Server
102100
-- The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released
103101
-- http://support.microsoft.com/kb/2567616
104102

105-
-- Microsoft released SQL Server 2008 R2 SP1 CU14 on 8/8/2013, then pulled it the next day, "since SP1 is ending mainstream support"
106-
107103
-- The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released
108104
-- http://support.microsoft.com/kb/2730301
109105

110-
-- SQL Server 2008 R2 SP2 CU13 is the final cumulative update for SQL Server 2008 R2
106+
-- SQL Server 2008 R2 SP2 CU13 is the final cumulative update for SQL Server 2008 R2 SP2
111107

112108
-- SQL Server 2008 R2 SP3 Release information
113109
-- http://support2.microsoft.com/kb/2979597
114110

111+
-- Download SQL Server Management Studio (SSMS)
112+
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
113+
114+
115+
115116
-- When was SQL Server installed (Query 2) (SQL Server Install Date)
116117
SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date]
117118
FROM sys.server_principals WITH (NOLOCK)
@@ -1111,7 +1112,8 @@ INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
11111112
ON mig.index_handle = mid.index_handle
11121113
INNER JOIN sys.partitions AS p WITH (NOLOCK)
11131114
ON p.[object_id] = mid.[object_id]
1114-
WHERE mid.database_id = DB_ID()
1115+
WHERE mid.database_id = DB_ID()
1116+
AND p.index_id < 2
11151117
ORDER BY index_advantage DESC OPTION (RECOMPILE);
11161118
------
11171119

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

Lines changed: 35 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
-- SQL Server 2012 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- Last Modified: October 7, 2017
4+
-- Last Modified: December 1, 2017
55
-- https://www.sqlskills.com/blogs/glenn/
66
-- http://sqlserverperformance.wordpress.com/
77
-- Twitter: GlennAlanBerry
@@ -88,6 +88,9 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
8888
-- 11.0.6607 SP3 CU10 8/8/2017
8989
-- 11.0.7001 SP4 RTM 10/3/2017
9090

91+
-- SQL Server 2012 Service Pack 4 (SP4) Released!
92+
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2012-service-pack-4-sp4-released/
93+
9194
-- How to determine the version, edition and update level of SQL Server and its components
9295
-- https://support.microsoft.com/en-us/kb/321185
9396

@@ -124,19 +127,26 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
124127
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
125128
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
126129

130+
-- Update Center for Microsoft SQL Server
131+
-- http://bit.ly/2pZptuQ
132+
127133
-- Download SQL Server Management Studio (SSMS)
128134
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
129135

136+
-- Download and install Microsoft SQL Operations Studio
137+
-- https://docs.microsoft.com/en-us/sql/sql-operations-studio/download
138+
130139

131140
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
132-
-- This query might take a few seconds if you have not recycled your error log recently
141+
-- This query might take a few seconds depending on the size of your error log
133142
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
134143
------
135144

145+
-- New in SQL Server 2012 SP4
136146
-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
137147
-- It can also help you confirm your SQL Server licensing model
138-
-- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing"
139-
-- (when you have more than 20 logical cores) which means grandfathered Server/CAL licensing
148+
-- Be on the lookout for this message "using 40 logical processors based on SQL Server licensing"
149+
-- (when you have more than 40 logical cores) which means grandfathered Server/CAL licensing
140150
-- This query will return no results if your error log has been recycled since the instance was last started
141151

142152

@@ -203,27 +213,36 @@ DBCC TRACESTATUS (-1);
203213

204214
-- Common trace flags that should be enabled in most cases
205215
-- TF 1117 - When growing a data file, grow all files at the same time so they remain the same size, reducing allocation contention points
206-
-- http://support2.microsoft.com/kb/2154845
216+
-- https://support2.microsoft.com/kb/2154845
207217
--
208218
-- TF 1118 - Helps alleviate allocation contention in tempdb, SQL Server allocates full extents to each database object,
209219
-- thereby eliminating the contention on SGAM pages (more important with older versions of SQL Server)
210220
-- Recommendations to reduce allocation contention in SQL Server tempdb database
211-
-- http://support2.microsoft.com/kb/2154845
221+
-- https://support2.microsoft.com/kb/2154845
222+
223+
-- TF 2371 - Lowers auto update statistics threshold for large tables (on tables with more than 25,000 rows)
224+
-- https://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
212225

213-
-- TF 2371 - Lowers auto update statistics threshold for large tables
214-
-- http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
226+
-- TF 3023 - Enables backup checksum default
227+
-- https://support.microsoft.com/en-us/help/2656988/how-to-enable-the-checksum-option-if-backup-utilities-do-not-expose-th
215228

216229
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
217230
-- https://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
218231

232+
-- TF 3449 - Enables use of dirty page manager (SQL Server 2012 SP3 CU3 and later)
233+
-- https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/
234+
219235
-- TF 6533 - Spatial performance improvements in SQL Server 2012 and 2014
220236
-- https://support.microsoft.com/en-us/kb/3107399
221237

222238
-- TF 6534 - Enables use of native code to improve performance with spatial data
223239
-- https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-native-spatial-implementations/
224240

225-
-- SQL Server query optimizer hotfix trace flag 4199 servicing model
226-
-- https://support.microsoft.com/en-us/kb/974006
241+
-- TF 8079 - Enables automatic soft-NUMA on systems with eight or more physical cores per NUMA node (with SQL Server 2012 SP4)
242+
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2012-service-pack-4-sp4-released/
243+
244+
--- DBCC TRACEON - Trace Flags (Transact-SQL)
245+
-- https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
227246

228247

229248

@@ -794,18 +813,19 @@ AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
794813
AND waiting_tasks_count > 0)
795814
SELECT
796815
MAX (W1.wait_type) AS [WaitType],
816+
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
817+
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
818+
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
819+
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
797820
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
798821
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
799822
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
800823
MAX (W1.WaitCount) AS [Wait Count],
801-
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
802-
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
803-
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
804-
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
824+
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
805825
FROM Waits AS W1
806826
INNER JOIN Waits AS W2
807827
ON W2.RowNum <= W1.RowNum
808-
GROUP BY W1.RowNum
828+
GROUP BY W1.RowNum, W1.wait_type
809829
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
810830
OPTION (RECOMPILE);
811831
------

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

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

22
-- SQL Server 2014 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- Last Modified: October 5, 2017
4+
-- Last Modified: December 1, 2017
55
-- https://www.sqlskills.com/blogs/glenn/
66
-- http://sqlserverperformance.wordpress.com/
77
-- Twitter: GlennAlanBerry
@@ -78,6 +78,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
7878
-- 12.0.4511 SP1 CU12 4/17/2017 12.0.5546 SP2 CU5 4/17/2017
7979
-- 12.0.4522 SP1 CU13 7/17/2017 12.0.5552 SP2 CU6 7/17/2017
8080
-- 12.0.5556 SP2 CU7 8/28/2017
81+
-- 12.0.5557 SP2 CU8 10/16/2017
8182

8283

8384

@@ -106,19 +107,25 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
106107
-- Announcing updates to the SQL Server Incremental Servicing Model (ISM)
107108
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
108109

110+
-- Update Center for Microsoft SQL Server
111+
-- http://bit.ly/2pZptuQ
112+
109113
-- Download SQL Server Management Studio (SSMS)
110114
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
111115

116+
-- Download and install Microsoft SQL Operations Studio
117+
-- https://docs.microsoft.com/en-us/sql/sql-operations-studio/download
118+
112119

113120
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
114-
-- This query might take a few seconds if you have not recycled your error log recently
121+
-- This query might take a few seconds depending on the size of your error log
115122
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
116123
------
117124

118125
-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
119126
-- It can also help you confirm your SQL Server licensing model
120-
-- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing"
121-
-- (when you have more than 20 logical cores) which means grandfathered Server/CAL licensing
127+
-- Be on the lookout for this message "using 40 logical processors based on SQL Server licensing"
128+
-- (when you have more than 40 logical cores) which means grandfathered Server/CAL licensing
122129
-- This query will return no results if your error log has been recycled since the instance was last started
123130

124131

@@ -187,27 +194,33 @@ DBCC TRACESTATUS (-1);
187194

188195
-- Common trace flags that should be enabled in most cases
189196
-- TF 1117 - When growing a data file, grow all files at the same time so they remain the same size, reducing allocation contention points
190-
-- http://support2.microsoft.com/kb/2154845
197+
-- https://support2.microsoft.com/kb/2154845
191198
--
192199
-- TF 1118 - Helps alleviate allocation contention in tempdb, SQL Server allocates full extents to each database object,
193200
-- thereby eliminating the contention on SGAM pages (more important with older versions of SQL Server)
194201
-- Recommendations to reduce allocation contention in SQL Server tempdb database
195-
-- http://support2.microsoft.com/kb/2154845
202+
-- https://support2.microsoft.com/kb/2154845
196203

197-
-- TF 2371 - Lowers auto update statistics threshold for large tables
198-
-- http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
204+
-- TF 2371 - Lowers auto update statistics threshold for large tables (on tables with more than 25,000 rows)
205+
-- https://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
199206

200207
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
201208
-- https://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
202209

210+
-- TF 3449 - Enables use of dirty page manager (SQL Server 2014 SP1 CU7 and later)
211+
-- https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/
212+
203213
-- TF 6533 - Spatial performance improvements in SQL Server 2012 and 2014
204214
-- https://support.microsoft.com/en-us/kb/3107399
205215

206216
-- TF 6534 - Enables use of native code to improve performance with spatial data
207217
-- https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-native-spatial-implementations/
208218

209-
-- SQL Server query optimizer hotfix trace flag 4199 servicing model
210-
-- https://support.microsoft.com/en-us/kb/974006
219+
-- TF 8079 - Enables automatic soft-NUMA on systems with eight or more physical cores per NUMA node (with SQL Server 2014 SP2)
220+
-- https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-2014-service-pack-2-is-now-available/
221+
222+
-- DBCC TRACEON - Trace Flags (Transact-SQL)
223+
-- https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
211224

212225

213226

@@ -860,18 +873,19 @@ AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
860873
AND waiting_tasks_count > 0)
861874
SELECT
862875
MAX (W1.wait_type) AS [WaitType],
876+
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
877+
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
878+
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
879+
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
863880
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
864881
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
865882
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
866883
MAX (W1.WaitCount) AS [Wait Count],
867-
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
868-
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
869-
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
870-
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
884+
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
871885
FROM Waits AS W1
872886
INNER JOIN Waits AS W2
873887
ON W2.RowNum <= W1.RowNum
874-
GROUP BY W1.RowNum
888+
GROUP BY W1.RowNum, W1.wait_type
875889
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
876890
OPTION (RECOMPILE);
877891
------

0 commit comments

Comments
 (0)