iz36NLSu

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