Skip to content

Commit 526d95a

Browse files
ktaranovktaranov
committed
Update Glenn Berry Diagnostic Information Queries
1 parent e266c48 commit 526d95a

4 files changed

Lines changed: 27 additions & 11 deletions

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

Lines changed: 3 additions & 3 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-
-- August 2017
5-
-- Last Modified: August 7, 2017
4+
-- September 2017
5+
-- Last Modified: September 4, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -86,7 +86,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
8686
-- 11.0.5678 SP2 CU16 1/17/2017 ----> 11.0.6579 SP3 CU7 1/17/2017
8787
-- 11.0.6594 SP3 CU8 3/20/2017
8888
-- 11.0.6598 SP3 CU9 5/15/2017
89-
--
89+
-- 11.0.6607 SP3 CU10 8/8/2017
9090

9191

9292
-- How to determine the version, edition and update level of SQL Server and its components

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

Lines changed: 4 additions & 2 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-
-- August 2017
5-
-- Last Modified: August 7, 2017
4+
-- September 2017
5+
-- Last Modified: September 4, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -77,6 +77,8 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
7777
-- 12.0.4491 SP1 CU10 12/28/2016 12.0.5537 SP2 CU3 12/28/2016
7878
-- 12.0.4502 SP1 CU11 2/21/2017 12.0.5540 SP2 CU4 2/21/2017
7979
-- 12.0.4511 SP1 CU12 4/17/2017 12.0.5546 SP2 CU5 4/17/2017
80+
-- 12.0.4522 SP1 CU13 7/17/2017 12.0.5552 SP2 CU6 7/17/2017
81+
-- 12.0.5556 SP2 CU7 8/28/2017
8082

8183

8284

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

Lines changed: 6 additions & 2 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-
-- August 2017
5-
-- Last Modified: August 7, 2017
4+
-- September 2017
5+
-- Last Modified: September 4, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -72,6 +72,7 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
7272
-- 13.0.2193.0 RTM CU4 1/18/2017 ----> 13.0.4411.0 SP1 CU1 1/18/2017
7373
-- 13.0.2197.0 RTM CU5 3/20/2017 ----> 13.0.4422.0 SP1 CU2 3/20/2017
7474
-- 13.0.2204.0 RTM CU6 5/15/2017 ----> 13.0.4435.0 SP1 CU3 5/15/2017
75+
-- 13.0.2210.0 RTM CU7 8/8/2017 ----> 13.0.4446.0 SP1 CU4 8/8/2017
7576

7677

7778
-- How to determine the version, edition and update level of SQL Server and its components
@@ -98,6 +99,9 @@ SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version In
9899
-- Download SQL Server Management Studio (SSMS)
99100
-- https://msdn.microsoft.com/en-us/library/mt238290.aspx
100101

102+
-- Update Center for Microsoft SQL Server
103+
-- http://bit.ly/2pZptuQ
104+
101105

102106

103107
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)

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

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

22
-- SQL Server 2017 Diagnostic Information Queries
33
-- Glenn Berry
4-
-- August 2017
5-
-- Last Modified: August 7, 2017
4+
-- September 2017
5+
-- Last Modified: August 28, 2017
66
-- https://www.sqlskills.com/blogs/glenn/
77
-- http://sqlserverperformance.wordpress.com/
88
-- Twitter: GlennAlanBerry
@@ -347,6 +347,7 @@ FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
347347
-- Available physical memory is high
348348
-- Physical memory usage is steady
349349
-- Available physical memory is low
350+
-- Available physical memory is running low
350351
-- Physical memory state is transitioning
351352

352353

@@ -1643,6 +1644,15 @@ ORDER BY OBJECT_NAME(i.[object_id]) OPTION (RECOMPILE);
16431644
-- https://msdn.microsoft.com/en-us/library/dn133166.aspx
16441645

16451646

1647+
SELECT SUM(allocated_bytes)/1024/1024 AS [XTP_MEMORY_IN_MB]
1648+
FROM sys.dm_db_xtp_memory_consumers;
1649+
1650+
SELECT SUM(allocated_bytes)/(1024*1024) AS total_allocated_MB,
1651+
SUM(used_bytes)/(1024*1024) AS total_used_MB
1652+
FROM sys.dm_db_xtp_memory_consumers;
1653+
1654+
1655+
16461656
-- Look at Columnstore index physical statistics (Query 75) (Columnstore Index Physical Stat)
16471657
SELECT OBJECT_NAME(ps.object_id) AS [TableName],
16481658
i.[name] AS [IndexName], ps.index_id, ps.partition_number,
@@ -1759,8 +1769,8 @@ ORDER BY total_duration DESC OPTION (RECOMPILE);
17591769

17601770
-- Get input buffer information for the current database (Query 80) (Input Buffer)
17611771
SELECT es.session_id, DB_NAME(es.database_id) AS [Database Name],
1762-
es.login_time, es.cpu_time, es.logical_reads,
1763-
es.[status], ib.event_info AS [Input Buffer]
1772+
es.login_time, es.cpu_time, es.logical_reads,
1773+
es.[status], ib.event_info AS [Input Buffer]
17641774
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
17651775
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
17661776
WHERE es.database_id = DB_ID()

0 commit comments

Comments
 (0)