Skip to content

Commit 8073ea2

Browse files
ktaranovktaranov
committed
Rename and fix some issues
 - some code formating, test for 2014 and 2017 SQL Servers, add CATCH block - fix issue with @instanceLevelOnly for SQL Server 2014, rename to sp_BlitzInMemoryOLTP
1 parent beb1248 commit 8073ea2

1 file changed

Lines changed: 62 additions & 49 deletions

File tree

‎Scripts/In-memory_OLTP_diagnostic_script.sql‎

Lines changed: 62 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -1,23 +1,33 @@
1+
USE master;
2+
GO
3+
4+
IF OBJECT_ID('dbo.sp_BlitzInMemoryOLTP', 'P') IS NULL EXECUTE ('CREATE PROCEDURE dbo.sp_BlitzInMemoryOLTP AS SELECT 1;');
5+
GO
6+
7+
ALTER PROCEDURE dbo.sp_BlitzInMemoryOLTP(
8+
@instanceLevelOnly BIT = 0
9+
, @dbName NVARCHAR(4000) = 'ALL'
10+
, @debug BIT = 0
11+
)
112
/*
213
Author: Ned Otter
314
Original link: http://nedotter.com/archive/2017/10/in-memory-oltp-diagnostic-script/
4-
*/
515
6-
/*
716
##########################################################################################
817
Copyright (C) 2017 Ned Otter (except where other authors are credited)
918
All rights reserved.
10-
19+
1120
You may alter this code for your own *non-commercial* purposes. You may
1221
republish altered code as long as you include this copyright and give due credit.
13-
14-
22+
23+
1524
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
1625
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
1726
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
1827
PARTICULAR PURPOSE.
1928
2029
Tested on:
30+
SQL 2014 SP2 CU8
2131
SQL 2016 SP1/CU4
2232
SQL 2017 RTM
2333
SQL 2017 CU1
@@ -39,44 +49,39 @@ Original link: http://nedotter.com/archive/2017/10/in-memory-oltp-diagnostic-scr
3949
18 Nov 2017 changed #temp to #inmemDatabases and created section for which native modules are loaded (right now only checks for procedures)
4050
19 Nov 2017 changed logic/fixed bug in loaded module display
4151
19 Nov 2017 added longest running xtp queries, as a possible way to investigate GC blocking
42-
29 Nov 2017 changed 'DROP IF EXISTS' like code to 'IF OBJECT_ID' like
43-
05 Dec 2017 changed 'EXEC' like code to 'EXEC sp_executesql' like (except lines 979, 1105), added CASE operator to exclude not supported staff from query
44-
06 Dec 2017 changed query to simple stored procedure
52+
Modfified author: Alexey Nagosrskiy
53+
2017-11-29 changed 'DROP IF EXISTS' like code to 'IF OBJECT_ID' like
54+
2017-12-05 changed 'EXEC' like code to 'EXEC sp_executesql', added CASE operator to exclude not supported staff from query
55+
2017-12-06 trasnform query to a stored procedure
56+
2017-12-07 Konstantin Taranov: some code formating, test for 2014 and 2017 SQL Servers, add CATCH block
57+
2017-12-11 Konstantin Taranov: fix issue with @instanceLevelOnly for SQL Server 2014, rename to sp_BlitzInMemoryOLTP
4558
##########################################################################################
4659
*/
47-
IF OBJECT_ID('dbo.usp_inMemoryOLTP_diag', 'P') IS NULL EXECUTE ('CREATE PROCEDURE dbo.usp_inMemoryOLTP_diag AS SELECT 1');
48-
GO
49-
50-
ALTER PROCEDURE [dbo].[usp_inMemoryOLTP_diag] (
51-
@instanceLevelOnly BIT = 0
52-
, @dbName VARCHAR(256) = 'ALL'
53-
, @Debug BIT = 0
54-
)
55-
AS BEGIN
60+
AS BEGIN TRY
5661

57-
SET NOCOUNT ON
62+
SET NOCOUNT ON;
5863

64+
DECLARE @crlf VARCHAR(10) = CHAR(10);
5965

