· 8 years ago · Mar 29, 2018, 03:44 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.2';
43SET @VersionDate = '20180201';
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 - Query Rolling Back - CheckID 9 */
1493 IF @Seconds > 0
1494 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, StartTime, LoginName, NTUserName, ProgramName, HostName, DatabaseID, DatabaseName, QueryText)
1495 SELECT 9 AS CheckID,
1496 1 AS Priority,
1497 'Query Problems' AS FindingGroup,
1498 'Query Rolling Back' AS Finding,
1499 'http://www.BrentOzar.com/askbrent/rollback/' AS URL,
1500 'Rollback started at ' + CAST(r.start_time AS NVARCHAR(100)) + ', is ' + CAST(r.percent_complete AS NVARCHAR(100)) + '% complete.' AS Details,
1501 '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,
1502 r.start_time AS StartTime,
1503 s.login_name AS LoginName,
1504 s.nt_user_name AS NTUserName,
1505 s.[program_name] AS ProgramName,
1506 s.[host_name] AS HostName,
1507 db.[resource_database_id] AS DatabaseID,
1508 DB_NAME(db.resource_database_id) AS DatabaseName,
1509 (SELECT TOP 1 [text] FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS QueryText
1510 FROM sys.dm_exec_sessions s
1511 INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
1512 INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
1513 LEFT OUTER JOIN (
1514 SELECT DISTINCT request_session_id, resource_database_id
1515 FROM sys.dm_tran_locks
1516 WHERE resource_type = N'DATABASE'
1517 AND request_mode = N'S'
1518 AND request_status = N'GRANT'
1519 AND request_owner_type = N'SHARED_TRANSACTION_WORKSPACE') AS db ON s.session_id = db.request_session_id
1520 WHERE r.status = 'rollback'
1521
1522
1523 /* Server Performance - Page Life Expectancy Low - CheckID 10 */
1524 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1525 SELECT 10 AS CheckID,
1526 50 AS Priority,
1527 'Server Performance' AS FindingGroup,
1528 'Page Life Expectancy Low' AS Finding,
1529 'http://www.BrentOzar.com/askbrent/page-life-expectancy/' AS URL,
1530 'SQL Server Buffer Manager:Page life expectancy is ' + CAST(c.cntr_value AS NVARCHAR(10)) + ' seconds.' + @LineFeed
1531 + 'This means SQL Server can only keep data pages in memory for that many seconds after reading those pages in from storage.' + @LineFeed
1532 + 'This is a symptom, not a cause - it indicates very read-intensive queries that need an index, or insufficient server memory.' AS Details,
1533 '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
1534 FROM sys.dm_os_performance_counters c
1535 WHERE object_name LIKE 'SQLServer:Buffer Manager%'
1536 AND counter_name LIKE 'Page life expectancy%'
1537 AND cntr_value < 300
1538
1539 /* Server Performance - Too Much Free Memory - CheckID 34 */
1540 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1541 SELECT 34 AS CheckID,
1542 50 AS Priority,
1543 'Server Performance' AS FindingGroup,
1544 'Too Much Free Memory' AS Finding,
1545 'https://BrentOzar.com/go/freememory' AS URL,
1546 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,
1547 'Run sp_BlitzCache @SortOrder = ''memory grant'' to find queries with huge memory grants and tune them.' AS HowToStopIt
1548 FROM sys.dm_os_performance_counters cFree
1549 INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
1550 AND cTotal.counter_name = N'Total Server Memory (KB) '
1551 WHERE cFree.object_name LIKE N'%Memory Manager%'
1552 AND cFree.counter_name = N'Free Memory (KB) '
1553 AND CAST(cTotal.cntr_value AS BIGINT) > 20480000000
1554 AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
1555 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%';
1556
1557 /* Server Performance - Target Memory Lower Than Max - CheckID 35 */
1558 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
1559 SELECT 35 AS CheckID,
1560 10 AS Priority,
1561 'Server Performance' AS FindingGroup,
1562 'Target Memory Lower Than Max' AS Finding,
1563 'https://BrentOzar.com/go/target' AS URL,
1564 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
1565 + N'indicating that SQL Server may be under external memory pressure or max server memory may be set too high.' AS Details,
1566 'Investigate what OS processes are using memory, and double-check the max server memory setting.' AS HowToStopIt
1567 FROM sys.configurations cMax
1568 INNER JOIN sys.dm_os_performance_counters cTarget ON cTarget.object_name LIKE N'%Memory Manager%'
1569 AND cTarget.counter_name = N'Target Server Memory (KB) '
1570 WHERE cMax.name = 'max server memory (MB)'
1571 AND CAST(cMax.value_in_use AS BIGINT) >= 1.5 * (CAST(cTarget.cntr_value AS BIGINT) / 1024)
1572 AND CAST(cMax.value_in_use AS BIGINT) < 2147483647 /* Not set to default of unlimited */
1573 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) */
1574
1575 /* Server Info - Database Size, Total GB - CheckID 21 */
1576 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1577 SELECT 21 AS CheckID,
1578 251 AS Priority,
1579 'Server Info' AS FindingGroup,
1580 'Database Size, Total GB' AS Finding,
1581 CAST(SUM (CAST(size AS BIGINT)*8./1024./1024.) AS VARCHAR(100)) AS Details,
1582 SUM (CAST(size AS BIGINT))*8./1024./1024. AS DetailsInt,
1583 'http://www.BrentOzar.com/askbrent/' AS URL
1584 FROM #MasterFiles
1585 WHERE database_id > 4
1586
1587 /* Server Info - Database Count - CheckID 22 */
1588 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1589 SELECT 22 AS CheckID,
1590 251 AS Priority,
1591 'Server Info' AS FindingGroup,
1592 'Database Count' AS Finding,
1593 CAST(SUM(1) AS VARCHAR(100)) AS Details,
1594 SUM (1) AS DetailsInt,
1595 'http://www.BrentOzar.com/askbrent/' AS URL
1596 FROM sys.databases
1597 WHERE database_id > 4
1598
1599 /* Server Performance - High CPU Utilization CheckID 24 */
1600 IF @Seconds < 30
1601 BEGIN
1602 /* If we're waiting less than 30 seconds, run this check now rather than wait til the end.
1603 We get this data from the ring buffers, and it's only updated once per minute, so might
1604 as well get it now - whereas if we're checking 30+ seconds, it might get updated by the
1605 end of our sp_BlitzFirst session. */
1606 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1607 SELECT 24, 50, 'Server Performance', 'High CPU Utilization', CAST(100 - SystemIdle AS NVARCHAR(20)) + N'%.', 100 - SystemIdle, 'http://www.BrentOzar.com/go/cpu'
1608 FROM (
1609 SELECT record,
1610 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1611 FROM (
1612 SELECT TOP 1 CONVERT(XML, record) AS record
1613 FROM sys.dm_os_ring_buffers
1614 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1615 AND record LIKE '%<SystemHealth>%'
1616 ORDER BY timestamp DESC) AS rb
1617 ) AS y
1618 WHERE 100 - SystemIdle >= 50
1619
1620 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1621 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'
1622 FROM (
1623 SELECT record,
1624 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1625 FROM (
1626 SELECT TOP 1 CONVERT(XML, record) AS record
1627 FROM sys.dm_os_ring_buffers
1628 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1629 AND record LIKE '%<SystemHealth>%'
1630 ORDER BY timestamp DESC) AS rb
1631 ) AS y
1632
1633 /* Highlight if non SQL processes are using >25% CPU */
1634 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
1635 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'
1636 FROM (
1637 SELECT record,
1638 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
1639 ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLUsage
1640 FROM (
1641 SELECT TOP 1 CONVERT(XML, record) AS record
1642 FROM sys.dm_os_ring_buffers
1643 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
1644 AND record LIKE '%<SystemHealth>%'
1645 ORDER BY timestamp DESC) AS rb
1646 ) AS y
1647 WHERE 100 - (y.SQLUsage + y.SystemIdle) >= 25
1648
1649 END /* IF @Seconds < 30 */
1650
1651 RAISERROR('Finished running investigatory queries',10,1) WITH NOWAIT;
1652
1653
1654 /* End of checks. If we haven't waited @Seconds seconds, wait. */
1655 IF SYSDATETIMEOFFSET() < @FinishSampleTime
1656 BEGIN
1657 RAISERROR('Waiting to match @Seconds parameter',10,1) WITH NOWAIT;
1658 WAITFOR TIME @FinishSampleTimeWaitFor;
1659 END
1660
1661 RAISERROR('Capturing second pass of wait stats, perfmon counters, file stats',10,1) WITH NOWAIT;
1662 /* Populate #FileStats, #PerfmonStats, #WaitStats with DMV data. In a second, we'll compare these. */
1663 INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
1664 SELECT
1665 x.Pass,
1666 x.SampleTime,
1667 x.wait_type,
1668 SUM(x.sum_wait_time_ms) AS sum_wait_time_ms,
1669 SUM(x.sum_signal_wait_time_ms) AS sum_signal_wait_time_ms,
1670 SUM(x.sum_waiting_tasks) AS sum_waiting_tasks
1671 FROM (
1672 SELECT
1673 2 AS Pass,
1674 SYSDATETIMEOFFSET() AS SampleTime,
1675 owt.wait_type,
1676 SUM(owt.wait_duration_ms) OVER (PARTITION BY owt.wait_type, owt.session_id)
1677 - CASE WHEN @Seconds = 0 THEN 0 ELSE (@Seconds * 1000) END AS sum_wait_time_ms,
1678 0 AS sum_signal_wait_time_ms,
1679 CASE @Seconds WHEN 0 THEN 0 ELSE 1 END AS sum_waiting_tasks
1680 FROM sys.dm_os_waiting_tasks owt
1681 WHERE owt.session_id > 50
1682 AND owt.wait_duration_ms >= CASE @Seconds WHEN 0 THEN 0 ELSE @Seconds * 1000 END
1683 UNION ALL
1684 SELECT
1685 2 AS Pass,
1686 SYSDATETIMEOFFSET() AS SampleTime,
1687 os.wait_type,
1688 SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) AS sum_wait_time_ms,
1689 SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) AS sum_signal_wait_time_ms,
1690 SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
1691 FROM sys.dm_os_wait_stats os
1692 ) x
1693 WHERE x.wait_type NOT IN (
1694 'BROKER_EVENTHANDLER'
1695 , 'BROKER_RECEIVE_WAITFOR'
1696 , 'BROKER_TASK_STOP'
1697 , 'BROKER_TO_FLUSH'
1698 , 'BROKER_TRANSMITTER'
1699 , 'CHECKPOINT_QUEUE'
1700 , 'DBMIRROR_DBM_EVENT'
1701 , 'DBMIRROR_DBM_MUTEX'
1702 , 'DBMIRROR_EVENTS_QUEUE'
1703 , 'DBMIRROR_WORKER_QUEUE'
1704 , 'DBMIRRORING_CMD'
1705 , 'DIRTY_PAGE_POLL'
1706 , 'DISPATCHER_QUEUE_SEMAPHORE'
1707 , 'FT_IFTS_SCHEDULER_IDLE_WAIT'
1708 , 'FT_IFTSHC_MUTEX'
1709 , 'HADR_CLUSAPI_CALL'
1710 , 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
1711 , 'HADR_LOGCAPTURE_WAIT'
1712 , 'HADR_NOTIFICATION_DEQUEUE'
1713 , 'HADR_TIMER_TASK'
1714 , 'HADR_WORK_QUEUE'
1715 , 'LAZYWRITER_SLEEP'
1716 , 'LOGMGR_QUEUE'
1717 , 'ONDEMAND_TASK_QUEUE'
1718 , 'PREEMPTIVE_HADR_LEASE_MECHANISM'
1719 , 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS'
1720 , 'QDS_ASYNC_QUEUE'
1721 , 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
1722 , 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
1723 , 'QDS_SHUTDOWN_QUEUE'
1724 , 'REDO_THREAD_PENDING_WORK'
1725 , 'REQUEST_FOR_DEADLOCK_SEARCH'
1726 , 'SLEEP_SYSTEMTASK'
1727 , 'SLEEP_TASK'
1728 , 'SP_SERVER_DIAGNOSTICS_SLEEP'
1729 , 'SQLTRACE_BUFFER_FLUSH'
1730 , 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
1731 , 'UCS_SESSION_REGISTRATION'
1732 , 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
1733 , 'WAITFOR'
1734 , 'XE_DISPATCHER_WAIT'
1735 , 'XE_LIVE_TARGET_TVF'
1736 , 'XE_TIMER_EVENT'
1737 )
1738 GROUP BY x.Pass, x.SampleTime, x.wait_type
1739 ORDER BY sum_wait_time_ms DESC;
1740
1741 INSERT INTO #FileStats (Pass, SampleTime, DatabaseID, FileID, DatabaseName, FileLogicalName, SizeOnDiskMB, io_stall_read_ms ,
1742 num_of_reads, [bytes_read] , io_stall_write_ms,num_of_writes, [bytes_written], PhysicalName, TypeDesc, avg_stall_read_ms, avg_stall_write_ms)
1743 SELECT 2 AS Pass,
1744 SYSDATETIMEOFFSET() AS SampleTime,
1745 mf.[database_id],
1746 mf.[file_id],
1747 DB_NAME(vfs.database_id) AS [db_name],
1748 mf.name + N' [' + mf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS file_logical_name ,
1749 CAST(( ( vfs.size_on_disk_bytes / 1024.0 ) / 1024.0 ) AS INT) AS size_on_disk_mb ,
1750 vfs.io_stall_read_ms ,
1751 vfs.num_of_reads ,
1752 vfs.[num_of_bytes_read],
1753 vfs.io_stall_write_ms ,
1754 vfs.num_of_writes ,
1755 vfs.[num_of_bytes_written],
1756 mf.physical_name,
1757 mf.type_desc,
1758 0,
1759 0
1760 FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS vfs
1761 INNER JOIN #MasterFiles AS mf ON vfs.file_id = mf.file_id
1762 AND vfs.database_id = mf.database_id
1763 WHERE vfs.num_of_reads > 0
1764 OR vfs.num_of_writes > 0;
1765
1766 INSERT INTO #PerfmonStats (Pass, SampleTime, [object_name],[counter_name],[instance_name],[cntr_value],[cntr_type])
1767 SELECT 2 AS Pass,
1768 SYSDATETIMEOFFSET() AS SampleTime,
1769 RTRIM(dmv.object_name), RTRIM(dmv.counter_name), RTRIM(dmv.instance_name), dmv.cntr_value, dmv.cntr_type
1770 FROM #PerfmonCounters counters
1771 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
1772 AND counters.[object_name] COLLATE SQL_Latin1_General_CP1_CI_AS = RTRIM(dmv.[object_name]) COLLATE SQL_Latin1_General_CP1_CI_AS
1773 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)
1774
1775 /* Set the latencies and averages. We could do this with a CTE, but we're not ambitious today. */
1776 UPDATE fNow
1777 SET avg_stall_read_ms = ((fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads))
1778 FROM #FileStats fNow
1779 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
1780 WHERE (fNow.num_of_reads - fBase.num_of_reads) > 0
1781
1782 UPDATE fNow
1783 SET avg_stall_write_ms = ((fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes))
1784 FROM #FileStats fNow
1785 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
1786 WHERE (fNow.num_of_writes - fBase.num_of_writes) > 0
1787
1788 UPDATE pNow
1789 SET [value_delta] = pNow.cntr_value - pFirst.cntr_value,
1790 [value_per_second] = ((1.0 * pNow.cntr_value - pFirst.cntr_value) / DATEDIFF(ss, pFirst.SampleTime, pNow.SampleTime))
1791 FROM #PerfmonStats pNow
1792 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))
1793 AND pNow.ID > pFirst.ID
1794 WHERE DATEDIFF(ss, pFirst.SampleTime, pNow.SampleTime) > 0;
1795
1796
1797 /* If we're within 10 seconds of our projected finish time, do the plan cache analysis. */
1798 IF DATEDIFF(ss, @FinishSampleTime, SYSDATETIME()) > 10 AND @CheckProcedureCache = 1
1799 BEGIN
1800
1801 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
1802 VALUES (18, 210, 'Query Stats', 'Plan Cache Analysis Skipped', 'http://www.BrentOzar.com/go/topqueries',
1803 'Due to excessive load, the plan cache analysis was skipped. To override this, use @ExpertMode = 1.')
1804
1805 END
1806 ELSE IF @CheckProcedureCache = 1
1807 BEGIN
1808
1809
1810 RAISERROR('@CheckProcedureCache = 1, capturing second pass of plan cache',10,1) WITH NOWAIT;
1811
1812 /* Populate #QueryStats. SQL 2005 doesn't have query hash or query plan hash. */
1813 IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
1814 BEGIN
1815 IF @FilterPlansByDatabase IS NULL
1816 BEGIN
1817 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)
1818 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
1819 FROM sys.dm_exec_query_stats qs
1820 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1821 END
1822 ELSE
1823 BEGIN
1824 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)
1825 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
1826 FROM sys.dm_exec_query_stats qs
1827 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1828 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1829 WHERE qs.last_execution_time >= @StartSampleTimeText
1830 AND attr.attribute = ''dbid'';';
1831 END
1832 END
1833 ELSE
1834 BEGIN
1835 IF @FilterPlansByDatabase IS NULL
1836 BEGIN
1837 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)
1838 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
1839 FROM sys.dm_exec_query_stats qs
1840 WHERE qs.last_execution_time >= @StartSampleTimeText';
1841 END
1842 ELSE
1843 BEGIN
1844 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)
1845 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
1846 FROM sys.dm_exec_query_stats qs
1847 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS attr
1848 INNER JOIN #FilterPlansByDatabase dbs ON CAST(attr.value AS INT) = dbs.DatabaseID
1849 WHERE qs.last_execution_time >= @StartSampleTimeText
1850 AND attr.attribute = ''dbid'';';
1851 END
1852 END
1853 /* Old version pre-2016/06/13:
1854 IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
1855 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)
1856 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
1857 FROM sys.dm_exec_query_stats qs
1858 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1859 ELSE
1860 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)
1861 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
1862 FROM sys.dm_exec_query_stats qs
1863 WHERE qs.last_execution_time >= @StartSampleTimeText;';
1864 */
1865 SET @ParmDefinitions = N'@StartSampleTimeText NVARCHAR(100)';
1866 SET @Parm1 = CONVERT(NVARCHAR(100), CAST(@StartSampleTime AS DATETIME), 127);
1867
1868 EXECUTE sp_executesql @StringToExecute, @ParmDefinitions, @StartSampleTimeText = @Parm1;
1869
1870 RAISERROR('@CheckProcedureCache = 1, totaling up plan cache metrics',10,1) WITH NOWAIT;
1871
1872 /* Get the totals for the entire plan cache */
1873 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)
1874 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)
1875 FROM sys.dm_exec_query_stats qs;
1876
1877
1878 RAISERROR('@CheckProcedureCache = 1, so analyzing execution plans',10,1) WITH NOWAIT;
1879 /*
1880 Pick the most resource-intensive queries to review. Update the Points field
1881 in #QueryStats - if a query is in the top 10 for logical reads, CPU time,
1882 duration, or execution, add 1 to its points.
1883 */
1884 WITH qsTop AS (
1885 SELECT TOP 10 qsNow.ID
1886 FROM #QueryStats qsNow
1887 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
1888 WHERE qsNow.total_elapsed_time > qsFirst.total_elapsed_time
1889 AND qsNow.Pass = 2
1890 AND qsNow.total_elapsed_time - qsFirst.total_elapsed_time > 1000000 /* Only queries with over 1 second of runtime */
1891 ORDER BY (qsNow.total_elapsed_time - COALESCE(qsFirst.total_elapsed_time, 0)) DESC)
1892 UPDATE #QueryStats
1893 SET Points = Points + 1
1894 FROM #QueryStats qs
1895 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1896
1897 WITH qsTop AS (
1898 SELECT TOP 10 qsNow.ID
1899 FROM #QueryStats qsNow
1900 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
1901 WHERE qsNow.total_logical_reads > qsFirst.total_logical_reads
1902 AND qsNow.Pass = 2
1903 AND qsNow.total_logical_reads - qsFirst.total_logical_reads > 1000 /* Only queries with over 1000 reads */
1904 ORDER BY (qsNow.total_logical_reads - COALESCE(qsFirst.total_logical_reads, 0)) DESC)
1905 UPDATE #QueryStats
1906 SET Points = Points + 1
1907 FROM #QueryStats qs
1908 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1909
1910 WITH qsTop AS (
1911 SELECT TOP 10 qsNow.ID
1912 FROM #QueryStats qsNow
1913 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
1914 WHERE qsNow.total_worker_time > qsFirst.total_worker_time
1915 AND qsNow.Pass = 2
1916 AND qsNow.total_worker_time - qsFirst.total_worker_time > 1000000 /* Only queries with over 1 second of worker time */
1917 ORDER BY (qsNow.total_worker_time - COALESCE(qsFirst.total_worker_time, 0)) DESC)
1918 UPDATE #QueryStats
1919 SET Points = Points + 1
1920 FROM #QueryStats qs
1921 INNER JOIN qsTop ON qs.ID = qsTop.ID;
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.execution_count > qsFirst.execution_count
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 OR qsNow.total_logical_reads - qsFirst.total_logical_reads > 1000 /* Only queries with over 1000 reads */
1931 OR qsNow.total_worker_time - qsFirst.total_worker_time > 1000000 /* Only queries with over 1 second of worker time */)
1932 ORDER BY (qsNow.execution_count - COALESCE(qsFirst.execution_count, 0)) DESC)
1933 UPDATE #QueryStats
1934 SET Points = Points + 1
1935 FROM #QueryStats qs
1936 INNER JOIN qsTop ON qs.ID = qsTop.ID;
1937
1938 /* Query Stats - CheckID 17 - Most Resource-Intensive Queries */
1939 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, QueryStatsNowID, QueryStatsFirstID, PlanHandle)
1940 SELECT 17, 210, 'Query Stats', 'Most Resource-Intensive Queries', 'http://www.BrentOzar.com/go/topqueries',
1941 'Query stats during the sample:' + @LineFeed +
1942 'Executions: ' + CAST(qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)) AS NVARCHAR(100)) + @LineFeed +
1943 'Elapsed Time: ' + CAST(qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1944 'CPU Time: ' + CAST(qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1945 'Logical Reads: ' + CAST(qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)) AS NVARCHAR(100)) + @LineFeed +
1946 'Logical Writes: ' + CAST(qsNow.total_logical_writes - (COALESCE(qsFirst.total_logical_writes, 0)) AS NVARCHAR(100)) + @LineFeed +
1947 'CLR Time: ' + CAST(qsNow.total_clr_time - (COALESCE(qsFirst.total_clr_time, 0)) AS NVARCHAR(100)) + @LineFeed +
1948 @LineFeed + @LineFeed + 'Query stats since ' + CONVERT(NVARCHAR(100), qsNow.creation_time ,121) + @LineFeed +
1949 'Executions: ' + CAST(qsNow.execution_count AS NVARCHAR(100)) +
1950 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 +
1951 'Elapsed Time: ' + CAST(qsNow.total_elapsed_time AS NVARCHAR(100)) +
1952 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 +
1953 'CPU Time: ' + CAST(qsNow.total_worker_time AS NVARCHAR(100)) +
1954 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 +
1955 'Logical Reads: ' + CAST(qsNow.total_logical_reads AS NVARCHAR(100)) +
1956 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 +
1957 'Logical Writes: ' + CAST(qsNow.total_logical_writes AS NVARCHAR(100)) +
1958 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 +
1959 'CLR Time: ' + CAST(qsNow.total_clr_time AS NVARCHAR(100)) +
1960 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 +
1961 --@LineFeed + @LineFeed + 'Query hash: ' + CAST(qsNow.query_hash AS NVARCHAR(100)) + @LineFeed +
1962 --@LineFeed + @LineFeed + 'Query plan hash: ' + CAST(qsNow.query_plan_hash AS NVARCHAR(100)) +
1963 @LineFeed AS Details,
1964 'See the URL for tuning tips on why this query may be consuming resources.' AS HowToStopIt,
1965 qp.query_plan,
1966 QueryText = SUBSTRING(st.text,
1967 (qsNow.statement_start_offset / 2) + 1,
1968 ((CASE qsNow.statement_end_offset
1969 WHEN -1 THEN DATALENGTH(st.text)
1970 ELSE qsNow.statement_end_offset
1971 END - qsNow.statement_start_offset) / 2) + 1),
1972 qsNow.ID AS QueryStatsNowID,
1973 qsFirst.ID AS QueryStatsFirstID,
1974 qsNow.plan_handle AS PlanHandle
1975 FROM #QueryStats qsNow
1976 INNER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
1977 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
1978 CROSS APPLY sys.dm_exec_sql_text(qsNow.sql_handle) AS st
1979 CROSS APPLY sys.dm_exec_query_plan(qsNow.plan_handle) AS qp
1980 WHERE qsNow.Points > 0 AND st.text IS NOT NULL AND qp.query_plan IS NOT NULL
1981
1982 UPDATE #BlitzFirstResults
1983 SET DatabaseID = CAST(attr.value AS INT),
1984 DatabaseName = DB_NAME(CAST(attr.value AS INT))
1985 FROM #BlitzFirstResults
1986 CROSS APPLY sys.dm_exec_plan_attributes(#BlitzFirstResults.PlanHandle) AS attr
1987 WHERE attr.attribute = 'dbid'
1988
1989
1990 END /* IF DATEDIFF(ss, @FinishSampleTime, SYSDATETIMEOFFSET()) > 10 AND @CheckProcedureCache = 1 */
1991
1992
1993 RAISERROR('Analyzing changes between first and second passes of DMVs',10,1) WITH NOWAIT;
1994
1995 /* Wait Stats - CheckID 6 */
1996 /* Compare the current wait stats to the sample we took at the start, and insert the top 10 waits. */
1997 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DetailsInt)
1998 SELECT TOP 10 6 AS CheckID,
1999 200 AS Priority,
2000 'Wait Stats' AS FindingGroup,
2001 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. */
2002 N'http://www.brentozar.com/sql/wait-stats/#' + wNow.wait_type AS URL,
2003 '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,
2004 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2005 ((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS DetailsInt
2006 FROM #WaitStats wNow
2007 LEFT OUTER JOIN #WaitStats wBase ON wNow.wait_type = wBase.wait_type AND wNow.SampleTime > wBase.SampleTime
2008 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 */
2009 ORDER BY (wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) DESC;
2010
2011 /* Server Performance - Poison Wait Detected - CheckID 30 */
2012 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DetailsInt)
2013 SELECT 30 AS CheckID,
2014 10 AS Priority,
2015 'Server Performance' AS FindingGroup,
2016 'Poison Wait Detected: ' + wNow.wait_type AS Finding,
2017 N'http://www.brentozar.com/go/poison/#' + wNow.wait_type AS URL,
2018 '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,
2019 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2020 ((wNow.wait_time_ms - COALESCE(wBase.wait_time_ms,0)) / 1000) AS DetailsInt
2021 FROM #WaitStats wNow
2022 LEFT OUTER JOIN #WaitStats wBase ON wNow.wait_type = wBase.wait_type AND wNow.SampleTime > wBase.SampleTime
2023 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;
2024
2025
2026 /* Server Performance - Slow Data File Reads - CheckID 11 */
2027 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE Finding LIKE 'PAGEIOLATCH%')
2028 BEGIN
2029 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DatabaseID, DatabaseName)
2030 SELECT TOP 10 11 AS CheckID,
2031 50 AS Priority,
2032 'Server Performance' AS FindingGroup,
2033 'Slow Data File Reads' AS Finding,
2034 'http://www.BrentOzar.com/go/slow/' AS URL,
2035 'Your server is experiencing PAGEIOLATCH% waits due to slow data file reads. This file is one of the reasons why.' + @LineFeed
2036 + 'File: ' + fNow.PhysicalName + @LineFeed
2037 + 'Number of reads during the sample: ' + CAST((fNow.num_of_reads - fBase.num_of_reads) AS NVARCHAR(20)) + @LineFeed
2038 + '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
2039 + '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
2040 + 'Microsoft guidance for data file read speed: 20ms or less.' + @LineFeed + @LineFeed AS Details,
2041 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2042 fNow.DatabaseID,
2043 fNow.DatabaseName
2044 FROM #FileStats fNow
2045 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)
2046 WHERE (fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads) >= @FileLatencyThresholdMS
2047 AND fNow.TypeDesc = 'ROWS'
2048 ORDER BY (fNow.io_stall_read_ms - fBase.io_stall_read_ms) / (fNow.num_of_reads - fBase.num_of_reads) DESC;
2049 END
2050
2051 /* Server Performance - Slow Log File Writes - CheckID 12 */
2052 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE Finding LIKE 'WRITELOG%')
2053 BEGIN
2054 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, DatabaseID, DatabaseName)
2055 SELECT TOP 10 12 AS CheckID,
2056 50 AS Priority,
2057 'Server Performance' AS FindingGroup,
2058 'Slow Log File Writes' AS Finding,
2059 'http://www.BrentOzar.com/go/slow/' AS URL,
2060 'Your server is experiencing WRITELOG waits due to slow log file writes. This file is one of the reasons why.' + @LineFeed
2061 + 'File: ' + fNow.PhysicalName + @LineFeed
2062 + 'Number of writes during the sample: ' + CAST((fNow.num_of_writes - fBase.num_of_writes) AS NVARCHAR(20)) + @LineFeed
2063 + '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
2064 + '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
2065 + 'Microsoft guidance for log file write speed: 3ms or less.' + @LineFeed + @LineFeed AS Details,
2066 'See the URL for more details on how to mitigate this wait type.' AS HowToStopIt,
2067 fNow.DatabaseID,
2068 fNow.DatabaseName
2069 FROM #FileStats fNow
2070 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)
2071 WHERE (fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes) >= @FileLatencyThresholdMS
2072 AND fNow.TypeDesc = 'LOG'
2073 ORDER BY (fNow.io_stall_write_ms - fBase.io_stall_write_ms) / (fNow.num_of_writes - fBase.num_of_writes) DESC;
2074 END
2075
2076
2077 /* SQL Server Internal Maintenance - Log File Growing - CheckID 13 */
2078 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2079 SELECT 13 AS CheckID,
2080 1 AS Priority,
2081 'SQL Server Internal Maintenance' AS FindingGroup,
2082 'Log File Growing' AS Finding,
2083 'http://www.BrentOzar.com/askbrent/file-growing/' AS URL,
2084 'Number of growths during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2085 + 'Determined by sampling Perfmon counter ' + ps.object_name + ' - ' + ps.counter_name + @LineFeed AS Details,
2086 '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
2087 FROM #PerfmonStats ps
2088 WHERE ps.Pass = 2
2089 AND object_name = @ServiceName + ':Databases'
2090 AND counter_name = 'Log Growths'
2091 AND value_delta > 0
2092
2093
2094 /* SQL Server Internal Maintenance - Log File Shrinking - CheckID 14 */
2095 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2096 SELECT 14 AS CheckID,
2097 1 AS Priority,
2098 'SQL Server Internal Maintenance' AS FindingGroup,
2099 'Log File Shrinking' AS Finding,
2100 'http://www.BrentOzar.com/askbrent/file-shrinking/' AS URL,
2101 'Number of shrinks during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2102 + 'Determined by sampling Perfmon counter ' + ps.object_name + ' - ' + ps.counter_name + @LineFeed AS Details,
2103 '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
2104 FROM #PerfmonStats ps
2105 WHERE ps.Pass = 2
2106 AND object_name = @ServiceName + ':Databases'
2107 AND counter_name = 'Log Shrinks'
2108 AND value_delta > 0
2109
2110 /* Query Problems - Compilations/Sec High - CheckID 15 */
2111 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2112 SELECT 15 AS CheckID,
2113 50 AS Priority,
2114 'Query Problems' AS FindingGroup,
2115 'Compilations/Sec High' AS Finding,
2116 'http://www.BrentOzar.com/askbrent/compilations/' AS URL,
2117 'Number of batch requests during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2118 + 'Number of compilations during the sample: ' + CAST(psComp.value_delta AS NVARCHAR(20)) + @LineFeed
2119 + '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,
2120 'To find the queries that are compiling, start with:' + @LineFeed
2121 + 'sp_BlitzCache @SortOrder = ''recent compilations''' + @LineFeed
2122 + 'If dynamic SQL or non-parameterized strings are involved, consider enabling Forced Parameterization. See the URL for more details.' AS HowToStopIt
2123 FROM #PerfmonStats ps
2124 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
2125 WHERE ps.Pass = 2
2126 AND ps.object_name = @ServiceName + ':SQL Statistics'
2127 AND ps.counter_name = 'Batch Requests/sec'
2128 AND ps.value_delta > (1000 * @Seconds) /* Ignore servers sitting idle */
2129 AND (psComp.value_delta * 10) > ps.value_delta /* Compilations are more than 10% of batch requests per second */
2130
2131 /* Query Problems - Re-Compilations/Sec High - CheckID 16 */
2132 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2133 SELECT 16 AS CheckID,
2134 50 AS Priority,
2135 'Query Problems' AS FindingGroup,
2136 'Re-Compilations/Sec High' AS Finding,
2137 'http://www.BrentOzar.com/askbrent/recompilations/' AS URL,
2138 'Number of batch requests during the sample: ' + CAST(ps.value_delta AS NVARCHAR(20)) + @LineFeed
2139 + 'Number of recompilations during the sample: ' + CAST(psComp.value_delta AS NVARCHAR(20)) + @LineFeed
2140 + 'More than 10% of our queries are being recompiled. This is typically due to statistics changing on objects.' + @LineFeed AS Details,
2141 'To find the queries that are being forced to recompile, start with:' + @LineFeed
2142 + 'sp_BlitzCache @SortOrder = ''recent compilations''' + @LineFeed
2143 + '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
2144 FROM #PerfmonStats ps
2145 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
2146 WHERE ps.Pass = 2
2147 AND ps.object_name = @ServiceName + ':SQL Statistics'
2148 AND ps.counter_name = 'Batch Requests/sec'
2149 AND ps.value_delta > (1000 * @Seconds) /* Ignore servers sitting idle */
2150 AND (psComp.value_delta * 10) > ps.value_delta /* Recompilations are more than 10% of batch requests per second */
2151
2152 /* Table Problems - Forwarded Fetches/Sec High - CheckID 29 */
2153 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2154 SELECT 29 AS CheckID,
2155 40 AS Priority,
2156 'Table Problems' AS FindingGroup,
2157 'Forwarded Fetches/Sec High' AS Finding,
2158 'https://BrentOzar.com/go/fetch/' AS URL,
2159 CAST(ps.value_delta AS NVARCHAR(20)) + ' Forwarded Records (from SQLServer:Access Methods counter)' + @LineFeed
2160 + 'Check your heaps: they need to be rebuilt, or they need a clustered index applied.' + @LineFeed AS Details,
2161 '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
2162 FROM #PerfmonStats ps
2163 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
2164 WHERE ps.Pass = 2
2165 AND ps.object_name = @ServiceName + ':Access Methods'
2166 AND ps.counter_name = 'Forwarded Records/sec'
2167 AND ps.value_delta > (100 * @Seconds) /* Ignore servers sitting idle */
2168
2169
2170 /* In-Memory OLTP - Garbage Collection in Progress - CheckID 31 */
2171 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2172 SELECT 31 AS CheckID,
2173 50 AS Priority,
2174 'In-Memory OLTP' AS FindingGroup,
2175 'Garbage Collection in Progress' AS Finding,
2176 'https://BrentOzar.com/go/garbage/' AS URL,
2177 CAST(ps.value_delta AS NVARCHAR(50)) + ' rows processed (from SQL Server YYYY XTP Garbage Collection:Rows processed/sec counter)' + @LineFeed
2178 + 'This can happen due to memory pressure (causing In-Memory OLTP to shrink its footprint) or' + @LineFeed
2179 + 'due to transactional workloads that constantly insert/delete data.' AS Details,
2180 '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
2181 FROM #PerfmonStats ps
2182 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
2183 WHERE ps.Pass = 2
2184 AND ps.object_name LIKE '%XTP Garbage Collection'
2185 AND ps.counter_name = 'Rows processed/sec'
2186 AND ps.value_delta > (100 * @Seconds) /* Ignore servers sitting idle */
2187
2188 /* In-Memory OLTP - Transactions Aborted - CheckID 32 */
2189 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2190 SELECT 32 AS CheckID,
2191 100 AS Priority,
2192 'In-Memory OLTP' AS FindingGroup,
2193 'Transactions Aborted' AS Finding,
2194 'https://BrentOzar.com/go/aborted/' AS URL,
2195 CAST(ps.value_delta AS NVARCHAR(50)) + ' transactions aborted (from SQL Server YYYY XTP Transactions:Transactions aborted/sec counter)' + @LineFeed
2196 + 'This may indicate that data is changing, or causing folks to retry their transactions, thereby increasing load.' AS Details,
2197 'Dig into your In-Memory OLTP transactions to figure out which ones are failing and being retried.' AS HowToStopIt
2198 FROM #PerfmonStats ps
2199 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
2200 WHERE ps.Pass = 2
2201 AND ps.object_name LIKE '%XTP Transactions'
2202 AND ps.counter_name = 'Transactions aborted/sec'
2203 AND ps.value_delta > (10 * @Seconds) /* Ignore servers sitting idle */
2204
2205 /* Query Problems - Suboptimal Plans/Sec High - CheckID 33 */
2206 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt)
2207 SELECT 32 AS CheckID,
2208 100 AS Priority,
2209 'Query Problems' AS FindingGroup,
2210 'Suboptimal Plans/Sec High' AS Finding,
2211 'https://BrentOzar.com/go/suboptimal/' AS URL,
2212 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
2213 + 'Even if you are not using Resource Governor, it still tracks information about user queries, memory grants, etc.' AS Details,
2214 'Check out sp_BlitzCache to get more information about recent queries, or try sp_BlitzWho to see currently running queries.' AS HowToStopIt
2215 FROM #PerfmonStats ps
2216 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
2217 WHERE ps.Pass = 2
2218 AND ps.object_name = @ServiceName + ':Workload GroupStats'
2219 AND ps.counter_name = 'Suboptimal plans/sec'
2220 AND ps.value_delta > (10 * @Seconds) /* Ignore servers sitting idle */
2221
2222
2223
2224 /* Server Info - Batch Requests per Sec - CheckID 19 */
2225 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2226 SELECT 19 AS CheckID,
2227 250 AS Priority,
2228 'Server Info' AS FindingGroup,
2229 'Batch Requests per Sec' AS Finding,
2230 'http://www.BrentOzar.com/go/measure' AS URL,
2231 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2232 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2233 FROM #PerfmonStats ps
2234 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2235 WHERE ps.Pass = 2
2236 AND ps.object_name = @ServiceName + ':SQL Statistics'
2237 AND ps.counter_name = 'Batch Requests/sec';
2238
2239
2240 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Compilations/sec', NULL)
2241 INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':SQL Statistics','SQL Re-Compilations/sec', NULL)
2242
2243 /* Server Info - SQL Compilations/sec - CheckID 25 */
2244 IF @ExpertMode = 1
2245 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2246 SELECT 25 AS CheckID,
2247 250 AS Priority,
2248 'Server Info' AS FindingGroup,
2249 'SQL Compilations per Sec' AS Finding,
2250 'http://www.BrentOzar.com/go/measure' AS URL,
2251 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2252 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2253 FROM #PerfmonStats ps
2254 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2255 WHERE ps.Pass = 2
2256 AND ps.object_name = @ServiceName + ':SQL Statistics'
2257 AND ps.counter_name = 'SQL Compilations/sec';
2258
2259 /* Server Info - SQL Re-Compilations/sec - CheckID 26 */
2260 IF @ExpertMode = 1
2261 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2262 SELECT 26 AS CheckID,
2263 250 AS Priority,
2264 'Server Info' AS FindingGroup,
2265 'SQL Re-Compilations per Sec' AS Finding,
2266 'http://www.BrentOzar.com/go/measure' AS URL,
2267 CAST(ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS NVARCHAR(20)) AS Details,
2268 ps.value_delta / (DATEDIFF(ss, ps1.SampleTime, ps.SampleTime)) AS DetailsInt
2269 FROM #PerfmonStats ps
2270 INNER JOIN #PerfmonStats ps1 ON ps.object_name = ps1.object_name AND ps.counter_name = ps1.counter_name AND ps1.Pass = 1
2271 WHERE ps.Pass = 2
2272 AND ps.object_name = @ServiceName + ':SQL Statistics'
2273 AND ps.counter_name = 'SQL Re-Compilations/sec';
2274
2275 /* Server Info - Wait Time per Core per Sec - CheckID 20 */
2276 IF @Seconds > 0
2277 BEGIN
2278 WITH waits1(SampleTime, waits_ms) AS (SELECT SampleTime, SUM(ws1.wait_time_ms) FROM #WaitStats ws1 WHERE ws1.Pass = 1 GROUP BY SampleTime),
2279 waits2(SampleTime, waits_ms) AS (SELECT SampleTime, SUM(ws2.wait_time_ms) FROM #WaitStats ws2 WHERE ws2.Pass = 2 GROUP BY SampleTime),
2280 cores(cpu_count) AS (SELECT SUM(1) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1)
2281 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, URL, Details, DetailsInt)
2282 SELECT 19 AS CheckID,
2283 250 AS Priority,
2284 'Server Info' AS FindingGroup,
2285 'Wait Time per Core per Sec' AS Finding,
2286 'http://www.BrentOzar.com/go/measure' AS URL,
2287 CAST((waits2.waits_ms - waits1.waits_ms) / 1000 / i.cpu_count / DATEDIFF(ss, waits1.SampleTime, waits2.SampleTime) AS NVARCHAR(20)) AS Details,
2288 (waits2.waits_ms - waits1.waits_ms) / 1000 / i.cpu_count / DATEDIFF(ss, waits1.SampleTime, waits2.SampleTime) AS DetailsInt
2289 FROM cores i
2290 CROSS JOIN waits1
2291 CROSS JOIN waits2;
2292 END
2293
2294 /* Server Performance - High CPU Utilization CheckID 24 */
2295 IF @Seconds >= 30
2296 BEGIN
2297 /* If we're waiting 30+ seconds, run this check at the end.
2298 We get this data from the ring buffers, and it's only updated once per minute, so might
2299 as well get it now - whereas if we're checking 30+ seconds, it might get updated by the
2300 end of our sp_BlitzFirst session. */
2301 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
2302 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'
2303 FROM (
2304 SELECT record,
2305 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
2306 FROM (
2307 SELECT TOP 1 CONVERT(XML, record) AS record
2308 FROM sys.dm_os_ring_buffers
2309 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
2310 AND record LIKE '%<SystemHealth>%'
2311 ORDER BY timestamp DESC) AS rb
2312 ) AS y
2313 WHERE 100 - SystemIdle >= 50
2314
2315 INSERT INTO #BlitzFirstResults (CheckID, Priority, FindingsGroup, Finding, Details, DetailsInt, URL)
2316 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'
2317 FROM (
2318 SELECT record,
2319 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
2320 FROM (
2321 SELECT TOP 1 CONVERT(XML, record) AS record
2322 FROM sys.dm_os_ring_buffers
2323 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
2324 AND record LIKE '%<SystemHealth>%'
2325 ORDER BY timestamp DESC) AS rb
2326 ) AS y
2327
2328 END /* IF @Seconds < 30 */
2329
2330
2331 /* If we didn't find anything, apologize. */
2332 IF NOT EXISTS (SELECT * FROM #BlitzFirstResults WHERE Priority < 250)
2333 BEGIN
2334
2335 INSERT INTO #BlitzFirstResults
2336 ( CheckID ,
2337 Priority ,
2338 FindingsGroup ,
2339 Finding ,
2340 URL ,
2341 Details
2342 )
2343 VALUES ( -1 ,
2344 1 ,
2345 'No Problems Found' ,
2346 'From Your Community Volunteers' ,
2347 'http://FirstResponderKit.org/' ,
2348 'Try running our more in-depth checks with sp_Blitz, or there may not be an unusual SQL Server performance problem. '
2349 );
2350
2351 END /*IF NOT EXISTS (SELECT * FROM #BlitzFirstResults) */
2352
2353 /* Add credits for the nice folks who put so much time into building and maintaining this for free: */
2354 INSERT INTO #BlitzFirstResults
2355 ( CheckID ,
2356 Priority ,
2357 FindingsGroup ,
2358 Finding ,
2359 URL ,
2360 Details
2361 )
2362 VALUES ( -1 ,
2363 255 ,
2364 'Thanks!' ,
2365 'From Your Community Volunteers' ,
2366 'http://FirstResponderKit.org/' ,
2367 'To get help or add your own contributions, join us at http://FirstResponderKit.org.'
2368 );
2369
2370 INSERT INTO #BlitzFirstResults
2371 ( CheckID ,
2372 Priority ,
2373 FindingsGroup ,
2374 Finding ,
2375 URL ,
2376 Details
2377
2378 )
2379 VALUES ( -1 ,
2380 0 ,
2381 'sp_BlitzFirst ' + CAST(CONVERT(DATETIMEOFFSET, @VersionDate, 102) AS VARCHAR(100)),
2382 'From Your Community Volunteers' ,
2383 'http://FirstResponderKit.org/' ,
2384 'We hope you found this tool useful.'
2385 );
2386
2387 /* Outdated sp_BlitzFirst - sp_BlitzFirst is Over 6 Months Old */
2388 IF DATEDIFF(MM, @VersionDate, SYSDATETIMEOFFSET()) > 6
2389 BEGIN
2390 INSERT INTO #BlitzFirstResults
2391 ( CheckID ,
2392 Priority ,
2393 FindingsGroup ,
2394 Finding ,
2395 URL ,
2396 Details
2397 )
2398 SELECT 27 AS CheckID ,
2399 0 AS Priority ,
2400 'Outdated sp_BlitzFirst' AS FindingsGroup ,
2401 'sp_BlitzFirst is Over 6 Months Old' AS Finding ,
2402 'http://FirstResponderKit.org/' AS URL ,
2403 '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
2404 END
2405
2406 RAISERROR('Analysis finished, outputting results',10,1) WITH NOWAIT;
2407
2408
2409 /* If they want to run sp_BlitzCache and export to table, go for it. */
2410 IF @OutputTableNameBlitzCache IS NOT NULL
2411 AND @OutputDatabaseName IS NOT NULL
2412 AND @OutputSchemaName IS NOT NULL
2413 AND EXISTS ( SELECT *
2414 FROM sys.databases
2415 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2416 BEGIN
2417 RAISERROR('Calling sp_BlitzCache',10,1) WITH NOWAIT;
2418
2419 /* Set the sp_BlitzCache sort order based on their top wait type */
2420
2421 /* First, check for poison waits - CheckID 30 */
2422 IF EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 30)
2423 BEGIN
2424 SELECT TOP 1 @BlitzCacheSortOrder = CASE
2425 WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE' THEN 'memory grant'
2426 WHEN Finding = 'Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE' THEN 'memory grant'
2427 WHEN Finding = 'Poison Wait Detected: THREADPOOL' THEN 'executions'
2428 WHEN Finding = 'Poison Wait Detected: LOG_RATE_GOVERNOR' THEN 'writes'
2429 WHEN Finding = 'Poison Wait Detected: SE_REPL_CATCHUP_THROTTLE' THEN 'writes'
2430 WHEN Finding = 'Poison Wait Detected: SE_REPL_COMMIT_ACK' THEN 'writes'
2431 WHEN Finding = 'Poison Wait Detected: SE_REPL_ROLLBACK_ACK' THEN 'writes'
2432 WHEN Finding = 'Poison Wait Detected: SE_REPL_SLOW_SECONDARY_THROTTLE' THEN 'writes'
2433 ELSE NULL
2434 END
2435 FROM #BlitzFirstResults
2436 WHERE CheckID = 30
2437 ORDER BY DetailsInt DESC;
2438 END
2439
2440 /* Too much free memory - which probably indicates queries finished w/huge grants - CheckID 34 */
2441 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 34)
2442 SET @BlitzCacheSortOrder = 'memory grant';
2443
2444 /* Next, Compilations/Sec High - CheckID 15 and 16 */
2445 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID IN (15,16))
2446 SET @BlitzCacheSortOrder = 'compilations';
2447
2448 /* Still not set? Use the top wait type. */
2449 IF @BlitzCacheSortOrder IS NULL AND EXISTS (SELECT * FROM #BlitzFirstResults WHERE CheckID = 6)
2450 BEGIN
2451 SELECT TOP 1 @BlitzCacheSortOrder = CASE
2452 WHEN Finding = 'ASYNC_NETWORK_IO' THEN 'duration'
2453 WHEN Finding = 'CXPACKET' THEN 'reads'
2454 WHEN Finding = 'LATCH_EX' THEN 'reads'
2455 WHEN Finding LIKE 'LCK%' THEN 'duration'
2456 WHEN Finding LIKE 'PAGEIOLATCH%' THEN 'reads'
2457 WHEN Finding = 'SOS_SCHEDULER_YIELD' THEN 'cpu'
2458 WHEN Finding = 'WRITELOG' THEN 'writes'
2459 ELSE NULL
2460 END
2461 FROM #BlitzFirstResults
2462 WHERE CheckID = 6
2463 ORDER BY DetailsInt DESC;
2464 END
2465 /* Still null? Just use the default. */
2466
2467
2468
2469 /* If they have an newer version of sp_BlitzCache that supports @MinutesBack and @CheckDateOverride */
2470 IF EXISTS (SELECT * FROM sys.objects o
2471 INNER JOIN sys.parameters pMB ON o.object_id = pMB.object_id AND pMB.name = '@MinutesBack'
2472 INNER JOIN sys.parameters pCDO ON o.object_id = pCDO.object_id AND pCDO.name = '@CheckDateOverride'
2473 WHERE o.name = 'sp_BlitzCache')
2474 BEGIN
2475 /* 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 */
2476 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2477 + @OutputDatabaseName
2478 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2479 + @OutputSchemaName + ''') SELECT TOP 1 @BlitzCacheMinutesBack = DATEDIFF(MI,CheckDate,SYSDATETIMEOFFSET()) FROM '
2480 + @OutputDatabaseName + '.'
2481 + @OutputSchemaName + '.'
2482 + @OutputTableNameBlitzCache
2483 + ' WHERE ServerName = ''' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) + ''' ORDER BY CheckDate DESC;';
2484 EXEC sp_executesql @StringToExecute, N'@BlitzCacheMinutesBack INT OUTPUT', @BlitzCacheMinutesBack OUTPUT;
2485
2486 /* If there's no data, let's just analyze the last 15 minutes of the plan cache */
2487 IF @BlitzCacheMinutesBack IS NULL OR @BlitzCacheMinutesBack < 1 OR @BlitzCacheMinutesBack > 60
2488 SET @BlitzCacheMinutesBack = 15;
2489
2490 IF @BlitzCacheSortOrder IS NOT NULL
2491 EXEC sp_BlitzCache
2492 @OutputDatabaseName = @UnquotedOutputDatabaseName,
2493 @OutputSchemaName = @UnquotedOutputSchemaName,
2494 @OutputTableName = @OutputTableNameBlitzCache,
2495 @CheckDateOverride = @StartSampleTime,
2496 @SortOrder = @BlitzCacheSortOrder,
2497 @MinutesBack = @BlitzCacheMinutesBack,
2498 @Debug = @Debug;
2499 ELSE
2500 EXEC sp_BlitzCache
2501 @OutputDatabaseName = @UnquotedOutputDatabaseName,
2502 @OutputSchemaName = @UnquotedOutputSchemaName,
2503 @OutputTableName = @OutputTableNameBlitzCache,
2504 @CheckDateOverride = @StartSampleTime,
2505 @MinutesBack = @BlitzCacheMinutesBack,
2506 @Debug = @Debug;
2507
2508 /* Delete history older than @OutputTableRetentionDays */
2509 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2510 + @OutputDatabaseName
2511 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2512 + @OutputSchemaName + ''') DELETE '
2513 + @OutputDatabaseName + '.'
2514 + @OutputSchemaName + '.'
2515 + @OutputTableNameBlitzCache
2516 + ' WHERE ServerName = '''
2517 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2518 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2519 EXEC(@StringToExecute);
2520
2521
2522 END
2523
2524 ELSE /* No sp_BlitzCache found, or it's outdated */
2525 BEGIN
2526 INSERT INTO #BlitzFirstResults
2527 ( CheckID ,
2528 Priority ,
2529 FindingsGroup ,
2530 Finding ,
2531 URL ,
2532 Details
2533 )
2534 SELECT 36 AS CheckID ,
2535 0 AS Priority ,
2536 'Outdated or Missing sp_BlitzCache' AS FindingsGroup ,
2537 'Update Your sp_BlitzCache' AS Finding ,
2538 'http://FirstResponderKit.org/' AS URL ,
2539 'You passed in @OutputTableNameBlitzCache, but we need a newer version of sp_BlitzCache in master or the current database.' AS Details
2540 END
2541
2542 RAISERROR('sp_BlitzCache Finished',10,1) WITH NOWAIT;
2543
2544 END /* End running sp_BlitzCache */
2545
2546 /* @OutputTableName lets us export the results to a permanent table */
2547 IF @OutputDatabaseName IS NOT NULL
2548 AND @OutputSchemaName IS NOT NULL
2549 AND @OutputTableName IS NOT NULL
2550 AND @OutputTableName NOT LIKE '#%'
2551 AND EXISTS ( SELECT *
2552 FROM sys.databases
2553 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2554 BEGIN
2555 SET @StringToExecute = 'USE '
2556 + @OutputDatabaseName
2557 + '; IF EXISTS(SELECT * FROM '
2558 + @OutputDatabaseName
2559 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2560 + @OutputSchemaName
2561 + ''') AND NOT EXISTS (SELECT * FROM '
2562 + @OutputDatabaseName
2563 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2564 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2565 + @OutputTableName + ''') CREATE TABLE '
2566 + @OutputSchemaName + '.'
2567 + @OutputTableName
2568 + ' (ID INT IDENTITY(1,1) NOT NULL,
2569 ServerName NVARCHAR(128),
2570 CheckDate DATETIMEOFFSET,
2571 CheckID INT NOT NULL,
2572 Priority TINYINT NOT NULL,
2573 FindingsGroup VARCHAR(50) NOT NULL,
2574 Finding VARCHAR(200) NOT NULL,
2575 URL VARCHAR(200) NOT NULL,
2576 Details NVARCHAR(4000) NULL,
2577 HowToStopIt [XML] NULL,
2578 QueryPlan [XML] NULL,
2579 QueryText NVARCHAR(MAX) NULL,
2580 StartTime DATETIMEOFFSET NULL,
2581 LoginName NVARCHAR(128) NULL,
2582 NTUserName NVARCHAR(128) NULL,
2583 OriginalLoginName NVARCHAR(128) NULL,
2584 ProgramName NVARCHAR(128) NULL,
2585 HostName NVARCHAR(128) NULL,
2586 DatabaseID INT NULL,
2587 DatabaseName NVARCHAR(128) NULL,
2588 OpenTransactionCount INT NULL,
2589 DetailsInt INT NULL,
2590 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2591
2592 EXEC(@StringToExecute);
2593 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2594 + @OutputDatabaseName
2595 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2596 + @OutputSchemaName + ''') INSERT '
2597 + @OutputDatabaseName + '.'
2598 + @OutputSchemaName + '.'
2599 + @OutputTableName
2600 + ' (ServerName, CheckDate, CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt) SELECT '''
2601 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2602 + ''', ''' + (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';
2603 EXEC(@StringToExecute);
2604
2605 /* Delete history older than @OutputTableRetentionDays */
2606 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2607 + @OutputDatabaseName
2608 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2609 + @OutputSchemaName + ''') DELETE '
2610 + @OutputDatabaseName + '.'
2611 + @OutputSchemaName + '.'
2612 + @OutputTableName
2613 + ' WHERE ServerName = '''
2614 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2615 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2616 EXEC(@StringToExecute);
2617
2618
2619 END
2620 ELSE IF (SUBSTRING(@OutputTableName, 2, 2) = '##')
2621 BEGIN
2622 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2623 + @OutputTableName
2624 + ''') IS NULL) CREATE TABLE '
2625 + @OutputTableName
2626 + ' (ID INT IDENTITY(1,1) NOT NULL,
2627 ServerName NVARCHAR(128),
2628 CheckDate DATETIMEOFFSET,
2629 CheckID INT NOT NULL,
2630 Priority TINYINT NOT NULL,
2631 FindingsGroup VARCHAR(50) NOT NULL,
2632 Finding VARCHAR(200) NOT NULL,
2633 URL VARCHAR(200) NOT NULL,
2634 Details NVARCHAR(4000) NULL,
2635 HowToStopIt [XML] NULL,
2636 QueryPlan [XML] NULL,
2637 QueryText NVARCHAR(MAX) NULL,
2638 StartTime DATETIMEOFFSET NULL,
2639 LoginName NVARCHAR(128) NULL,
2640 NTUserName NVARCHAR(128) NULL,
2641 OriginalLoginName NVARCHAR(128) NULL,
2642 ProgramName NVARCHAR(128) NULL,
2643 HostName NVARCHAR(128) NULL,
2644 DatabaseID INT NULL,
2645 DatabaseName NVARCHAR(128) NULL,
2646 OpenTransactionCount INT NULL,
2647 DetailsInt INT NULL,
2648 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2649 + ' INSERT '
2650 + @OutputTableName
2651 + ' (ServerName, CheckDate, CheckID, Priority, FindingsGroup, Finding, URL, Details, HowToStopIt, QueryPlan, QueryText, StartTime, LoginName, NTUserName, OriginalLoginName, ProgramName, HostName, DatabaseID, DatabaseName, OpenTransactionCount, DetailsInt) SELECT '''
2652 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2653 + ''', ''' + 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';
2654 EXEC(@StringToExecute);
2655 END
2656 ELSE IF (SUBSTRING(@OutputTableName, 2, 1) = '#')
2657 BEGIN
2658 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0)
2659 END
2660
2661 /* @OutputTableNameFileStats lets us export the results to a permanent table */
2662 IF @OutputDatabaseName IS NOT NULL
2663 AND @OutputSchemaName IS NOT NULL
2664 AND @OutputTableNameFileStats IS NOT NULL
2665 AND @OutputTableNameFileStats NOT LIKE '#%'
2666 AND EXISTS ( SELECT *
2667 FROM sys.databases
2668 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2669 BEGIN
2670 /* Create the table */
2671 SET @StringToExecute = 'USE '
2672 + @OutputDatabaseName
2673 + '; IF EXISTS(SELECT * FROM '
2674 + @OutputDatabaseName
2675 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2676 + @OutputSchemaName
2677 + ''') AND NOT EXISTS (SELECT * FROM '
2678 + @OutputDatabaseName
2679 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2680 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2681 + @OutputTableNameFileStats + ''') CREATE TABLE '
2682 + @OutputSchemaName + '.'
2683 + @OutputTableNameFileStats
2684 + ' (ID INT IDENTITY(1,1) NOT NULL,
2685 ServerName NVARCHAR(128),
2686 CheckDate DATETIMEOFFSET,
2687 DatabaseID INT NOT NULL,
2688 FileID INT NOT NULL,
2689 DatabaseName NVARCHAR(256) ,
2690 FileLogicalName NVARCHAR(256) ,
2691 TypeDesc NVARCHAR(60) ,
2692 SizeOnDiskMB BIGINT ,
2693 io_stall_read_ms BIGINT ,
2694 num_of_reads BIGINT ,
2695 bytes_read BIGINT ,
2696 io_stall_write_ms BIGINT ,
2697 num_of_writes BIGINT ,
2698 bytes_written BIGINT,
2699 PhysicalName NVARCHAR(520) ,
2700 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2701 EXEC(@StringToExecute);
2702
2703 /* Create the view */
2704 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameFileStats_View;
2705 IF OBJECT_ID(@ObjectFullName) IS NULL
2706 BEGIN
2707 SET @StringToExecute = 'USE '
2708 + @OutputDatabaseName
2709 + '; EXEC (''CREATE VIEW '
2710 + @OutputSchemaName + '.'
2711 + @OutputTableNameFileStats_View + ' AS ' + @LineFeed
2712 + 'SELECT f.ServerName, f.CheckDate, f.DatabaseID, f.DatabaseName, f.FileID, f.FileLogicalName, f.TypeDesc, f.PhysicalName, f.SizeOnDiskMB' + @LineFeed
2713 + ', DATEDIFF(ss, fPrior.CheckDate, f.CheckDate) AS ElapsedSeconds' + @LineFeed
2714 + ', (f.SizeOnDiskMB - fPrior.SizeOnDiskMB) AS SizeOnDiskMBgrowth' + @LineFeed
2715 + ', (f.io_stall_read_ms - fPrior.io_stall_read_ms) AS io_stall_read_ms' + @LineFeed
2716 + ', 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
2717 + ', (f.num_of_reads - fPrior.num_of_reads) AS num_of_reads' + @LineFeed
2718 + ', (f.bytes_read - fPrior.bytes_read) / 1024.0 / 1024.0 AS megabytes_read' + @LineFeed
2719 + ', (f.io_stall_write_ms - fPrior.io_stall_write_ms) AS io_stall_write_ms' + @LineFeed
2720 + ', 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
2721 + ', (f.num_of_writes - fPrior.num_of_writes) AS num_of_writes' + @LineFeed
2722 + ', (f.bytes_written - fPrior.bytes_written) / 1024.0 / 1024.0 AS megabytes_written' + @LineFeed
2723 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNameFileStats + ' f' + @LineFeed
2724 + '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
2725 + '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
2726 + 'WHERE fMiddle.ID IS NULL AND f.num_of_reads >= fPrior.num_of_reads AND f.num_of_writes >= fPrior.num_of_writes
2727 AND DATEDIFF(MI, fPrior.CheckDate, f.CheckDate) BETWEEN 1 AND 60;'')'
2728 EXEC(@StringToExecute);
2729 END
2730
2731 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2732 + @OutputDatabaseName
2733 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2734 + @OutputSchemaName + ''') INSERT '
2735 + @OutputDatabaseName + '.'
2736 + @OutputSchemaName + '.'
2737 + @OutputTableNameFileStats
2738 + ' (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 '''
2739 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2740 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2741 + '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';
2742 EXEC(@StringToExecute);
2743
2744 /* Delete history older than @OutputTableRetentionDays */
2745 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2746 + @OutputDatabaseName
2747 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2748 + @OutputSchemaName + ''') DELETE '
2749 + @OutputDatabaseName + '.'
2750 + @OutputSchemaName + '.'
2751 + @OutputTableNameFileStats
2752 + ' WHERE ServerName = '''
2753 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2754 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2755 EXEC(@StringToExecute);
2756
2757 END
2758 ELSE IF (SUBSTRING(@OutputTableNameFileStats, 2, 2) = '##')
2759 BEGIN
2760 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2761 + @OutputTableNameFileStats
2762 + ''') IS NULL) CREATE TABLE '
2763 + @OutputTableNameFileStats
2764 + ' (ID INT IDENTITY(1,1) NOT NULL,
2765 ServerName NVARCHAR(128),
2766 CheckDate DATETIMEOFFSET,
2767 DatabaseID INT NOT NULL,
2768 FileID INT NOT NULL,
2769 DatabaseName NVARCHAR(256) ,
2770 FileLogicalName NVARCHAR(256) ,
2771 TypeDesc NVARCHAR(60) ,
2772 SizeOnDiskMB BIGINT ,
2773 io_stall_read_ms BIGINT ,
2774 num_of_reads BIGINT ,
2775 bytes_read BIGINT ,
2776 io_stall_write_ms BIGINT ,
2777 num_of_writes BIGINT ,
2778 bytes_written BIGINT,
2779 PhysicalName NVARCHAR(520) ,
2780 DetailsInt INT NULL,
2781 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2782 + ' INSERT '
2783 + @OutputTableNameFileStats
2784 + ' (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 '''
2785 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2786 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2787 + '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';
2788 EXEC(@StringToExecute);
2789 END
2790 ELSE IF (SUBSTRING(@OutputTableNameFileStats, 2, 1) = '#')
2791 BEGIN
2792 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0)
2793 END
2794
2795
2796 /* @OutputTableNamePerfmonStats lets us export the results to a permanent table */
2797 IF @OutputDatabaseName IS NOT NULL
2798 AND @OutputSchemaName IS NOT NULL
2799 AND @OutputTableNamePerfmonStats IS NOT NULL
2800 AND @OutputTableNamePerfmonStats NOT LIKE '#%'
2801 AND EXISTS ( SELECT *
2802 FROM sys.databases
2803 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2804 BEGIN
2805 /* Create the table */
2806 SET @StringToExecute = 'USE '
2807 + @OutputDatabaseName
2808 + '; IF EXISTS(SELECT * FROM '
2809 + @OutputDatabaseName
2810 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2811 + @OutputSchemaName
2812 + ''') AND NOT EXISTS (SELECT * FROM '
2813 + @OutputDatabaseName
2814 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2815 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2816 + @OutputTableNamePerfmonStats + ''') CREATE TABLE '
2817 + @OutputSchemaName + '.'
2818 + @OutputTableNamePerfmonStats
2819 + ' (ID INT IDENTITY(1,1) NOT NULL,
2820 ServerName NVARCHAR(128),
2821 CheckDate DATETIMEOFFSET,
2822 [object_name] NVARCHAR(128) NOT NULL,
2823 [counter_name] NVARCHAR(128) NOT NULL,
2824 [instance_name] NVARCHAR(128) NULL,
2825 [cntr_value] BIGINT NULL,
2826 [cntr_type] INT NOT NULL,
2827 [value_delta] BIGINT NULL,
2828 [value_per_second] DECIMAL(18,2) NULL,
2829 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2830 EXEC(@StringToExecute);
2831
2832 /* Create the view */
2833 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNamePerfmonStats_View;
2834 IF OBJECT_ID(@ObjectFullName) IS NULL
2835 BEGIN
2836 SET @StringToExecute = 'USE '
2837 + @OutputDatabaseName
2838 + '; EXEC (''CREATE VIEW '
2839 + @OutputSchemaName + '.'
2840 + @OutputTableNamePerfmonStats_View + ' AS ' + @LineFeed
2841 + 'SELECT p.ServerName, p.CheckDate, p.object_name, p.counter_name, p.instance_name' + @LineFeed
2842 + ', DATEDIFF(ss, pPrior.CheckDate, p.CheckDate) AS ElapsedSeconds' + @LineFeed
2843 + ', p.cntr_value' + @LineFeed
2844 + ', p.cntr_type' + @LineFeed
2845 + ', (p.cntr_value - pPrior.cntr_value) AS cntr_delta' + @LineFeed
2846 + ', (p.cntr_value - pPrior.cntr_value) * 1.0 / DATEDIFF(ss, pPrior.CheckDate, p.CheckDate) AS cntr_delta_per_second' + @LineFeed
2847 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNamePerfmonStats + ' p' + @LineFeed
2848 + '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
2849 + '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
2850 + 'WHERE pMiddle.ID IS NULL AND DATEDIFF(MI, pPrior.CheckDate, p.CheckDate) BETWEEN 1 AND 60;'')'
2851 EXEC(@StringToExecute);
2852 END;
2853
2854 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2855 + @OutputDatabaseName
2856 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2857 + @OutputSchemaName + ''') INSERT '
2858 + @OutputDatabaseName + '.'
2859 + @OutputSchemaName + '.'
2860 + @OutputTableNamePerfmonStats
2861 + ' (ServerName, CheckDate, object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second) SELECT '''
2862 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2863 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2864 + 'object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second FROM #PerfmonStats WHERE Pass = 2';
2865 EXEC(@StringToExecute);
2866
2867 /* Delete history older than @OutputTableRetentionDays */
2868 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
2869 + @OutputDatabaseName
2870 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2871 + @OutputSchemaName + ''') DELETE '
2872 + @OutputDatabaseName + '.'
2873 + @OutputSchemaName + '.'
2874 + @OutputTableNamePerfmonStats
2875 + ' WHERE ServerName = '''
2876 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2877 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
2878 EXEC(@StringToExecute);
2879
2880
2881
2882 END
2883 ELSE IF (SUBSTRING(@OutputTableNamePerfmonStats, 2, 2) = '##')
2884 BEGIN
2885 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
2886 + @OutputTableNamePerfmonStats
2887 + ''') IS NULL) CREATE TABLE '
2888 + @OutputTableNamePerfmonStats
2889 + ' (ID INT IDENTITY(1,1) NOT NULL,
2890 ServerName NVARCHAR(128),
2891 CheckDate DATETIMEOFFSET,
2892 [object_name] NVARCHAR(128) NOT NULL,
2893 [counter_name] NVARCHAR(128) NOT NULL,
2894 [instance_name] NVARCHAR(128) NULL,
2895 [cntr_value] BIGINT NULL,
2896 [cntr_type] INT NOT NULL,
2897 [value_delta] BIGINT NULL,
2898 [value_per_second] DECIMAL(18,2) NULL,
2899 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
2900 + ' INSERT '
2901 + @OutputTableNamePerfmonStats
2902 + ' (ServerName, CheckDate, object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second) SELECT '''
2903 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
2904 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
2905 + 'object_name, counter_name, instance_name, cntr_value, cntr_type, value_delta, value_per_second FROM #PerfmonStats WHERE Pass = 2';
2906 EXEC(@StringToExecute);
2907 END
2908 ELSE IF (SUBSTRING(@OutputTableNamePerfmonStats, 2, 1) = '#')
2909 BEGIN
2910 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0)
2911 END
2912
2913
2914 /* @OutputTableNameWaitStats lets us export the results to a permanent table */
2915 IF @OutputDatabaseName IS NOT NULL
2916 AND @OutputSchemaName IS NOT NULL
2917 AND @OutputTableNameWaitStats IS NOT NULL
2918 AND @OutputTableNameWaitStats NOT LIKE '#%'
2919 AND EXISTS ( SELECT *
2920 FROM sys.databases
2921 WHERE QUOTENAME([name]) = @OutputDatabaseName)
2922 BEGIN
2923 /* Create the table */
2924 SET @StringToExecute = 'USE '
2925 + @OutputDatabaseName
2926 + '; IF EXISTS(SELECT * FROM '
2927 + @OutputDatabaseName
2928 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
2929 + @OutputSchemaName
2930 + ''') AND NOT EXISTS (SELECT * FROM '
2931 + @OutputDatabaseName
2932 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
2933 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
2934 + @OutputTableNameWaitStats + ''') ' + @LineFeed
2935 + 'BEGIN' + @LineFeed
2936 + 'CREATE TABLE '
2937 + @OutputSchemaName + '.'
2938 + @OutputTableNameWaitStats
2939 + ' (ID INT IDENTITY(1,1) NOT NULL,
2940 ServerName NVARCHAR(128),
2941 CheckDate DATETIMEOFFSET,
2942 wait_type NVARCHAR(60),
2943 wait_time_ms BIGINT,
2944 signal_wait_time_ms BIGINT,
2945 waiting_tasks_count BIGINT ,
2946 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID));' + @LineFeed
2947 + 'CREATE NONCLUSTERED INDEX IX_ServerName_wait_type_CheckDate_Includes ON ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + @LineFeed
2948 + '(ServerName, wait_type, CheckDate) INCLUDE (wait_time_ms, signal_wait_time_ms, waiting_tasks_count);' + @LineFeed
2949 + 'END'
2950
2951 EXEC(@StringToExecute);
2952
2953 /* Create the wait stats category table */
2954 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameWaitStats_Categories;
2955 IF OBJECT_ID(@ObjectFullName) IS NULL
2956 BEGIN
2957 SET @StringToExecute = 'USE '
2958 + @OutputDatabaseName
2959 + '; EXEC (''CREATE TABLE '
2960 + @OutputSchemaName + '.'
2961 + @OutputTableNameWaitStats_Categories + ' (WaitType NVARCHAR(60) PRIMARY KEY CLUSTERED, WaitCategory NVARCHAR(128) NOT NULL, Ignorable BIT DEFAULT 0);'')'
2962 EXEC(@StringToExecute);
2963 END
2964
2965 /* Make sure the wait stats category table has the current number of rows */
2966 SET @StringToExecute = 'USE '
2967 + @OutputDatabaseName
2968 + '; EXEC (''IF (SELECT COALESCE(SUM(1),0) FROM ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ') <> (SELECT COALESCE(SUM(1),0) FROM ##WaitCategories)' + @LineFeed
2969 + 'BEGIN ' + @LineFeed
2970 + 'TRUNCATE TABLE ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + @LineFeed
2971 + 'INSERT INTO ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ' (WaitType, WaitCategory, Ignorable) SELECT WaitType, WaitCategory, Ignorable FROM ##WaitCategories;' + @LineFeed
2972 + 'END'')'
2973 EXEC(@StringToExecute);
2974
2975
2976 /* Create the wait stats view */
2977 SET @ObjectFullName = @OutputDatabaseName + N'.' + @OutputSchemaName + N'.' + @OutputTableNameWaitStats_View;
2978 IF OBJECT_ID(@ObjectFullName) IS NULL
2979 BEGIN
2980 SET @StringToExecute = 'USE '
2981 + @OutputDatabaseName
2982 + '; EXEC (''CREATE VIEW '
2983 + @OutputSchemaName + '.'
2984 + @OutputTableNameWaitStats_View + ' AS ' + @LineFeed
2985 + 'SELECT w.ServerName, w.CheckDate, w.wait_type, COALESCE(wc.WaitCategory, ''''Other'''') AS WaitCategory, COALESCE(wc.Ignorable,0) AS Ignorable' + @LineFeed
2986 + ', DATEDIFF(ss, wPrior.CheckDate, w.CheckDate) AS ElapsedSeconds' + @LineFeed
2987 + ', (w.wait_time_ms - wPrior.wait_time_ms) AS wait_time_ms_delta' + @LineFeed
2988 + ', (w.wait_time_ms - wPrior.wait_time_ms) / 60000.0 AS wait_time_minutes_delta' + @LineFeed
2989 + ', (w.wait_time_ms - wPrior.wait_time_ms) / 1000.0 / DATEDIFF(ss, wPrior.CheckDate, w.CheckDate) AS wait_time_minutes_per_minute' + @LineFeed
2990 + ', (w.signal_wait_time_ms - wPrior.signal_wait_time_ms) AS signal_wait_time_ms_delta' + @LineFeed
2991 + ', (w.waiting_tasks_count - wPrior.waiting_tasks_count) AS waiting_tasks_count_delta' + @LineFeed
2992 + 'FROM ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + ' w' + @LineFeed
2993 + 'INNER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats + ' wPrior ON w.ServerName = wPrior.ServerName AND w.wait_type = wPrior.wait_type AND w.CheckDate > wPrior.CheckDate' + @LineFeed
2994 + '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
2995 + 'LEFT OUTER JOIN ' + @OutputSchemaName + '.' + @OutputTableNameWaitStats_Categories + ' wc ON w.wait_type = wc.WaitType' + @LineFeed
2996 + '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;'')'
2997 EXEC(@StringToExecute);
2998 END
2999
3000
3001 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
3002 + @OutputDatabaseName
3003 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
3004 + @OutputSchemaName + ''') INSERT '
3005 + @OutputDatabaseName + '.'
3006 + @OutputSchemaName + '.'
3007 + @OutputTableNameWaitStats
3008 + ' (ServerName, CheckDate, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count) SELECT '''
3009 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3010 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
3011 + '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';
3012 EXEC(@StringToExecute);
3013
3014 /* Delete history older than @OutputTableRetentionDays */
3015 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
3016 + @OutputDatabaseName
3017 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
3018 + @OutputSchemaName + ''') DELETE '
3019 + @OutputDatabaseName + '.'
3020 + @OutputSchemaName + '.'
3021 + @OutputTableNameWaitStats
3022 + ' WHERE ServerName = '''
3023 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3024 + ''' AND CheckDate < ''' + CAST(CAST( (DATEADD(DAY, -1 * @OutputTableRetentionDays, GETDATE() ) ) AS DATE) AS NVARCHAR(20)) + ''';';
3025 EXEC(@StringToExecute);
3026
3027 END
3028 ELSE IF (SUBSTRING(@OutputTableNameWaitStats, 2, 2) = '##')
3029 BEGIN
3030 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
3031 + @OutputTableNameWaitStats
3032 + ''') IS NULL) CREATE TABLE '
3033 + @OutputTableNameWaitStats
3034 + ' (ID INT IDENTITY(1,1) NOT NULL,
3035 ServerName NVARCHAR(128),
3036 CheckDate DATETIMEOFFSET,
3037 wait_type NVARCHAR(60),
3038 wait_time_ms BIGINT,
3039 signal_wait_time_ms BIGINT,
3040 waiting_tasks_count BIGINT ,
3041 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
3042 + ' INSERT '
3043 + @OutputTableNameWaitStats
3044 + ' (ServerName, CheckDate, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count) SELECT '''
3045 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
3046 + ''', ''' + CONVERT(NVARCHAR(100), @StartSampleTime, 121) + ''', '
3047 + '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';
3048 EXEC(@StringToExecute);
3049 END
3050 ELSE IF (SUBSTRING(@OutputTableNameWaitStats, 2, 1) = '#')
3051 BEGIN
3052 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0)
3053 END
3054
3055
3056
3057
3058 DECLARE @separator AS VARCHAR(1);
3059 IF @OutputType = 'RSV'
3060 SET @separator = CHAR(31);
3061 ELSE
3062 SET @separator = ',';
3063
3064 IF @OutputType = 'COUNT' AND @SinceStartup = 0
3065 BEGIN
3066 SELECT COUNT(*) AS Warnings
3067 FROM #BlitzFirstResults
3068 END
3069 ELSE
3070 IF @OutputType = 'Opserver1' AND @SinceStartup = 0
3071 BEGIN
3072
3073 SELECT r.[Priority] ,
3074 r.[FindingsGroup] ,
3075 r.[Finding] ,
3076 r.[URL] ,
3077 r.[Details],
3078 r.[HowToStopIt] ,
3079 r.[CheckID] ,
3080 r.[StartTime],
3081 r.[LoginName],
3082 r.[NTUserName],
3083 r.[OriginalLoginName],
3084 r.[ProgramName],
3085 r.[HostName],
3086 r.[DatabaseID],
3087 r.[DatabaseName],
3088 r.[OpenTransactionCount],
3089 r.[QueryPlan],
3090 r.[QueryText],
3091 qsNow.plan_handle AS PlanHandle,
3092 qsNow.sql_handle AS SqlHandle,
3093 qsNow.statement_start_offset AS StatementStartOffset,
3094 qsNow.statement_end_offset AS StatementEndOffset,
3095 [Executions] = qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)),
3096 [ExecutionsPercent] = CAST(100.0 * (qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0))) / (qsTotal.execution_count - qsTotalFirst.execution_count) AS DECIMAL(6,2)),
3097 [Duration] = qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)),
3098 [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)),
3099 [CPU] = qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)),
3100 [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)),
3101 [Reads] = qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)),
3102 [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)),
3103 [PlanCreationTime] = CONVERT(NVARCHAR(100), qsNow.creation_time ,121),
3104 [TotalExecutions] = qsNow.execution_count,
3105 [TotalExecutionsPercent] = CAST(100.0 * qsNow.execution_count / qsTotal.execution_count AS DECIMAL(6,2)),
3106 [TotalDuration] = qsNow.total_elapsed_time,
3107 [TotalDurationPercent] = CAST(100.0 * qsNow.total_elapsed_time / qsTotal.total_elapsed_time AS DECIMAL(6,2)),
3108 [TotalCPU] = qsNow.total_worker_time,
3109 [TotalCPUPercent] = CAST(100.0 * qsNow.total_worker_time / qsTotal.total_worker_time AS DECIMAL(6,2)),
3110 [TotalReads] = qsNow.total_logical_reads,
3111 [TotalReadsPercent] = CAST(100.0 * qsNow.total_logical_reads / qsTotal.total_logical_reads AS DECIMAL(6,2)),
3112 r.[DetailsInt]
3113 FROM #BlitzFirstResults r
3114 LEFT OUTER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
3115 LEFT OUTER JOIN #QueryStats qsTotalFirst ON qsTotalFirst.Pass = -1
3116 LEFT OUTER JOIN #QueryStats qsNow ON r.QueryStatsNowID = qsNow.ID
3117 LEFT OUTER JOIN #QueryStats qsFirst ON r.QueryStatsFirstID = qsFirst.ID
3118 ORDER BY r.Priority ,
3119 r.FindingsGroup ,
3120 CASE
3121 WHEN r.CheckID = 6 THEN DetailsInt
3122 ELSE 0
3123 END DESC,
3124 r.Finding,
3125 r.ID;
3126 END
3127 ELSE IF @OutputType IN ( 'CSV', 'RSV' ) AND @SinceStartup = 0
3128 BEGIN
3129
3130 SELECT Result = CAST([Priority] AS NVARCHAR(100))
3131 + @separator + CAST(CheckID AS NVARCHAR(100))
3132 + @separator + COALESCE([FindingsGroup],
3133 '(N/A)') + @separator
3134 + COALESCE([Finding], '(N/A)') + @separator
3135 + COALESCE(DatabaseName, '(N/A)') + @separator
3136 + COALESCE([URL], '(N/A)') + @separator
3137 + COALESCE([Details], '(N/A)')
3138 FROM #BlitzFirstResults
3139 ORDER BY Priority ,
3140 FindingsGroup ,
3141 CASE
3142 WHEN CheckID = 6 THEN DetailsInt
3143 ELSE 0
3144 END DESC,
3145 Finding,
3146 Details;
3147 END
3148 ELSE IF @ExpertMode = 0 AND @OutputXMLasNVARCHAR = 0 AND @SinceStartup = 0
3149 BEGIN
3150 SELECT [Priority] ,
3151 [FindingsGroup] ,
3152 [Finding] ,
3153 [URL] ,
3154 CAST(@StockDetailsHeader + [Details] + @StockDetailsFooter AS XML) AS Details,
3155 CAST(@StockWarningHeader + HowToStopIt + @StockWarningFooter AS XML) AS HowToStopIt,
3156 [QueryText],
3157 [QueryPlan]
3158 FROM #BlitzFirstResults
3159 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3160 ORDER BY Priority ,
3161 FindingsGroup ,
3162 CASE
3163 WHEN CheckID = 6 THEN DetailsInt
3164 ELSE 0
3165 END DESC,
3166 Finding,
3167 ID;
3168 END
3169 ELSE IF @ExpertMode = 0 AND @OutputXMLasNVARCHAR = 1 AND @SinceStartup = 0
3170 BEGIN
3171 SELECT [Priority] ,
3172 [FindingsGroup] ,
3173 [Finding] ,
3174 [URL] ,
3175 CAST(@StockDetailsHeader + [Details] + @StockDetailsFooter AS NVARCHAR(MAX)) AS Details,
3176 CAST([HowToStopIt] AS NVARCHAR(MAX)) AS HowToStopIt,
3177 CAST([QueryText] AS NVARCHAR(MAX)) AS QueryText,
3178 CAST([QueryPlan] AS NVARCHAR(MAX)) AS QueryPlan
3179 FROM #BlitzFirstResults
3180 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3181 ORDER BY Priority ,
3182 FindingsGroup ,
3183 CASE
3184 WHEN CheckID = 6 THEN DetailsInt
3185 ELSE 0
3186 END DESC,
3187 Finding,
3188 ID;
3189 END
3190 ELSE IF @ExpertMode = 1
3191 BEGIN
3192 IF @SinceStartup = 0
3193 SELECT r.[Priority] ,
3194 r.[FindingsGroup] ,
3195 r.[Finding] ,
3196 r.[URL] ,
3197 CAST(@StockDetailsHeader + r.[Details] + @StockDetailsFooter AS XML) AS Details,
3198 CAST(@StockWarningHeader + r.HowToStopIt + @StockWarningFooter AS XML) AS HowToStopIt,
3199 r.[CheckID] ,
3200 r.[StartTime],
3201 r.[LoginName],
3202 r.[NTUserName],
3203 r.[OriginalLoginName],
3204 r.[ProgramName],
3205 r.[HostName],
3206 r.[DatabaseID],
3207 r.[DatabaseName],
3208 r.[OpenTransactionCount],
3209 r.[QueryPlan],
3210 r.[QueryText],
3211 qsNow.plan_handle AS PlanHandle,
3212 qsNow.sql_handle AS SqlHandle,
3213 qsNow.statement_start_offset AS StatementStartOffset,
3214 qsNow.statement_end_offset AS StatementEndOffset,
3215 [Executions] = qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0)),
3216 [ExecutionsPercent] = CAST(100.0 * (qsNow.execution_count - (COALESCE(qsFirst.execution_count, 0))) / (qsTotal.execution_count - qsTotalFirst.execution_count) AS DECIMAL(6,2)),
3217 [Duration] = qsNow.total_elapsed_time - (COALESCE(qsFirst.total_elapsed_time, 0)),
3218 [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)),
3219 [CPU] = qsNow.total_worker_time - (COALESCE(qsFirst.total_worker_time, 0)),
3220 [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)),
3221 [Reads] = qsNow.total_logical_reads - (COALESCE(qsFirst.total_logical_reads, 0)),
3222 [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)),
3223 [PlanCreationTime] = CONVERT(NVARCHAR(100), qsNow.creation_time ,121),
3224 [TotalExecutions] = qsNow.execution_count,
3225 [TotalExecutionsPercent] = CAST(100.0 * qsNow.execution_count / qsTotal.execution_count AS DECIMAL(6,2)),
3226 [TotalDuration] = qsNow.total_elapsed_time,
3227 [TotalDurationPercent] = CAST(100.0 * qsNow.total_elapsed_time / qsTotal.total_elapsed_time AS DECIMAL(6,2)),
3228 [TotalCPU] = qsNow.total_worker_time,
3229 [TotalCPUPercent] = CAST(100.0 * qsNow.total_worker_time / qsTotal.total_worker_time AS DECIMAL(6,2)),
3230 [TotalReads] = qsNow.total_logical_reads,
3231 [TotalReadsPercent] = CAST(100.0 * qsNow.total_logical_reads / qsTotal.total_logical_reads AS DECIMAL(6,2)),
3232 r.[DetailsInt]
3233 FROM #BlitzFirstResults r
3234 LEFT OUTER JOIN #QueryStats qsTotal ON qsTotal.Pass = 0
3235 LEFT OUTER JOIN #QueryStats qsTotalFirst ON qsTotalFirst.Pass = -1
3236 LEFT OUTER JOIN #QueryStats qsNow ON r.QueryStatsNowID = qsNow.ID
3237 LEFT OUTER JOIN #QueryStats qsFirst ON r.QueryStatsFirstID = qsFirst.ID
3238 WHERE (@Seconds > 0 OR (Priority IN (0, 250, 251, 255))) /* For @Seconds = 0, filter out broken checks for now */
3239 ORDER BY r.Priority ,
3240 r.FindingsGroup ,
3241 CASE
3242 WHEN r.CheckID = 6 THEN DetailsInt
3243 ELSE 0
3244 END DESC,
3245 r.Finding,
3246 r.ID;
3247
3248 -------------------------
3249 --What happened: #WaitStats
3250 -------------------------
3251 IF @Seconds = 0
3252 BEGIN
3253 /* Measure waits in hours */
3254 ;WITH max_batch AS (
3255 SELECT MAX(SampleTime) AS SampleTime
3256 FROM #WaitStats
3257 )
3258 SELECT
3259 'WAIT STATS' AS Pattern,
3260 b.SampleTime AS [Sample Ended],
3261 CAST(DATEDIFF(mi,wd1.SampleTime, wd2.SampleTime) / 60.0 AS DECIMAL(18,1)) AS [Hours Sample],
3262 wd1.wait_type,
3263 COALESCE(wcat.WaitCategory, 'Other') AS wait_category,
3264 CAST(c.[Wait Time (Seconds)] / 60.0 / 60 AS DECIMAL(18,1)) AS [Wait Time (Hours)],
3265 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],
3266 CAST(c.[Signal Wait Time (Seconds)] / 60.0 / 60 AS DECIMAL(18,1)) AS [Signal Wait Time (Hours)],
3267 CASE WHEN c.[Wait Time (Seconds)] > 0
3268 THEN CAST(100.*(c.[Signal Wait Time (Seconds)]/c.[Wait Time (Seconds)]) AS NUMERIC(4,1))
3269 ELSE 0 END AS [Percent Signal Waits],
3270 (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits],
3271 CASE WHEN (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3272 THEN
3273 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/
3274 (1.0*(wd2.waiting_tasks_count - wd1.waiting_tasks_count)) AS NUMERIC(12,1))
3275 ELSE 0 END AS [Avg ms Per Wait],
3276 N'http://www.brentozar.com/sql/wait-stats/#' + wd1.wait_type AS URL
3277 FROM max_batch b
3278 JOIN #WaitStats wd2 ON
3279 wd2.SampleTime =b.SampleTime
3280 JOIN #WaitStats wd1 ON
3281 wd1.wait_type=wd2.wait_type AND
3282 wd2.SampleTime > wd1.SampleTime
3283 CROSS APPLY (SELECT SUM(1) AS cpu_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1) AS cores
3284 CROSS APPLY (SELECT
3285 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Wait Time (Seconds)],
3286 CAST((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Signal Wait Time (Seconds)]) AS c
3287 LEFT OUTER JOIN ##WaitCategories wcat ON wd1.wait_type = wcat.WaitType
3288 WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3289 AND wd2.wait_time_ms-wd1.wait_time_ms > 0
3290 ORDER BY [Wait Time (Seconds)] DESC;
3291 END
3292 ELSE
3293 BEGIN
3294 /* Measure waits in seconds */
3295 ;WITH max_batch AS (
3296 SELECT MAX(SampleTime) AS SampleTime
3297 FROM #WaitStats
3298 )
3299 SELECT
3300 'WAIT STATS' AS Pattern,
3301 b.SampleTime AS [Sample Ended],
3302 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Seconds Sample],
3303 wd1.wait_type,
3304 COALESCE(wcat.WaitCategory, 'Other') AS wait_category,
3305 c.[Wait Time (Seconds)],
3306 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],
3307 c.[Signal Wait Time (Seconds)],
3308 CASE WHEN c.[Wait Time (Seconds)] > 0
3309 THEN CAST(100.*(c.[Signal Wait Time (Seconds)]/c.[Wait Time (Seconds)]) AS NUMERIC(4,1))
3310 ELSE 0 END AS [Percent Signal Waits],
3311 (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits],
3312 CASE WHEN (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3313 THEN
3314 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/
3315 (1.0*(wd2.waiting_tasks_count - wd1.waiting_tasks_count)) AS NUMERIC(12,1))
3316 ELSE 0 END AS [Avg ms Per Wait],
3317 N'http://www.brentozar.com/sql/wait-stats/#' + wd1.wait_type AS URL
3318 FROM max_batch b
3319 JOIN #WaitStats wd2 ON
3320 wd2.SampleTime =b.SampleTime
3321 JOIN #WaitStats wd1 ON
3322 wd1.wait_type=wd2.wait_type AND
3323 wd2.SampleTime > wd1.SampleTime
3324 CROSS APPLY (SELECT SUM(1) AS cpu_count FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1) AS cores
3325 CROSS APPLY (SELECT
3326 CAST((wd2.wait_time_ms-wd1.wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Wait Time (Seconds)],
3327 CAST((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. AS NUMERIC(12,1)) AS [Signal Wait Time (Seconds)]) AS c
3328 LEFT OUTER JOIN ##WaitCategories wcat ON wd1.wait_type = wcat.WaitType
3329 WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
3330 AND wd2.wait_time_ms-wd1.wait_time_ms > 0
3331 ORDER BY [Wait Time (Seconds)] DESC;
3332 END;
3333
3334 -------------------------
3335 --What happened: #FileStats
3336 -------------------------
3337 WITH readstats AS (
3338 SELECT 'PHYSICAL READS' AS Pattern,
3339 ROW_NUMBER() OVER (ORDER BY wd2.avg_stall_read_ms DESC) AS StallRank,
3340 wd2.SampleTime AS [Sample Time],
3341 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Sample (seconds)],
3342 wd1.DatabaseName ,
3343 wd1.FileLogicalName AS [File Name],
3344 UPPER(SUBSTRING(wd1.PhysicalName, 1, 2)) AS [Drive] ,
3345 wd1.SizeOnDiskMB ,
3346 ( wd2.num_of_reads - wd1.num_of_reads ) AS [# Reads/Writes],
3347 CASE WHEN wd2.num_of_reads - wd1.num_of_reads > 0
3348 THEN CAST(( wd2.bytes_read - wd1.bytes_read)/1024./1024. AS NUMERIC(21,1))
3349 ELSE 0
3350 END AS [MB Read/Written],
3351 wd2.avg_stall_read_ms AS [Avg Stall (ms)],
3352 wd1.PhysicalName AS [file physical name]
3353 FROM #FileStats wd2
3354 JOIN #FileStats wd1 ON wd2.SampleTime > wd1.SampleTime
3355 AND wd1.DatabaseID = wd2.DatabaseID
3356 AND wd1.FileID = wd2.FileID
3357 ),
3358 writestats AS (
3359 SELECT
3360 'PHYSICAL WRITES' AS Pattern,
3361 ROW_NUMBER() OVER (ORDER BY wd2.avg_stall_write_ms DESC) AS StallRank,
3362 wd2.SampleTime AS [Sample Time],
3363 DATEDIFF(ss,wd1.SampleTime, wd2.SampleTime) AS [Sample (seconds)],
3364 wd1.DatabaseName ,
3365 wd1.FileLogicalName AS [File Name],
3366 UPPER(SUBSTRING(wd1.PhysicalName, 1, 2)) AS [Drive] ,
3367 wd1.SizeOnDiskMB ,
3368 ( wd2.num_of_writes - wd1.num_of_writes ) AS [# Reads/Writes],
3369 CASE WHEN wd2.num_of_writes - wd1.num_of_writes > 0
3370 THEN CAST(( wd2.bytes_written - wd1.bytes_written)/1024./1024. AS NUMERIC(21,1))
3371 ELSE 0
3372 END AS [MB Read/Written],
3373 wd2.avg_stall_write_ms AS [Avg Stall (ms)],
3374 wd1.PhysicalName AS [file physical name]
3375 FROM #FileStats wd2
3376 JOIN #FileStats wd1 ON wd2.SampleTime > wd1.SampleTime
3377 AND wd1.DatabaseID = wd2.DatabaseID
3378 AND wd1.FileID = wd2.FileID
3379 )
3380 SELECT
3381 Pattern, [Sample Time], [Sample (seconds)], [File Name], [Drive], [# Reads/Writes],[MB Read/Written],[Avg Stall (ms)], [file physical name]
3382 FROM readstats
3383 WHERE StallRank <=5 AND [MB Read/Written] > 0
3384 UNION ALL
3385 SELECT Pattern, [Sample Time], [Sample (seconds)], [File Name], [Drive], [# Reads/Writes],[MB Read/Written],[Avg Stall (ms)], [file physical name]
3386 FROM writestats
3387 WHERE StallRank <=5 AND [MB Read/Written] > 0;
3388
3389
3390 -------------------------
3391 --What happened: #PerfmonStats
3392 -------------------------
3393
3394 SELECT 'PERFMON' AS Pattern, pLast.[object_name], pLast.counter_name, pLast.instance_name,
3395 pFirst.SampleTime AS FirstSampleTime, pFirst.cntr_value AS FirstSampleValue,
3396 pLast.SampleTime AS LastSampleTime, pLast.cntr_value AS LastSampleValue,
3397 pLast.cntr_value - pFirst.cntr_value AS ValueDelta,
3398 ((1.0 * pLast.cntr_value - pFirst.cntr_value) / DATEDIFF(ss, pFirst.SampleTime, pLast.SampleTime)) AS ValuePerSecond
3399 FROM #PerfmonStats pLast
3400 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))
3401 AND pLast.ID > pFirst.ID
3402 WHERE pLast.cntr_value <> pFirst.cntr_value
3403 ORDER BY Pattern, pLast.[object_name], pLast.counter_name, pLast.instance_name
3404
3405
3406 -------------------------
3407 --What happened: #QueryStats
3408 -------------------------
3409 IF @CheckProcedureCache = 1
3410 BEGIN
3411
3412 SELECT qsNow.*, qsFirst.*
3413 FROM #QueryStats qsNow
3414 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
3415 WHERE qsNow.Pass = 2
3416 END
3417 ELSE
3418 BEGIN
3419 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]
3420 END
3421 END
3422
3423 DROP TABLE #BlitzFirstResults;
3424
3425 /* What's running right now? This is the first and last result set. */
3426 IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1
3427IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1
3428 BEGIN
3429 IF OBJECT_ID('master.dbo.sp_BlitzWho') IS NULL AND OBJECT_ID('dbo.sp_BlitzWho') IS NULL
3430 BEGIN
3431 PRINT N'sp_BlitzWho is not installed in the current database_files. You can get a copy from http://FirstResponderKit.org'
3432 END
3433 ELSE
3434 BEGIN
3435 EXEC (@BlitzWho)
3436 END
3437 END /* IF @SinceStartup = 0 AND @Seconds > 0 AND @ExpertMode = 1 - What's running right now? This is the first and last result set. */
3438
3439END /* IF @LogMessage IS NULL */
3440END /* ELSE IF @OutputType = 'SCHEMA' */
3441
3442SET NOCOUNT OFF;
3443GO
3444
3445
3446
3447/* How to run it:
3448EXEC dbo.sp_BlitzFirst
3449
3450With extra diagnostic info:
3451EXEC dbo.sp_BlitzFirst @ExpertMode = 1;
3452
3453Saving output to tables:
3454EXEC sp_BlitzFirst
3455, @OutputDatabaseName = 'DBAtools'
3456, @OutputSchemaName = 'dbo'
3457, @OutputTableName = 'BlitzFirst'
3458, @OutputTableNameFileStats = 'BlitzFirst_FileStats'
3459, @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats'
3460, @OutputTableNameWaitStats = 'BlitzFirst_WaitStats'
3461, @OutputTableNameBlitzCache = 'BlitzCache'
3462*/