· 8 years ago · Apr 19, 2018, 04:08 PM
1IF OBJECT_ID('dbo.sp_BlitzFirst') IS NULL
2 EXEC ('CREATE PROCEDURE dbo.sp_BlitzFirst AS RETURN 0;');
3GO
4
5
6ALTER PROCEDURE [dbo].[sp_BlitzFirst]
7 @LogMessage NVARCHAR(4000) = NULL ,
8 @Help TINYINT = 0 ,
9 @AsOf DATETIMEOFFSET = NULL ,
10 @ExpertMode TINYINT = 0 ,
11 @Seconds INT = 5 ,
12 @OutputType VARCHAR(20) = 'TABLE' ,
13 @OutputServerName NVARCHAR(256) = NULL ,
14 @OutputDatabaseName NVARCHAR(256) = NULL ,
15 @OutputSchemaName NVARCHAR(256) = NULL ,
16 @OutputTableName NVARCHAR(256) = NULL ,
17 @OutputTableNameFileStats NVARCHAR(256) = NULL ,
18 @OutputTableNamePerfmonStats NVARCHAR(256) = NULL ,
19 @OutputTableNameWaitStats NVARCHAR(256) = NULL ,
20 @OutputTableNameBlitzCache NVARCHAR(256) = NULL ,
21 @OutputTableRetentionDays TINYINT = 7 ,
22 @OutputXMLasNVARCHAR TINYINT = 0 ,
23 @FilterPlansByDatabase VARCHAR(MAX) = NULL ,
24 @CheckProcedureCache TINYINT = 0 ,
25 @FileLatencyThresholdMS INT = 100 ,
26 @SinceStartup TINYINT = 0 ,
27 @ShowSleepingSPIDs TINYINT = 0 ,
28 @LogMessageCheckID INT = 38,
29 @LogMessagePriority TINYINT = 1,
30 @LogMessageFindingsGroup VARCHAR(50) = 'Logged Message',
31 @LogMessageFinding VARCHAR(200) = 'Logged from sp_BlitzFirst',
32 @LogMessageURL VARCHAR(200) = '',
33 @LogMessageCheckDate DATETIMEOFFSET = NULL,
34 @Debug BIT = 0,
35 @VersionDate DATETIME = NULL OUTPUT
36 WITH EXECUTE AS CALLER, RECOMPILE
37AS
38BEGIN
39SET NOCOUNT ON;
40SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
41DECLARE @Version VARCHAR(30);
42SET @Version = '6.4';
43SET @VersionDate = '20180401';
44
45
46IF @Help = 1 PRINT '
47sp_BlitzFirst from http://FirstResponderKit.org
48
49This script gives you a prioritized list of why your SQL Server is slow right now.
50
51This is not an overall health check - for that, check out sp_Blitz.
52
53To learn more, visit http://FirstResponderKit.org where you can download new
54versions for free, watch training videos on how it works, get more info on
55the findings, contribute your own code, and more.
56
57Known limitations of this version:
58 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000. It
59 may work just fine on 2005, and if it does, hug your parents. Just don''t
60 file support issues if it breaks.
61 - If a temp table called #CustomPerfmonCounters exists for any other session,
62 but not our session, this stored proc will fail with an error saying the
63 temp table #CustomPerfmonCounters does not exist.
64 - @OutputServerName is not functional yet.
65 - If @OutputDatabaseName, SchemaName, TableName, etc are quoted with brackets,
66 the write to table may silently fail. Look, I never said I was good at this.
67
68Unknown limitations of this version:
69 - None. Like Zombo.com, the only limit is yourself.
70
71Changes - for the full list of improvements and fixes in this version, see:
72https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
73
74
75MIT License
76
77Copyright (c) 2017 Brent Ozar Unlimited
78
79Permission is hereby granted, free of charge, to any person obtaining a copy
80of this software and associated documentation files (the "Software"), to deal
81in the Software without restriction, including without limitation the rights
82to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
83copies of the Software, and to permit persons to whom the Software is
84furnished to do so, subject to the following conditions:
85
86The above copyright notice and this permission notice shall be included in all
87copies or substantial portions of the Software.
88
89THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
90IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
91FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
92AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
93LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
94OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
95SOFTWARE.
96
97';
98
99
100RAISERROR('Setting up configuration variables',10,1) WITH NOWAIT;
101DECLARE @StringToExecute NVARCHAR(MAX),
102 @ParmDefinitions NVARCHAR(4000),
103 @Parm1 NVARCHAR(4000),
104 @OurSessionID INT,
105 @LineFeed NVARCHAR(10),
106 @StockWarningHeader NVARCHAR(500),
107 @StockWarningFooter NVARCHAR(100),
108 @StockDetailsHeader NVARCHAR(100),
109 @StockDetailsFooter NVARCHAR(100),
110 @StartSampleTime DATETIMEOFFSET,
111 @FinishSampleTime DATETIMEOFFSET,
112 @FinishSampleTimeWaitFor DATETIME,
113 @ServiceName sysname,
114 @OutputTableNameFileStats_View NVARCHAR(256),
115 @OutputTableNamePerfmonStats_View NVARCHAR(256),
116 @OutputTableNameWaitStats_View NVARCHAR(256),
117 @OutputTableNameWaitStats_Categories NVARCHAR(256),
118 @ObjectFullName NVARCHAR(2000),
119 @BlitzWho NVARCHAR(MAX) = N'EXEC dbo.sp_BlitzWho @ShowSleepingSPIDs = ' + CONVERT(NVARCHAR(1), @ShowSleepingSPIDs) + N';',
120 @BlitzCacheMinutesBack INT,
121 @BlitzCacheSortOrder VARCHAR(50),
122 @UnquotedOutputServerName NVARCHAR(256) = @OutputServerName ,
123 @UnquotedOutputDatabaseName NVARCHAR(256) = @OutputDatabaseName ,
124 @UnquotedOutputSchemaName NVARCHAR(256) = @OutputSchemaName ;
125
126/* Sanitize our inputs */
127SELECT
128 @OutputTableNameFileStats_View = QUOTENAME(@OutputTableNameFileStats + '_Deltas'),
129 @OutputTableNamePerfmonStats_View = QUOTENAME(@OutputTableNamePerfmonStats + '_Deltas'),
130 @OutputTableNameWaitStats_View = QUOTENAME(@OutputTableNameWaitStats + '_Deltas'),
131 @OutputTableNameWaitStats_Categories = QUOTENAME(@OutputTableNameWaitStats + '_Categories');
132
133SELECT
134 @OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
135 @OutputSchemaName = QUOTENAME(@OutputSchemaName),
136 @OutputTableName = QUOTENAME(@OutputTableName),
137 @OutputTableNameFileStats = QUOTENAME(@OutputTableNameFileStats),
138 @OutputTableNamePerfmonStats = QUOTENAME(@OutputTableNamePerfmonStats),
139 @OutputTableNameWaitStats = QUOTENAME(@OutputTableNameWaitStats),
140 /* @OutputTableNameBlitzCache = QUOTENAME(@OutputTableNameBlitzCache), We purposely don't sanitize this because sp_BlitzCache will */
141 @LineFeed = CHAR(13) + CHAR(10),
142 @StartSampleTime = SYSDATETIMEOFFSET(),
143 @FinishSampleTime = DATEADD(ss, @Seconds, SYSDATETIMEOFFSET()),
144 @FinishSampleTimeWaitFor = DATEADD(ss, @Seconds, GETDATE()),
145 @OurSessionID = @@SPID;
146
147IF @LogMessage IS NOT NULL
148 BEGIN
149
150 RAISERROR('Saving LogMessage to table',10,1) WITH NOWAIT;
151
152 /* Try to set the output table parameters if they don't exist */
153 IF @OutputSchemaName IS NULL AND @OutputTableName IS NULL AND @OutputDatabaseName IS NULL
154 BEGIN
155 SET @OutputSchemaName = N'[dbo]';
156 SET @OutputTableName = N'[BlitzFirst]';
157
158 /* Look for the table in the current database */
159 SELECT TOP 1 @OutputDatabaseName = QUOTENAME(TABLE_CATALOG)
160 FROM INFORMATION_SCHEMA.TABLES
161 WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'BlitzFirst';
162
163 IF @OutputDatabaseName IS NULL AND EXISTS (SELECT * FROM sys.databases WHERE name = 'DBAtools')
164 SET @OutputDatabaseName = '[DBAtools]';
165
166 END;
167
168 IF @OutputDatabaseName IS NULL OR @OutputSchemaName IS NULL OR @OutputTableName IS NULL
169 OR NOT EXISTS ( SELECT *
170 FROM sys.databases
171 WHERE QUOTENAME([name]) = @OutputDatabaseName)
172 BEGIN
173 RAISERROR('We have a hard time logging a message without a valid @OutputDatabaseName, @OutputSchemaName, and @OutputTableName to log it to.', 0, 1) WITH NOWAIT;
174 RETURN;
175 END;
176 IF @LogMessageCheckDate IS NULL
177 SET @LogMessageCheckDate = SYSDATETIMEOFFSET();
178 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
179 + @OutputDatabaseName
180 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
181 + @OutputSchemaName + ''') INSERT '
182 + @OutputDatabaseName + '.'
183 + @OutputSchemaName + '.'
184 + @OutputTableName
185 + ' (ServerName, CheckDate, CheckID, Priority, FindingsGroup, Finding, Details, URL) VALUES( '''
186 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
187 + ''', ''' + CONVERT(NVARCHAR(100), @LogMessageCheckDate, 121) + ''', @LogMessageCheckID, @LogMessagePriority, @LogMessageFindingsGroup, @LogMessageFinding, @LogMessage, @LogMessageURL)';
188
189 EXECUTE sp_executesql @StringToExecute,
190 N'@LogMessageCheckID INT, @LogMessagePriority TINYINT, @LogMessageFindingsGroup VARCHAR(50), @LogMessageFinding VARCHAR(200), @LogMessage NVARCHAR(4000), @LogMessageCheckDate DATETIMEOFFSET, @LogMessageURL VARCHAR(200)',
191 @LogMessageCheckID, @LogMessagePriority, @LogMessageFindingsGroup, @LogMessageFinding, @LogMessage, @LogMessageCheckDate, @LogMessageURL;
192
193 RAISERROR('LogMessage saved to table. We have made a note of your activity. Keep up the good work.',10,1) WITH NOWAIT;
194
195 RETURN;
196 END;
197
198IF @SinceStartup = 1
199 SELECT @Seconds = 0, @ExpertMode = 1;
200
201IF @Seconds = 0 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) = 'SQL Azure'
202 SELECT @StartSampleTime = DATEADD(ms, AVG(-wait_time_ms), SYSDATETIMEOFFSET()), @FinishSampleTime = SYSDATETIMEOFFSET()
203 FROM sys.dm_os_wait_stats w
204 WHERE wait_type IN ('BROKER_TASK_STOP','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP',
205 'LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT');
206ELSE IF @Seconds = 0 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) <> 'SQL Azure'
207 SELECT @StartSampleTime = DATEADD(MINUTE,DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()),create_date) , @FinishSampleTime = SYSDATETIMEOFFSET()
208 FROM sys.databases
209 WHERE database_id = 2;
210ELSE
211 SELECT @StartSampleTime = SYSDATETIMEOFFSET(), @FinishSampleTime = DATEADD(ss, @Seconds, SYSDATETIMEOFFSET());
212
213IF @OutputType = 'SCHEMA'
214BEGIN
215 SELECT FieldList = '[Priority] TINYINT, [FindingsGroup] VARCHAR(50), [Finding] VARCHAR(200), [URL] VARCHAR(200), [Details] NVARCHAR(4000), [HowToStopIt] NVARCHAR(MAX), [QueryPlan] XML, [QueryText] NVARCHAR(MAX)';
216
217END;
218ELSE IF @AsOf IS NOT NULL AND @OutputDatabaseName IS NOT NULL AND @OutputSchemaName IS NOT NULL AND @OutputTableName IS NOT NULL
219BEGIN
220 /* They want to look into the past. */
221
222 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
223 + @OutputDatabaseName
224 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
225 + @OutputSchemaName + ''') SELECT CheckDate, [Priority], [FindingsGroup], [Finding], [URL], CAST([Details] AS [XML]) AS Details,'
226 + '[HowToStopIt], [CheckID], [StartTime], [LoginName], [NTUserName], [OriginalLoginName], [ProgramName], [HostName], [DatabaseID],'
227 + '[DatabaseName], [OpenTransactionCount], [QueryPlan], [QueryText] FROM '
228 + @OutputDatabaseName + '.'
229 + @OutputSchemaName + '.'
230 + @OutputTableName
231 + ' WHERE CheckDate >= DATEADD(mi, -15, CONVERT(DATETIMEOFFSET, ''' + CAST(@AsOf AS NVARCHAR(100)) + '''))'
232 + ' AND CheckDate <= DATEADD(mi, 15, CONVERT(DATETIMEOFFSET, ''' + CAST(@AsOf AS NVARCHAR(100)) + '''))'
233 + ' /*ORDER BY CheckDate, Priority , FindingsGroup , Finding , Details*/;';
234 EXEC(@StringToExecute);
235
236
237END; /* IF @AsOf IS NOT NULL AND @OutputDatabaseName IS NOT NULL AND @OutputSchemaName IS NOT NULL AND @OutputTableName IS NOT NULL */
238ELSE IF @LogMessage IS NULL /* IF @OutputType = 'SCHEMA' */
239BEGIN
240 /* What's running right now? This is the first and last result set. */
241 IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1
242 BEGIN
243 IF OBJECT_ID('master.dbo.sp_BlitzWho') IS NULL AND OBJECT_ID('dbo.sp_BlitzWho') IS NULL
244 BEGIN
245 PRINT N'sp_BlitzWho is not installed in the current database_files. You can get a copy from http://FirstResponderKit.org';
246 END;
247 ELSE
248 BEGIN
249 EXEC (@BlitzWho);
250 END;
251 END; /* IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1 - What's running right now? This is the first and last result set. */
252
253
254 RAISERROR('Now starting diagnostic analysis',10,1) WITH NOWAIT;
255
256 /*
257 We start by creating #BlitzFirstResults. It's a temp table that will store
258 the results from our checks. Throughout the rest of this stored procedure,
259 we're running a series of checks looking for dangerous things inside the SQL
260 Server. When we find a problem, we insert rows into #BlitzResults. At the
261 end, we return these results to the end user.
262
263 #BlitzFirstResults has a CheckID field, but there's no Check table. As we do
264 checks, we insert data into this table, and we manually put in the CheckID.
265 We (Brent Ozar Unlimited) maintain a list of the checks by ID#. You can
266 download that from http://FirstResponderKit.org if you want to build
267 a tool that relies on the output of sp_BlitzFirst.
268 */
269
270 IF OBJECT_ID('tempdb..#BlitzFirstResults') IS NOT NULL
271 DROP TABLE #BlitzFirstResults;
272 CREATE TABLE #BlitzFirstResults
273 (
274 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
275 CheckID INT NOT NULL,
276 Priority TINYINT NOT NULL,
277 FindingsGroup VARCHAR(50) NOT NULL,
278 Finding VARCHAR(200) NOT NULL,
279 URL VARCHAR(200) NULL,
280 Details NVARCHAR(4000) NULL,
281 HowToStopIt NVARCHAR(MAX) NULL,
282 QueryPlan [XML] NULL,
283 QueryText NVARCHAR(MAX) NULL,
284 StartTime DATETIMEOFFSET NULL,
285 LoginName NVARCHAR(128) NULL,
286 NTUserName NVARCHAR(128) NULL,
287 OriginalLoginName NVARCHAR(128) NULL,
288 ProgramName NVARCHAR(128) NULL,
289 HostName NVARCHAR(128) NULL,
290 DatabaseID INT NULL,
291 DatabaseName NVARCHAR(128) NULL,
292 OpenTransactionCount INT NULL,
293 QueryStatsNowID INT NULL,
294 QueryStatsFirstID INT NULL,
295 PlanHandle VARBINARY(64) NULL,
296 DetailsInt INT NULL,
297 );
298
299 IF OBJECT_ID('tempdb..#WaitStats') IS NOT NULL
300 DROP TABLE #WaitStats;
301 CREATE TABLE #WaitStats (Pass TINYINT NOT NULL, wait_type NVARCHAR(60), wait_time_ms BIGINT, signal_wait_time_ms BIGINT, waiting_tasks_count BIGINT, SampleTime DATETIMEOFFSET);
302
303 IF OBJECT_ID('tempdb..#FileStats') IS NOT NULL
304 DROP TABLE #FileStats;
305 CREATE TABLE #FileStats (
306 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
307 Pass TINYINT NOT NULL,
308 SampleTime DATETIMEOFFSET NOT NULL,
309 DatabaseID INT NOT NULL,
310 FileID INT NOT NULL,
311 DatabaseName NVARCHAR(256) ,
312 FileLogicalName NVARCHAR(256) ,
313 TypeDesc NVARCHAR(60) ,
314 SizeOnDiskMB BIGINT ,
315 io_stall_read_ms BIGINT ,
316 num_of_reads BIGINT ,
317 bytes_read BIGINT ,
318 io_stall_write_ms BIGINT ,
319 num_of_writes BIGINT ,
320 bytes_written BIGINT,
321 PhysicalName NVARCHAR(520) ,
322 avg_stall_read_ms INT ,
323 avg_stall_write_ms INT
324 );
325
326 IF OBJECT_ID('tempdb..#QueryStats') IS NOT NULL
327 DROP TABLE #QueryStats;
328 CREATE TABLE #QueryStats (
329 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
330 Pass INT NOT NULL,
331 SampleTime DATETIMEOFFSET NOT NULL,
332 [sql_handle] VARBINARY(64),
333 statement_start_offset INT,
334 statement_end_offset INT,
335 plan_generation_num BIGINT,
336 plan_handle VARBINARY(64),
337 execution_count BIGINT,
338 total_worker_time BIGINT,
339 total_physical_reads BIGINT,
340 total_logical_writes BIGINT,
341 total_logical_reads BIGINT,
342 total_clr_time BIGINT,
343 total_elapsed_time BIGINT,
344 creation_time DATETIMEOFFSET,
345 query_hash BINARY(8),
346 query_plan_hash BINARY(8),
347 Points TINYINT
348 );
349
350 IF OBJECT_ID('tempdb..#PerfmonStats') IS NOT NULL
351 DROP TABLE #PerfmonStats;
352 CREATE TABLE #PerfmonStats (
353 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
354 Pass TINYINT NOT NULL,
355 SampleTime DATETIMEOFFSET NOT NULL,
356 [object_name] NVARCHAR(128) NOT NULL,
357 [counter_name] NVARCHAR(128) NOT NULL,
358 [instance_name] NVARCHAR(128) NULL,
359 [cntr_value] BIGINT NULL,
360 [cntr_type] INT NOT NULL,
361 [value_delta] BIGINT NULL,
362 [value_per_second] DECIMAL(18,2) NULL
363 );
364
365 IF OBJECT_ID('tempdb..#PerfmonCounters') IS NOT NULL
366 DROP TABLE #PerfmonCounters;
367 CREATE TABLE #PerfmonCounters (
368 ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
369 [object_name] NVARCHAR(128) NOT NULL,
370 [counter_name] NVARCHAR(128) NOT NULL,
371 [instance_name] NVARCHAR(128) NULL
372 );
373
374 IF OBJECT_ID('tempdb..#FilterPlansByDatabase') IS NOT NULL
375 DROP TABLE #FilterPlansByDatabase;
376 CREATE TABLE #FilterPlansByDatabase (DatabaseID INT PRIMARY KEY CLUSTERED);
377
378 IF OBJECT_ID('tempdb..##WaitCategories') IS NULL
379 BEGIN
380 /* We reuse this one by default rather than recreate it every time. */
381 CREATE TABLE ##WaitCategories
382 (
383 WaitType NVARCHAR(60) PRIMARY KEY CLUSTERED,
384 WaitCategory NVARCHAR(128) NOT NULL,
385 Ignorable BIT DEFAULT 0
386 );
387 END; /* IF OBJECT_ID('tempdb..##WaitCategories') IS NULL */
388
389 IF 504 <> (SELECT COALESCE(SUM(1),0) FROM ##WaitCategories)
390 BEGIN
391 TRUNCATE TABLE ##WaitCategories;
392 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('ASYNC_IO_COMPLETION','Other Disk IO',0);
393 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('ASYNC_NETWORK_IO','Network IO',0);
394 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BACKUPIO','Other Disk IO',0);
395 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_CONNECTION_RECEIVE_TASK','Service Broker',0);
396 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_DISPATCHER','Service Broker',0);
397 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_ENDPOINT_STATE_MUTEX','Service Broker',0);
398 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_EVENTHANDLER','Service Broker',1);
399 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_FORWARDER','Service Broker',0);
400 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_INIT','Service Broker',0);
401 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_MASTERSTART','Service Broker',0);
402 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_RECEIVE_WAITFOR','User Wait',1);
403 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_REGISTERALLENDPOINTS','Service Broker',0);
404 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_SERVICE','Service Broker',0);
405 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_SHUTDOWN','Service Broker',0);
406 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_START','Service Broker',0);
407 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TASK_SHUTDOWN','Service Broker',0);
408 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TASK_STOP','Service Broker',1);
409 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TASK_SUBMIT','Service Broker',0);
410 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TO_FLUSH','Service Broker',1);
411 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TRANSMISSION_OBJECT','Service Broker',0);
412 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TRANSMISSION_TABLE','Service Broker',0);
413 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TRANSMISSION_WORK','Service Broker',0);
414 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('BROKER_TRANSMITTER','Service Broker',1);
415 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CHECKPOINT_QUEUE','Idle',1);
416 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CHKPT','Tran Log IO',0);
417 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_AUTO_EVENT','SQL CLR',0);
418 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_CRST','SQL CLR',0);
419 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_JOIN','SQL CLR',0);
420 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_MANUAL_EVENT','SQL CLR',0);
421 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_MEMORY_SPY','SQL CLR',0);
422 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_MONITOR','SQL CLR',0);
423 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_RWLOCK_READER','SQL CLR',0);
424 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_RWLOCK_WRITER','SQL CLR',0);
425 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_SEMAPHORE','SQL CLR',0);
426 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLR_TASK_START','SQL CLR',0);
427 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CLRHOST_STATE_ACCESS','SQL CLR',0);
428 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CMEMPARTITIONED','Memory',0);
429 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CMEMTHREAD','Memory',0);
430 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CXPACKET','Parallelism',0);
431 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('CXCONSUMER','Parallelism',0);
432 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRROR_DBM_EVENT','Mirroring',1);
433 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRROR_DBM_MUTEX','Mirroring',1);
434 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRROR_EVENTS_QUEUE','Mirroring',1);
435 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRROR_SEND','Mirroring',0);
436 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRROR_WORKER_QUEUE','Mirroring',1);
437 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DBMIRRORING_CMD','Mirroring',1);
438 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DIRTY_PAGE_POLL','Other',1);
439 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DISPATCHER_QUEUE_SEMAPHORE','Other',1);
440 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC','Transaction',0);
441 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC_ABORT_REQUEST','Transaction',0);
442 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC_RESOLVE','Transaction',0);
443 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC_STATE','Transaction',0);
444 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC_TMDOWN_REQUEST','Transaction',0);
445 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTC_WAITFOR_OUTCOME','Transaction',0);
446 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCNEW_ENLIST','Transaction',0);
447 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCNEW_PREPARE','Transaction',0);
448 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCNEW_RECOVERY','Transaction',0);
449 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCNEW_TM','Transaction',0);
450 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCNEW_TRANSACTION_ENLISTMENT','Transaction',0);
451 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('DTCPNTSYNC','Transaction',0);
452 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('EE_PMOLOCK','Memory',0);
453 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('EXCHANGE','Parallelism',0);
454 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('EXTERNAL_SCRIPT_NETWORK_IOF','Network IO',0);
455 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FCB_REPLICA_READ','Replication',0);
456 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FCB_REPLICA_WRITE','Replication',0);
457 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_COMPROWSET_RWLOCK','Full Text Search',0);
458 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_IFTS_RWLOCK','Full Text Search',0);
459 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT','Idle',1);
460 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_IFTSHC_MUTEX','Full Text Search',0);
461 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_IFTSISM_MUTEX','Full Text Search',1);
462 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_MASTER_MERGE','Full Text Search',0);
463 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_MASTER_MERGE_COORDINATOR','Full Text Search',0);
464 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_METADATA_MUTEX','Full Text Search',0);
465 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_PROPERTYLIST_CACHE','Full Text Search',0);
466 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FT_RESTART_CRAWL','Full Text Search',0);
467 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('FULLTEXT GATHERER','Full Text Search',0);
468 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_AG_MUTEX','Replication',0);
469 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_AR_CRITICAL_SECTION_ENTRY','Replication',0);
470 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_AR_MANAGER_MUTEX','Replication',0);
471 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_AR_UNLOAD_COMPLETED','Replication',0);
472 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST','Replication',0);
473 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_BACKUP_BULK_LOCK','Replication',0);
474 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_BACKUP_QUEUE','Replication',0);
475 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_CLUSAPI_CALL','Replication',1);
476 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_COMPRESSED_CACHE_SYNC','Replication',0);
477 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_CONNECTIVITY_INFO','Replication',0);
478 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DATABASE_FLOW_CONTROL','Replication',0);
479 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DATABASE_VERSIONING_STATE','Replication',0);
480 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DATABASE_WAIT_FOR_RECOVERY','Replication',0);
481 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DATABASE_WAIT_FOR_RESTART','Replication',0);
482 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING','Replication',0);
483 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DB_COMMAND','Replication',0);
484 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DB_OP_COMPLETION_SYNC','Replication',0);
485 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DB_OP_START_SYNC','Replication',0);
486 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DBR_SUBSCRIBER','Replication',0);
487 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DBR_SUBSCRIBER_FILTER_LIST','Replication',0);
488 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DBSEEDING','Replication',0);
489 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DBSEEDING_LIST','Replication',0);
490 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_DBSTATECHANGE_SYNC','Replication',0);
491 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FABRIC_CALLBACK','Replication',0);
492 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_BLOCK_FLUSH','Replication',0);
493 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_FILE_CLOSE','Replication',0);
494 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_FILE_REQUEST','Replication',0);
495 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_IOMGR','Replication',0);
496 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION','Replication',1);
497 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_MANAGER','Replication',0);
498 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_FILESTREAM_PREPROC','Replication',0);
499 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_GROUP_COMMIT','Replication',0);
500 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_LOGCAPTURE_SYNC','Replication',0);
501 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_LOGCAPTURE_WAIT','Replication',1);
502 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_LOGPROGRESS_SYNC','Replication',0);
503 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_NOTIFICATION_DEQUEUE','Replication',1);
504 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS','Replication',0);
505 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_NOTIFICATION_WORKER_STARTUP_SYNC','Replication',0);
506 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_NOTIFICATION_WORKER_TERMINATION_SYNC','Replication',0);
507 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_PARTNER_SYNC','Replication',0);
508 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_READ_ALL_NETWORKS','Replication',0);
509 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_RECOVERY_WAIT_FOR_CONNECTION','Replication',0);
510 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_RECOVERY_WAIT_FOR_UNDO','Replication',0);
511 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_REPLICAINFO_SYNC','Replication',0);
512 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_CANCELLATION','Replication',0);
513 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_FILE_LIST','Replication',0);
514 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_LIMIT_BACKUPS','Replication',0);
515 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_SYNC_COMPLETION','Replication',0);
516 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_TIMEOUT_TASK','Replication',0);
517 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SEEDING_WAIT_FOR_COMPLETION','Replication',0);
518 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SYNC_COMMIT','Replication',0);
519 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_SYNCHRONIZING_THROTTLE','Replication',0);
520 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TDS_LISTENER_SYNC','Replication',0);
521 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TDS_LISTENER_SYNC_PROCESSING','Replication',0);
522 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_THROTTLE_LOG_RATE_GOVERNOR','Log Rate Governor',0);
523 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TIMER_TASK','Replication',1);
524 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TRANSPORT_DBRLIST','Replication',0);
525 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TRANSPORT_FLOW_CONTROL','Replication',0);
526 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_TRANSPORT_SESSION','Replication',0);
527 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_WORK_POOL','Replication',0);
528 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_WORK_QUEUE','Replication',1);
529 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('HADR_XRF_STACK_ACCESS','Replication',0);
530 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('INSTANCE_LOG_RATE_GOVERNOR','Log Rate Governor',0);
531 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('IO_COMPLETION','Other Disk IO',0);
532 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('IO_QUEUE_LIMIT','Other Disk IO',0);
533 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('IO_RETRY','Other Disk IO',0);
534 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_DT','Latch',0);
535 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_EX','Latch',0);
536 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_KP','Latch',0);
537 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_NL','Latch',0);
538 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_SH','Latch',0);
539 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LATCH_UP','Latch',0);
540 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LAZYWRITER_SLEEP','Idle',1);
541 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_BU','Lock',0);
542 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_BU_ABORT_BLOCKERS','Lock',0);
543 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_BU_LOW_PRIORITY','Lock',0);
544 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IS','Lock',0);
545 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IS_ABORT_BLOCKERS','Lock',0);
546 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IS_LOW_PRIORITY','Lock',0);
547 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IU','Lock',0);
548 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IU_ABORT_BLOCKERS','Lock',0);
549 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IU_LOW_PRIORITY','Lock',0);
550 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IX','Lock',0);
551 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IX_ABORT_BLOCKERS','Lock',0);
552 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_IX_LOW_PRIORITY','Lock',0);
553 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_NL','Lock',0);
554 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_NL_ABORT_BLOCKERS','Lock',0);
555 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_NL_LOW_PRIORITY','Lock',0);
556 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_S','Lock',0);
557 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_S_ABORT_BLOCKERS','Lock',0);
558 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_S_LOW_PRIORITY','Lock',0);
559 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_U','Lock',0);
560 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_U_ABORT_BLOCKERS','Lock',0);
561 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_U_LOW_PRIORITY','Lock',0);
562 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_X','Lock',0);
563 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_X_ABORT_BLOCKERS','Lock',0);
564 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RIn_X_LOW_PRIORITY','Lock',0);
565 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_S','Lock',0);
566 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_S_ABORT_BLOCKERS','Lock',0);
567 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_S_LOW_PRIORITY','Lock',0);
568 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_U','Lock',0);
569 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_U_ABORT_BLOCKERS','Lock',0);
570 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RS_U_LOW_PRIORITY','Lock',0);
571 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_S','Lock',0);
572 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_S_ABORT_BLOCKERS','Lock',0);
573 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_S_LOW_PRIORITY','Lock',0);
574 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_U','Lock',0);
575 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_U_ABORT_BLOCKERS','Lock',0);
576 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_U_LOW_PRIORITY','Lock',0);
577 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_X','Lock',0);
578 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_X_ABORT_BLOCKERS','Lock',0);
579 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_RX_X_LOW_PRIORITY','Lock',0);
580 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_S','Lock',0);
581 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_S_ABORT_BLOCKERS','Lock',0);
582 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_S_LOW_PRIORITY','Lock',0);
583 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_M','Lock',0);
584 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_M_ABORT_BLOCKERS','Lock',0);
585 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_M_LOW_PRIORITY','Lock',0);
586 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_S','Lock',0);
587 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_S_ABORT_BLOCKERS','Lock',0);
588 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SCH_S_LOW_PRIORITY','Lock',0);
589 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIU','Lock',0);
590 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIU_ABORT_BLOCKERS','Lock',0);
591 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIU_LOW_PRIORITY','Lock',0);
592 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIX','Lock',0);
593 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIX_ABORT_BLOCKERS','Lock',0);
594 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_SIX_LOW_PRIORITY','Lock',0);
595 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_U','Lock',0);
596 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_U_ABORT_BLOCKERS','Lock',0);
597 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_U_LOW_PRIORITY','Lock',0);
598 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_UIX','Lock',0);
599 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_UIX_ABORT_BLOCKERS','Lock',0);
600 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_UIX_LOW_PRIORITY','Lock',0);
601 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_X','Lock',0);
602 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_X_ABORT_BLOCKERS','Lock',0);
603 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LCK_M_X_LOW_PRIORITY','Lock',0);
604 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGBUFFER','Tran Log IO',0);
605 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGMGR','Tran Log IO',0);
606 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGMGR_FLUSH','Tran Log IO',0);
607 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGMGR_PMM_LOG','Tran Log IO',0);
608 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGMGR_QUEUE','Idle',1);
609 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('LOGMGR_RESERVE_APPEND','Tran Log IO',0);
610 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('MEMORY_ALLOCATION_EXT','Memory',0);
611 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('MEMORY_GRANT_UPDATE','Memory',0);
612 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('MSQL_XACT_MGR_MUTEX','Transaction',0);
613 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('MSQL_XACT_MUTEX','Transaction',0);
614 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('MSSEARCH','Full Text Search',0);
615 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('NET_WAITFOR_PACKET','Network IO',0);
616 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('ONDEMAND_TASK_QUEUE','Idle',1);
617 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_DT','Buffer IO',0);
618 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_EX','Buffer IO',0);
619 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_KP','Buffer IO',0);
620 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_NL','Buffer IO',0);
621 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_SH','Buffer IO',0);
622 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGEIOLATCH_UP','Buffer IO',0);
623 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_DT','Buffer Latch',0);
624 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_EX','Buffer Latch',0);
625 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_KP','Buffer Latch',0);
626 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_NL','Buffer Latch',0);
627 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_SH','Buffer Latch',0);
628 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PAGELATCH_UP','Buffer Latch',0);
629 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('POOL_LOG_RATE_GOVERNOR','Log Rate Governor',0);
630 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_ABR','Preemptive',0);
631 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CLOSEBACKUPMEDIA','Preemptive',0);
632 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CLOSEBACKUPTAPE','Preemptive',0);
633 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CLOSEBACKUPVDIDEVICE','Preemptive',0);
634 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL','Preemptive',0);
635 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_COCREATEINSTANCE','Preemptive',0);
636 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_COGETCLASSOBJECT','Preemptive',0);
637 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_CREATEACCESSOR','Preemptive',0);
638 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_DELETEROWS','Preemptive',0);
639 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_GETCOMMANDTEXT','Preemptive',0);
640 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_GETDATA','Preemptive',0);
641 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_GETNEXTROWS','Preemptive',0);
642 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_GETRESULT','Preemptive',0);
643 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_GETROWSBYBOOKMARK','Preemptive',0);
644 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBFLUSH','Preemptive',0);
645 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBLOCKREGION','Preemptive',0);
646 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBREADAT','Preemptive',0);
647 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBSETSIZE','Preemptive',0);
648 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBSTAT','Preemptive',0);
649 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBUNLOCKREGION','Preemptive',0);
650 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_LBWRITEAT','Preemptive',0);
651 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_QUERYINTERFACE','Preemptive',0);
652 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_RELEASE','Preemptive',0);
653 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_RELEASEACCESSOR','Preemptive',0);
654 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_RELEASEROWS','Preemptive',0);
655 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_RELEASESESSION','Preemptive',0);
656 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_RESTARTPOSITION','Preemptive',0);
657 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_SEQSTRMREAD','Preemptive',0);
658 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_SEQSTRMREADANDWRITE','Preemptive',0);
659 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_SETDATAFAILURE','Preemptive',0);
660 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_SETPARAMETERINFO','Preemptive',0);
661 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_SETPARAMETERPROPERTIES','Preemptive',0);
662 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMLOCKREGION','Preemptive',0);
663 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMSEEKANDREAD','Preemptive',0);
664 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMSEEKANDWRITE','Preemptive',0);
665 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMSETSIZE','Preemptive',0);
666 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMSTAT','Preemptive',0);
667 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_COM_STRMUNLOCKREGION','Preemptive',0);
668 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CONSOLEWRITE','Preemptive',0);
669 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_CREATEPARAM','Preemptive',0);
670 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DEBUG','Preemptive',0);
671 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSADDLINK','Preemptive',0);
672 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSLINKEXISTCHECK','Preemptive',0);
673 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSLINKHEALTHCHECK','Preemptive',0);
674 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSREMOVELINK','Preemptive',0);
675 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSREMOVEROOT','Preemptive',0);
676 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSROOTFOLDERCHECK','Preemptive',0);
677 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSROOTINIT','Preemptive',0);
678 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DFSROOTSHARECHECK','Preemptive',0);
679 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_ABORT','Preemptive',0);
680 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_ABORTREQUESTDONE','Preemptive',0);
681 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_BEGINTRANSACTION','Preemptive',0);
682 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_COMMITREQUESTDONE','Preemptive',0);
683 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_ENLIST','Preemptive',0);
684 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_DTC_PREPAREREQUESTDONE','Preemptive',0);
685 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_FILESIZEGET','Preemptive',0);
686 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION','Preemptive',0);
687 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION','Preemptive',0);
688 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_FSAOLEDB_STARTTRANSACTION','Preemptive',0);
689 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO','Preemptive',0);
690 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_GETRMINFO','Preemptive',0);
691 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_HADR_LEASE_MECHANISM','Preemptive',1);
692 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_HTTP_EVENT_WAIT','Preemptive',0);
693 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_HTTP_REQUEST','Preemptive',0);
694 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_LOCKMONITOR','Preemptive',0);
695 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_MSS_RELEASE','Preemptive',0);
696 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_ODBCOPS','Preemptive',0);
697 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLE_UNINIT','Preemptive',0);
698 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN','Preemptive',0);
699 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_ABORTTRAN','Preemptive',0);
700 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_GETDATASOURCE','Preemptive',0);
701 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_GETLITERALINFO','Preemptive',0);
702 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_GETPROPERTIES','Preemptive',0);
703 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_GETPROPERTYINFO','Preemptive',0);
704 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_GETSCHEMALOCK','Preemptive',0);
705 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_JOINTRANSACTION','Preemptive',0);
706 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_RELEASE','Preemptive',0);
707 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDB_SETPROPERTIES','Preemptive',0);
708 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OLEDBOPS','Preemptive',0);
709 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT','Preemptive',0);
710 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE','Preemptive',0);
711 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_AUTHENTICATIONOPS','Preemptive',0);
712 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_AUTHORIZATIONOPS','Preemptive',0);
713 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT','Preemptive',0);
714 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID','Preemptive',0);
715 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER','Preemptive',0);
716 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_BACKUPREAD','Preemptive',0);
717 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CLOSEHANDLE','Preemptive',0);
718 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CLUSTEROPS','Preemptive',0);
719 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_COMOPS','Preemptive',0);
720 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_COMPLETEAUTHTOKEN','Preemptive',0);
721 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_COPYFILE','Preemptive',0);
722 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CREATEDIRECTORY','Preemptive',0);
723 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CREATEFILE','Preemptive',0);
724 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CRYPTACQUIRECONTEXT','Preemptive',0);
725 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CRYPTIMPORTKEY','Preemptive',0);
726 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_CRYPTOPS','Preemptive',0);
727 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DECRYPTMESSAGE','Preemptive',0);
728 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DELETEFILE','Preemptive',0);
729 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DELETESECURITYCONTEXT','Preemptive',0);
730 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DEVICEIOCONTROL','Preemptive',0);
731 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DEVICEOPS','Preemptive',0);
732 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DIRSVC_NETWORKOPS','Preemptive',0);
733 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DISCONNECTNAMEDPIPE','Preemptive',0);
734 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DOMAINSERVICESOPS','Preemptive',0);
735 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DSGETDCNAME','Preemptive',0);
736 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_DTCOPS','Preemptive',0);
737 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_ENCRYPTMESSAGE','Preemptive',0);
738 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FILEOPS','Preemptive',0);
739 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FINDFILE','Preemptive',0);
740 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FLUSHFILEBUFFERS','Preemptive',0);
741 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FORMATMESSAGE','Preemptive',0);
742 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FREECREDENTIALSHANDLE','Preemptive',0);
743 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_FREELIBRARY','Preemptive',0);
744 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GENERICOPS','Preemptive',0);
745 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETADDRINFO','Preemptive',0);
746 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE','Preemptive',0);
747 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETDISKFREESPACE','Preemptive',0);
748 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETFILEATTRIBUTES','Preemptive',0);
749 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETFILESIZE','Preemptive',0);
750 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE','Preemptive',0);
751 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETLONGPATHNAME','Preemptive',0);
752 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETPROCADDRESS','Preemptive',0);
753 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT','Preemptive',0);
754 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_GETVOLUMEPATHNAME','Preemptive',0);
755 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT','Preemptive',0);
756 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_LIBRARYOPS','Preemptive',0);
757 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_LOADLIBRARY','Preemptive',0);
758 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_LOGONUSER','Preemptive',0);
759 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_LOOKUPACCOUNTSID','Preemptive',0);
760 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_MESSAGEQUEUEOPS','Preemptive',0);
761 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_MOVEFILE','Preemptive',0);
762 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETGROUPGETUSERS','Preemptive',0);
763 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS','Preemptive',0);
764 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETUSERGETGROUPS','Preemptive',0);
765 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETUSERGETLOCALGROUPS','Preemptive',0);
766 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETUSERMODALSGET','Preemptive',0);
767 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY','Preemptive',0);
768 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE','Preemptive',0);
769 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_OPENDIRECTORY','Preemptive',0);
770 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_PDH_WMI_INIT','Preemptive',0);
771 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_PIPEOPS','Preemptive',0);
772 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_PROCESSOPS','Preemptive',0);
773 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES','Preemptive',0);
774 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_QUERYREGISTRY','Preemptive',0);
775 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN','Preemptive',0);
776 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_REMOVEDIRECTORY','Preemptive',0);
777 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_REPORTEVENT','Preemptive',0);
778 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_REVERTTOSELF','Preemptive',0);
779 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_RSFXDEVICEOPS','Preemptive',0);
780 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SECURITYOPS','Preemptive',0);
781 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SERVICEOPS','Preemptive',0);
782 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SETENDOFFILE','Preemptive',0);
783 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SETFILEPOINTER','Preemptive',0);
784 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SETFILEVALIDDATA','Preemptive',0);
785 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SETNAMEDSECURITYINFO','Preemptive',0);
786 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SQLCLROPS','Preemptive',0);
787 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_SQMLAUNCH','Preemptive',0);
788 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_VERIFYSIGNATURE','Preemptive',0);
789 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_VERIFYTRUST','Preemptive',0);
790 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_VSSOPS','Preemptive',0);
791 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_WAITFORSINGLEOBJECT','Preemptive',0);
792 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_WINSOCKOPS','Preemptive',0);
793 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_WRITEFILE','Preemptive',0);
794 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_WRITEFILEGATHER','Preemptive',0);
795 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_OS_WSASETLASTERROR','Preemptive',0);
796 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_REENLIST','Preemptive',0);
797 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_RESIZELOG','Preemptive',0);
798 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_ROLLFORWARDREDO','Preemptive',0);
799 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_ROLLFORWARDUNDO','Preemptive',0);
800 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SB_STOPENDPOINT','Preemptive',0);
801 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SERVER_STARTUP','Preemptive',0);
802 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SETRMINFO','Preemptive',0);
803 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SHAREDMEM_GETDATA','Preemptive',0);
804 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SNIOPEN','Preemptive',0);
805 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SOSHOST','Preemptive',0);
806 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SOSTESTING','Preemptive',0);
807 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_SP_SERVER_DIAGNOSTICS','Preemptive',1);
808 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_STARTRM','Preemptive',0);
809 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_STREAMFCB_CHECKPOINT','Preemptive',0);
810 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_STREAMFCB_RECOVER','Preemptive',0);
811 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_STRESSDRIVER','Preemptive',0);
812 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_TESTING','Preemptive',0);
813 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_TRANSIMPORT','Preemptive',0);
814 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN','Preemptive',0);
815 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_VSS_CREATESNAPSHOT','Preemptive',0);
816 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT','Preemptive',0);
817 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_CALLBACKEXECUTE','Preemptive',0);
818 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_CX_FILE_OPEN','Preemptive',0);
819 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_CX_HTTP_CALL','Preemptive',0);
820 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_DISPATCHER','Preemptive',0);
821 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_ENGINEINIT','Preemptive',0);
822 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_GETTARGETSTATE','Preemptive',0);
823 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_SESSIONCOMMIT','Preemptive',0);
824 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_TARGETFINALIZE','Preemptive',0);
825 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_TARGETINIT','Preemptive',0);
826 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XE_TIMERRUN','Preemptive',0);
827 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PREEMPTIVE_XETESTING','Preemptive',0);
828 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_ACTION_COMPLETED','Replication',0);
829 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC','Replication',0);
830 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_CLUSTER_INTEGRATION','Replication',0);
831 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_FAILOVER_COMPLETED','Replication',0);
832 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_JOIN','Replication',0);
833 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_OFFLINE_COMPLETED','Replication',0);
834 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_ONLINE_COMPLETED','Replication',0);
835 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_POST_ONLINE_COMPLETED','Replication',0);
836 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_SERVER_READY_CONNECTIONS','Replication',0);
837 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADR_WORKITEM_COMPLETED','Replication',0);
838 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_HADRSIM','Replication',0);
839 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC','Full Text Search',0);
840 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('QDS_ASYNC_QUEUE','Other',1);
841 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP','Other',1);
842 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','Other',1);
843 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('QDS_SHUTDOWN_QUEUE','Other',1);
844 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('QUERY_TRACEOUT','Tracing',0);
845 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REDO_THREAD_PENDING_WORK','Other',1);
846 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_CACHE_ACCESS','Replication',0);
847 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_HISTORYCACHE_ACCESS','Replication',0);
848 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_SCHEMA_ACCESS','Replication',0);
849 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_TRANFSINFO_ACCESS','Replication',0);
850 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_TRANHASHTABLE_ACCESS','Replication',0);
851 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPL_TRANTEXTINFO_ACCESS','Replication',0);
852 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REPLICA_WRITES','Replication',0);
853 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('REQUEST_FOR_DEADLOCK_SEARCH','Idle',1);
854 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('RESERVED_MEMORY_ALLOCATION_EXT','Memory',0);
855 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('RESOURCE_SEMAPHORE','Memory',0);
856 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('RESOURCE_SEMAPHORE_QUERY_COMPILE','Compilation',0);
857 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_BPOOL_FLUSH','Idle',0);
858 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_BUFFERPOOL_HELPLW','Idle',0);
859 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_DBSTARTUP','Idle',0);
860 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_DCOMSTARTUP','Idle',0);
861 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_MASTERDBREADY','Idle',0);
862 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_MASTERMDREADY','Idle',0);
863 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_MASTERUPGRADED','Idle',0);
864 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_MEMORYPOOL_ALLOCATEPAGES','Idle',0);
865 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_MSDBSTARTUP','Idle',0);
866 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_RETRY_VIRTUALALLOC','Idle',0);
867 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_SYSTEMTASK','Idle',1);
868 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_TASK','Idle',1);
869 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_TEMPDBSTARTUP','Idle',0);
870 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SLEEP_WORKSPACE_ALLOCATEPAGE','Idle',0);
871 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SOS_SCHEDULER_YIELD','CPU',0);
872 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP','Other',1);
873 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLCLR_APPDOMAIN','SQL CLR',0);
874 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLCLR_ASSEMBLY','SQL CLR',0);
875 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLCLR_DEADLOCK_DETECTION','SQL CLR',0);
876 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLCLR_QUANTUM_PUNISHMENT','SQL CLR',0);
877 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_BUFFER_FLUSH','Idle',1);
878 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_FILE_BUFFER','Tracing',0);
879 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_FILE_READ_IO_COMPLETION','Tracing',0);
880 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_FILE_WRITE_IO_COMPLETION','Tracing',0);
881 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP','Idle',1);
882 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_PENDING_BUFFER_WRITERS','Tracing',0);
883 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_SHUTDOWN','Tracing',0);
884 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('SQLTRACE_WAIT_ENTRIES','Idle',0);
885 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('THREADPOOL','Worker Thread',0);
886 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRACE_EVTNOTIF','Tracing',0);
887 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRACEWRITE','Tracing',0);
888 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_DT','Transaction',0);
889 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_EX','Transaction',0);
890 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_KP','Transaction',0);
891 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_NL','Transaction',0);
892 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_SH','Transaction',0);
893 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRAN_MARKLATCH_UP','Transaction',0);
894 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('TRANSACTION_MUTEX','Transaction',0);
895 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('UCS_SESSION_REGISTRATION','Other',1);
896 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('WAIT_FOR_RESULTS','User Wait',0);
897 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG','Other',1);
898 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('WAITFOR','User Wait',1);
899 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('WRITE_COMPLETION','Other Disk IO',0);
900 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('WRITELOG','Tran Log IO',0);
901 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XACT_OWN_TRANSACTION','Transaction',0);
902 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XACT_RECLAIM_SESSION','Transaction',0);
903 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XACTLOCKINFO','Transaction',0);
904 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XACTWORKSPACE_MUTEX','Transaction',0);
905 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XE_DISPATCHER_WAIT','Idle',1);
906 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XE_LIVE_TARGET_TVF','Other',1);
907 INSERT INTO ##WaitCategories(WaitType, WaitCategory, Ignorable) VALUES ('XE_TIMER_EVENT','Idle',1);
908 END; /* IF SELECT SUM(1) FROM ##WaitCategories <> 504 */
909
910
911
912 IF OBJECT_ID('tempdb..#MasterFiles') IS NOT NULL
913 DROP TABLE #MasterFiles;
914 CREATE TABLE #MasterFiles (database_id INT, file_id INT, type_desc NVARCHAR(50), name NVARCHAR(255), physical_name NVARCHAR(255), size BIGINT);
915 /* Azure SQL Database doesn't have sys.master_files, so we have to build our own. */
916 IF CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) = 'SQL Azure'
917 SET @StringToExecute = 'INSERT INTO #MasterFiles (database_id, file_id, type_desc, name, physical_name, size) SELECT DB_ID(), file_id, type_desc, name, physical_name, size FROM sys.database_files;';
918 ELSE
919 SET @StringToExecute = 'INSERT INTO #MasterFiles (database_id, file_id, type_desc, name, physical_name, size) SELECT database_id, file_id, type_desc, name, physical_name, size FROM sys.master_files;';
920 EXEC(@StringToExecute);
921
922 IF @FilterPlansByDatabase IS NOT NULL
923 BEGIN
924 IF UPPER(LEFT(@FilterPlansByDatabase,4)) = 'USER'
925 BEGIN
926 INSERT INTO #FilterPlansByDatabase (DatabaseID)
927 SELECT database_id
928 FROM sys.databases
929 WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb');
930 END;
931 ELSE
932 BEGIN
933 SET @FilterPlansByDatabase = @FilterPlansByDatabase + ','
934 ;WITH a AS
935 (
936 SELECT CAST(1 AS BIGINT) f, CHARINDEX(',', @FilterPlansByDatabase) t, 1 SEQ
937 UNION ALL
938 SELECT t + 1, CHARINDEX(',', @FilterPlansByDatabase, t + 1), SEQ + 1
939 FROM a
940 WHERE CHARINDEX(',', @FilterPlansByDatabase, t + 1) > 0
941 )
942 INSERT #FilterPlansByDatabase (DatabaseID)
943 SELECT SUBSTRING(@FilterPlansByDatabase, f, t - f)
944 FROM a
945 WHERE SUBSTRING(@FilterPlansByDatabase, f, t - f) IS NOT NULL
946 OPTION (MAXRECURSION 0);
947 END;
948 END;
949
950
951 SET @StockWarningHeader = '<?ClickToSeeCommmand -- ' + @LineFeed + @LineFeed
952 + 'WARNING: Running this command may result in data loss or an outage.' + @LineFeed
953 + 'This tool is meant as a shortcut to help generate scripts for DBAs.' + @LineFeed
954 + 'It is not a substitute for database training and experience.' + @LineFeed
955 + 'Now, having said that, here''s the details:' + @LineFeed + @LineFeed;
956
957 SELECT @StockWarningFooter = @LineFeed + @LineFeed + '-- ?>',
958 @StockDetailsHeader = '<?ClickToSeeDetails -- ' + @LineFeed,
959 @StockDetailsFooter = @LineFeed + ' -- ?>';
960
961 /* Get the instance name to use as a Perfmon counter prefix. */
962 IF CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) = 'SQL Azure'
963 SELECT TOP 1 @ServiceName = LEFT(object_name, (CHARINDEX(':', object_name) - 1))
964 FROM sys.dm_os_performance_counters;
965 ELSE
966 BEGIN
967 SET @StringToExecute = 'INSERT INTO #PerfmonStats(object_name, Pass, SampleTime, counter_name, cntr_type) SELECT CASE WHEN @@SERVICENAME = ''MSSQLSERVER'' THEN ''SQLServer'' ELSE ''MSSQL$'' + @@SERVICENAME END, 0, SYSDATETIMEOFFSET(), ''stuffing'', 0 ;';
968 EXEC(@StringToExecute);
969 SELECT @ServiceName = object_name FROM #PerfmonStats;
970 DELETE #PerfmonStats;
971 END;
972
973 /* Build a list of queries that were run in the last 10 seconds.
974 We're looking for the death-by-a-thousand-small-cuts scenario
975 where a query is constantly running, and it doesn't have that
976 big of an impact individually, but it has a ton of impact
977 overall. We're going to build this list, and then after we
978 finish our @Seconds sample, we'll compare our plan cache to
979 this list to see what ran the most. */
980
981 /* Populate #QueryStats. SQL 2005 doesn't have query hash or query plan hash. */
982 IF @CheckProcedureCache = 1
983 BEGIN
984 RAISERROR('@CheckProcedureCache = 1, capturing first pass of plan cache',10,1) WITH NOWAIT;
985 IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
986 BEGIN
987 IF @FilterPlansByDatabase IS NULL
988 BEGIN
989 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
990 SELECT [sql_handle], 1 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
991 FROM sys.dm_exec_query_stats qs
992 WHERE qs.last_execution_time >= (DATEADD(ss, -10, SYSDATETIMEOFFSET()));';
993 END;
994 ELSE
995 BEGIN
996 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
997 SELECT [sql_handle], 1 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
998 FROM sys.dm_exec_query_stats qs
999 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1000 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1001 WHERE qs.last_execution_time >= (DATEADD(ss, -10, SYSDATETIMEOFFSET()))
1002 AND attr.attribute = ''dbid'';';
1003 END;
1004 END;
1005 ELSE
1006 BEGIN
1007 IF @FilterPlansByDatabase IS NULL
1008 BEGIN
1009 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1010 SELECT [sql_handle], 1 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
1011 FROM sys.dm_exec_query_stats qs
1012 WHERE qs.last_execution_time >= (DATEADD(ss, -10, SYSDATETIMEOFFSET()));';
1013 END;
1014 ELSE
1015 BEGIN
1016 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1017 SELECT [sql_handle], 1 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
1018 FROM sys.dm_exec_query_stats qs
1019 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1020 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1021 WHERE qs.last_execution_time >= (DATEADD(ss, -10, SYSDATETIMEOFFSET()))
1022 AND attr.attribute = ''dbid'';';
1023 END;
1024 END;
1025 EXEC(@StringToExecute);
1026
1027 /* Get the totals for the entire plan cache */
1028 INSERT INTO #QueryStats (Pass, SampleTime, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time)
1029 SELECT -1 AS Pass, SYSDATETIMEOFFSET(), SUM(execution_count), SUM(total_worker_time), SUM(total_physical_reads), SUM(total_logical_writes), SUM(total_logical_reads), SUM(total_clr_time), SUM(total_elapsed_time), MIN(creation_time)
1030 FROM sys.dm_exec_query_stats qs;
1031 END; /*IF @CheckProcedureCache = 1 */
1032
1033
1034 IF EXISTS (SELECT *
1035 FROM tempdb.sys.all_objects obj
1036 INNER JOIN tempdb.sys.all_columns col1 ON obj.object_id = col1.object_id AND col1.name = 'object_name'
1037 INNER JOIN tempdb.sys.all_columns col2 ON obj.object_id = col2.object_id AND col2.name = 'counter_name'
1038 INNER JOIN tempdb.sys.all_columns col3 ON obj.object_id = col3.object_id AND col3.name = 'instance_name'
1039 WHERE obj.name LIKE '%CustomPerfmonCounters%')
1040 BEGIN
1041 SET @StringToExecute = 'INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) SELECT [object_name],[counter_name],[instance_name] FROM #CustomPerfmonCounters';
1042 EXEC(@StringToExecute);
1043 END;
1044 ELSE
1045 BEGIN
1046 /* Add our default Perfmon counters */
1047 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Forwarded Records/sec', NULL);
1048 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Page compression attempts/sec', NULL);
1049 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Page Splits/sec', NULL);
1050 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Skipped Ghosted Records/sec', NULL);
1051 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Table Lock Escalations/sec', NULL);
1052 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Worktables Created/sec', NULL);
1053 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Page life expectancy', NULL);
1054 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Page reads/sec', NULL);
1055 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Page writes/sec', NULL);
1056 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Readahead pages/sec', NULL);
1057 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Target pages', NULL);
1058 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Total pages', NULL);
1059 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','', NULL);
1060 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Active Transactions','_Total');
1061 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','Log Growths', '_Total');
1062 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','Log Shrinks', '_Total');
1063 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','Transactions/sec',NULL);
1064 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','Write Transactions/sec',NULL);
1065 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Databases','XTP Memory Used (KB)',NULL);
1066 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Exec Statistics','Distributed Query', 'Execs in progress');
1067 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Exec Statistics','DTC calls', 'Execs in progress');
1068 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Exec Statistics','Extended Procedures', 'Execs in progress');
1069 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Exec Statistics','OLEDB calls', 'Execs in progress');
1070 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','Active Temp Tables', NULL);
1071 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','Logins/sec', NULL);
1072 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','Logouts/sec', NULL);
1073 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','Mars Deadlocks', NULL);
1074 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','Processes blocked', NULL);
1075 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Number of Deadlocks/sec', NULL);
1076 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Memory Manager','Memory Grants Pending', NULL);
1077 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Errors','Errors/sec', '_Total');
1078 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Batch Requests/sec', NULL);
1079 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Forced Parameterizations/sec', NULL);
1080 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Guided plan executions/sec', NULL);
1081 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Attention rate', NULL);
1082 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Compilations/sec', NULL);
1083 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Re-Compilations/sec', NULL);
1084 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Workload Group Stats','Query optimizations/sec',NULL);
1085 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Workload Group Stats','Suboptimal plans/sec',NULL);
1086 /* Below counters added by Jefferson Elias */
1087 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Worktables From Cache Base',NULL);
1088 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Worktables From Cache Ratio',NULL);
1089 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Database pages',NULL);
1090 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Free pages',NULL);
1091 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Stolen pages',NULL);
1092 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Memory Manager','Granted Workspace Memory (KB)',NULL);
1093 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Memory Manager','Maximum Workspace Memory (KB)',NULL);
1094 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Memory Manager','Target Server Memory (KB)',NULL);
1095 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Memory Manager','Total Server Memory (KB)',NULL);
1096 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Buffer cache hit ratio',NULL);
1097 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Buffer cache hit ratio base',NULL);
1098 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Checkpoint pages/sec',NULL);
1099 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Free list stalls/sec',NULL);
1100 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Lazy writes/sec',NULL);
1101 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Auto-Param Attempts/sec',NULL);
1102 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Failed Auto-Params/sec',NULL);
1103 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Safe Auto-Params/sec',NULL);
1104 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','Unsafe Auto-Params/sec',NULL);
1105 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Workfiles Created/sec',NULL);
1106 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':General Statistics','User Connections',NULL);
1107 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Latches','Average Latch Wait Time (ms)',NULL);
1108 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Latches','Average Latch Wait Time Base',NULL);
1109 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Latches','Latch Waits/sec',NULL);
1110 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Latches','Total Latch Wait Time (ms)',NULL);
1111 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Average Wait Time (ms)',NULL);
1112 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Average Wait Time Base',NULL);
1113 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Lock Requests/sec',NULL);
1114 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Lock Timeouts/sec',NULL);
1115 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Lock Wait Time (ms)',NULL);
1116 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Locks','Lock Waits/sec',NULL);
1117 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Transactions','Longest Transaction Running Time',NULL);
1118 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Full Scans/sec',NULL);
1119 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Index Searches/sec',NULL);
1120 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Buffer Manager','Page lookups/sec',NULL);
1121 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Cursor Manager by Type','Active cursors',NULL);
1122 /* Below counters are for In-Memory OLTP (Hekaton), which have a different naming convention.
1123 And yes, they actually hard-coded the version numbers into the counters.
1124 For why, see: https://connect.microsoft.com/SQLServer/feedback/details/817216/xtp-perfmon-counters-should-appear-under-sql-server-perfmon-counter-group
1125 */
1126 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Cursors','Expired rows removed/sec',NULL);
1127 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Cursors','Expired rows touched/sec',NULL);
1128 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Garbage Collection','Rows processed/sec',NULL);
1129 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP IO Governor','Io Issued/sec',NULL);
1130 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Phantom Processor','Phantom expired rows touched/sec',NULL);
1131 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Phantom Processor','Phantom rows touched/sec',NULL);
1132 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Transaction Log','Log bytes written/sec',NULL);
1133 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Transaction Log','Log records written/sec',NULL);
1134 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Transactions','Transactions aborted by user/sec',NULL);
1135 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Transactions','Transactions aborted/sec',NULL);
1136 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2014 XTP Transactions','Transactions created/sec',NULL);
1137 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Cursors','Expired rows removed/sec',NULL);
1138 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Cursors','Expired rows touched/sec',NULL);
1139 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Garbage Collection','Rows processed/sec',NULL);
1140 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP IO Governor','Io Issued/sec',NULL);
1141 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Phantom Processor','Phantom expired rows touched/sec',NULL);
1142 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Phantom Processor','Phantom rows touched/sec',NULL);
1143 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Transaction Log','Log bytes written/sec',NULL);
1144 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Transaction Log','Log records written/sec',NULL);
1145 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Transactions','Transactions aborted by user/sec',NULL);
1146 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Transactions','Transactions aborted/sec',NULL);
1147 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQL Server 2016 XTP Transactions','Transactions created/sec',NULL);
1148 END;
1149
1150 /* Populate #FileStats, #PerfmonStats, #WaitStats with DMV data.
1151 After we finish doing our checks, we'll take another sample and compare them. */
1152 RAISERROR('Capturing first pass of wait stats, perfmon counters, file stats',10,1) WITH NOWAIT;
1153 INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
1154 SELECT
1155 x.Pass,
1156 x.SampleTime,
1157 x.wait_type,
1158 SUM(x.sum_wait_time_ms) AS sum_wait_time_ms,
1159 SUM(x.sum_signal_wait_time_ms) AS sum_signal_wait_time_ms,
1160 SUM(x.sum_waiting_tasks) AS sum_waiting_tasks
1161 FROM (
1162 SELECT
1163 1 AS Pass,
1164 CASE @Seconds WHEN 0 THEN @StartSampleTime ELSE SYSDATETIMEOFFSET() END AS SampleTime,
1165 owt.wait_type,
1166 CASE @Seconds WHEN 0 THEN 0 ELSE SUM(owt.wait_duration_ms) OVER (PARTITION BY owt.wait_type, owt.session_id)
1167 - CASE WHEN @Seconds = 0 THEN 0 ELSE (@Seconds * 1000) END END AS sum_wait_time_ms,
1168 0 AS sum_signal_wait_time_ms,
1169 0 AS sum_waiting_tasks
1170 FROM sys.dm_os_waiting_tasks owt
1171 WHERE owt.session_id > 50
1172 AND owt.wait_duration_ms >= CASE @Seconds WHEN 0 THEN 0 ELSE @Seconds * 1000 END
1173 UNION ALL
1174 SELECT
1175 1 AS Pass,
1176 CASE @Seconds WHEN 0 THEN @StartSampleTime ELSE SYSDATETIMEOFFSET() END AS SampleTime,
1177 os.wait_type,
1178 CASE @Seconds WHEN 0 THEN 0 ELSE SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) END AS sum_wait_time_ms,
1179 CASE @Seconds WHEN 0 THEN 0 ELSE SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) END AS sum_signal_wait_time_ms,
1180 CASE @Seconds WHEN 0 THEN 0 ELSE SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) END AS sum_waiting_tasks
1181 FROM sys.dm_os_wait_stats os
1182 ) x
1183 WHERE x.wait_type NOT IN (
1184 'BROKER_EVENTHANDLER'
1185 , 'BROKER_RECEIVE_WAITFOR'
1186 , 'BROKER_TASK_STOP'
1187 , 'BROKER_TO_FLUSH'
1188 , 'BROKER_TRANSMITTER'
1189 , 'CHECKPOINT_QUEUE'
1190 , 'DBMIRROR_DBM_EVENT'
1191 , 'DBMIRROR_DBM_MUTEX'
1192 , 'DBMIRROR_EVENTS_QUEUE'
1193 , 'DBMIRROR_WORKER_QUEUE'
1194 , 'DBMIRRORING_CMD'
1195 , 'DIRTY_PAGE_POLL'
1196 , 'DISPATCHER_QUEUE_SEMAPHORE'
1197 , 'FT_IFTS_SCHEDULER_IDLE_WAIT'
1198 , 'FT_IFTSHC_MUTEX'
1199 , 'HADR_CLUSAPI_CALL'
1200 , 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
1201 , 'HADR_LOGCAPTURE_WAIT'
1202 , 'HADR_NOTIFICATION_DEQUEUE'
1203 , 'HADR_TIMER_TASK'
1204 , 'HADR_WORK_QUEUE'
1205 , 'LAZYWRITER_SLEEP'
1206 , 'LOGMGR_QUEUE'
1207 , 'ONDEMAND_TASK_QUEUE'
1208 , 'PREEMPTIVE_HADR_LEASE_MECHANISM'
1209 , 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS'
1210 , 'QDS_ASYNC_QUEUE'
1211 , 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
1212 , 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
1213 , 'QDS_SHUTDOWN_QUEUE'
1214 , 'REDO_THREAD_PENDING_WORK'
1215 , 'REQUEST_FOR_DEADLOCK_SEARCH'
1216 , 'SLEEP_SYSTEMTASK'
1217 , 'SLEEP_TASK'
1218 , 'SP_SERVER_DIAGNOSTICS_SLEEP'
1219 , 'SQLTRACE_BUFFER_FLUSH'
1220 , 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
1221 , 'UCS_SESSION_REGISTRATION'
1222 , 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
1223 , 'WAITFOR'
1224 , 'XE_DISPATCHER_WAIT'
1225 , 'XE_LIVE_TARGET_TVF'
1226 , 'XE_TIMER_EVENT'
1227 )
1228 GROUP BY x.Pass, x.SampleTime, x.wait_type
1229 ORDER BY sum_wait_time_ms DESC;
1230
1231
1232 INSERT INTO #FileStats (Pass, SampleTime, DatabaseID, FileID, DatabaseName, FileLogicalName, SizeOnDiskMB, io_stall_read_ms ,
1233 num_of_reads, [bytes_read] , io_stall_write_ms,num_of_writes, [bytes_written], PhysicalName, TypeDesc)
1234 SELECT
1235 1 AS Pass,
1236 CASE @Seconds WHEN 0 THEN @StartSampleTime ELSE SYSDATETIMEOFFSET() END AS SampleTime,
1237 mf.[database_id],
1238 mf.[file_id],
1239 DB_NAME(vfs.database_id) AS [db_name],
1240 mf.name + N' [' + mf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS file_logical_name ,
1241 CAST(( ( vfs.size_on_disk_bytes / 1024.0 ) / 1024.0 ) AS INT) AS size_on_disk_mb ,
1242 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.io_stall_read_ms END ,
1243 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.num_of_reads END ,
1244 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.[num_of_bytes_read] END ,
1245 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.io_stall_write_ms END ,
1246 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.num_of_writes END ,
1247 CASE @Seconds WHEN 0 THEN 0 ELSE vfs.[num_of_bytes_written] END ,
1248 mf.physical_name,
1249 mf.type_desc
1250 FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS vfs
1251 INNER JOIN #MasterFiles AS mf ON vfs.file_id = mf.file_id
1252 AND vfs.database_id = mf.database_id
1253 WHERE vfs.num_of_reads > 0
1254 OR vfs.num_of_writes > 0;
1255
1256 INSERT INTO #PerfmonStats (Pass, SampleTime, [object_name],[counter_name],[instance_name],[cntr_value],[cntr_type])
1257 SELECT 1 AS Pass,
1258 CASE @Seconds WHEN 0 THEN @StartSampleTime ELSE SYSDATETIMEOFFSET() END AS SampleTime, RTRIM(dmv.object_name), RTRIM(dmv.counter_name), RTRIM(dmv.instance_name), CASE @Seconds WHEN 0 THEN 0 ELSE dmv.cntr_value END, dmv.cntr_type
1259 FROM #PerfmonCounters counters
1260 INNER JOIN sys.dm_os_performance_counters dmv ON counters.counter_name COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.counter_name) COLLATE SQL_Latin1_General_CP1_CI_AS
1261 AND counters.[object_name] COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.[object_name]) COLLATE SQL_Latin1_General_CP1_CI_AS
1262 AND (counters.[instance_name] IS NULL OR counters.[instance_name] COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.[instance_name]) COLLATE SQL_Latin1_General_CP1_CI_AS);
1263
1264 RAISERROR('Beginning investigatory queries',10,1) WITH NOWAIT;
1265
1266
1267 /* Maintenance Tasks Running - Backup Running - CheckID 1 */
1268 IF @Seconds > 0
1269 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount)
1270 SELECT 1 AS CheckID,
1271 1 AS Priority,
1272 'Maintenance Tasks Running' AS FindingGroup,
1273 'Backup Running' AS Finding,
1274 'http://www.BrentOzar.com/askbrent/backups/' AS URL,
1275 'Backup of ' + DB_NAME(db.resource_database_id) + ' database (' + (SELECT CAST(CAST(SUM(size * 8.0 / 1024 / 1024) AS BIGINT) AS NVARCHAR) FROM #MasterFiles WHERE database_id = db.resource_database_id) + 'GB) is ' + CAST(r.percent_complete AS NVARCHAR(100)) + '% complete, has been running since ' + CAST(r.start_time AS NVARCHAR(100)) + '. '
1276 + CASE WHEN COALESCE(s.nt_user_name, s.login_name) IS NOT NULL THEN (' Login: ' + COALESCE(s.nt_user_name, s.login_name) + ' ') ELSE '' END AS Details,
1277 'KILL ' + CAST(r.session_id AS NVARCHAR(100)) + ';' AS HowToStopIt,
1278 pl.query_plan AS QueryPlan,
1279 r.start_time AS StartTime,
1280 s.login_name AS LoginName,
1281 s.nt_user_name AS NTUserName,
1282 s.[program_name] AS ProgramName,
1283 s.[host_name] AS HostName,
1284 db.[resource_database_id] AS DatabaseID,
1285 DB_NAME(db.resource_database_id) AS DatabaseName,
1286 0 AS OpenTransactionCount
1287 FROM sys.dm_exec_requests r
1288 INNER JOIN sys.dm_exec_connections c ON r.session_id = c.session_id
1289 INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
1290 INNER JOIN (
1291 SELECT DISTINCT request_session_id, resource_database_id
1292 FROM sys.dm_tran_locks
1293 WHERE resource_type = N'DATABASE'
1294 AND request_mode = N'S'
1295 AND request_status = N'GRANT'
1296 AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE') AS db ON s.session_id = db.request_session_id
1297 CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) pl
1298 WHERE r.command LIKE 'BACKUP%'
1299 AND r.start_time <= DATEADD(minute, -5, GETDATE());
1300
1301 /* If there's a backup running, add details explaining how long full backup has been taking in the last month. */
1302 IF @Seconds > 0 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) <> 'SQL Azure'
1303 BEGIN
1304 SET @StringToExecute = 'UPDATE #BlitzFirstResults SET Details = Details + '' Over the last 60 days, the full backup usually takes '' + CAST((SELECT AVG(DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date)) FROM msdb.dbo.backupset bs WHERE abr.DatabaseName = bs.database_name AND bs.type = ''D'' AND bs.backup_start_date > DATEADD(dd, -60, SYSDATETIMEOFFSET()) AND bs.backup_finish_date IS NOT NULL) AS NVARCHAR(100)) + '' minutes.'' FROM #BlitzFirstResults abr WHERE abr.CheckID = 1 AND EXISTS (SELECT * FROM msdb.dbo.backupset bs WHERE bs.type = ''D'' AND bs.backup_start_date > DATEADD(dd, -60, SYSDATETIMEOFFSET()) AND bs.backup_finish_date IS NOT NULL AND abr.DatabaseName = bs.database_name AND DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) > 1)';
1305 EXEC(@StringToExecute);
1306 END;
1307
1308
1309 /* Maintenance Tasks Running - DBCC CHECK* Running - CheckID 2 */
1310 IF @Seconds > 0 AND EXISTS(SELECT * FROM sys.dm_exec_requests WHERE command LIKE 'DBCC%')
1311 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount)
1312 SELECT 2 AS CheckID,
1313 1 AS Priority,
1314 'Maintenance Tasks Running' AS FindingGroup,
1315 'DBCC CHECK* Running' AS Finding,
1316 'http://www.BrentOzar.com/askbrent/dbcc/' AS URL,
1317 'Corruption check of ' + DB_NAME(db.resource_database_id) + ' database (' + (SELECT CAST(CAST(SUM(size * 8.0 / 1024 / 1024) AS BIGINT) AS NVARCHAR) FROM #MasterFiles WHERE database_id = db.resource_database_id) + 'GB) has been running since ' + CAST(r.start_time AS NVARCHAR(100)) + '. ' AS Details,
1318 'KILL ' + CAST(r.session_id AS NVARCHAR(100)) + ';' AS HowToStopIt,
1319 pl.query_plan AS QueryPlan,
1320 r.start_time AS StartTime,
1321 s.login_name AS LoginName,
1322 s.nt_user_name AS NTUserName,
1323 s.[program_name] AS ProgramName,
1324 s.[host_name] AS HostName,
1325 db.[resource_database_id] AS DatabaseID,
1326 DB_NAME(db.resource_database_id) AS DatabaseName,
1327 0 AS OpenTransactionCount
1328 FROM sys.dm_exec_requests r
1329 INNER JOIN sys.dm_exec_connections c ON r.session_id = c.session_id
1330 INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
1331 INNER JOIN (SELECT DISTINCT l.request_session_id, l.resource_database_id
1332 FROM sys.dm_tran_locks l
1333 INNER JOIN sys.databases d ON l.resource_database_id = d.database_id
1334 WHERE l.resource_type = N'DATABASE'
1335 AND l.request_mode = N'S'
1336 AND l.request_status = N'GRANT'
1337 AND l.request_owner_type = N'SHARED_TRANSACTION_WORKSPACE') AS db ON s.session_id = db.request_session_id
1338 CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) pl
1339 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
1340 WHERE r.command LIKE 'DBCC%'
1341 AND CAST(t.text AS NVARCHAR(4000)) NOT LIKE '%dm_db_index_physical_stats%';
1342
1343
1344 /* Maintenance Tasks Running - Restore Running - CheckID 3 */
1345 IF @Seconds > 0
1346 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount)
1347 SELECT 3 AS CheckID,
1348 1 AS Priority,
1349 'Maintenance Tasks Running' AS FindingGroup,
1350 'Restore Running' AS Finding,
1351 'http://www.BrentOzar.com/askbrent/backups/' AS URL,
1352 'Restore of ' + DB_NAME(db.resource_database_id) + ' database (' + (SELECT CAST(CAST(SUM(size * 8.0 / 1024 / 1024) AS BIGINT) AS NVARCHAR) FROM #MasterFiles WHERE database_id = db.resource_database_id) + 'GB) is ' + CAST(r.percent_complete AS NVARCHAR(100)) + '% complete, has been running since ' + CAST(r.start_time AS NVARCHAR(100)) + '. ' AS Details,
1353 'KILL ' + CAST(r.session_id AS NVARCHAR(100)) + ';' AS HowToStopIt,
1354 pl.query_plan AS QueryPlan,
1355 r.start_time AS StartTime,
1356 s.login_name AS LoginName,
1357 s.nt_user_name AS NTUserName,
1358 s.[program_name] AS ProgramName,
1359 s.[host_name] AS HostName,
1360 db.[resource_database_id] AS DatabaseID,
1361 DB_NAME(db.resource_database_id) AS DatabaseName,
1362 0 AS OpenTransactionCount
1363 FROM sys.dm_exec_requests r
1364 INNER JOIN sys.dm_exec_connections c ON r.session_id = c.session_id
1365 INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
1366 INNER JOIN (
1367 SELECT DISTINCT request_session_id, resource_database_id
1368 FROM sys.dm_tran_locks
1369 WHERE resource_type = N'DATABASE'
1370 AND request_mode = N'S'
1371 AND request_status = N'GRANT') AS db ON s.session_id = db.request_session_id
1372 CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) pl
1373 WHERE r.command LIKE 'RESTORE%';
1374
1375
1376 /* SQL Server Internal Maintenance - Database File Growing - CheckID 4 */
1377 IF @Seconds > 0
1378 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount)
1379 SELECT 4 AS CheckID,
1380 1 AS Priority,
1381 'SQL Server Internal Maintenance' AS FindingGroup,
1382 'Database File Growing' AS Finding,
1383 'http://www.BrentOzar.com/go/instant' AS URL,
1384 'SQL Server is waiting for Windows to provide storage space for a database restore, a data file growth, or a log file growth. This task has been running since ' + CAST(r.start_time AS NVARCHAR(100)) + '.' + @LineFeed + 'Check the query plan (expert mode) to identify the database involved.' AS Details,
1385 'Unfortunately, you can''t stop this, but you can prevent it next time. Check out http://www.BrentOzar.com/go/instant for details.' AS HowToStopIt,
1386 pl.query_plan AS QueryPlan,
1387 r.start_time AS StartTime,
1388 s.login_name AS LoginName,
1389 s.nt_user_name AS NTUserName,
1390 s.[program_name] AS ProgramName,
1391 s.[host_name] AS HostName,
1392 NULL AS DatabaseID,
1393 NULL AS DatabaseName,
1394 0 AS OpenTransactionCount
1395 FROM sys.dm_os_waiting_tasks t
1396 INNER JOIN sys.dm_exec_connections c ON t.session_id = c.session_id
1397 INNER JOIN sys.dm_exec_requests r ON t.session_id = r.session_id
1398 INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
1399 CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) pl
1400 WHERE t.wait_type = 'PREEMPTIVE_OS_WRITEFILEGATHER';
1401
1402
1403 /* Query Problems - Long-Running Query Blocking Others - CheckID 5 */
1404 IF @@VERSION NOT LIKE '%Azure%' AND @Seconds > 0 AND EXISTS(SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'LCK%' AND wait_duration_ms > 30000)
1405 BEGIN
1406 SET @StringToExecute = N'INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount)
1407 SELECT 5 AS CheckID,
1408 1 AS Priority,
1409 ''Query Problems'' AS FindingGroup,
1410 ''Long-Running Query Blocking Others'' AS Finding,
1411 ''http://www.BrentOzar.com/go/blocking'' AS URL,
1412 ''Query in '' + COALESCE(DB_NAME(COALESCE((SELECT TOP 1 dbid FROM sys.dm_exec_sql_text(r.sql_handle)),
1413 (SELECT TOP 1 t.dbid FROM master..sysprocesses spBlocker CROSS APPLY sys.dm_exec_sql_text(spBlocker.sql_handle) t WHERE spBlocker.spid = tBlocked.blocking_session_id))), ''(Unknown)'') + '' has a last request start time of '' + CAST(s.last_request_start_time AS NVARCHAR(100)) + ''. Query follows:'' '
1414 + @LineFeed + @LineFeed +
1415 '+ CAST(COALESCE((SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(r.sql_handle)),
1416 (SELECT TOP 1 [text] FROM master..sysprocesses spBlocker CROSS APPLY sys.dm_exec_sql_text(spBlocker.sql_handle) WHERE spBlocker.spid = tBlocked.blocking_session_id), '''') AS NVARCHAR(2000)) AS Details,
1417 ''KILL '' + CAST(tBlocked.blocking_session_id AS NVARCHAR(100)) + '';'' AS HowToStopIt,
1418 (SELECT TOP 1 query_plan FROM sys.dm_exec_query_plan(r.plan_handle)) AS QueryPlan,
1419 COALESCE((SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(r.sql_handle)),
1420 (SELECT TOP 1 [text] FROM master..sysprocesses spBlocker CROSS APPLY sys.dm_exec_sql_text(spBlocker.sql_handle) WHERE spBlocker.spid = tBlocked.blocking_session_id)) AS QueryText,
1421 r.start_time AS StartTime,
1422 s.login_name AS LoginName,
1423 s.nt_user_name AS NTUserName,
1424 s.[program_name] AS ProgramName,
1425 s.[host_name] AS HostName,
1426 r.[database_id] AS DatabaseID,
1427 DB_NAME(r.database_id) AS DatabaseName,
1428 0 AS OpenTransactionCount
1429 FROM sys.dm_os_waiting_tasks tBlocked
1430 INNER JOIN sys.dm_exec_sessions s ON tBlocked.blocking_session_id = s.session_id
1431 LEFT OUTER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
1432 INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
1433 WHERE tBlocked.wait_type LIKE ''LCK%'' AND tBlocked.wait_duration_ms > 30000;';
1434 EXECUTE sp_executesql @StringToExecute;
1435 END;
1436
1437 /* Query Problems - Plan Cache Erased Recently */
1438 IF DATEADD(mi, -15, SYSDATETIME()) < (SELECT TOP 1 creation_time FROM sys.dm_exec_query_stats ORDER BY creation_time)
1439 BEGIN
1440 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1441 SELECT TOP 1 7 AS CheckID,
1442 50 AS Priority,
1443 'Query Problems' AS FindingGroup,
1444 'Plan Cache Erased Recently' AS Finding,
1445 'http://www.BrentOzar.com/askbrent/plan-cache-erased-recently/' AS URL,
1446 'The oldest query in the plan cache was created at ' + CAST(creation_time AS NVARCHAR(50)) + '. ' + @LineFeed + @LineFeed
1447 + 'This indicates that someone ran DBCC FREEPROCCACHE at that time,' + @LineFeed
1448 + 'Giving SQL Server temporary amnesia. Now, as queries come in,' + @LineFeed
1449 + 'SQL Server has to use a lot of CPU power in order to build execution' + @LineFeed
1450 + 'plans and put them in cache again. This causes high CPU loads.' AS Details,
1451 'Find who did that, and stop them from doing it again.' AS HowToStopIt
1452 FROM sys.dm_exec_query_stats
1453 ORDER BY creation_time;
1454 END;
1455
1456
1457 /* Query Problems - Sleeping Query with Open Transactions - CheckID 8 */
1458 IF @Seconds > 0
1459 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, QueryText, OpenTransactionCount)
1460 SELECT 8 AS CheckID,
1461 50 AS Priority,
1462 'Query Problems' AS FindingGroup,
1463 'Sleeping Query with Open Transactions' AS Finding,
1464 'http://www.brentozar.com/askbrent/sleeping-query-with-open-transactions/' AS URL,
1465 'Database: ' + DB_NAME(db.resource_database_id) + @LineFeed + 'Host: ' + s.[host_name] + @LineFeed + 'Program: ' + s.[program_name] + @LineFeed + 'Asleep with open transactions and locks since ' + CAST(s.last_request_end_time AS NVARCHAR(100)) + '. ' AS Details,
1466 'KILL ' + CAST(s.session_id AS NVARCHAR(100)) + ';' AS HowToStopIt,
1467 s.last_request_start_time AS StartTime,
1468 s.login_name AS LoginName,
1469 s.nt_user_name AS NTUserName,
1470 s.[program_name] AS ProgramName,
1471 s.[host_name] AS HostName,
1472 db.[resource_database_id] AS DatabaseID,
1473 DB_NAME(db.resource_database_id) AS DatabaseName,
1474 (SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS QueryText,
1475 sessions_with_transactions.open_transaction_count AS OpenTransactionCount
1476 FROM (SELECT session_id, SUM(open_transaction_count) AS open_transaction_count FROM sys.dm_exec_requests WHERE open_transaction_count > 0 GROUP BY session_id) AS sessions_with_transactions
1477 INNER JOIN sys.dm_exec_sessions s ON sessions_with_transactions.session_id = s.session_id
1478 INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
1479 INNER JOIN (
1480 SELECT DISTINCT request_session_id, resource_database_id
1481 FROM sys.dm_tran_locks
1482 WHERE resource_type = N'DATABASE'
1483 AND request_mode = N'S'
1484 AND request_status = N'GRANT'
1485 AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE') AS db ON s.session_id = db.request_session_id
1486 WHERE s.status = 'sleeping'
1487 AND s.last_request_end_time < DATEADD(ss, -10, SYSDATETIME())
1488 AND EXISTS(SELECT * FROM sys.dm_tran_locks WHERE request_session_id = s.session_id
1489 AND NOT (resource_type = N'DATABASE' AND request_mode = N'S' AND request_status = N'GRANT' AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE'));
1490
1491
1492 /*Query Problems - Clients using implicit transactions */
1493 IF @Seconds > 0
1494 AND ( @@VERSION NOT LIKE 'Microsoft SQL Server 2005%'
1495 AND @@VERSION NOT LIKE 'Microsoft SQL Server 2008%'
1496 AND @@VERSION NOT LIKE 'Microsoft SQL Server 2008 R2%' )
1497 BEGIN
1498 SET @StringToExecute = N'INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, QueryText, OpenTransactionCount)
1499 SELECT 37 AS CheckId,
1500 50 AS Priority,
1501 ''Implicit Transactions'' AS FindingsGroup,
1502 ''Queries were found running using implicit transactions'',
1503 ''https://www.brentozar.com/go/ImplicitTransactions/'' AS URL,
1504 ''Database: '' + DB_NAME(s.database_id) + '' '' +
1505 ''Host: '' + s.[host_name] + '' '' +
1506 ''Program: '' + s.[program_name] + '' '' +
1507 CONVERT(NVARCHAR(10), s.open_transaction_count) +
1508 '' open transactions since: '' +
1509 CONVERT(NVARCHAR(30), tat.transaction_begin_time) + ''. ''
1510 AS Details,
1511 ''Check client configuration options'' AS HowToStopit,
1512 tat.transaction_begin_time,
1513 s.login_name,
1514 s.nt_user_name,
1515 s.program_name,
1516 s.host_name,
1517 s.database_id,
1518 DB_NAME(s.database_id) AS DatabaseName,
1519 NULL AS Querytext,
1520 s.open_transaction_count AS OpenTransactionCount
1521 FROM sys.dm_tran_active_transactions AS tat
1522 LEFT JOIN sys.dm_tran_session_transactions AS tst
1523 ON tst.transaction_id = tat.transaction_id
1524 LEFT JOIN sys.dm_exec_sessions AS s
1525 ON s.session_id = tst.session_id
1526 WHERE tat.name = ''implicit_transaction'';
1527 '
1528 EXECUTE sp_executesql @StringToExecute;
1529 END;
1530
1531 /* Query Problems - Query Rolling Back - CheckID 9 */
1532 IF @Seconds > 0
1533 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, QueryText)
1534 SELECT 9 AS CheckID,
1535 1 AS Priority,
1536 'Query Problems' AS FindingGroup,
1537 'Query Rolling Back' AS Finding,
1538 'http://www.BrentOzar.com/askbrent/rollback/' AS URL,
1539 'Rollback started at ' + CAST(r.start_time AS NVARCHAR(100)) + ', is ' + CAST(r.percent_complete AS NVARCHAR(100)) + '% complete.' AS Details,
1540 'Unfortunately, you can''t stop this. Whatever you do, don''t restart the server in an attempt to fix it - SQL Server will keep rolling back.' AS HowToStopIt,
1541 r.start_time AS StartTime,
1542 s.login_name AS LoginName,
1543 s.nt_user_name AS NTUserName,
1544 s.[program_name] AS ProgramName,
1545 s.[host_name] AS HostName,
1546 db.[resource_database_id] AS DatabaseID,
1547 DB_NAME(db.resource_database_id) AS DatabaseName,
1548 (SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS QueryText
1549 FROM sys.dm_exec_sessions s
1550 INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
1551 INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
1552 LEFT OUTER JOIN (
1553 SELECT DISTINCT request_session_id, resource_database_id
1554 FROM sys.dm_tran_locks
1555 WHERE resource_type = N'DATABASE'
1556 AND request_mode = N'S'
1557 AND request_status = N'GRANT'
1558 AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE') AS db ON s.session_id = db.request_session_id
1559 WHERE r.status = 'rollback';
1560
1561
1562 /* Server Performance - Page Life Expectancy Low - CheckID 10 */
1563 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1564 SELECT 10 AS CheckID,
1565 50 AS Priority,
1566 'Server Performance' AS FindingGroup,
1567 'Page Life Expectancy Low' AS Finding,
1568 'http://www.BrentOzar.com/askbrent/page-life-expectancy/' AS URL,
1569 'SQL Server Buffer Manager:Page life expectancy is ' + CAST(c.cntr_value AS NVARCHAR(10)) + ' seconds.' + @LineFeed
1570 + 'This means SQL Server can only keep data pages in memory for that many seconds after reading those pages in from storage.' + @LineFeed
1571 + 'This is a symptom, not a cause - it indicates very read-intensive queries that need an index, or insufficient server memory.' AS Details,
1572 'Add more memory to the server, or find the queries reading a lot of data, and make them more efficient (or fix them with indexes).' AS HowToStopIt
1573 FROM sys.dm_os_performance_counters c
1574 WHERE object_name LIKE 'SQLServer:Buffer Manager%'
1575 AND counter_name LIKE 'Page life expectancy%'
1576 AND cntr_value < 300;
1577
1578 /* Server Performance - Too Much Free Memory - CheckID 34 */
1579 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1580 SELECT 34 AS CheckID,
1581 50 AS Priority,
1582 'Server Performance' AS FindingGroup,
1583 'Too Much Free Memory' AS Finding,
1584 'https://BrentOzar.com/go/freememory' AS URL,
1585 CAST((CAST(cFree.cntr_value AS BIGINT) / 1024 / 1024 ) AS NVARCHAR(100)) + N'GB of free memory inside SQL Server''s buffer pool,' + @LineFeed + ' which is ' + CAST((CAST(cTotal.cntr_value AS BIGINT) / 1024 / 1024) AS NVARCHAR(100)) + N'GB. You would think lots of free memory would be good, but check out the URL for more information.' AS Details,
1586 'Run sp_BlitzCache @SortOrder = ''memory grant'' to find queries with huge memory grants and tune them.' AS HowToStopIt
1587 FROM sys.dm_os_performance_counters cFree
1588 INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
1589 AND cTotal.counter_name = N'Total Server Memory (KB) '
1590 WHERE cFree.object_name LIKE N'%Memory Manager%'
1591 AND cFree.counter_name = N'Free Memory (KB) '
1592 AND CAST(cTotal.cntr_value AS BIGINT) > 20480000000
1593 AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
1594 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%';
1595
1596 /* Server Performance - Target Memory Lower Than Max - CheckID 35 */
1597 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1598 SELECT 35 AS CheckID,
1599 10 AS Priority,
1600 'Server Performance' AS FindingGroup,
1601 'Target Memory Lower Than Max' AS Finding,
1602 'https://BrentOzar.com/go/target' AS URL,
1603 N'Max server memory is ' + CAST(cMax.value_in_use AS NVARCHAR(50)) + N' MB but target server memory is only ' + CAST((CAST(cTarget.cntr_value AS BIGINT) / 1024) AS NVARCHAR(50)) + N' MB,' + @LineFeed
1604 + N'indicating that SQL Server may be under external memory pressure or max server memory may be set too high.' AS Details,
1605 'Investigate what OS processes are using memory, and double-check the max server memory setting.' AS HowToStopIt
1606 FROM sys.configurations cMax
1607 INNER JOIN sys.dm_os_performance_counters cTarget ON cTarget.object_name LIKE N'%Memory Manager%'
1608 AND cTarget.counter_name = N'Target Server Memory (KB) '
1609 WHERE cMax.name = 'max server memory (MB)'
1610 AND CAST(cMax.value_in_use AS BIGINT) >= 1.5 * (CAST(cTarget.cntr_value AS BIGINT) / 1024)
1611 AND CAST(cMax.value_in_use AS BIGINT) < 2147483647 /* Not set to default of unlimited */
1612 AND CAST(cTarget.cntr_value AS BIGINT) < .8 * (SELECT available_physical_memory_kb FROM sys.dm_os_sys_memory); /* Target memory less than 80% of physical memory (in case they set max too high) */
1613
1614 /* Server Info - Database Size, Total GB - CheckID 21 */
1615 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1616 SELECT 21 AS CheckID,
1617 251 AS Priority,
1618 'Server Info' AS FindingGroup,
1619 'Database Size, Total GB' AS Finding,
1620 CAST(SUM (CAST(size AS BIGINT)*8./1024./1024.) AS VARCHAR(100)) AS Details,
1621 SUM (CAST(size AS BIGINT))*8./1024./1024. AS DetailsInt,
1622 'http://www.BrentOzar.com/askbrent/' AS URL
1623 FROM #MasterFiles
1624 WHERE database_id > 4;
1625
1626 /* Server Info - Database Count - CheckID 22 */
1627 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1628 SELECT 22 AS CheckID,
1629 251 AS Priority,
1630 'Server Info' AS FindingGroup,
1631 'Database Count' AS Finding,
1632 CAST(SUM(1) AS VARCHAR(100)) AS Details,
1633 SUM (1) AS DetailsInt,
1634 'http://www.BrentOzar.com/askbrent/' AS URL
1635 FROM sys.databases
1636 WHERE database_id > 4;
1637
1638 /* Server Performance - High CPU Utilization CheckID 24 */
1639 IF @Seconds < 30
1640 BEGIN
1641 /* If we're waiting less than 30 seconds, run this check now rather than wait til the end.
1642 We get this data from the ring buffers, and it's only updated once per minute, so might
1643 as well get it now - whereas if we're checking 30+ seconds, it might get updated by the
1644 end of our sp_BlitzFirst session. */
1645 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1646 SELECT 24, 50, 'Server Performance', 'High CPU Utilization', CAST(100 - SystemIdle AS NVARCHAR(20)) + N'%.', 100 - SystemIdle, 'http://www.BrentOzar.com/go/cpu'
1647 FROM (
1648 SELECT record,
1649 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1650 FROM (
1651 SELECT TOP 1 CONVERT(XML, record) AS record
1652 FROM sys.dm_os_ring_buffers
1653 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1654 AND record LIKE '%<SystemHealth>%'
1655 ORDER BY timestamp DESC) AS rb
1656 ) AS y
1657 WHERE 100 - SystemIdle >= 50;
1658
1659 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1660 SELECT 23, 250, 'Server Info', 'CPU Utilization', CAST(100 - SystemIdle AS NVARCHAR(20)) + N'%. Ring buffer details: ' + CAST(record AS NVARCHAR(4000)), 100 - SystemIdle, 'http://www.BrentOzar.com/go/cpu'
1661 FROM (
1662 SELECT record,
1663 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1664 FROM (
1665 SELECT TOP 1 CONVERT(XML, record) AS record
1666 FROM sys.dm_os_ring_buffers
1667 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1668 AND record LIKE '%<SystemHealth>%'
1669 ORDER BY timestamp DESC) AS rb
1670 ) AS y;
1671
1672 /* Highlight if non SQL processes are using >25% CPU */
1673 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1674 SELECT 28, 50, 'Server Performance', 'High CPU Utilization - Not SQL', CONVERT(NVARCHAR(100),100 - (y.SQLUsage + y.SystemIdle)) + N'% - Other Processes (not SQL Server) are using this much CPU. This may impact on the performance of your SQL Server instance', 100 - (y.SQLUsage + y.SystemIdle), 'http://www.BrentOzar.com/go/cpu'
1675 FROM (
1676 SELECT record,
1677 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1678 ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLUsage
1679 FROM (
1680 SELECT TOP 1 CONVERT(XML, record) AS record
1681 FROM sys.dm_os_ring_buffers
1682 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1683 AND record LIKE '%<SystemHealth>%'
1684 ORDER BY timestamp DESC) AS rb
1685 ) AS y
1686 WHERE 100 - (y.SQLUsage + y.SystemIdle) >= 25;
1687
1688 END; /* IF @Seconds < 30 */
1689
1690 RAISERROR('Finished running investigatory queries',10,1) WITH NOWAIT;
1691
1692
1693 /* End of checks. If we haven't waited @Seconds seconds, wait. */
1694 IF DATEADD(SECOND,1,SYSDATETIMEOFFSET()) < @FinishSampleTime
1695 BEGIN
1696 RAISERROR('Waiting to match @Seconds parameter',10,1) WITH NOWAIT;
1697 WAITFOR TIME @FinishSampleTimeWaitFor;
1698 END;
1699
1700 RAISERROR('Capturing second pass of wait stats, perfmon counters, file stats',10,1) WITH NOWAIT;
1701 /* Populate #FileStats, #PerfmonStats, #WaitStats with DMV data. In a second, we'll compare these. */
1702 INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
1703 SELECT
1704 x.Pass,
1705 x.SampleTime,
1706 x.wait_type,
1707 SUM(x.sum_wait_time_ms) AS sum_wait_time_ms,
1708 SUM(x.sum_signal_wait_time_ms) AS sum_signal_wait_time_ms,
1709 SUM(x.sum_waiting_tasks) AS sum_waiting_tasks
1710 FROM (
1711 SELECT
1712 2 AS Pass,
1713 SYSDATETIMEOFFSET() AS SampleTime,
1714 owt.wait_type,
1715 SUM(owt.wait_duration_ms) OVER (PARTITION BY owt.wait_type, owt.session_id)
1716 - CASE WHEN @Seconds = 0 THEN 0 ELSE (@Seconds * 1000) END AS sum_wait_time_ms,
1717 0 AS sum_signal_wait_time_ms,
1718 CASE @Seconds WHEN 0 THEN 0 ELSE 1 END AS sum_waiting_tasks
1719 FROM sys.dm_os_waiting_tasks owt
1720 WHERE owt.session_id > 50
1721 AND owt.wait_duration_ms >= CASE @Seconds WHEN 0 THEN 0 ELSE @Seconds * 1000 END
1722 UNION ALL
1723 SELECT
1724 2 AS Pass,
1725 SYSDATETIMEOFFSET() AS SampleTime,
1726 os.wait_type,
1727 SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) AS sum_wait_time_ms,
1728 SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) AS sum_signal_wait_time_ms,
1729 SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
1730 FROM sys.dm_os_wait_stats os
1731 ) x
1732 WHERE x.wait_type NOT IN (
1733 'BROKER_EVENTHANDLER'
1734 , 'BROKER_RECEIVE_WAITFOR'
1735 , 'BROKER_TASK_STOP'
1736 , 'BROKER_TO_FLUSH'
1737 , 'BROKER_TRANSMITTER'
1738 , 'CHECKPOINT_QUEUE'
1739 , 'DBMIRROR_DBM_EVENT'
1740 , 'DBMIRROR_DBM_MUTEX'
1741 , 'DBMIRROR_EVENTS_QUEUE'
1742 , 'DBMIRROR_WORKER_QUEUE'
1743 , 'DBMIRRORING_CMD'
1744 , 'DIRTY_PAGE_POLL'
1745 , 'DISPATCHER_QUEUE_SEMAPHORE'
1746 , 'FT_IFTS_SCHEDULER_IDLE_WAIT'
1747 , 'FT_IFTSHC_MUTEX'
1748 , 'HADR_CLUSAPI_CALL'
1749 , 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
1750 , 'HADR_LOGCAPTURE_WAIT'
1751 , 'HADR_NOTIFICATION_DEQUEUE'
1752 , 'HADR_TIMER_TASK'
1753 , 'HADR_WORK_QUEUE'
1754 , 'LAZYWRITER_SLEEP'
1755 , 'LOGMGR_QUEUE'
1756 , 'ONDEMAND_TASK_QUEUE'
1757 , 'PREEMPTIVE_HADR_LEASE_MECHANISM'
1758 , 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS'
1759 , 'QDS_ASYNC_QUEUE'
1760 , 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
1761 , 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
1762 , 'QDS_SHUTDOWN_QUEUE'
1763 , 'REDO_THREAD_PENDING_WORK'
1764 , 'REQUEST_FOR_DEADLOCK_SEARCH'
1765 , 'SLEEP_SYSTEMTASK'
1766 , 'SLEEP_TASK'
1767 , 'SP_SERVER_DIAGNOSTICS_SLEEP'
1768 , 'SQLTRACE_BUFFER_FLUSH'
1769 , 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
1770 , 'UCS_SESSION_REGISTRATION'
1771 , 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
1772 , 'WAITFOR'
1773 , 'XE_DISPATCHER_WAIT'
1774 , 'XE_LIVE_TARGET_TVF'
1775 , 'XE_TIMER_EVENT'
1776 )
1777 GROUP BY x.Pass, x.SampleTime, x.wait_type
1778 ORDER BY sum_wait_time_ms DESC;
1779
1780 INSERT INTO #FileStats (Pass, SampleTime, DatabaseID, FileID, DatabaseName, FileLogicalName, SizeOnDiskMB, io_stall_read_ms ,
1781 num_of_reads, [bytes_read] , io_stall_write_ms,num_of_writes, [bytes_written], PhysicalName, TypeDesc, avg_stall_read_ms, avg_stall_write_ms)
1782 SELECT 2 AS Pass,
1783 SYSDATETIMEOFFSET() AS SampleTime,
1784 mf.[database_id],
1785 mf.[file_id],
1786 DB_NAME(vfs.database_id) AS [db_name],
1787 mf.name + N' [' + mf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS file_logical_name ,
1788 CAST(( ( vfs.size_on_disk_bytes / 1024.0 ) / 1024.0 ) AS INT) AS size_on_disk_mb ,
1789 vfs.io_stall_read_ms ,
1790 vfs.num_of_reads ,
1791 vfs.[num_of_bytes_read],
1792 vfs.io_stall_write_ms ,
1793 vfs.num_of_writes ,
1794 vfs.[num_of_bytes_written],
1795 mf.physical_name,
1796 mf.type_desc,
1797 0,
1798 0
1799 FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS vfs
1800 INNER JOIN #MasterFiles AS mf ON vfs.file_id = mf.file_id
1801 AND vfs.database_id = mf.database_id
1802 WHERE vfs.num_of_reads > 0
1803 OR vfs.num_of_writes > 0;
1804
1805 INSERT INTO #PerfmonStats (Pass, SampleTime, [object_name],[counter_name],[instance_name],[cntr_value],[cntr_type])
1806 SELECT 2 AS Pass,
1807 SYSDATETIMEOFFSET() AS SampleTime,
1808 RTRIM(dmv.object_name), RTRIM(dmv.counter_name), RTRIM(dmv.instance_name), dmv.cntr_value, dmv.cntr_type
1809 FROM #PerfmonCounters counters
1810 INNER JOIN sys.dm_os_performance_counters dmv ON counters.counter_name COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.counter_name) COLLATE SQL_Latin1_General_CP1_CI_AS
1811 AND counters.[object_name] COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.[object_name]) COLLATE SQL_Latin1_General_CP1_CI_AS
1812 AND (counters.[instance_name] IS NULL OR counters.[instance_name] COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.[instance_name]) COLLATE SQL_Latin1_General_CP1_CI_AS);
1813
1814 /* Set the latencies and averages. We could do this with a CTE, but we're not ambitious today. */
1815 UPDATE fNow
1816 SET avg_stall_read_ms = ((fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads))
1817 FROM #FileStats fNow
1818 INNER JOIN #FileStats fBase ON fNow.DatabaseID = fBase.DatabaseID AND fNow.FileID = fBase.FileID AND fNow.SampleTime > fBase.SampleTime AND fNow.num_of_reads > fBase.num_of_reads AND fNow.io_stall_read_ms > fBase.io_stall_read_ms
1819 WHERE (fNow.num_of_reads - fBase.num_of_reads) > 0;
1820
1821 UPDATE fNow
1822 SET avg_stall_write_ms = ((fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes))
1823 FROM #FileStats fNow
1824 INNER JOIN #FileStats fBase ON fNow.DatabaseID = fBase.DatabaseID AND fNow.FileID = fBase.FileID AND fNow.SampleTime > fBase.SampleTime AND fNow.num_of_writes > fBase.num_of_writes AND fNow.io_stall_write_ms > fBase.io_stall_write_ms
1825 WHERE (fNow.num_of_writes - fBase.num_of_writes) > 0;
1826
1827 UPDATE pNow
1828 SET [value_delta] = pNow.cntr_value - pFirst.cntr_value,
1829 [value_per_second] = ((1.0 * pNow.cntr_value - pFirst.cntr_value) / DATEDIFF(ss, pFirst.SampleTime, pNow.SampleTime))
1830 FROM #PerfmonStats pNow
1831 INNER JOIN #PerfmonStats pFirst ON pFirst.[object_name] = pNow.[object_name] AND pFirst.counter_name = pNow.counter_name AND (pFirst.instance_name = pNow.instance_name OR (pFirst.instance_name IS NULL AND pNow.instance_name IS NULL))
1832 AND pNow.ID > pFirst.ID
1833 WHERE DATEDIFF(ss, pFirst.SampleTime, pNow.SampleTime) > 0;
1834
1835
1836 /* If we're within 10 seconds of our projected finish time, do the plan cache analysis. */
1837 IF DATEDIFF(ss, @FinishSampleTime, SYSDATETIME()) > 10 AND @CheckProcedureCache = 1
1838 BEGIN
1839
1840 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
1841 VALUES (18, 210, 'Query Stats', 'Plan Cache Analysis Skipped', 'http://www.BrentOzar.com/go/topqueries',
1842 'Due to excessive load, the plan cache analysis was skipped. To override this, use @ExpertMode = 1.');
1843
1844 END;
1845 ELSE IF @CheckProcedureCache = 1
1846 BEGIN
1847
1848
1849 RAISERROR('@CheckProcedureCache = 1, capturing second pass of plan cache',10,1) WITH NOWAIT;
1850
1851 /* Populate #QueryStats. SQL 2005 doesn't have query hash or query plan hash. */
1852 IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
1853 BEGIN
1854 IF @FilterPlansByDatabase IS NULL
1855 BEGIN
1856 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1857 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
1858 FROM sys.dm_exec_query_stats qs
1859 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1860 END;
1861 ELSE
1862 BEGIN
1863 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1864 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
1865 FROM sys.dm_exec_query_stats qs
1866 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1867 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1868 WHERE qs.last_execution_time >= @StartSampleTimeText
1869 AND attr.attribute = ''dbid'';';
1870 END;
1871 END;
1872 ELSE
1873 BEGIN
1874 IF @FilterPlansByDatabase IS NULL
1875 BEGIN
1876 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1877 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
1878 FROM sys.dm_exec_query_stats qs
1879 WHERE qs.last_execution_time >= @StartSampleTimeText';
1880 END;
1881 ELSE
1882 BEGIN
1883 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1884 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
1885 FROM sys.dm_exec_query_stats qs
1886 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1887 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1888 WHERE qs.last_execution_time >= @StartSampleTimeText
1889 AND attr.attribute = ''dbid'';';
1890 END;
1891 END;
1892 /* Old version pre-2016/06/13:
1893 IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
1894 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1895 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
1896 FROM sys.dm_exec_query_stats qs
1897 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1898 ELSE
1899 SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
1900 SELECT [sql_handle], 2 AS Pass, SYSDATETIMEOFFSET(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
1901 FROM sys.dm_exec_query_stats qs
1902 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1903 */
1904 SET @ParmDefinitions = N'@StartSampleTimeText NVARCHAR(100)';
1905 SET @Parm1 = CONVERT(NVARCHAR(100), CAST(@StartSampleTime AS DATETIME), 127);
1906
1907 EXECUTE sp_executesql @StringToExecute, @ParmDefinitions, @StartSampleTimeText = @Parm1;
1908
1909 RAISERROR('@CheckProcedureCache = 1, totaling up plan cache metrics',10,1) WITH NOWAIT;
1910
1911 /* Get the totals for the entire plan cache */
1912 INSERT INTO #QueryStats (Pass, SampleTime, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time)
1913 SELECT 0 AS Pass, SYSDATETIMEOFFSET(), SUM(execution_count), SUM(total_worker_time), SUM(total_physical_reads), SUM(total_logical_writes), SUM(total_logical_reads), SUM(total_clr_time), SUM(total_elapsed_time), MIN(creation_time)
1914 FROM sys.dm_exec_query_stats qs;
1915
1916
1917 RAISERROR('@CheckProcedureCache = 1, so analyzing execution plans',10,1) WITH NOWAIT;
1918 /*
1919 Pick the most resource-intensive queries to review. Update the Points field
1920 in #QueryStats - if a query is in the top 10 for logical reads, CPU time,
1921 duration, or execution, add 1 to its points.
1922 */
1923 WITH qsTop AS (
1924 SELECT TOP 10 qsNow.ID
1925 FROM #QueryStats qsNow
1926 INNER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
1927 WHERE qsNow.total_elapsed_time > qsFirst.total_elapsed_time
1928 AND qsNow.Pass = 2
1929 AND qsNow.total_elapsed_time - qsFirst.total_elapsed_time > 1000000 /* Only queries with over 1 second of runtime */
1930 ORDER BY (qsNow.total_elapsed_time - COALESCE(qsFirst.total_elapsed_time, 0)) DESC)
1931 UPDATE #QueryStats
1932 SET Points = Points + 1
1933 FROM #QueryStats qs
1934 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1935
1936 WITH qsTop AS (
1937 SELECT TOP 10 qsNow.ID
1938 FROM #QueryStats qsNow
1939 INNER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
1940 WHERE qsNow.total_logical_reads > qsFirst.total_logical_reads
1941 AND qsNow.Pass = 2
1942 AND qsNow.total_logical_reads - qsFirst.total_logical_reads > 1000 /* Only queries with over 1000 reads */
1943 ORDER BY (qsNow.total_logical_reads - COALESCE(qsFirst.total_logical_reads, 0)) DESC)
1944 UPDATE #QueryStats
1945 SET Points = Points + 1
1946 FROM #QueryStats qs
1947 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1948
1949 WITH qsTop AS (
1950 SELECT TOP 10 qsNow.ID
1951 FROM #QueryStats qsNow
1952 INNER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
1953 WHERE qsNow.total_worker_time > qsFirst.total_worker_time
1954 AND qsNow.Pass = 2
1955 AND qsNow.total_worker_time - qsFirst.total_worker_time > 1000000 /* Only queries with over 1 second of worker time */
1956 ORDER BY (qsNow.total_worker_time - COALESCE(qsFirst.total_worker_time, 0)) DESC)
1957 UPDATE #QueryStats
1958 SET Points = Points + 1
1959 FROM #QueryStats qs
1960 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1961
1962 WITH qsTop AS (
1963 SELECT TOP 10 qsNow.ID
1964 FROM #QueryStats qsNow
1965 INNER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
1966 WHERE qsNow.execution_count > qsFirst.execution_count
1967 AND qsNow.Pass = 2
1968 AND (qsNow.total_elapsed_time - qsFirst.total_elapsed_time > 1000000 /* Only queries with over 1 second of runtime */
1969 OR qsNow.total_logical_reads - qsFirst.total_logical_reads > 1000 /* Only queries with over 1000 reads */
1970 OR qsNow.total_worker_time - qsFirst.total_worker_time > 1000000 /* Only queries with over 1 second of worker time */)
1971 ORDER BY (qsNow.execution_count - COALESCE(qsFirst.execution_count, 0)) DESC)
1972 UPDATE #QueryStats
1973 SET Points = Points + 1
1974 FROM #QueryStats qs
1975 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1976
1977 /* Query Stats - CheckID 17 - Most Resource-Intensive Queries */
1978 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, QueryStatsNowID, QueryStatsFirstID, PlanHandle)
1979 SELECT 17, 210, 'Query Stats', 'Most Resource-Intensive Queries', 'http://www.BrentOzar.com/go/topqueries',
1980 'Query stats during the sample:' + @LineFeed +
1981 'Executions: ' + CAST(qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)) AS NVARCHAR(100)) + @LineFeed +
1982 'Elapsed Time: ' + CAST(qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1983 'CPU Time: ' + CAST(qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1984 'Logical Reads: ' + CAST(qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)) AS NVARCHAR(100)) + @LineFeed +
1985 'Logical Writes: ' + CAST(qsNow.total_logical_writes - (COALESCE(qsFirst.total_logical_writes, 0)) AS NVARCHAR(100)) + @LineFeed +
1986 'CLR Time: ' + CAST(qsNow.total_clr_time - (COALESCE(qsFirst.total_clr_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1987 @LineFeed + @LineFeed + 'Query stats since ' + CONVERT(NVARCHAR(100), qsNow.creation_time ,121) + @LineFeed +
1988 'Executions: ' + CAST(qsNow.execution_count AS NVARCHAR(100)) +
1989 CASE qsTotal.execution_count WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.execution_count / qsTotal.execution_count AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
1990 'Elapsed Time: ' + CAST(qsNow.total_elapsed_time AS NVARCHAR(100)) +
1991 CASE qsTotal.total_elapsed_time WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.total_elapsed_time / qsTotal.total_elapsed_time AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
1992 'CPU Time: ' + CAST(qsNow.total_worker_time AS NVARCHAR(100)) +
1993 CASE qsTotal.total_worker_time WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.total_worker_time / qsTotal.total_worker_time AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
1994 'Logical Reads: ' + CAST(qsNow.total_logical_reads AS NVARCHAR(100)) +
1995 CASE qsTotal.total_logical_reads WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.total_logical_reads / qsTotal.total_logical_reads AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
1996 'Logical Writes: ' + CAST(qsNow.total_logical_writes AS NVARCHAR(100)) +
1997 CASE qsTotal.total_logical_writes WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.total_logical_writes / qsTotal.total_logical_writes AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
1998 'CLR Time: ' + CAST(qsNow.total_clr_time AS NVARCHAR(100)) +
1999 CASE qsTotal.total_clr_time WHEN 0 THEN '' ELSE (' - Percent of Server Total: ' + CAST(CAST(100.0 * qsNow.total_clr_time / qsTotal.total_clr_time AS DECIMAL(6,2)) AS NVARCHAR(100)) + '%') END + @LineFeed +
2000 --@LineFeed + @LineFeed + 'Query hash: ' + CAST(qsNow.query_hash AS NVARCHAR(100)) + @LineFeed +
2001 --@LineFeed + @LineFeed + 'Query plan hash: ' + CAST(qsNow.query_plan_hash AS NVARCHAR(100)) +
2002 @LineFeed AS Details,
2003 'See the URL for tuning tips on why this query may be consuming resources.' AS HowToStopIt,
2004 qp.query_plan,
2005 QueryText = SUBSTRING(st.text,
2006 (qsNow.statement_start_offset / 2) + 1,
2007 ((CASE qsNow.statement_end_offset
2008 WHEN -1 THEN DATALENGTH(st.text)
2009 ELSE qsNow.statement_end_offset
2010 END - qsNow.statement_start_offset) / 2) + 1),
2011 qsNow.ID AS QueryStatsNowID,
2012 qsFirst.ID AS QueryStatsFirstID,
2013 qsNow.plan_handle AS PlanHandle
2014 FROM #QueryStats qsNow
2015 INNER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
2016 LEFT OUTER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
2017 CROSS APPLY sys.dm_exec_sql_text(qsNow.sql_handle) AS st
2018 CROSS APPLY sys.dm_exec_query_plan(qsNow.plan_handle) AS qp
2019 WHERE qsNow.Points > 0 AND st.text IS NOT NULL AND qp.query_plan IS NOT NULL;
2020
2021 UPDATE #BlitzFirstResults
2022 SET DatabaseID = CAST(attr.value AS INT),
2023 DatabaseName = DB_NAME(CAST(attr.value AS INT))
2024 FROM #BlitzFirstResults
2025 CROSS APPLY sys.dm_exec_plan_attributes(#BlitzFirstResults.PlanHandle) AS attr
2026 WHERE attr.attribute = 'dbid';
2027
2028
2029 END; /* IF DATEDIFF(ss, @FinishSampleTime, SYSDATETIMEOFFSET()) > 10 AND @CheckProcedureCache = 1 */
2030
2031
2032 RAISERROR('Analyzing changes between first and second passes of DMVs',10,1) WITH NOWAIT;
2033
2034 /* Wait Stats - CheckID 6 */
2035 /* Compare the current wait stats to the sample we took at the start, and insert the top 10 waits. */
2036 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DetailsInt)
2037 SELECT TOP 10 6 AS CheckID,
2038 200 AS Priority,
2039 'Wait Stats' AS FindingGroup,
2040 wNow.wait_type AS Finding, /* IF YOU CHANGE THIS, STUFF WILL BREAK. Other checks look for wait type names in the Finding field. See checks 11, 12 as example. */
2041 N'https://www.sqlskills.com/help/waits/' + LOWER(wNow.wait_type) + '/' AS URL,
2042 'For ' + CAST(((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS NVARCHAR(100)) + ' seconds over the last ' + CASE @Seconds WHEN 0 THEN (CAST(DATEDIFF(dd,@StartSampleTime,@FinishSampleTime) AS NVARCHAR(10)) + ' days') ELSE (CAST(@Seconds AS NVARCHAR(10)) + ' seconds') END + ', SQL Server was waiting on this particular bottleneck.' + @LineFeed + @LineFeed AS Details,
2043 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2044 ((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS DetailsInt
2045 FROM #WaitStats wNow
2046 LEFT OUTER JOIN #WaitStats wBase ON wNow.wait_type = wBase.wait_type AND wNow.SampleTime > wBase.SampleTime
2047 WHERE wNow.wait_time_ms > (wBase.wait_time_ms + (.5 * (DATEDIFF(ss,@StartSampleTime,@FinishSampleTime)) * 1000)) /* Only look for things we've actually waited on for half of the time or more */
2048 ORDER BY (wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) DESC;
2049
2050 /* Server Performance - Poison Wait Detected - CheckID 30 */
2051 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DetailsInt)
2052 SELECT 30 AS CheckID,
2053 10 AS Priority,
2054 'Server Performance' AS FindingGroup,
2055 'Poison Wait Detected: ' + wNow.wait_type AS Finding,
2056 N'http://www.brentozar.com/go/poison/#' + wNow.wait_type AS URL,
2057 'For ' + CAST(((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS NVARCHAR(100)) + ' seconds over the last ' + CASE @Seconds WHEN 0 THEN (CAST(DATEDIFF(dd,@StartSampleTime,@FinishSampleTime) AS NVARCHAR(10)) + ' days') ELSE (CAST(@Seconds AS NVARCHAR(10)) + ' seconds') END + ', SQL Server was waiting on this particular bottleneck.' + @LineFeed + @LineFeed AS Details,
2058 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2059 ((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS DetailsInt
2060 FROM #WaitStats wNow
2061 LEFT OUTER JOIN #WaitStats wBase ON wNow.wait_type = wBase.wait_type AND wNow.SampleTime > wBase.SampleTime
2062 WHERE wNow.wait_type IN ('IO_QUEUE_LIMIT', 'IO_RETRY', 'LOG_RATE_GOVERNOR', 'PREEMPTIVE_DEBUG', 'RESMGR_THROTTLED', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE','SE_REPL_CATCHUP_THROTTLE','SE_REPL_COMMIT_ACK','SE_REPL_COMMIT_TURN','SE_REPL_ROLLBACK_ACK','SE_REPL_SLOW_SECONDARY_THROTTLE','THREADPOOL') AND wNow.wait_time_ms > wBase.wait_time_ms;
2063
2064
2065 /* Server Performance - Slow Data File Reads - CheckID 11 */
2066 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE Finding LIKE 'PAGEIOLATCH%')
2067 BEGIN
2068 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DatabaseID, DatabaseName)
2069 SELECT TOP 10 11 AS CheckID,
2070 50 AS Priority,
2071 'Server Performance' AS FindingGroup,
2072 'Slow Data File Reads' AS Finding,
2073 'http://www.BrentOzar.com/go/slow/' AS URL,
2074 'Your server is experiencing PAGEIOLATCH% waits due to slow data file reads. This file is one of the reasons why.' + @LineFeed
2075 + 'File: ' + fNow.PhysicalName + @LineFeed
2076 + 'Number of reads during the sample: ' + CAST((fNow.num_of_reads - fBase.num_of_reads) AS NVARCHAR(20)) + @LineFeed
2077 + 'Seconds spent waiting on storage for these reads: ' + CAST(((fNow.io_stall_read_ms - fBase.io_stall_read_ms) / 1000.0) AS NVARCHAR(20)) + @LineFeed
2078 + 'Average read latency during the sample: ' + CAST(((fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads) ) AS NVARCHAR(20)) + ' milliseconds' + @LineFeed
2079 + 'Microsoft guidance for data file read speed: 20ms or less.' + @LineFeed + @LineFeed AS Details,
2080 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2081 fNow.DatabaseID,
2082 fNow.DatabaseName
2083 FROM #FileStats fNow
2084 INNER JOIN #FileStats fBase ON fNow.DatabaseID = fBase.DatabaseID AND fNow.FileID = fBase.FileID AND fNow.SampleTime > fBase.SampleTime AND fNow.num_of_reads > fBase.num_of_reads AND fNow.io_stall_read_ms > (fBase.io_stall_read_ms + 1000)
2085 WHERE (fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads) >= @FileLatencyThresholdMS
2086 AND fNow.TypeDesc = 'ROWS'
2087 ORDER BY (fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads) DESC;
2088 END;
2089
2090 /* Server Performance - Slow Log File Writes - CheckID 12 */
2091 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE Finding LIKE 'WRITELOG%')
2092 BEGIN
2093 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DatabaseID, DatabaseName)
2094 SELECT TOP 10 12 AS CheckID,
2095 50 AS Priority,
2096 'Server Performance' AS FindingGroup,
2097 'Slow Log File Writes' AS Finding,
2098 'http://www.BrentOzar.com/go/slow/' AS URL,
2099 'Your server is experiencing WRITELOG waits due to slow log file writes. This file is one of the reasons why.' + @LineFeed
2100 + 'File: ' + fNow.PhysicalName + @LineFeed
2101 + 'Number of writes during the sample: ' + CAST((fNow.num_of_writes - fBase.num_of_writes) AS NVARCHAR(20)) + @LineFeed
2102 + 'Seconds spent waiting on storage for these writes: ' + CAST(((fNow.io_stall_write_ms - fBase.io_stall_write_ms) / 1000.0) AS NVARCHAR(20)) + @LineFeed
2103 + 'Average write latency during the sample: ' + CAST(((fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes) ) AS NVARCHAR(20)) + ' milliseconds' + @LineFeed
2104 + 'Microsoft guidance for log file write speed: 3ms or less.' + @LineFeed + @LineFeed AS Details,
2105 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2106 fNow.DatabaseID,
2107 fNow.DatabaseName
2108 FROM #FileStats fNow
2109 INNER JOIN #FileStats fBase ON fNow.DatabaseID = fBase.DatabaseID AND fNow.FileID = fBase.FileID AND fNow.SampleTime > fBase.SampleTime AND fNow.num_of_writes > fBase.num_of_writes AND fNow.io_stall_write_ms > (fBase.io_stall_write_ms + 1000)
2110 WHERE (fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes) >= @FileLatencyThresholdMS
2111 AND fNow.TypeDesc = 'LOG'
2112 ORDER BY (fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes) DESC;
2113 END;
2114
2115
2116 /* SQL Server Internal Maintenance - Log File Growing - CheckID 13 */
2117 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2118 SELECT 13 AS CheckID,
2119 1 AS Priority,
2120 'SQL Server Internal Maintenance' AS FindingGroup,
2121 'Log File Growing' AS Finding,
2122 'http://www.BrentOzar.com/askbrent/file-growing/' AS URL,
2123 'Number of growths during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2124 + 'Determined by sampling Perfmon counter ' + ps.object_name + ' - ' + ps.counter_name + @LineFeed AS Details,
2125 'Pre-grow data and log files during maintenance windows so that they do not grow during production loads. See the URL for more details.' AS HowToStopIt
2126 FROM #PerfmonStats ps
2127 WHERE ps.Pass = 2
2128 AND object_name = @ServiceName + ':Databases'
2129 AND counter_name = 'Log Growths'
2130 AND value_delta > 0;
2131
2132
2133 /* SQL Server Internal Maintenance - Log File Shrinking - CheckID 14 */
2134 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2135 SELECT 14 AS CheckID,
2136 1 AS Priority,
2137 'SQL Server Internal Maintenance' AS FindingGroup,
2138 'Log File Shrinking' AS Finding,
2139 'http://www.BrentOzar.com/askbrent/file-shrinking/' AS URL,
2140 'Number of shrinks during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2141 + 'Determined by sampling Perfmon counter ' + ps.object_name + ' - ' + ps.counter_name + @LineFeed AS Details,
2142 'Pre-grow data and log files during maintenance windows so that they do not grow during production loads. See the URL for more details.' AS HowToStopIt
2143 FROM #PerfmonStats ps
2144 WHERE ps.Pass = 2
2145 AND object_name = @ServiceName + ':Databases'
2146 AND counter_name = 'Log Shrinks'
2147 AND value_delta > 0;
2148
2149 /* Query Problems - Compilations/Sec High - CheckID 15 */
2150 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2151 SELECT 15 AS CheckID,
2152 50 AS Priority,
2153 'Query Problems' AS FindingGroup,
2154 'Compilations/Sec High' AS Finding,
2155 'http://www.BrentOzar.com/askbrent/compilations/' AS URL,
2156 'Number of batch requests during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2157 + 'Number of compilations during the sample: ' + CAST(psComp.value_delta AS NVARCHAR(20)) + @LineFeed
2158 + 'For OLTP environments, Microsoft recommends that 90% of batch requests should hit the plan cache, and not be compiled from scratch. We are exceeding that threshold.' + @LineFeed AS Details,
2159 'To find the queries that are compiling, start with:' + @LineFeed
2160 + 'sp_BlitzCache @SortOrder = ''recent compilations''' + @LineFeed
2161 + 'If dynamic SQL or non-parameterized strings are involved, consider enabling Forced Parameterization. See the URL for more details.' AS HowToStopIt
2162 FROM #PerfmonStats ps
2163 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name = @ServiceName + ':SQL Statistics' AND psComp.counter_name = 'SQL Compilations/sec' AND psComp.value_delta > 0
2164 WHERE ps.Pass = 2
2165 AND ps.object_name = @ServiceName + ':SQL Statistics'
2166 AND ps.counter_name = 'Batch Requests/sec'
2167 AND ps.value_delta > (1000 * @Seconds) /* Ignore servers sitting idle */
2168 AND (psComp.value_delta * 10) > ps.value_delta; /* Compilations are more than 10% of batch requests per second */
2169
2170 /* Query Problems - Re-Compilations/Sec High - CheckID 16 */
2171 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2172 SELECT 16 AS CheckID,
2173 50 AS Priority,
2174 'Query Problems' AS FindingGroup,
2175 'Re-Compilations/Sec High' AS Finding,
2176 'http://www.BrentOzar.com/askbrent/recompilations/' AS URL,
2177 'Number of batch requests during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2178 + 'Number of recompilations during the sample: ' + CAST(psComp.value_delta AS NVARCHAR(20)) + @LineFeed
2179 + 'More than 10% of our queries are being recompiled. This is typically due to statistics changing on objects.' + @LineFeed AS Details,
2180 'To find the queries that are being forced to recompile, start with:' + @LineFeed
2181 + 'sp_BlitzCache @SortOrder = ''recent compilations''' + @LineFeed
2182 + 'Examine those plans to find out which objects are changing so quickly that they hit the stats update threshold. See the URL for more details.' AS HowToStopIt
2183 FROM #PerfmonStats ps
2184 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name = @ServiceName + ':SQL Statistics' AND psComp.counter_name = 'SQL Re-Compilations/sec' AND psComp.value_delta > 0
2185 WHERE ps.Pass = 2
2186 AND ps.object_name = @ServiceName + ':SQL Statistics'
2187 AND ps.counter_name = 'Batch Requests/sec'
2188 AND ps.value_delta > (1000 * @Seconds) /* Ignore servers sitting idle */
2189 AND (psComp.value_delta * 10) > ps.value_delta; /* Recompilations are more than 10% of batch requests per second */
2190
2191 /* Table Problems - Forwarded Fetches/Sec High - CheckID 29 */
2192 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2193 SELECT 29 AS CheckID,
2194 40 AS Priority,
2195 'Table Problems' AS FindingGroup,
2196 'Forwarded Fetches/Sec High' AS Finding,
2197 'https://BrentOzar.com/go/fetch/' AS URL,
2198 CAST(ps.value_delta AS NVARCHAR(20)) + ' Forwarded Records (from SQLServer:Access Methods counter)' + @LineFeed
2199 + 'Check your heaps: they need to be rebuilt, or they need a clustered index applied.' + @LineFeed AS Details,
2200 'Rebuild your heaps. If you use Ola Hallengren maintenance scripts, those do not rebuild heaps by default: https://www.brentozar.com/archive/2016/07/fix-forwarded-records/' AS HowToStopIt
2201 FROM #PerfmonStats ps
2202 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name = @ServiceName + ':Access Methods' AND psComp.counter_name = 'Forwarded Records/sec' AND psComp.value_delta > 100
2203 WHERE ps.Pass = 2
2204 AND ps.object_name = @ServiceName + ':Access Methods'
2205 AND ps.counter_name = 'Forwarded Records/sec'
2206 AND ps.value_delta > (100 * @Seconds); /* Ignore servers sitting idle */
2207
2208
2209 /* In-Memory OLTP - Garbage Collection in Progress - CheckID 31 */
2210 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2211 SELECT 31 AS CheckID,
2212 50 AS Priority,
2213 'In-Memory OLTP' AS FindingGroup,
2214 'Garbage Collection in Progress' AS Finding,
2215 'https://BrentOzar.com/go/garbage/' AS URL,
2216 CAST(ps.value_delta AS NVARCHAR(50)) + ' rows processed (from SQL Server YYYY XTP Garbage Collection:Rows processed/sec counter)' + @LineFeed
2217 + 'This can happen due to memory pressure (causing In-Memory OLTP to shrink its footprint) or' + @LineFeed
2218 + 'due to transactional workloads that constantly insert/delete data.' AS Details,
2219 'Sadly, you cannot choose when garbage collection occurs. This is one of the many gotchas of Hekaton. Learn more: http://nedotter.com/archive/2016/04/row-version-lifecycle-for-in-memory-oltp/' AS HowToStopIt
2220 FROM #PerfmonStats ps
2221 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name LIKE '%XTP Garbage Collection' AND psComp.counter_name = 'Rows processed/sec' AND psComp.value_delta > 100
2222 WHERE ps.Pass = 2
2223 AND ps.object_name LIKE '%XTP Garbage Collection'
2224 AND ps.counter_name = 'Rows processed/sec'
2225 AND ps.value_delta > (100 * @Seconds); /* Ignore servers sitting idle */
2226
2227 /* In-Memory OLTP - Transactions Aborted - CheckID 32 */
2228 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2229 SELECT 32 AS CheckID,
2230 100 AS Priority,
2231 'In-Memory OLTP' AS FindingGroup,
2232 'Transactions Aborted' AS Finding,
2233 'https://BrentOzar.com/go/aborted/' AS URL,
2234 CAST(ps.value_delta AS NVARCHAR(50)) + ' transactions aborted (from SQL Server YYYY XTP Transactions:Transactions aborted/sec counter)' + @LineFeed
2235 + 'This may indicate that data is changing, or causing folks to retry their transactions, thereby increasing load.' AS Details,
2236 'Dig into your In-Memory OLTP transactions to figure out which ones are failing and being retried.' AS HowToStopIt
2237 FROM #PerfmonStats ps
2238 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name LIKE '%XTP Transactions' AND psComp.counter_name = 'Transactions aborted/sec' AND psComp.value_delta > 100
2239 WHERE ps.Pass = 2
2240 AND ps.object_name LIKE '%XTP Transactions'
2241 AND ps.counter_name = 'Transactions aborted/sec'
2242 AND ps.value_delta > (10 * @Seconds); /* Ignore servers sitting idle */
2243
2244 /* Query Problems - Suboptimal Plans/Sec High - CheckID 33 */
2245 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2246 SELECT 32 AS CheckID,
2247 100 AS Priority,
2248 'Query Problems' AS FindingGroup,
2249 'Suboptimal Plans/Sec High' AS Finding,
2250 'https://BrentOzar.com/go/suboptimal/' AS URL,
2251 CAST(ps.value_delta AS NVARCHAR(50)) + ' plans reported in the ' + CAST(ps.instance_name AS NVARCHAR(100)) + ' workload group (from Workload GroupStats:Suboptimal plans/sec counter)' + @LineFeed
2252 + 'Even if you are not using Resource Governor, it still tracks information about user queries, memory grants, etc.' AS Details,
2253 'Check out sp_BlitzCache to get more information about recent queries, or try sp_BlitzWho to see currently running queries.' AS HowToStopIt
2254 FROM #PerfmonStats ps
2255 INNER JOIN #PerfmonStats psComp ON psComp.Pass = 2 AND psComp.object_name = @ServiceName + ':Workload GroupStats' AND psComp.counter_name = 'Suboptimal plans/sec' AND psComp.value_delta > 100
2256 WHERE ps.Pass = 2
2257 AND ps.object_name = @ServiceName + ':Workload GroupStats'
2258 AND ps.counter_name = 'Suboptimal plans/sec'
2259 AND ps.value_delta > (10 * @Seconds); /* Ignore servers sitting idle */
2260
2261
2262
2263 /* Server Info - Batch Requests per Sec - CheckID 19 */
2264 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2265 SELECT 19 AS CheckID,
2266 250 AS Priority,
2267 'Server Info' AS FindingGroup,
2268 'Batch Requests per Sec' AS Finding,
2269 'http://www.BrentOzar.com/go/measure' AS URL,
2270 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2271 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2272 FROM #PerfmonStats ps
2273 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2274 WHERE ps.Pass = 2
2275 AND ps.object_name = @ServiceName + ':SQL Statistics'
2276 AND ps.counter_name = 'Batch Requests/sec';
2277
2278
2279 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Compilations/sec', NULL);
2280 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Re-Compilations/sec', NULL);
2281
2282 /* Server Info - SQL Compilations/sec - CheckID 25 */
2283 IF @ExpertMode = 1
2284 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2285 SELECT 25 AS CheckID,
2286 250 AS Priority,
2287 'Server Info' AS FindingGroup,
2288 'SQL Compilations per Sec' AS Finding,
2289 'http://www.BrentOzar.com/go/measure' AS URL,
2290 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2291 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2292 FROM #PerfmonStats ps
2293 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2294 WHERE ps.Pass = 2
2295 AND ps.object_name = @ServiceName + ':SQL Statistics'
2296 AND ps.counter_name = 'SQL Compilations/sec';
2297
2298 /* Server Info - SQL Re-Compilations/sec - CheckID 26 */
2299 IF @ExpertMode = 1
2300 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2301 SELECT 26 AS CheckID,
2302 250 AS Priority,
2303 'Server Info' AS FindingGroup,
2304 'SQL Re-Compilations per Sec' AS Finding,
2305 'http://www.BrentOzar.com/go/measure' AS URL,
2306 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2307 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2308 FROM #PerfmonStats ps
2309 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2310 WHERE ps.Pass = 2
2311 AND ps.object_name = @ServiceName + ':SQL Statistics'
2312 AND ps.counter_name = 'SQL Re-Compilations/sec';
2313
2314 /* Server Info - Wait Time per Core per Sec - CheckID 20 */
2315 IF @Seconds > 0
2316 BEGIN;
2317 WITH waits1(SampleTime, waits_ms) AS (SELECT SampleTime, SUM(ws1.wait_time_ms) FROM #WaitStats ws1 WHERE ws1.Pass = 1 GROUP BY SampleTime),
2318 waits2(SampleTime, waits_ms) AS (SELECT SampleTime, SUM(ws2.wait_time_ms) FROM #WaitStats ws2 WHERE ws2.Pass = 2 GROUP BY SampleTime),
2319 cores(cpu_count) AS (SELECT SUM(1) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1)
2320 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2321 SELECT 19 AS CheckID,
2322 250 AS Priority,
2323 'Server Info' AS FindingGroup,
2324 'Wait Time per Core per Sec' AS Finding,
2325 'http://www.BrentOzar.com/go/measure' AS URL,
2326 CAST((waits2.waits_ms - waits1.waits_ms) / 1000 / i.cpu_count / DATEDIFF(ss, waits1.SampleTime, waits2.SampleTime) AS NVARCHAR(20)) AS Details,
2327 (waits2.waits_ms - waits1.waits_ms) / 1000 / i.cpu_count / DATEDIFF(ss, waits1.SampleTime, waits2.SampleTime) AS DetailsInt
2328 FROM cores i
2329 CROSS JOIN waits1
2330 CROSS JOIN waits2;
2331 END;
2332
2333 /* Server Performance - High CPU Utilization CheckID 24 */
2334 IF @Seconds >= 30
2335 BEGIN
2336 /* If we're waiting 30+ seconds, run this check at the end.
2337 We get this data from the ring buffers, and it's only updated once per minute, so might
2338 as well get it now - whereas if we're checking 30+ seconds, it might get updated by the
2339 end of our sp_BlitzFirst session. */
2340 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
2341 SELECT 24, 50, 'Server Performance', 'High CPU Utilization', CAST(100 - SystemIdle AS NVARCHAR(20)) + N'%. Ring buffer details: ' + CAST(record AS NVARCHAR(4000)), 100 - SystemIdle, 'http://www.BrentOzar.com/go/cpu'
2342 FROM (
2343 SELECT record,
2344 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
2345 FROM (
2346 SELECT TOP 1 CONVERT(XML, record) AS record
2347 FROM sys.dm_os_ring_buffers
2348 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
2349 AND record LIKE '%<SystemHealth>%'
2350 ORDER BY timestamp DESC) AS rb
2351 ) AS y
2352 WHERE 100 - SystemIdle >= 50;
2353
2354 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
2355 SELECT 23, 250, 'Server Info', 'CPU Utilization', CAST(100 - SystemIdle AS NVARCHAR(20)) + N'%. Ring buffer details: ' + CAST(record AS NVARCHAR(4000)), 100 - SystemIdle, 'http://www.BrentOzar.com/go/cpu'
2356 FROM (
2357 SELECT record,
2358 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
2359 FROM (
2360 SELECT TOP 1 CONVERT(XML, record) AS record
2361 FROM sys.dm_os_ring_buffers
2362 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
2363 AND record LIKE '%<SystemHealth>%'
2364 ORDER BY timestamp DESC) AS rb
2365 ) AS y;
2366
2367 END; /* IF @Seconds < 30 */
2368
2369
2370 /* If we didn't find anything, apologize. */
2371 IF NOT EXISTS (SELECT * FROM #BlitzFirstResults WHERE Priority < 250)
2372 BEGIN
2373
2374 INSERT INTO #BlitzFirstResults
2375 ( CheckID ,
2376 Priority ,
2377 FindingsGroup ,
2378 Finding ,
2379 URL ,
2380 Details
2381 )
2382 VALUES ( -1 ,
2383 1 ,
2384 'No Problems Found' ,
2385 'From Your Community Volunteers' ,
2386 'http://FirstResponderKit.org/' ,
2387 'Try running our more in-depth checks with sp_Blitz, or there may not be an unusual SQL Server performance problem. '
2388 );
2389
2390 END; /*IF NOT EXISTS (SELECT * FROM #BlitzFirstResults) */
2391
2392 /* Add credits for the nice folks who put so much time into building and maintaining this for free: */
2393 INSERT INTO #BlitzFirstResults
2394 ( CheckID ,
2395 Priority ,
2396 FindingsGroup ,
2397 Finding ,
2398 URL ,
2399 Details
2400 )
2401 VALUES ( -1 ,
2402 255 ,
2403 'Thanks!' ,
2404 'From Your Community Volunteers' ,
2405 'http://FirstResponderKit.org/' ,
2406 'To get help or add your own contributions, join us at http://FirstResponderKit.org.'
2407 );
2408
2409 INSERT INTO #BlitzFirstResults
2410 ( CheckID ,
2411 Priority ,
2412 FindingsGroup ,
2413 Finding ,
2414 URL ,
2415 Details
2416
2417 )
2418 VALUES ( -1 ,
2419 0 ,
2420 'sp_BlitzFirst ' + CAST(CONVERT(DATETIMEOFFSET, @VersionDate, 102) AS VARCHAR(100)),
2421 'From Your Community Volunteers' ,
2422 'http://FirstResponderKit.org/' ,
2423 'We hope you found this tool useful.'
2424 );
2425
2426 /* Outdated sp_BlitzFirst - sp_BlitzFirst is Over 6 Months Old */
2427 IF DATEDIFF(MM, @VersionDate, SYSDATETIMEOFFSET()) > 6
2428 BEGIN
2429 INSERT INTO #BlitzFirstResults
2430 ( CheckID ,
2431 Priority ,
2432 FindingsGroup ,
2433 Finding ,
2434 URL ,
2435 Details
2436 )
2437 SELECT 27 AS CheckID ,
2438 0 AS Priority ,
2439 'Outdated sp_BlitzFirst' AS FindingsGroup ,
2440 'sp_BlitzFirst is Over 6 Months Old' AS Finding ,
2441 'http://FirstResponderKit.org/' AS URL ,
2442 'Some things get better with age, like fine wine and your T-SQL. However, sp_BlitzFirst is not one of those things - time to go download the current one.' AS Details;
2443 END;
2444
2445 RAISERROR('Analysis finished, outputting results',10,1) WITH NOWAIT;
2446
2447
2448 /* If they want to run sp_BlitzCache and export to table, go for it. */
2449 IF @OutputTableNameBlitzCache IS NOT NULL
2450 AND @OutputDatabaseName IS NOT NULL
2451 AND @OutputSchemaName IS NOT NULL
2452 AND EXISTS ( SELECT *
2453 FROM sys.databases
2454 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2455 BEGIN
2456 RAISERROR('Calling sp_BlitzCache',10,1) WITH NOWAIT;
2457
2458 /* Set the sp_BlitzCache sort order based on their top wait type */
2459
2460 /* First, check for poison waits - CheckID 30 */
2461 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 30)
2462 BEGIN
2463 SELECT TOP 1 @BlitzCacheSortOrder = CASE
2464 WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE' THEN 'memory grant'
2465 WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE' THEN 'memory grant'
2466 WHEN Finding = 'Poison Wait Detected: THREADPOOL' THEN 'executions'
2467 WHEN Finding = 'Poison Wait Detected: LOG_RATE_GOVERNOR' THEN 'writes'
2468 WHEN Finding = 'Poison Wait Detected: SE_REPL_CATCHUP_THROTTLE' THEN 'writes'
2469 WHEN Finding = 'Poison Wait Detected: SE_REPL_COMMIT_ACK' THEN 'writes'
2470 WHEN Finding = 'Poison Wait Detected: SE_REPL_ROLLBACK_ACK' THEN 'writes'
2471 WHEN Finding = 'Poison Wait Detected: SE_REPL_SLOW_SECONDARY_THROTTLE' THEN 'writes'
2472 ELSE NULL
2473 END
2474 FROM #BlitzFirstResults
2475 WHERE CheckID = 30
2476 ORDER BY DetailsInt DESC;
2477 END;
2478
2479 /* Too much free memory - which probably indicates queries finished w/huge grants - CheckID 34 */
2480 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 34)
2481 SET @BlitzCacheSortOrder = 'memory grant';
2482
2483 /* Next, Compilations/Sec High - CheckID 15 and 16 */
2484 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID IN (15,16))
2485 SET @BlitzCacheSortOrder = 'compilations';
2486
2487 /* Still not set? Use the top wait type. */
2488 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 6)
2489 BEGIN
2490 SELECT TOP 1 @BlitzCacheSortOrder = CASE
2491 WHEN Finding = 'ASYNC_NETWORK_IO' THEN 'duration'
2492 WHEN Finding = 'CXPACKET' THEN 'reads'
2493 WHEN Finding = 'LATCH_EX' THEN 'reads'
2494 WHEN Finding LIKE 'LCK%' THEN 'duration'
2495 WHEN Finding LIKE 'PAGEIOLATCH%' THEN 'reads'
2496 WHEN Finding = 'SOS_SCHEDULER_YIELD' THEN 'cpu'
2497 WHEN Finding = 'WRITELOG' THEN 'writes'
2498 ELSE NULL
2499 END
2500 FROM #BlitzFirstResults
2501 WHERE CheckID = 6
2502 ORDER BY DetailsInt DESC;
2503 END;
2504 /* Still null? Just use the default. */
2505
2506
2507
2508 /* If they have an newer version of sp_BlitzCache that supports @MinutesBack and @CheckDateOverride */
2509 IF EXISTS (SELECT * FROM sys.objects o
2510 INNER JOIN sys.parameters pMB ON o.object_id = pMB.object_id AND pMB.name = '@MinutesBack'
2511 INNER JOIN sys.parameters pCDO ON o.object_id = pCDO.object_id AND pCDO.name = '@CheckDateOverride'
2512 WHERE o.name = 'sp_BlitzCache')
2513 BEGIN
2514 /* Get the most recent sp_BlitzCache execution before this one - don't use sp_BlitzFirst because user logs are added in there at any time */
2515 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2516 + @OutputDatabaseName
2517 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2518 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2519 + QUOTENAME(@OutputTableNameBlitzCache) + ''') SELECT TOP 1 @BlitzCacheMinutesBack = DATEDIFF(MI,CheckDate,SYSDATETIMEOFFSET()) FROM '
2520 + @OutputDatabaseName + '.'
2521 + @OutputSchemaName + '.'
2522 + QUOTENAME(@OutputTableNameBlitzCache)
2523 + ' WHERE ServerName = ''' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) + ''' ORDER BY CheckDate DESC;';
2524 EXEC sp_executesql @StringToExecute, N'@BlitzCacheMinutesBack INT OUTPUT', @BlitzCacheMinutesBack OUTPUT;
2525
2526 /* If there's no data, let's just analyze the last 15 minutes of the plan cache */
2527 IF @BlitzCacheMinutesBack IS NULL OR @BlitzCacheMinutesBack < 1 OR @BlitzCacheMinutesBack > 60
2528 SET @BlitzCacheMinutesBack = 15;
2529
2530 IF @BlitzCacheSortOrder IS NOT NULL
2531 EXEC sp_BlitzCache
2532 @OutputDatabaseName = @UnquotedOutputDatabaseName,
2533 @OutputSchemaName = @UnquotedOutputSchemaName,
2534 @OutputTableName = @OutputTableNameBlitzCache,
2535 @CheckDateOverride = @StartSampleTime,
2536 @SortOrder = @BlitzCacheSortOrder,
2537 @MinutesBack = @BlitzCacheMinutesBack,
2538 @Debug = @Debug;
2539 ELSE
2540 EXEC sp_BlitzCache
2541 @OutputDatabaseName = @UnquotedOutputDatabaseName,
2542 @OutputSchemaName = @UnquotedOutputSchemaName,
2543 @OutputTableName = @OutputTableNameBlitzCache,
2544 @CheckDateOverride = @StartSampleTime,
2545 @MinutesBack = @BlitzCacheMinutesBack,
2546 @Debug = @Debug;
2547
2548 /* Delete history older than @OutputTableRetentionDays */
2549 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2550 + @OutputDatabaseName
2551 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2552 + @OutputSchemaName + ''') DELETE '
2553 + @OutputDatabaseName + '.'
2554 + @OutputSchemaName + '.'
2555 + QUOTENAME(@OutputTableNameBlitzCache)
2556 + ' WHERE ServerName = '''
2557 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2558 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2559 EXEC(@StringToExecute);
2560
2561
2562 END;
2563
2564 ELSE /* No sp_BlitzCache found, or it's outdated */
2565 BEGIN
2566 INSERT INTO #BlitzFirstResults
2567 ( CheckID ,
2568 Priority ,
2569 FindingsGroup ,
2570 Finding ,
2571 URL ,
2572 Details
2573 )
2574 SELECT 36 AS CheckID ,
2575 0 AS Priority ,
2576 'Outdated or Missing sp_BlitzCache' AS FindingsGroup ,
2577 'Update Your sp_BlitzCache' AS Finding ,
2578 'http://FirstResponderKit.org/' AS URL ,
2579 'You passed in @OutputTableNameBlitzCache, but we need a newer version of sp_BlitzCache in master or the current database.' AS Details;
2580 END;
2581
2582 RAISERROR('sp_BlitzCache Finished',10,1) WITH NOWAIT;
2583
2584 END; /* End running sp_BlitzCache */
2585
2586 /* @OutputTableName lets us export the results to a permanent table */
2587 IF @OutputDatabaseName IS NOT NULL
2588 AND @OutputSchemaName IS NOT NULL
2589 AND @OutputTableName IS NOT NULL
2590 AND @OutputTableName NOT LIKE '#%'
2591 AND EXISTS ( SELECT *
2592 FROM sys.databases
2593 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2594 BEGIN
2595 SET @StringToExecute = 'USE '
2596 + @OutputDatabaseName
2597 + '; IF EXISTS(SELECT * FROM '
2598 + @OutputDatabaseName
2599 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2600 + @OutputSchemaName
2601 + ''') AND NOT EXISTS (SELECT * FROM '
2602 + @OutputDatabaseName
2603 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2604 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2605 + @OutputTableName + ''') CREATE TABLE '
2606 + @OutputSchemaName + '.'
2607 + @OutputTableName
2608 + ' (ID INT IDENTITY(1,1) NOT NULL,
2609 ServerName NVARCHAR(128),
2610 CheckDate DATETIMEOFFSET,
2611 CheckID INT NOT NULL,
2612 Priority TINYINT NOT NULL,
2613 FindingsGroup VARCHAR(50) NOT NULL,
2614 Finding VARCHAR(200) NOT NULL,
2615 URL VARCHAR(200) NOT NULL,
2616 Details NVARCHAR(4000) NULL,
2617 HowToStopIt [XML] NULL,
2618 QueryPlan [XML] NULL,
2619 QueryText NVARCHAR(MAX) NULL,
2620 StartTime DATETIMEOFFSET NULL,
2621 LoginName NVARCHAR(128) NULL,
2622 NTUserName NVARCHAR(128) NULL,
2623 OriginalLoginName NVARCHAR(128) NULL,
2624 ProgramName NVARCHAR(128) NULL,
2625 HostName NVARCHAR(128) NULL,
2626 DatabaseID INT NULL,
2627 DatabaseName NVARCHAR(128) NULL,
2628 OpenTransactionCount INT NULL,
2629 DetailsInt INT NULL,
2630 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));';
2631
2632 EXEC(@StringToExecute);
2633 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2634 + @OutputDatabaseName
2635 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2636 + @OutputSchemaName + ''') INSERT '
2637 + @OutputDatabaseName + '.'
2638 + @OutputSchemaName + '.'
2639 + @OutputTableName
2640 + ' (ServerName, CheckDate, CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt) SELECT '''
2641 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2642 + ''', ''' + (CONVERT(NVARCHAR(100), @StartSampleTime, 121)) + ''', CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt FROM #BlitzFirstResults ORDER BY Priority , FindingsGroup , Finding , Details';
2643 EXEC(@StringToExecute);
2644
2645 /* Delete history older than @OutputTableRetentionDays */
2646 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2647 + @OutputDatabaseName
2648 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2649 + @OutputSchemaName + ''') DELETE '
2650 + @OutputDatabaseName + '.'
2651 + @OutputSchemaName + '.'
2652 + @OutputTableName
2653 + ' WHERE ServerName = '''
2654 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2655 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2656 EXEC(@StringToExecute);
2657
2658
2659 END;
2660 ELSE IF (SUBSTRING(@OutputTableName, 2, 2) = '##')
2661 BEGIN
2662 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2663 + @OutputTableName
2664 + ''') IS NULL) CREATE TABLE '
2665 + @OutputTableName
2666 + ' (ID INT IDENTITY(1,1) NOT NULL,
2667 ServerName NVARCHAR(128),
2668 CheckDate DATETIMEOFFSET,
2669 CheckID INT NOT NULL,
2670 Priority TINYINT NOT NULL,
2671 FindingsGroup VARCHAR(50) NOT NULL,
2672 Finding VARCHAR(200) NOT NULL,
2673 URL VARCHAR(200) NOT NULL,
2674 Details NVARCHAR(4000) NULL,
2675 HowToStopIt [XML] NULL,
2676 QueryPlan [XML] NULL,
2677 QueryText NVARCHAR(MAX) NULL,
2678 StartTime DATETIMEOFFSET NULL,
2679 LoginName NVARCHAR(128) NULL,
2680 NTUserName NVARCHAR(128) NULL,
2681 OriginalLoginName NVARCHAR(128) NULL,
2682 ProgramName NVARCHAR(128) NULL,
2683 HostName NVARCHAR(128) NULL,
2684 DatabaseID INT NULL,
2685 DatabaseName NVARCHAR(128) NULL,
2686 OpenTransactionCount INT NULL,
2687 DetailsInt INT NULL,
2688 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2689 + ' INSERT '
2690 + @OutputTableName
2691 + ' (ServerName, CheckDate, CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt) SELECT '''
2692 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2693 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt FROM #BlitzFirstResults ORDER BY Priority , FindingsGroup , Finding , Details';
2694 EXEC(@StringToExecute);
2695 END;
2696 ELSE IF (SUBSTRING(@OutputTableName, 2, 1) = '#')
2697 BEGIN
2698 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0);
2699 END;
2700
2701 /* @OutputTableNameFileStats lets us export the results to a permanent table */
2702 IF @OutputDatabaseName IS NOT NULL
2703 AND @OutputSchemaName IS NOT NULL
2704 AND @OutputTableNameFileStats IS NOT NULL
2705 AND @OutputTableNameFileStats NOT LIKE '#%'
2706 AND EXISTS ( SELECT *
2707 FROM sys.databases
2708 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2709 BEGIN
2710 /* Create the table */
2711 SET @StringToExecute = 'USE '
2712 + @OutputDatabaseName
2713 + '; IF EXISTS(SELECT * FROM '
2714 + @OutputDatabaseName
2715 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2716 + @OutputSchemaName
2717 + ''') AND NOT EXISTS (SELECT * FROM '
2718 + @OutputDatabaseName
2719 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2720 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2721 + @OutputTableNameFileStats + ''') CREATE TABLE '
2722 + @OutputSchemaName + '.'
2723 + @OutputTableNameFileStats
2724 + ' (ID INT IDENTITY(1,1) NOT NULL,
2725 ServerName NVARCHAR(128),
2726 CheckDate DATETIMEOFFSET,
2727 DatabaseID INT NOT NULL,
2728 FileID INT NOT NULL,
2729 DatabaseName NVARCHAR(256) ,
2730 FileLogicalName NVARCHAR(256) ,
2731 TypeDesc NVARCHAR(60) ,
2732 SizeOnDiskMB BIGINT ,
2733 io_stall_read_ms BIGINT ,
2734 num_of_reads BIGINT ,
2735 bytes_read BIGINT ,
2736 io_stall_write_ms BIGINT ,
2737 num_of_writes BIGINT ,
2738 bytes_written BIGINT,
2739 PhysicalName NVARCHAR(520) ,
2740 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));';
2741 EXEC(@StringToExecute);
2742
2743 /* Create the view */
2744 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameFileStats_View;
2745 IF OBJECT_ID(@ObjectFullName) IS NULL
2746 BEGIN
2747 SET @StringToExecute = 'USE '
2748 + @OutputDatabaseName
2749 + '; EXEC (''CREATE VIEW '
2750 + @OutputSchemaName + '.'
2751 + @OutputTableNameFileStats_View + ' AS ' + @LineFeed
2752 + 'SELECT f.ServerName, f.CheckDate, f.DatabaseID, f.DatabaseName, f.FileID, f.FileLogicalName, f.TypeDesc, f.PhysicalName, f.SizeOnDiskMB' + @LineFeed
2753 + ', DATEDIFF(ss, fPrior.CheckDate, f.CheckDate) AS ElapsedSeconds' + @LineFeed
2754 + ', (f.SizeOnDiskMB - fPrior.SizeOnDiskMB) AS SizeOnDiskMBgrowth' + @LineFeed
2755 + ', (f.io_stall_read_ms - fPrior.io_stall_read_ms) AS io_stall_read_ms' + @LineFeed
2756 + ', io_stall_read_ms_average = CASE WHEN (f.num_of_reads - fPrior.num_of_reads) = 0 THEN 0 ELSE (f.io_stall_read_ms - fPrior.io_stall_read_ms) / (f.num_of_reads - fPrior.num_of_reads) END' + @LineFeed
2757 + ', (f.num_of_reads - fPrior.num_of_reads) AS num_of_reads' + @LineFeed
2758 + ', (f.bytes_read - fPrior.bytes_read) / 1024.0 / 1024.0 AS megabytes_read' + @LineFeed
2759 + ', (f.io_stall_write_ms - fPrior.io_stall_write_ms) AS io_stall_write_ms' + @LineFeed
2760 + ', io_stall_write_ms_average = CASE WHEN (f.num_of_writes - fPrior.num_of_writes) = 0 THEN 0 ELSE (f.io_stall_write_ms - fPrior.io_stall_write_ms) / (f.num_of_writes - fPrior.num_of_writes) END' + @LineFeed
2761 + ', (f.num_of_writes - fPrior.num_of_writes) AS num_of_writes' + @LineFeed
2762 + ', (f.bytes_written - fPrior.bytes_written) / 1024.0 / 1024.0 AS megabytes_written' + @LineFeed
2763 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNameFileStats + ' f' + @LineFeed
2764 + 'INNER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameFileStats + ' fPrior ON f.ServerName = fPrior.ServerName AND f.DatabaseID = fPrior.DatabaseID AND f.FileID = fPrior.FileID AND f.CheckDate > fPrior.CheckDate' + @LineFeed
2765 + 'LEFT OUTER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameFileStats + ' fMiddle ON f.ServerName = fMiddle.ServerName AND f.DatabaseID = fMiddle.DatabaseID AND f.FileID = fMiddle.FileID AND f.CheckDate > fMiddle.CheckDate AND fMiddle.CheckDate > fPrior.CheckDate' + @LineFeed
2766 + 'WHERE fMiddle.ID IS NULL AND f.num_of_reads >= fPrior.num_of_reads AND f.num_of_writes >= fPrior.num_of_writes
2767 AND DATEDIFF(MI, fPrior.CheckDate, f.CheckDate) BETWEEN 1 AND 60;'')';
2768 EXEC(@StringToExecute);
2769 END;
2770
2771 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2772 + @OutputDatabaseName
2773 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2774 + @OutputSchemaName + ''') INSERT '
2775 + @OutputDatabaseName + '.'
2776 + @OutputSchemaName + '.'
2777 + @OutputTableNameFileStats
2778 + ' (ServerName, CheckDate, DatabaseID, FileID, DatabaseName, FileLogicalName, TypeDesc, SizeOnDiskMB, io_stall_read_ms, num_of_reads, bytes_read, io_stall_write_ms, num_of_writes, bytes_written, PhysicalName) SELECT '''
2779 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2780 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2781 + 'DatabaseID, FileID, DatabaseName, FileLogicalName, TypeDesc, SizeOnDiskMB, io_stall_read_ms, num_of_reads, bytes_read, io_stall_write_ms, num_of_writes, bytes_written, PhysicalName FROM #FileStats WHERE Pass = 2';
2782 EXEC(@StringToExecute);
2783
2784 /* Delete history older than @OutputTableRetentionDays */
2785 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2786 + @OutputDatabaseName
2787 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2788 + @OutputSchemaName + ''') DELETE '
2789 + @OutputDatabaseName + '.'
2790 + @OutputSchemaName + '.'
2791 + @OutputTableNameFileStats
2792 + ' WHERE ServerName = '''
2793 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2794 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2795 EXEC(@StringToExecute);
2796
2797 END;
2798 ELSE IF (SUBSTRING(@OutputTableNameFileStats, 2, 2) = '##')
2799 BEGIN
2800 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2801 + @OutputTableNameFileStats
2802 + ''') IS NULL) CREATE TABLE '
2803 + @OutputTableNameFileStats
2804 + ' (ID INT IDENTITY(1,1) NOT NULL,
2805 ServerName NVARCHAR(128),
2806 CheckDate DATETIMEOFFSET,
2807 DatabaseID INT NOT NULL,
2808 FileID INT NOT NULL,
2809 DatabaseName NVARCHAR(256) ,
2810 FileLogicalName NVARCHAR(256) ,
2811 TypeDesc NVARCHAR(60) ,
2812 SizeOnDiskMB BIGINT ,
2813 io_stall_read_ms BIGINT ,
2814 num_of_reads BIGINT ,
2815 bytes_read BIGINT ,
2816 io_stall_write_ms BIGINT ,
2817 num_of_writes BIGINT ,
2818 bytes_written BIGINT,
2819 PhysicalName NVARCHAR(520) ,
2820 DetailsInt INT NULL,
2821 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2822 + ' INSERT '
2823 + @OutputTableNameFileStats
2824 + ' (ServerName, CheckDate, DatabaseID, FileID, DatabaseName, FileLogicalName, TypeDesc, SizeOnDiskMB, io_stall_read_ms, num_of_reads, bytes_read, io_stall_write_ms, num_of_writes, bytes_written, PhysicalName) SELECT '''
2825 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2826 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2827 + 'DatabaseID, FileID, DatabaseName, FileLogicalName, TypeDesc, SizeOnDiskMB, io_stall_read_ms, num_of_reads, bytes_read, io_stall_write_ms, num_of_writes, bytes_written, PhysicalName FROM #FileStats WHERE Pass = 2';
2828 EXEC(@StringToExecute);
2829 END;
2830 ELSE IF (SUBSTRING(@OutputTableNameFileStats, 2, 1) = '#')
2831 BEGIN
2832 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0);
2833 END;
2834
2835
2836 /* @OutputTableNamePerfmonStats lets us export the results to a permanent table */
2837 IF @OutputDatabaseName IS NOT NULL
2838 AND @OutputSchemaName IS NOT NULL
2839 AND @OutputTableNamePerfmonStats IS NOT NULL
2840 AND @OutputTableNamePerfmonStats NOT LIKE '#%'
2841 AND EXISTS ( SELECT *
2842 FROM sys.databases
2843 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2844 BEGIN
2845 /* Create the table */
2846 SET @StringToExecute = 'USE '
2847 + @OutputDatabaseName
2848 + '; IF EXISTS(SELECT * FROM '
2849 + @OutputDatabaseName
2850 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2851 + @OutputSchemaName
2852 + ''') AND NOT EXISTS (SELECT * FROM '
2853 + @OutputDatabaseName
2854 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2855 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2856 + @OutputTableNamePerfmonStats + ''') CREATE TABLE '
2857 + @OutputSchemaName + '.'
2858 + @OutputTableNamePerfmonStats
2859 + ' (ID INT IDENTITY(1,1) NOT NULL,
2860 ServerName NVARCHAR(128),
2861 CheckDate DATETIMEOFFSET,
2862 [object_name] NVARCHAR(128) NOT NULL,
2863 [counter_name] NVARCHAR(128) NOT NULL,
2864 [instance_name] NVARCHAR(128) NULL,
2865 [cntr_value] BIGINT NULL,
2866 [cntr_type] INT NOT NULL,
2867 [value_delta] BIGINT NULL,
2868 [value_per_second] DECIMAL(18,2) NULL,
2869 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));';
2870 EXEC(@StringToExecute);
2871
2872 /* Create the view */
2873 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNamePerfmonStats_View;
2874 IF OBJECT_ID(@ObjectFullName) IS NULL
2875 BEGIN
2876 SET @StringToExecute = 'USE '
2877 + @OutputDatabaseName
2878 + '; EXEC (''CREATE VIEW '
2879 + @OutputSchemaName + '.'
2880 + @OutputTableNamePerfmonStats_View + ' AS ' + @LineFeed
2881 + 'SELECT p.ServerName, p.CheckDate, p.object_name, p.counter_name, p.instance_name' + @LineFeed
2882 + ', DATEDIFF(ss, pPrior.CheckDate, p.CheckDate) AS ElapsedSeconds' + @LineFeed
2883 + ', p.cntr_value' + @LineFeed
2884 + ', p.cntr_type' + @LineFeed
2885 + ', (p.cntr_value - pPrior.cntr_value) AS cntr_delta' + @LineFeed
2886 + ', (p.cntr_value - pPrior.cntr_value) * 1.0 / DATEDIFF(ss, pPrior.CheckDate, p.CheckDate) AS cntr_delta_per_second' + @LineFeed
2887 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNamePerfmonStats + ' p' + @LineFeed
2888 + 'INNER JOIN ' + @OutputSchemaName + '.' + @OutputTableNamePerfmonStats + ' pPrior ON p.ServerName = pPrior.ServerName AND p.object_name = pPrior.object_name AND p.counter_name = pPrior.counter_name AND p.instance_name = pPrior.instance_name AND p.CheckDate > pPrior.CheckDate' + @LineFeed
2889 + 'LEFT OUTER JOIN ' + @OutputSchemaName + '.' + @OutputTableNamePerfmonStats + ' pMiddle ON p.ServerName = pMiddle.ServerName AND p.object_name = pMiddle.object_name AND p.counter_name = pMiddle.counter_name AND p.instance_name = pMiddle.instance_name AND p.CheckDate > pMiddle.CheckDate AND pMiddle.CheckDate > pPrior.CheckDate' + @LineFeed
2890 + 'WHERE pMiddle.ID IS NULL AND DATEDIFF(MI, pPrior.CheckDate, p.CheckDate) BETWEEN 1 AND 60;'')';
2891 EXEC(@StringToExecute);
2892 END;
2893
2894 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2895 + @OutputDatabaseName
2896 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2897 + @OutputSchemaName + ''') INSERT '
2898 + @OutputDatabaseName + '.'
2899 + @OutputSchemaName + '.'
2900 + @OutputTableNamePerfmonStats
2901 + ' (ServerName, CheckDate, object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second) SELECT '''
2902 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2903 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2904 + 'object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second FROM #PerfmonStats WHERE Pass = 2';
2905 EXEC(@StringToExecute);
2906
2907 /* Delete history older than @OutputTableRetentionDays */
2908 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2909 + @OutputDatabaseName
2910 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2911 + @OutputSchemaName + ''') DELETE '
2912 + @OutputDatabaseName + '.'
2913 + @OutputSchemaName + '.'
2914 + @OutputTableNamePerfmonStats
2915 + ' WHERE ServerName = '''
2916 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2917 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2918 EXEC(@StringToExecute);
2919
2920
2921
2922 END;
2923 ELSE IF (SUBSTRING(@OutputTableNamePerfmonStats, 2, 2) = '##')
2924 BEGIN
2925 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2926 + @OutputTableNamePerfmonStats
2927 + ''') IS NULL) CREATE TABLE '
2928 + @OutputTableNamePerfmonStats
2929 + ' (ID INT IDENTITY(1,1) NOT NULL,
2930 ServerName NVARCHAR(128),
2931 CheckDate DATETIMEOFFSET,
2932 [object_name] NVARCHAR(128) NOT NULL,
2933 [counter_name] NVARCHAR(128) NOT NULL,
2934 [instance_name] NVARCHAR(128) NULL,
2935 [cntr_value] BIGINT NULL,
2936 [cntr_type] INT NOT NULL,
2937 [value_delta] BIGINT NULL,
2938 [value_per_second] DECIMAL(18,2) NULL,
2939 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2940 + ' INSERT '
2941 + @OutputTableNamePerfmonStats
2942 + ' (ServerName, CheckDate, object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second) SELECT '''
2943 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2944 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2945 + 'object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second FROM #PerfmonStats WHERE Pass = 2';
2946 EXEC(@StringToExecute);
2947 END;
2948 ELSE IF (SUBSTRING(@OutputTableNamePerfmonStats, 2, 1) = '#')
2949 BEGIN
2950 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0);
2951 END;
2952
2953
2954 /* @OutputTableNameWaitStats lets us export the results to a permanent table */
2955 IF @OutputDatabaseName IS NOT NULL
2956 AND @OutputSchemaName IS NOT NULL
2957 AND @OutputTableNameWaitStats IS NOT NULL
2958 AND @OutputTableNameWaitStats NOT LIKE '#%'
2959 AND EXISTS ( SELECT *
2960 FROM sys.databases
2961 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2962 BEGIN
2963 /* Create the table */
2964 SET @StringToExecute = 'USE '
2965 + @OutputDatabaseName
2966 + '; IF EXISTS(SELECT * FROM '
2967 + @OutputDatabaseName
2968 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2969 + @OutputSchemaName
2970 + ''') AND NOT EXISTS (SELECT * FROM '
2971 + @OutputDatabaseName
2972 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2973 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2974 + @OutputTableNameWaitStats + ''') ' + @LineFeed
2975 + 'BEGIN' + @LineFeed
2976 + 'CREATE TABLE '
2977 + @OutputSchemaName + '.'
2978 + @OutputTableNameWaitStats
2979 + ' (ID INT IDENTITY(1,1) NOT NULL,
2980 ServerName NVARCHAR(128),
2981 CheckDate DATETIMEOFFSET,
2982 wait_type NVARCHAR(60),
2983 wait_time_ms BIGINT,
2984 signal_wait_time_ms BIGINT,
2985 waiting_tasks_count BIGINT ,
2986 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID));' + @LineFeed
2987 + 'CREATE NONCLUSTERED INDEX IX_ServerName_wait_type_CheckDate_Includes ON ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + @LineFeed
2988 + '(ServerName, wait_type, CheckDate) INCLUDE (wait_time_ms, signal_wait_time_ms, waiting_tasks_count);' + @LineFeed
2989 + 'END';
2990
2991 EXEC(@StringToExecute);
2992
2993 /* Create the wait stats category table */
2994 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameWaitStats_Categories;
2995 IF OBJECT_ID(@ObjectFullName) IS NULL
2996 BEGIN
2997 SET @StringToExecute = 'USE '
2998 + @OutputDatabaseName
2999 + '; EXEC (''CREATE TABLE '
3000 + @OutputSchemaName + '.'
3001 + @OutputTableNameWaitStats_Categories + ' (WaitType NVARCHAR(60) PRIMARY KEY CLUSTERED, WaitCategory NVARCHAR(128) NOT NULL, Ignorable BIT DEFAULT 0);'')';
3002 EXEC(@StringToExecute);
3003 END;
3004
3005 /* Make sure the wait stats category table has the current number of rows */
3006 SET @StringToExecute = 'USE '
3007 + @OutputDatabaseName
3008 + '; EXEC (''IF (SELECT COALESCE(SUM(1),0) FROM ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ') <> (SELECT COALESCE(SUM(1),0) FROM ##WaitCategories)' + @LineFeed
3009 + 'BEGIN ' + @LineFeed
3010 + 'TRUNCATE TABLE ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + @LineFeed
3011 + 'INSERT INTO ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ' (WaitType, WaitCategory, Ignorable) SELECT WaitType, WaitCategory, Ignorable FROM ##WaitCategories;' + @LineFeed
3012 + 'END'')';
3013 EXEC(@StringToExecute);
3014
3015
3016 /* Create the wait stats view */
3017 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameWaitStats_View;
3018 IF OBJECT_ID(@ObjectFullName) IS NULL
3019 BEGIN
3020 SET @StringToExecute = 'USE '
3021 + @OutputDatabaseName
3022 + '; EXEC (''CREATE VIEW '
3023 + @OutputSchemaName + '.'
3024 + @OutputTableNameWaitStats_View + ' AS ' + @LineFeed
3025 + 'SELECT w.ServerName, w.CheckDate, w.wait_type, COALESCE(wc.WaitCategory, ''''Other'''') AS WaitCategory, COALESCE(wc.Ignorable,0) AS Ignorable' + @LineFeed
3026 + ', DATEDIFF(ss, wPrior.CheckDate, w.CheckDate) AS ElapsedSeconds' + @LineFeed
3027 + ', (w.wait_time_ms - wPrior.wait_time_ms) AS wait_time_ms_delta' + @LineFeed
3028 + ', (w.wait_time_ms - wPrior.wait_time_ms) / 60000.0 AS wait_time_minutes_delta' + @LineFeed
3029 + ', (w.wait_time_ms - wPrior.wait_time_ms) / 1000.0 / DATEDIFF(ss, wPrior.CheckDate, w.CheckDate) AS wait_time_minutes_per_minute' + @LineFeed
3030 + ', (w.signal_wait_time_ms - wPrior.signal_wait_time_ms) AS signal_wait_time_ms_delta' + @LineFeed
3031 + ', (w.waiting_tasks_count - wPrior.waiting_tasks_count) AS waiting_tasks_count_delta' + @LineFeed
3032 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + ' w' + @LineFeed
3033 + 'INNER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + ' wPrior ON w.ServerName = wPrior.ServerName AND w.wait_type = wPrior.wait_type AND w.CheckDate > wPrior.CheckDate' + @LineFeed
3034 + 'LEFT OUTER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + ' wMiddle ON w.ServerName = wMiddle.ServerName AND w.wait_type = wMiddle.wait_type AND w.CheckDate > wMiddle.CheckDate AND wMiddle.CheckDate > wPrior.CheckDate' + @LineFeed
3035 + 'LEFT OUTER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ' wc ON w.wait_type = wc.WaitType' + @LineFeed
3036 + 'WHERE wMiddle.ID IS NULL AND w.wait_time_ms >= wPrior.wait_time_ms AND DATEDIFF(MI, wPrior.CheckDate, w.CheckDate) BETWEEN 1 AND 60;'')';
3037 EXEC(@StringToExecute);
3038 END;
3039
3040
3041 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
3042 + @OutputDatabaseName
3043 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
3044 + @OutputSchemaName + ''') INSERT '
3045 + @OutputDatabaseName + '.'
3046 + @OutputSchemaName + '.'
3047 + @OutputTableNameWaitStats
3048 + ' (ServerName, CheckDate, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count) SELECT '''
3049 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3050 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
3051 + 'wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count FROM #WaitStats WHERE Pass = 2 AND wait_time_ms > 0 AND waiting_tasks_count > 0';
3052 EXEC(@StringToExecute);
3053
3054 /* Delete history older than @OutputTableRetentionDays */
3055 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
3056 + @OutputDatabaseName
3057 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
3058 + @OutputSchemaName + ''') DELETE '
3059 + @OutputDatabaseName + '.'
3060 + @OutputSchemaName + '.'
3061 + @OutputTableNameWaitStats
3062 + ' WHERE ServerName = '''
3063 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3064 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
3065 EXEC(@StringToExecute);
3066
3067 END;
3068 ELSE IF (SUBSTRING(@OutputTableNameWaitStats, 2, 2) = '##')
3069 BEGIN
3070 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
3071 + @OutputTableNameWaitStats
3072 + ''') IS NULL) CREATE TABLE '
3073 + @OutputTableNameWaitStats
3074 + ' (ID INT IDENTITY(1,1) NOT NULL,
3075 ServerName NVARCHAR(128),
3076 CheckDate DATETIMEOFFSET,
3077 wait_type NVARCHAR(60),
3078 wait_time_ms BIGINT,
3079 signal_wait_time_ms BIGINT,
3080 waiting_tasks_count BIGINT ,
3081 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
3082 + ' INSERT '
3083 + @OutputTableNameWaitStats
3084 + ' (ServerName, CheckDate, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count) SELECT '''
3085 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3086 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
3087 + 'wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count FROM #WaitStats WHERE Pass = 2 AND wait_time_ms > 0 AND waiting_tasks_count > 0';
3088 EXEC(@StringToExecute);
3089 END;
3090 ELSE IF (SUBSTRING(@OutputTableNameWaitStats, 2, 1) = '#')
3091 BEGIN
3092 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0);
3093 END;
3094
3095
3096
3097
3098 DECLARE @separator AS VARCHAR(1);
3099 IF @OutputType = 'RSV'
3100 SET @separator = CHAR(31);
3101 ELSE
3102 SET @separator = ',';
3103
3104 IF @OutputType = 'COUNT' AND @SinceStartup = 0
3105 BEGIN
3106 SELECT COUNT(*) AS Warnings
3107 FROM #BlitzFirstResults;
3108 END;
3109 ELSE
3110 IF @OutputType = 'Opserver1' AND @SinceStartup = 0
3111 BEGIN
3112
3113 SELECT r.[Priority] ,
3114 r.[FindingsGroup] ,
3115 r.[Finding] ,
3116 r.[URL] ,
3117 r.[Details],
3118 r.[HowToStopIt] ,
3119 r.[CheckID] ,
3120 r.[StartTime],
3121 r.[LoginName],
3122 r.[NTUserName],
3123 r.[OriginalLoginName],
3124 r.[ProgramName],
3125 r.[HostName],
3126 r.[DatabaseID],
3127 r.[DatabaseName],
3128 r.[OpenTransactionCount],
3129 r.[QueryPlan],
3130 r.[QueryText],
3131 qsNow.plan_handle AS PlanHandle,
3132 qsNow.sql_handle AS SqlHandle,
3133 qsNow.statement_start_offset AS StatementStartOffset,
3134 qsNow.statement_end_offset AS StatementEndOffset,
3135 [Executions] = qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)),
3136 [ExecutionsPercent] = CAST(100.0 * (qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0))) / (qsTotal.execution_count - qsTotalFirst.execution_count) AS DECIMAL(6,2)),
3137 [Duration] = qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)),
3138 [DurationPercent] = CAST(100.0 * (qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0))) / (qsTotal.total_elapsed_time - qsTotalFirst.total_elapsed_time) AS DECIMAL(6,2)),
3139 [CPU] = qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)),
3140 [CPUPercent] = CAST(100.0 * (qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0))) / (qsTotal.total_worker_time - qsTotalFirst.total_worker_time) AS DECIMAL(6,2)),
3141 [Reads] = qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)),
3142 [ReadsPercent] = CAST(100.0 * (qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0))) / (qsTotal.total_logical_reads - qsTotalFirst.total_logical_reads) AS DECIMAL(6,2)),
3143 [PlanCreationTime] = CONVERT(NVARCHAR(100), qsNow.creation_time ,121),
3144 [TotalExecutions] = qsNow.execution_count,
3145 [TotalExecutionsPercent] = CAST(100.0 * qsNow.execution_count / qsTotal.execution_count AS DECIMAL(6,2)),
3146 [TotalDuration] = qsNow.total_elapsed_time,
3147 [TotalDurationPercent] = CAST(100.0 * qsNow.total_elapsed_time / qsTotal.total_elapsed_time AS DECIMAL(6,2)),
3148 [TotalCPU] = qsNow.total_worker_time,
3149 [TotalCPUPercent] = CAST(100.0 * qsNow.total_worker_time / qsTotal.total_worker_time AS DECIMAL(6,2)),
3150 [TotalReads] = qsNow.total_logical_reads,
3151 [TotalReadsPercent] = CAST(100.0 * qsNow.total_logical_reads / qsTotal.total_logical_reads AS DECIMAL(6,2)),
3152 r.[DetailsInt]
3153 FROM #BlitzFirstResults r
3154 LEFT OUTER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
3155 LEFT OUTER JOIN #QueryStats qsTotalFirst ON qsTotalFirst.Pass = -1
3156 LEFT OUTER JOIN #QueryStats qsNow ON r.QueryStatsNowID = qsNow.ID
3157 LEFT OUTER JOIN #QueryStats qsFirst ON r.QueryStatsFirstID = qsFirst.ID
3158 ORDER BY r.Priority ,
3159 r.FindingsGroup ,
3160 CASE
3161 WHEN r.CheckID = 6 THEN DetailsInt
3162 ELSE 0
3163 END DESC,
3164 r.Finding,
3165 r.ID;
3166 END;
3167 ELSE IF @OutputType IN ( 'CSV', 'RSV' ) AND @SinceStartup = 0
3168 BEGIN
3169
3170 SELECT Result = CAST([Priority] AS NVARCHAR(100))
3171 + @separator + CAST(CheckID AS NVARCHAR(100))
3172 + @separator + COALESCE([FindingsGroup],
3173 '(N/A)') + @separator
3174 + COALESCE([Finding], '(N/A)') + @separator
3175 + COALESCE(DatabaseName, '(N/A)') + @separator
3176 + COALESCE([URL], '(N/A)') + @separator
3177 + COALESCE([Details], '(N/A)')
3178 FROM #BlitzFirstResults
3179 ORDER BY Priority ,
3180 FindingsGroup ,
3181 CASE
3182 WHEN CheckID = 6 THEN DetailsInt
3183 ELSE 0
3184 END DESC,
3185 Finding,
3186 Details;
3187 END;
3188 ELSE IF @ExpertMode = 0 AND @OutputXMLasNVARCHAR = 0 AND @SinceStartup = 0
3189 BEGIN
3190 SELECT [Priority] ,
3191 [FindingsGroup] ,
3192 [Finding] ,
3193 [URL] ,
3194 CAST(@StockDetailsHeader + [Details] + @StockDetailsFooter AS XML) AS Details,
3195 CAST(@StockWarningHeader + HowToStopIt + @StockWarningFooter AS XML) AS HowToStopIt,
3196 [QueryText],
3197 [QueryPlan]
3198 FROM #BlitzFirstResults
3199 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3200 ORDER BY Priority ,
3201 FindingsGroup ,
3202 CASE
3203 WHEN CheckID = 6 THEN DetailsInt
3204 ELSE 0
3205 END DESC,
3206 Finding,
3207 ID;
3208 END;
3209 ELSE IF @ExpertMode = 0 AND @OutputXMLasNVARCHAR = 1 AND @SinceStartup = 0
3210 BEGIN
3211 SELECT [Priority] ,
3212 [FindingsGroup] ,
3213 [Finding] ,
3214 [URL] ,
3215 CAST(@StockDetailsHeader + [Details] + @StockDetailsFooter AS NVARCHAR(MAX)) AS Details,
3216 CAST([HowToStopIt] AS NVARCHAR(MAX)) AS HowToStopIt,
3217 CAST([QueryText] AS NVARCHAR(MAX)) AS QueryText,
3218 CAST([QueryPlan] AS NVARCHAR(MAX)) AS QueryPlan
3219 FROM #BlitzFirstResults
3220 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3221 ORDER BY Priority ,
3222 FindingsGroup ,
3223 CASE
3224 WHEN CheckID = 6 THEN DetailsInt
3225 ELSE 0
3226 END DESC,
3227 Finding,
3228 ID;
3229 END;
3230 ELSE IF @ExpertMode = 1
3231 BEGIN
3232 IF @SinceStartup = 0
3233 SELECT r.[Priority] ,
3234 r.[FindingsGroup] ,
3235 r.[Finding] ,
3236 r.[URL] ,
3237 CAST(@StockDetailsHeader + r.[Details] + @StockDetailsFooter AS XML) AS Details,
3238 CAST(@StockWarningHeader + r.HowToStopIt + @StockWarningFooter AS XML) AS HowToStopIt,
3239 r.[CheckID] ,
3240 r.[StartTime],
3241 r.[LoginName],
3242 r.[NTUserName],
3243 r.[OriginalLoginName],
3244 r.[ProgramName],
3245 r.[HostName],
3246 r.[DatabaseID],
3247 r.[DatabaseName],
3248 r.[OpenTransactionCount],
3249 r.[QueryPlan],
3250 r.[QueryText],
3251 qsNow.plan_handle AS PlanHandle,
3252 qsNow.sql_handle AS SqlHandle,
3253 qsNow.statement_start_offset AS StatementStartOffset,
3254 qsNow.statement_end_offset AS StatementEndOffset,
3255 [Executions] = qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)),
3256 [ExecutionsPercent] = CAST(100.0 * (qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0))) / (qsTotal.execution_count - qsTotalFirst.execution_count) AS DECIMAL(6,2)),
3257 [Duration] = qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)),
3258 [DurationPercent] = CAST(100.0 * (qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0))) / (qsTotal.total_elapsed_time - qsTotalFirst.total_elapsed_time) AS DECIMAL(6,2)),
3259 [CPU] = qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)),
3260 [CPUPercent] = CAST(100.0 * (qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0))) / (qsTotal.total_worker_time - qsTotalFirst.total_worker_time) AS DECIMAL(6,2)),
3261 [Reads] = qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)),
3262 [ReadsPercent] = CAST(100.0 * (qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0))) / (qsTotal.total_logical_reads - qsTotalFirst.total_logical_reads) AS DECIMAL(6,2)),
3263 [PlanCreationTime] = CONVERT(NVARCHAR(100), qsNow.creation_time ,121),
3264 [TotalExecutions] = qsNow.execution_count,
3265 [TotalExecutionsPercent] = CAST(100.0 * qsNow.execution_count / qsTotal.execution_count AS DECIMAL(6,2)),
3266 [TotalDuration] = qsNow.total_elapsed_time,
3267 [TotalDurationPercent] = CAST(100.0 * qsNow.total_elapsed_time / qsTotal.total_elapsed_time AS DECIMAL(6,2)),
3268 [TotalCPU] = qsNow.total_worker_time,
3269 [TotalCPUPercent] = CAST(100.0 * qsNow.total_worker_time / qsTotal.total_worker_time AS DECIMAL(6,2)),
3270 [TotalReads] = qsNow.total_logical_reads,
3271 [TotalReadsPercent] = CAST(100.0 * qsNow.total_logical_reads / qsTotal.total_logical_reads AS DECIMAL(6,2)),
3272 r.[DetailsInt]
3273 FROM #BlitzFirstResults r
3274 LEFT OUTER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
3275 LEFT OUTER JOIN #QueryStats qsTotalFirst ON qsTotalFirst.Pass = -1
3276 LEFT OUTER JOIN #QueryStats qsNow ON r.QueryStatsNowID = qsNow.ID
3277 LEFT OUTER JOIN #QueryStats qsFirst ON r.QueryStatsFirstID = qsFirst.ID
3278 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3279 ORDER BY r.Priority ,
3280 r.FindingsGroup ,
3281 CASE
3282 WHEN r.CheckID = 6 THEN DetailsInt
3283 ELSE 0
3284 END DESC,
3285 r.Finding,
3286 r.ID;
3287
3288 -------------------------
3289 --What happened: #WaitStats
3290 -------------------------
3291 IF @Seconds = 0
3292 BEGIN
3293 /* Measure waits in hours */
3294 ;WITH max_batch AS (
3295 SELECT MAX(SampleTime) AS SampleTime
3296 FROM #WaitStats
3297 )
3298 SELECT
3299 'WAIT STATS' AS Pattern,
3300 b.SampleTime AS [Sample Ended],
3301 CAST(DATEDIFF(mi,wd1.SampleTime, wd2.SampleTime) / 60.0 AS DECIMAL(18,1)) AS [Hours Sample],
3302 wd1.wait_type,
3303 COALESCE(wcat.WaitCategory, 'Other') AS wait_category,
3304 CAST(c.[Wait Time (Seconds)] / 60.0 / 60 AS DECIMAL(18,1)) AS [Wait Time (Hours)],
3305 CAST((wd2.wait_time_ms - wd1.wait_time_ms) / 1000.0 / 60 / 60 / cores.cpu_count / DATEDIFF(ss, wd1.SampleTime, wd2.SampleTime) AS DECIMAL(18,1)) AS [Per Core Per Hour],
3306 CAST(c.[Signal Wait Time (Seconds)] / 60.0 / 60 AS DECIMAL(18,1)) AS [Signal Wait Time (Hours)],
3307 CASE WHEN c.[Wait Time (Seconds)] > 0
3308 THEN CAST(100.*(c.[Signal Wait Time (Seconds)]/c.[Wait Time (Seconds)]) AS NUMERIC(4,1))
3309 ELSE 0 END AS [Percent Signal Waits],
3310 (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits],
3311 CASE WHEN (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3312 THEN
3313 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/
3314 (1.0*(wd2.waiting_tasks_count - wd1.waiting_tasks_count)) AS NUMERIC(12,1))
3315 ELSE 0 END AS [Avg ms Per Wait],
3316 N'https://www.sqlskills.com/help/waits/' + LOWER(wd1.wait_type) + '/' AS URL
3317 FROM max_batch b
3318 JOIN #WaitStats wd2 ON
3319 wd2.SampleTime =b.SampleTime
3320 JOIN #WaitStats wd1 ON
3321 wd1.wait_type=wd2.wait_type AND
3322 wd2.SampleTime > wd1.SampleTime
3323 CROSS APPLY (SELECT SUM(1) AS cpu_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1) AS cores
3324 CROSS APPLY (SELECT
3325 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Wait Time (Seconds)],
3326 CAST((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Signal Wait Time (Seconds)]) AS c
3327 LEFT OUTER JOIN ##WaitCategories wcat ON wd1.wait_type = wcat.WaitType
3328 WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3329 AND wd2.wait_time_ms-wd1.wait_time_ms > 0
3330 ORDER BY [Wait Time (Seconds)] DESC;
3331 END;
3332 ELSE
3333 BEGIN
3334 /* Measure waits in seconds */
3335 ;WITH max_batch AS (
3336 SELECT MAX(SampleTime) AS SampleTime
3337 FROM #WaitStats
3338 )
3339 SELECT
3340 'WAIT STATS' AS Pattern,
3341 b.SampleTime AS [Sample Ended],
3342 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Seconds Sample],
3343 wd1.wait_type,
3344 COALESCE(wcat.WaitCategory, 'Other') AS wait_category,
3345 c.[Wait Time (Seconds)],
3346 CAST((wd2.wait_time_ms - wd1.wait_time_ms) / 1000.0 / cores.cpu_count / DATEDIFF(ss, wd1.SampleTime, wd2.SampleTime) AS DECIMAL(18,1)) AS [Per Core Per Second],
3347 c.[Signal Wait Time (Seconds)],
3348 CASE WHEN c.[Wait Time (Seconds)] > 0
3349 THEN CAST(100.*(c.[Signal Wait Time (Seconds)]/c.[Wait Time (Seconds)]) AS NUMERIC(4,1))
3350 ELSE 0 END AS [Percent Signal Waits],
3351 (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits],
3352 CASE WHEN (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3353 THEN
3354 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/
3355 (1.0*(wd2.waiting_tasks_count - wd1.waiting_tasks_count)) AS NUMERIC(12,1))
3356 ELSE 0 END AS [Avg ms Per Wait],
3357 N'https://www.sqlskills.com/help/waits/' + LOWER(wd1.wait_type) + '/' AS URL
3358 FROM max_batch b
3359 JOIN #WaitStats wd2 ON
3360 wd2.SampleTime =b.SampleTime
3361 JOIN #WaitStats wd1 ON
3362 wd1.wait_type=wd2.wait_type AND
3363 wd2.SampleTime > wd1.SampleTime
3364 CROSS APPLY (SELECT SUM(1) AS cpu_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1) AS cores
3365 CROSS APPLY (SELECT
3366 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Wait Time (Seconds)],
3367 CAST((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Signal Wait Time (Seconds)]) AS c
3368 LEFT OUTER JOIN ##WaitCategories wcat ON wd1.wait_type = wcat.WaitType
3369 WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3370 AND wd2.wait_time_ms-wd1.wait_time_ms > 0
3371 ORDER BY [Wait Time (Seconds)] DESC;
3372 END;
3373
3374 -------------------------
3375 --What happened: #FileStats
3376 -------------------------
3377 WITH readstats AS (
3378 SELECT 'PHYSICAL READS' AS Pattern,
3379 ROW_NUMBER() OVER (ORDER BY wd2.avg_stall_read_ms DESC) AS StallRank,
3380 wd2.SampleTime AS [Sample Time],
3381 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Sample (seconds)],
3382 wd1.DatabaseName ,
3383 wd1.FileLogicalName AS [File Name],
3384 UPPER(SUBSTRING(wd1.PhysicalName, 1, 2)) AS [Drive] ,
3385 wd1.SizeOnDiskMB ,
3386 ( wd2.num_of_reads - wd1.num_of_reads ) AS [# Reads/Writes],
3387 CASE WHEN wd2.num_of_reads - wd1.num_of_reads > 0
3388 THEN CAST(( wd2.bytes_read - wd1.bytes_read)/1024./1024. AS NUMERIC(21,1))
3389 ELSE 0
3390 END AS [MB Read/Written],
3391 wd2.avg_stall_read_ms AS [Avg Stall (ms)],
3392 wd1.PhysicalName AS [file physical name]
3393 FROM #FileStats wd2
3394 JOIN #FileStats wd1 ON wd2.SampleTime > wd1.SampleTime
3395 AND wd1.DatabaseID = wd2.DatabaseID
3396 AND wd1.FileID = wd2.FileID
3397 ),
3398 writestats AS (
3399 SELECT
3400 'PHYSICAL WRITES' AS Pattern,
3401 ROW_NUMBER() OVER (ORDER BY wd2.avg_stall_write_ms DESC) AS StallRank,
3402 wd2.SampleTime AS [Sample Time],
3403 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Sample (seconds)],
3404 wd1.DatabaseName ,
3405 wd1.FileLogicalName AS [File Name],
3406 UPPER(SUBSTRING(wd1.PhysicalName, 1, 2)) AS [Drive] ,
3407 wd1.SizeOnDiskMB ,
3408 ( wd2.num_of_writes - wd1.num_of_writes ) AS [# Reads/Writes],
3409 CASE WHEN wd2.num_of_writes - wd1.num_of_writes > 0
3410 THEN CAST(( wd2.bytes_written - wd1.bytes_written)/1024./1024. AS NUMERIC(21,1))
3411 ELSE 0
3412 END AS [MB Read/Written],
3413 wd2.avg_stall_write_ms AS [Avg Stall (ms)],
3414 wd1.PhysicalName AS [file physical name]
3415 FROM #FileStats wd2
3416 JOIN #FileStats wd1 ON wd2.SampleTime > wd1.SampleTime
3417 AND wd1.DatabaseID = wd2.DatabaseID
3418 AND wd1.FileID = wd2.FileID
3419 )
3420 SELECT
3421 Pattern, [Sample Time], [Sample (seconds)], [File Name], [Drive], [# Reads/Writes],[MB Read/Written],[Avg Stall (ms)], [file physical name]
3422 FROM readstats
3423 WHERE StallRank <=5 AND [MB Read/Written] > 0
3424 UNION ALL
3425 SELECT Pattern, [Sample Time], [Sample (seconds)], [File Name], [Drive], [# Reads/Writes],[MB Read/Written],[Avg Stall (ms)], [file physical name]
3426 FROM writestats
3427 WHERE StallRank <=5 AND [MB Read/Written] > 0;
3428
3429
3430 -------------------------
3431 --What happened: #PerfmonStats
3432 -------------------------
3433
3434 SELECT 'PERFMON' AS Pattern, pLast.[object_name], pLast.counter_name, pLast.instance_name,
3435 pFirst.SampleTime AS FirstSampleTime, pFirst.cntr_value AS FirstSampleValue,
3436 pLast.SampleTime AS LastSampleTime, pLast.cntr_value AS LastSampleValue,
3437 pLast.cntr_value - pFirst.cntr_value AS ValueDelta,
3438 ((1.0 * pLast.cntr_value - pFirst.cntr_value) / DATEDIFF(ss, pFirst.SampleTime, pLast.SampleTime)) AS ValuePerSecond
3439 FROM #PerfmonStats pLast
3440 INNER JOIN #PerfmonStats pFirst ON pFirst.[object_name] = pLast.[object_name] AND pFirst.counter_name = pLast.counter_name AND (pFirst.instance_name = pLast.instance_name OR (pFirst.instance_name IS NULL AND pLast.instance_name IS NULL))
3441 AND pLast.ID > pFirst.ID
3442 WHERE pLast.cntr_value <> pFirst.cntr_value
3443 ORDER BY Pattern, pLast.[object_name], pLast.counter_name, pLast.instance_name;
3444
3445
3446 -------------------------
3447 --What happened: #QueryStats
3448 -------------------------
3449 IF @CheckProcedureCache = 1
3450 BEGIN
3451
3452 SELECT qsNow.*, qsFirst.*
3453 FROM #QueryStats qsNow
3454 INNER JOIN #QueryStats qsFirst ON qsNow.[sql_handle] = qsFirst.[sql_handle] AND qsNow.statement_start_offset = qsFirst.statement_start_offset AND qsNow.statement_end_offset = qsFirst.statement_end_offset AND qsNow.plan_generation_num = qsFirst.plan_generation_num AND qsNow.plan_handle = qsFirst.plan_handle AND qsFirst.Pass = 1
3455 WHERE qsNow.Pass = 2;
3456 END;
3457 ELSE
3458 BEGIN
3459 SELECT 'Plan Cache' AS [Pattern], 'Plan cache not analyzed' AS [Finding], 'Use @CheckProcedureCache = 1 or run sp_BlitzCache for more analysis' AS [More Info], CONVERT(XML, @StockDetailsHeader + 'firstresponderkit.org' + @StockDetailsFooter) AS [Details];
3460 END;
3461 END;
3462
3463 DROP TABLE #BlitzFirstResults;
3464
3465 /* What's running right now? This is the first and last result set. */
3466 IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1
3467IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1
3468 BEGIN
3469 IF OBJECT_ID('master.dbo.sp_BlitzWho') IS NULL AND OBJECT_ID('dbo.sp_BlitzWho') IS NULL
3470 BEGIN
3471 PRINT N'sp_BlitzWho is not installed in the current database_files. You can get a copy from http://FirstResponderKit.org';
3472 END;
3473 ELSE
3474 BEGIN
3475 EXEC (@BlitzWho);
3476 END;
3477 END; /* IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1 - What's running right now? This is the first and last result set. */
3478
3479END; /* IF @LogMessage IS NULL */
3480END; /* ELSE IF @OutputType = 'SCHEMA' */
3481
3482SET NOCOUNT OFF;
3483GO
3484
3485
3486
3487/* How to run it:
3488EXEC dbo.sp_BlitzFirst
3489
3490With extra diagnostic info:
3491EXEC dbo.sp_BlitzFirst @ExpertMode = 1;
3492
3493Saving output to tables:
3494EXEC sp_BlitzFirst
3495, @OutputDatabaseName = 'DBAtools'
3496, @OutputSchemaName = 'dbo'
3497, @OutputTableName = 'BlitzFirst'
3498, @OutputTableNameFileStats = 'BlitzFirst_FileStats'
3499, @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats'
3500, @OutputTableNameWaitStats = 'BlitzFirst_WaitStats'
3501, @OutputTableNameBlitzCache = 'BlitzCache'
3502*/