60-
SET NOCOUNT ON
6166
DECLARE @VersionString NVARCHAR(MAX) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
6267
,@errorMessage NVARCHAR(512);
6368

6469
DECLARE @Version INT = CONVERT(INT, SUBSTRING(@VersionString, 1, CHARINDEX('.', @VersionString) - 1));
6570

71+
IF @debug = 1 PRINT('@Version = ' + CAST(@Version AS VARCHAR(30)));
72+
6673
IF @Version < 12
6774
BEGIN
68-
SET @errorMessage = CONCAT('In-Memory OLTP is not supported if SQL Server version is less than 2014. You are running SQL Server version ', @Version)
69-
;THROW 55000, @errorMessage, 1
75+
SET @errorMessage = CONCAT('In-Memory OLTP is not supported if SQL Server version is less than 2014. You are running SQL Server version ', @Version);
76+
THROW 55000, @errorMessage, 1;
7077
END;
7178

72-
7379
/*
7480
######################################################################################################################
7581
DATABASE LEVEL
7682
######################################################################################################################
7783
*/
7884

79-
--DROP TABLE IF EXISTS #inmemDatabases;
8085
IF OBJECT_ID('tempdb..#inmemDatabases') IS NOT NULL DROP TABLE #inmemDatabases;
8186
SELECT name
8287
,database_id
@@ -109,21 +114,19 @@ AS BEGIN
109114
RETURN;
110115
END;
111116

112-
--DROP TABLE IF EXISTS #moduleSplit
113117
IF OBJECT_ID('tempdb..#moduleSplit') IS NOT NULL DROP TABLE #moduleSplit;
114118

115-
--DECLARE @moduleSplit TABLE
116119
CREATE TABLE #moduleSplit
117120
(
118121
rowNumber INT IDENTITY PRIMARY KEY
119122
,value NVARCHAR(MAX) NULL
120-
)
123+
);
121124

122125
DECLARE @loadedModules TABLE
123126
(
124127
rowNumber INT IDENTITY PRIMARY KEY
125128
,name NVARCHAR(MAX) NULL
126-
)
129+
);
127130

128131
INSERT @loadedModules
129132
(
@@ -132,29 +135,29 @@ AS BEGIN
132135
SELECT name
133136
FROM sys.dm_os_loaded_modules AS a
134137
WHERE description = 'XTP Native DLL'
135-
AND PATINDEX('%[_]p[_]%', name) > 0
138+
AND PATINDEX('%[_]p[_]%', name) > 0;
136139

137-
DECLARE @maxLoadedModules INT = (SELECT COUNT(*) FROM @loadedModules)
138-
DECLARE @moduleCounter INT = 1
139-
DECLARE @loadedModuleName NVARCHAR(MAX) = ''
140+
DECLARE @maxLoadedModules INT = (SELECT COUNT(*) FROM @loadedModules);
141+
DECLARE @moduleCounter INT = 1;
142+
DECLARE @loadedModuleName NVARCHAR(MAX) = '';
140143

141-
SET @moduleCounter = 1
144+
SET @moduleCounter = 1;
142145

143146
WHILE @moduleCounter <= @maxLoadedModules
144147
BEGIN
145148

146149
SELECT @loadedModuleName = name
147150
FROM @loadedModules
148-
WHERE rowNumber = @moduleCounter
151+
WHERE rowNumber = @moduleCounter;
149152

150153
INSERT #moduleSplit
151154
(
152155
value
153156
)
154-
SELECT value
155-
FROM STRING_SPLIT(@loadedModuleName, '_')
157+
SELECT value
158+
FROM STRING_SPLIT(@loadedModuleName, '_');
156159

157-
SELECT @moduleCounter += 1
160+
SELECT @moduleCounter += 1;
158161

159162
END
160163

@@ -1027,7 +1030,8 @@ AS BEGIN
10271030

10281031
END; -- This is the loop that processes each database
10291032
END;
1030-
--DROP TABLE IF EXISTS #NativeModules;
1033+
1034+
10311035
IF OBJECT_ID('#NativeModules', 'U') IS NOT NULL DROP TABLE #NativeModules;
10321036

10331037
/*
@@ -1044,7 +1048,7 @@ AS BEGIN
10441048
@@version
10451049
###################################################
10461050
*/
1047-
IF @instanceLevelOnly = 1 AND @Version > 12
1051+
IF @instanceLevelOnly = 1 AND @Version >= 12
10481052
BEGIN
10491053

10501054
SELECT @@version AS Version;
@@ -1061,9 +1065,7 @@ AS BEGIN
10611065
,FORMAT(committed_target_kb / 1048576, '###,###,###,###,###') AS committedTargetGB
10621066
FROM sys.dm_os_sys_info;
10631067

1064-
1065-
--DROP TABLE IF EXISTS #TraceFlags
1066-
IF OBJECT_ID('#TraceFlags', 'U') IS NOT NULL DROP TABLE #TraceFlags;
1068+
IF OBJECT_ID('#TraceFlags', 'U') IS NOT NULL DROP TABLE #TraceFlags;
10671069

10681070
CREATE TABLE #TraceFlags
10691071
(
@@ -1077,7 +1079,7 @@ AS BEGIN
10771079
INSERT #TraceFlags
10781080

10791081
EXECUTE sp_executesql @sql
1080-
IF @Debug = 1 PRINT @sql
1082+
IF @debug = 1 PRINT(@sql)
10811083

10821084
DECLARE @msg NVARCHAR(MAX);
10831085

@@ -1113,7 +1115,7 @@ AS BEGIN
11131115
EXEC sys.sp_xtp_control_query_exec_stats
11141116
@old_collection_value = @InstanceCollectionStatus OUTPUT;
11151117

1116-
SELECT
1118+
SELECT
11171119
CASE
11181120
WHEN @InstanceCollectionStatus = 1 THEN 'YES'
11191121
ELSE 'NO'
@@ -1155,15 +1157,15 @@ AS BEGIN
11551157
,target_memory_kb / 1024 AS targetMemoryMB
11561158
FROM sys.databases d
11571159
INNER JOIN sys.dm_resource_governor_resource_pools AS Pools ON Pools.pool_id = d.resource_pool_id
1158-
ORDER BY poolName, databaseName
1159-
1160+
ORDER BY poolName, databaseName;
1161+
11601162
/*
11611163
###########################################################
11621164
Memory breakdown
11631165
###########################################################
11641166
*/
11651167

1166-
;WITH clerksAggregated AS
1168+
;WITH clerksAggregated AS
11671169
(
11681170
SELECT clerks.[type] AS clerkType
11691171
,CONVERT(CHAR(20)
@@ -1173,7 +1175,7 @@ AS BEGIN
11731175
AND clerks.type IN ('MEMORYCLERK_SQLBUFFERPOOL', 'MEMORYCLERK_XTP')
11741176
GROUP BY clerks.[type]
11751177
)
1176-
,clerksAggregatedString AS
1178+
,clerksAggregatedString AS
11771179
(
11781180
SELECT clerkType
11791181
,clerkTypeUsageMB
@@ -1213,7 +1215,7 @@ AS BEGIN
12131215
,state_desc
12141216
,result_desc
12151217
FROM sys.dm_db_xtp_transactions
1216-
ORDER BY begin_tsn DESC
1218+
ORDER BY begin_tsn DESC;
12171219

12181220
/*
12191221
#################################################################
@@ -1233,4 +1235,15 @@ AS BEGIN
12331235
FROM sys.event_notifications;
12341236
END;
12351237
END;
1236-
END;
1238+
END TRY
1239+
1240+
BEGIN CATCH
1241+
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
1242+
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
1243+
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
1244+
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
1245+
', Line: ' + CONVERT(varchar(5), ERROR_LINE()) +
1246+
', User name: ' + CONVERT(sysname, CURRENT_USER);
1247+
PRINT ERROR_MESSAGE();
1248+
END CATCH;
1249+
GO

0 commit comments

Comments
 (0)