wH9KDSPy

1USE master
2GO
3
4IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')
5	EXEC ('CREATE PROC dbo.sp_WhoIsActive AS SELECT ''stub version, to be replaced''')
6GO
7
8/*********************************************************************************************
9Who Is Active? v11.17 (2016-10-18)
10(C) 2007-2016, Adam Machanic
11
12Feedback: mailto:amachanic@gmail.com
13Updates: http://whoisactive.com
14
15License: 
16	Who is Active? is free to download and use for personal, educational, and internal 
17	corporate purposes, provided that this header is preserved. Redistribution or sale 
18	of Who is Active?, in whole or in part, is prohibited without the author's express 
19	written consent.
20*********************************************************************************************/
21ALTER PROC dbo.sp_WhoIsActive
22(
23--~
24	--Filters--Both inclusive and exclusive
25	--Set either filter to '' to disable
26	--Valid filter types are: session, program, database, login, and host
27	--Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
28	--All other filter types support % or _ as wildcards
29	@filter sysname = '',
30	@filter_type VARCHAR(10) = 'session',
31	@not_filter sysname = '',
32	@not_filter_type VARCHAR(10) = 'session',
33
34	--Retrieve data about the calling session?
35	@show_own_spid BIT = 0,
36
37	--Retrieve data about system sessions?
38	@show_system_spids BIT = 0,
39
40	--Controls how sleeping SPIDs are handled, based on the idea of levels of interest
41	--0 does not pull any sleeping SPIDs
42	--1 pulls only those sleeping SPIDs that also have an open transaction
43	--2 pulls all sleeping SPIDs
44	@show_sleeping_spids TINYINT = 1,
45
46	--If 1, gets the full stored procedure or running batch, when available
47	--If 0, gets only the actual statement that is currently running in the batch or procedure
48	@get_full_inner_text BIT = 0,
49
50	--Get associated query plans for running tasks, if available
51	--If @get_plans = 1, gets the plan based on the request's statement offset
52	--If @get_plans = 2, gets the entire plan based on the request's plan_handle
53	@get_plans TINYINT = 0,
54
55	--Get the associated outer ad hoc query or stored procedure call, if available
56	@get_outer_command BIT = 0,
57
58	--Enables pulling transaction log write info and transaction duration
59	@get_transaction_info BIT = 0,
60
61	--Get information on active tasks, based on three interest levels
62	--Level 0 does not pull any task-related information
63	--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
64	--Level 2 pulls all available task-based metrics, including: 
65	--number of active tasks, current wait stats, physical I/O, context switches, and blocker information
66	@get_task_info TINYINT = 1,
67
68	--Gets associated locks for each request, aggregated in an XML format
69	@get_locks BIT = 0,
70
71	--Get average time for past runs of an active query
72	--(based on the combination of plan handle, sql handle, and offset)
73	@get_avg_time BIT = 0,
74
75	--Get additional non-performance-related information about the session or request
76	--text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, 
77	--ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, 
78	--transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
79	--
80	--If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
81	--the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
82	--
83	--If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
84	--populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id, 
85	--applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
86	@get_additional_info BIT = 0,
87
88	--Walk the blocking chain and count the number of 
89	--total SPIDs blocked all the way down by a given session
90	--Also enables task_info Level 1, if @get_task_info is set to 0
91	@find_block_leaders BIT = 0,
92
93	--Pull deltas on various metrics
94	--Interval in seconds to wait before doing the second data pull
95	@delta_interval TINYINT = 0,
96
97	--List of desired output columns, in desired order
98	--Note that the final output will be the intersection of all enabled features and all 
99	--columns in the list. Therefore, only columns associated with enabled features will 
100	--actually appear in the output. Likewise, removing columns from this list may effectively
101	--disable features, even if they are turned on
102	--
103	--Each element in this list must be one of the valid output column names. Names must be
104	--delimited by square brackets. White space, formatting, and additional characters are
105	--allowed, as long as the list contains exact matches of delimited valid column names.
106	@output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
107
108	--Column(s) by which to sort output, optionally with sort directions. 
109		--Valid column choices:
110		--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
111		--tempdb_current, CPU, context_switches, used_memory, physical_io_delta, 
112		--reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta, 
113		--tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta, 
114		--tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
115		--percent_complete, host_name, login_name, database_name, start_time, login_time
116		--
117		--Note that column names in the list must be bracket-delimited. Commas and/or white
118		--space are not required. 
119	@sort_order VARCHAR(500) = '[start_time] ASC',
120
121	--Formats some of the output columns in a more "human readable" form
122	--0 disables outfput format
123	--1 formats the output for variable-width fonts
124	--2 formats the output for fixed-width fonts
125	@format_output TINYINT = 1,
126
127	--If set to a non-blank value, the script will attempt to insert into the specified 
128	--destination table. Please note that the script will not verify that the table exists, 
129	--or that it has the correct schema, before doing the insert.
130	--Table can be specified in one, two, or three-part format
131	@destination_table VARCHAR(4000) = '',
132
133	--If set to 1, no data collection will happen and no result set will be returned; instead,
134	--a CREATE TABLE statement will be returned via the @schema parameter, which will match 
135	--the schema of the result set that would be returned by using the same collection of the
136	--rest of the parameters. The CREATE TABLE statement will have a placeholder token of 
137	--<table_name> in place of an actual table name.
138	@return_schema BIT = 0,
139	@schema VARCHAR(MAX) = NULL OUTPUT,
140
141	--Help! What do I do?
142	@help BIT = 0
143--~
144)
145/*
146OUTPUT COLUMNS
147--------------
148Formatted/Non:	[session_id] [smallint] NOT NULL
149	Session ID (a.k.a. SPID)
150
151Formatted:		[dd hh:mm:ss.mss] [varchar](15) NULL
152Non-Formatted:	<not returned>
153	For an active request, time the query has been running
154	For a sleeping session, time since the last batch completed
155
156Formatted:		[dd hh:mm:ss.mss (avg)] [varchar](15) NULL
157Non-Formatted:	[avg_elapsed_time] [int] NULL
158	(Requires @get_avg_time option)
159	How much time has the active portion of the query taken in the past, on average?
160
161Formatted:		[physical_io] [varchar](30) NULL
162Non-Formatted:	[physical_io] [bigint] NULL
163	Shows the number of physical I/Os, for active requests
164
165Formatted:		[reads] [varchar](30) NULL
166Non-Formatted:	[reads] [bigint] NULL
167	For an active request, number of reads done for the current query
168	For a sleeping session, total number of reads done over the lifetime of the session
169
170Formatted:		[physical_reads] [varchar](30) NULL
171Non-Formatted:	[physical_reads] [bigint] NULL
172	For an active request, number of physical reads done for the current query
173	For a sleeping session, total number of physical reads done over the lifetime of the session
174
175Formatted:		[writes] [varchar](30) NULL
176Non-Formatted:	[writes] [bigint] NULL
177	For an active request, number of writes done for the current query
178	For a sleeping session, total number of writes done over the lifetime of the session
179
180Formatted:		[tempdb_allocations] [varchar](30) NULL
181Non-Formatted:	[tempdb_allocations] [bigint] NULL
182	For an active request, number of TempDB writes done for the current query
183	For a sleeping session, total number of TempDB writes done over the lifetime of the session
184
185Formatted:		[tempdb_current] [varchar](30) NULL
186Non-Formatted:	[tempdb_current] [bigint] NULL
187	For an active request, number of TempDB pages currently allocated for the query
188	For a sleeping session, number of TempDB pages currently allocated for the session
189
190Formatted:		[CPU] [varchar](30) NULL
191Non-Formatted:	[CPU] [int] NULL
192	For an active request, total CPU time consumed by the current query
193	For a sleeping session, total CPU time consumed over the lifetime of the session
194
195Formatted:		[context_switches] [varchar](30) NULL
196Non-Formatted:	[context_switches] [bigint] NULL
197	Shows the number of context switches, for active requests
198
199Formatted:		[used_memory] [varchar](30) NOT NULL
200Non-Formatted:	[used_memory] [bigint] NOT NULL
201	For an active request, total memory consumption for the current query
202	For a sleeping session, total current memory consumption
203
204Formatted:		[physical_io_delta] [varchar](30) NULL
205Non-Formatted:	[physical_io_delta] [bigint] NULL
206	(Requires @delta_interval option)
207	Difference between the number of physical I/Os reported on the first and second collections. 
208	If the request started after the first collection, the value will be NULL
209
210Formatted:		[reads_delta] [varchar](30) NULL
211Non-Formatted:	[reads_delta] [bigint] NULL
212	(Requires @delta_interval option)
213	Difference between the number of reads reported on the first and second collections. 
214	If the request started after the first collection, the value will be NULL
215
216Formatted:		[physical_reads_delta] [varchar](30) NULL
217Non-Formatted:	[physical_reads_delta] [bigint] NULL
218	(Requires @delta_interval option)
219	Difference between the number of physical reads reported on the first and second collections. 
220	If the request started after the first collection, the value will be NULL
221
222Formatted:		[writes_delta] [varchar](30) NULL
223Non-Formatted:	[writes_delta] [bigint] NULL
224	(Requires @delta_interval option)
225	Difference between the number of writes reported on the first and second collections. 
226	If the request started after the first collection, the value will be NULL
227
228Formatted:		[tempdb_allocations_delta] [varchar](30) NULL
229Non-Formatted:	[tempdb_allocations_delta] [bigint] NULL
230	(Requires @delta_interval option)
231	Difference between the number of TempDB writes reported on the first and second collections. 
232	If the request started after the first collection, the value will be NULL
233
234Formatted:		[tempdb_current_delta] [varchar](30) NULL
235Non-Formatted:	[tempdb_current_delta] [bigint] NULL
236	(Requires @delta_interval option)
237	Difference between the number of allocated TempDB pages reported on the first and second 
238	collections. If the request started after the first collection, the value will be NULL
239
240Formatted:		[CPU_delta] [varchar](30) NULL
241Non-Formatted:	[CPU_delta] [int] NULL
242	(Requires @delta_interval option)
243	Difference between the CPU time reported on the first and second collections. 
244	If the request started after the first collection, the value will be NULL
245
246Formatted:		[context_switches_delta] [varchar](30) NULL
247Non-Formatted:	[context_switches_delta] [bigint] NULL
248	(Requires @delta_interval option)
249	Difference between the context switches count reported on the first and second collections
250	If the request started after the first collection, the value will be NULL
251
252Formatted:		[used_memory_delta] [varchar](30) NULL
253Non-Formatted:	[used_memory_delta] [bigint] NULL
254	Difference between the memory usage reported on the first and second collections
255	If the request started after the first collection, the value will be NULL
256
257Formatted:		[tasks] [varchar](30) NULL
258Non-Formatted:	[tasks] [smallint] NULL
259	Number of worker tasks currently allocated, for active requests
260
261Formatted/Non:	[status] [varchar](30) NOT NULL
262	Activity status for the session (running, sleeping, etc)
263
264Formatted/Non:	[wait_info] [nvarchar](4000) NULL
265	Aggregates wait information, in the following format:
266		(Ax: Bms/Cms/Dms)E
267	A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
268	times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
269	If two tasks are waiting, each of their wait times will be shown (B/C). If three or more 
270	tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
271	If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM), 
272	the page type will be identified.
273	If wait type E is CXPACKET, the nodeId from the query plan will be identified
274
275Formatted/Non:	[locks] [xml] NULL
276	(Requires @get_locks option)
277	Aggregates lock information, in XML format.
278	The lock XML includes the lock mode, locked object, and aggregates the number of requests. 
279	Attempts are made to identify locked objects by name
280
281Formatted/Non:	[tran_start_time] [datetime] NULL
282	(Requires @get_transaction_info option)
283	Date and time that the first transaction opened by a session caused a transaction log 
284	write to occur.
285
286Formatted/Non:	[tran_log_writes] [nvarchar](4000) NULL
287	(Requires @get_transaction_info option)
288	Aggregates transaction log write information, in the following format:
289	A:wB (C kB)
290	A is a database that has been touched by an active transaction
291	B is the number of log writes that have been made in the database as a result of the transaction
292	C is the number of log kilobytes consumed by the log records
293
294Formatted:		[open_tran_count] [varchar](30) NULL
295Non-Formatted:	[open_tran_count] [smallint] NULL
296	Shows the number of open transactions the session has open
297
298Formatted:		[sql_command] [xml] NULL
299Non-Formatted:	[sql_command] [nvarchar](max) NULL
300	(Requires @get_outer_command option)
301	Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server, 
302	if available
303
304Formatted:		[sql_text] [xml] NULL
305Non-Formatted:	[sql_text] [nvarchar](max) NULL
306	Shows the SQL text for active requests or the last statement executed
307	for sleeping sessions, if available in either case.
308	If @get_full_inner_text option is set, shows the full text of the batch.
309	Otherwise, shows only the active statement within the batch.
310	If the query text is locked, a special timeout message will be sent, in the following format:
311		<timeout_exceeded />
312	If an error occurs, an error message will be sent, in the following format:
313		<error message="message" />
314
315Formatted/Non:	[query_plan] [xml] NULL
316	(Requires @get_plans option)
317	Shows the query plan for the request, if available.
318	If the plan is locked, a special timeout message will be sent, in the following format:
319		<timeout_exceeded />
320	If an error occurs, an error message will be sent, in the following format:
321		<error message="message" />
322
323Formatted/Non:	[blocking_session_id] [smallint] NULL
324	When applicable, shows the blocking SPID
325
326Formatted:		[blocked_session_count] [varchar](30) NULL
327Non-Formatted:	[blocked_session_count] [smallint] NULL
328	(Requires @find_block_leaders option)
329	The total number of SPIDs blocked by this session,
330	all the way down the blocking chain.
331
332Formatted:		[percent_complete] [varchar](30) NULL
333Non-Formatted:	[percent_complete] [real] NULL
334	When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)
335
336Formatted/Non:	[host_name] [sysname] NOT NULL
337	Shows the host name for the connection
338
339Formatted/Non:	[login_name] [sysname] NOT NULL
340	Shows the login name for the connection
341
342Formatted/Non:	[database_name] [sysname] NULL
343	Shows the connected database
344
345Formatted/Non:	[program_name] [sysname] NULL
346	Shows the reported program/application name
347
348Formatted/Non:	[additional_info] [xml] NULL
349	(Requires @get_additional_info option)
350	Returns additional non-performance-related session/request information
351	If the script finds a SQL Agent job running, the name of the job and job step will be reported
352	If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported
353
354Formatted/Non:	[start_time] [datetime] NOT NULL
355	For active requests, shows the time the request started
356	For sleeping sessions, shows the time the last batch completed
357
358Formatted/Non:	[login_time] [datetime] NOT NULL
359	Shows the time that the session connected
360
361Formatted/Non:	[request_id] [int] NULL
362	For active requests, shows the request_id
363	Should be 0 unless MARS is being used
364
365Formatted/Non:	[collection_time] [datetime] NOT NULL
366	Time that this script's final SELECT ran
367*/
368AS
369BEGIN;
370	SET NOCOUNT ON; 
371	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
372	SET QUOTED_IDENTIFIER ON;
373	SET ANSI_PADDING ON;
374	SET CONCAT_NULL_YIELDS_NULL ON;
375	SET ANSI_WARNINGS ON;
376	SET NUMERIC_ROUNDABORT OFF;
377	SET ARITHABORT ON;
378
379	IF
380		@filter IS NULL
381		OR @filter_type IS NULL
382		OR @not_filter IS NULL
383		OR @not_filter_type IS NULL
384		OR @show_own_spid IS NULL
385		OR @show_system_spids IS NULL
386		OR @show_sleeping_spids IS NULL
387		OR @get_full_inner_text IS NULL
388		OR @get_plans IS NULL
389		OR @get_outer_command IS NULL
390		OR @get_transaction_info IS NULL
391		OR @get_task_info IS NULL
392		OR @get_locks IS NULL
393		OR @get_avg_time IS NULL
394		OR @get_additional_info IS NULL
395		OR @find_block_leaders IS NULL
396		OR @delta_interval IS NULL
397		OR @format_output IS NULL
398		OR @output_column_list IS NULL
399		OR @sort_order IS NULL
400		OR @return_schema IS NULL
401		OR @destination_table IS NULL
402		OR @help IS NULL
403	BEGIN;
404		RAISERROR('Input parameters cannot be NULL', 16, 1);
405		RETURN;
406	END;
407	
408	IF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
409	BEGIN;
410		RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
411		RETURN;
412	END;
413	
414	IF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%'
415	BEGIN;
416		RAISERROR('Session filters must be valid integers', 16, 1);
417		RETURN;
418	END;
419	
420	IF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
421	BEGIN;
422		RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
423		RETURN;
424	END;
425	
426	IF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%'
427	BEGIN;
428		RAISERROR('Session filters must be valid integers', 16, 1);
429		RETURN;
430	END;
431	
432	IF @show_sleeping_spids NOT IN (0, 1, 2)
433	BEGIN;
434		RAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1);
435		RETURN;
436	END;
437	
438	IF @get_plans NOT IN (0, 1, 2)
439	BEGIN;
440		RAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1);
441		RETURN;
442	END;
443
444	IF @get_task_info NOT IN (0, 1, 2)
445	BEGIN;
446		RAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1);
447		RETURN;
448	END;
449
450	IF @format_output NOT IN (0, 1, 2)
451	BEGIN;
452		RAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1);
453		RETURN;
454	END;
455	
456	IF @help = 1
457	BEGIN;
458		DECLARE 
459			@header VARCHAR(MAX),
460			@params VARCHAR(MAX),
461			@outputs VARCHAR(MAX);
462
463		SELECT 
464			@header =
465				REPLACE
466				(
467					REPLACE
468					(
469						CONVERT
470						(
471							VARCHAR(MAX),
472							SUBSTRING
473							(
474								t.text, 
475								CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94,
476								CHARINDEX(REPLICATE('*', 93) + '/', t.text) - (CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94)
477							)
478						),
479						CHAR(13)+CHAR(10),
480						CHAR(13)
481					),
482					'	',
483					''
484				),
485			@params =
486				CHAR(13) +
487					REPLACE
488					(
489						REPLACE
490						(
491							CONVERT
492							(
493								VARCHAR(MAX),
494								SUBSTRING
495								(
496									t.text, 
497									CHARINDEX('--~', t.text) + 5, 
498									CHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5)
499								)
500							),
501							CHAR(13)+CHAR(10),
502							CHAR(13)
503						),
504						'	',
505						''
506					),
507				@outputs = 
508					CHAR(13) +
509						REPLACE
510						(
511							REPLACE
512							(
513								REPLACE
514								(
515									CONVERT
516									(
517										VARCHAR(MAX),
518										SUBSTRING
519										(
520											t.text, 
521											CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32,
522											CHARINDEX('*/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32)
523										)
524									),
525									CHAR(9),
526									CHAR(255)
527								),
528								CHAR(13)+CHAR(10),
529								CHAR(13)
530							),
531							'	',
532							''
533						) +
534						CHAR(13)
535		FROM sys.dm_exec_requests AS r
536		CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
537		WHERE
538			r.session_id = @@SPID;
539
540		WITH
541		a0 AS
542		(SELECT 1 AS n UNION ALL SELECT 1),
543		a1 AS
544		(SELECT 1 AS n FROM a0 AS a, a0 AS b),
545		a2 AS
546		(SELECT 1 AS n FROM a1 AS a, a1 AS b),
547		a3 AS
548		(SELECT 1 AS n FROM a2 AS a, a2 AS b),
549		a4 AS
550		(SELECT 1 AS n FROM a3 AS a, a3 AS b),
551		numbers AS
552		(
553			SELECT TOP(LEN(@header) - 1)
554				ROW_NUMBER() OVER
555				(
556					ORDER BY (SELECT NULL)
557				) AS number
558			FROM a4
559			ORDER BY
560				number
561		)
562		SELECT
563			RTRIM(LTRIM(
564				SUBSTRING
565				(
566					@header,
567					number + 1,
568					CHARINDEX(CHAR(13), @header, number + 1) - number - 1
569				)
570			)) AS [------header---------------------------------------------------------------------------------------------------------------]
571		FROM numbers
572		WHERE
573			SUBSTRING(@header, number, 1) = CHAR(13);
574
575		WITH
576		a0 AS
577		(SELECT 1 AS n UNION ALL SELECT 1),
578		a1 AS
579		(SELECT 1 AS n FROM a0 AS a, a0 AS b),
580		a2 AS
581		(SELECT 1 AS n FROM a1 AS a, a1 AS b),
582		a3 AS
583		(SELECT 1 AS n FROM a2 AS a, a2 AS b),
584		a4 AS
585		(SELECT 1 AS n FROM a3 AS a, a3 AS b),
586		numbers AS
587		(
588			SELECT TOP(LEN(@params) - 1)
589				ROW_NUMBER() OVER
590				(
591					ORDER BY (SELECT NULL)
592				) AS number
593			FROM a4
594			ORDER BY
595				number
596		),
597		tokens AS
598		(
599			SELECT 
600				RTRIM(LTRIM(
601					SUBSTRING
602					(
603						@params,
604						number + 1,
605						CHARINDEX(CHAR(13), @params, number + 1) - number - 1
606					)
607				)) AS token,
608				number,
609				CASE
610					WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
611					ELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) 
612				END AS param_group,
613				ROW_NUMBER() OVER
614				(
615					PARTITION BY
616						CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number),
617						SUBSTRING(@params, number+1, 1)
618					ORDER BY 
619						number
620				) AS group_order
621			FROM numbers
622			WHERE
623				SUBSTRING(@params, number, 1) = CHAR(13)
624		),
625		parsed_tokens AS
626		(
627			SELECT
628				MIN
629				(
630					CASE
631						WHEN token LIKE '@%' THEN token
632						ELSE NULL
633					END
634				) AS parameter,
635				MIN
636				(
637					CASE
638						WHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2)
639						ELSE NULL
640					END
641				) AS description,
642				param_group,
643				group_order
644			FROM tokens
645			WHERE
646				NOT 
647				(
648					token = '' 
649					AND group_order > 1
650				)
651			GROUP BY
652				param_group,
653				group_order
654		)
655		SELECT
656			CASE
657				WHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------'
658				WHEN param_group = MAX(param_group) OVER() THEN parameter
659				ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '')
660			END AS [------parameter----------------------------------------------------------],
661			CASE
662				WHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------'
663				ELSE COALESCE(description, '')
664			END AS [------description-----------------------------------------------------------------------------------------------------]
665		FROM parsed_tokens
666		ORDER BY
667			param_group, 
668			group_order;
669		
670		WITH
671		a0 AS
672		(SELECT 1 AS n UNION ALL SELECT 1),
673		a1 AS
674		(SELECT 1 AS n FROM a0 AS a, a0 AS b),
675		a2 AS
676		(SELECT 1 AS n FROM a1 AS a, a1 AS b),
677		a3 AS
678		(SELECT 1 AS n FROM a2 AS a, a2 AS b),
679		a4 AS
680		(SELECT 1 AS n FROM a3 AS a, a3 AS b),
681		numbers AS
682		(
683			SELECT TOP(LEN(@outputs) - 1)
684				ROW_NUMBER() OVER
685				(
686					ORDER BY (SELECT NULL)
687				) AS number
688			FROM a4
689			ORDER BY
690				number
691		),
692		tokens AS
693		(
694			SELECT 
695				RTRIM(LTRIM(
696					SUBSTRING
697					(
698						@outputs,
699						number + 1,
700						CASE
701							WHEN 
702								COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) < 
703								COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))
704								THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) - number - 1
705							ELSE
706								COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1
707						END
708					)
709				)) AS token,
710				number,
711				COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group,
712				ROW_NUMBER() OVER
713				(
714					PARTITION BY 
715						COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs))
716					ORDER BY
717						number
718				) AS output_group_order
719			FROM numbers
720			WHERE
721				SUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted'
722				OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
723		),
724		output_tokens AS
725		(
726			SELECT 
727				*,
728				CASE output_group_order
729					WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)
730					ELSE ''
731				END COLLATE Latin1_General_Bin2 AS column_info
732			FROM tokens
733		)
734		SELECT
735			CASE output_group_order
736				WHEN 1 THEN '-----------------------------------'
737				WHEN 2 THEN 
738					CASE
739						WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN
740							SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info))
741						ELSE
742							SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1)
743					END
744				ELSE ''
745			END AS formatted_column_name,
746			CASE output_group_order
747				WHEN 1 THEN '-----------------------------------'
748				WHEN 2 THEN 
749					CASE
750						WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN
751							SUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info))
752						ELSE
753							SUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)
754					END
755				ELSE ''
756			END AS formatted_column_type,
757			CASE output_group_order
758				WHEN 1 THEN '---------------------------------------'
759				WHEN 2 THEN 
760					CASE
761						WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
762						ELSE
763							CASE
764								WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN
765									SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX('>', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info)))
766								ELSE
767									SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info)))
768							END
769					END
770				ELSE ''
771			END AS unformatted_column_name,
772			CASE output_group_order
773				WHEN 1 THEN '---------------------------------------'
774				WHEN 2 THEN 
775					CASE
776						WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
777						ELSE
778							CASE
779								WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN ''
780								ELSE
781									SUBSTRING(column_info, CHARINDEX(']', column_info, CHARINDEX('Non-Formatted:', column_info))+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)
782							END
783					END
784				ELSE ''
785			END AS unformatted_column_type,
786			CASE output_group_order
787				WHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------'
788				ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '')
789			END AS [------description-----------------------------------------------------------------------------------------------------]
790		FROM output_tokens
791		WHERE
792			NOT 
793			(
794				output_group_order = 1 
795				AND output_group = LEN(@outputs)
796			)
797		ORDER BY
798			output_group,
799			CASE output_group_order
800				WHEN 1 THEN 99
801				ELSE output_group_order
802			END;
803
804		RETURN;
805	END;
806
807	WITH
808	a0 AS
809	(SELECT 1 AS n UNION ALL SELECT 1),
810	a1 AS
811	(SELECT 1 AS n FROM a0 AS a, a0 AS b),
812	a2 AS
813	(SELECT 1 AS n FROM a1 AS a, a1 AS b),
814	a3 AS
815	(SELECT 1 AS n FROM a2 AS a, a2 AS b),
816	a4 AS
817	(SELECT 1 AS n FROM a3 AS a, a3 AS b),
818	numbers AS
819	(
820		SELECT TOP(LEN(@output_column_list))
821			ROW_NUMBER() OVER
822			(
823				ORDER BY (SELECT NULL)
824			) AS number
825		FROM a4
826		ORDER BY
827			number
828	),
829	tokens AS
830	(
831		SELECT 
832			'|[' +
833				SUBSTRING
834				(
835					@output_column_list,
836					number + 1,
837					CHARINDEX(']', @output_column_list, number) - number - 1
838				) + '|]' AS token,
839			number
840		FROM numbers
841		WHERE
842			SUBSTRING(@output_column_list, number, 1) = '['
843	),
844	ordered_columns AS
845	(
846		SELECT
847			x.column_name,
848			ROW_NUMBER() OVER
849			(
850				PARTITION BY
851					x.column_name
852				ORDER BY
853					tokens.number,
854					x.default_order
855			) AS r,
856			ROW_NUMBER() OVER
857			(
858				ORDER BY
859					tokens.number,
860					x.default_order
861			) AS s
862		FROM tokens
863		JOIN
864		(
865			SELECT '[session_id]' AS column_name, 1 AS default_order
866			UNION ALL
867			SELECT '[dd hh:mm:ss.mss]', 2
868			WHERE
869				@format_output IN (1, 2)
870			UNION ALL
871			SELECT '[dd hh:mm:ss.mss (avg)]', 3
872			WHERE
873				@format_output IN (1, 2)
874				AND @get_avg_time = 1
875			UNION ALL
876			SELECT '[avg_elapsed_time]', 4
877			WHERE
878				@format_output = 0
879				AND @get_avg_time = 1
880			UNION ALL
881			SELECT '[physical_io]', 5
882			WHERE
883				@get_task_info = 2
884			UNION ALL
885			SELECT '[reads]', 6
886			UNION ALL
887			SELECT '[physical_reads]', 7
888			UNION ALL
889			SELECT '[writes]', 8
890			UNION ALL
891			SELECT '[tempdb_allocations]', 9
892			UNION ALL
893			SELECT '[tempdb_current]', 10
894			UNION ALL
895			SELECT '[CPU]', 11
896			UNION ALL
897			SELECT '[context_switches]', 12
898			WHERE
899				@get_task_info = 2
900			UNION ALL
901			SELECT '[used_memory]', 13
902			UNION ALL
903			SELECT '[physical_io_delta]', 14
904			WHERE
905				@delta_interval > 0	
906				AND @get_task_info = 2
907			UNION ALL
908			SELECT '[reads_delta]', 15
909			WHERE
910				@delta_interval > 0
911			UNION ALL
912			SELECT '[physical_reads_delta]', 16
913			WHERE
914				@delta_interval > 0
915			UNION ALL
916			SELECT '[writes_delta]', 17
917			WHERE
918				@delta_interval > 0
919			UNION ALL
920			SELECT '[tempdb_allocations_delta]', 18
921			WHERE
922				@delta_interval > 0
923			UNION ALL
924			SELECT '[tempdb_current_delta]', 19
925			WHERE
926				@delta_interval > 0
927			UNION ALL
928			SELECT '[CPU_delta]', 20
929			WHERE
930				@delta_interval > 0
931			UNION ALL
932			SELECT '[context_switches_delta]', 21
933			WHERE
934				@delta_interval > 0
935				AND @get_task_info = 2
936			UNION ALL
937			SELECT '[used_memory_delta]', 22
938			WHERE
939				@delta_interval > 0
940			UNION ALL
941			SELECT '[tasks]', 23
942			WHERE
943				@get_task_info = 2
944			UNION ALL
945			SELECT '[status]', 24
946			UNION ALL
947			SELECT '[wait_info]', 25
948			WHERE
949				@get_task_info > 0
950				OR @find_block_leaders = 1
951			UNION ALL
952			SELECT '[locks]', 26
953			WHERE
954				@get_locks = 1
955			UNION ALL
956			SELECT '[tran_start_time]', 27
957			WHERE
958				@get_transaction_info = 1
959			UNION ALL
960			SELECT '[tran_log_writes]', 28
961			WHERE
962				@get_transaction_info = 1
963			UNION ALL
964			SELECT '[open_tran_count]', 29
965			UNION ALL
966			SELECT '[sql_command]', 30
967			WHERE
968				@get_outer_command = 1
969			UNION ALL
970			SELECT '[sql_text]', 31
971			UNION ALL
972			SELECT '[query_plan]', 32
973			WHERE
974				@get_plans >= 1
975			UNION ALL
976			SELECT '[blocking_session_id]', 33
977			WHERE
978				@get_task_info > 0
979				OR @find_block_leaders = 1
980			UNION ALL
981			SELECT '[blocked_session_count]', 34
982			WHERE
983				@find_block_leaders = 1
984			UNION ALL
985			SELECT '[percent_complete]', 35
986			UNION ALL
987			SELECT '[host_name]', 36
988			UNION ALL
989			SELECT '[login_name]', 37
990			UNION ALL
991			SELECT '[database_name]', 38
992			UNION ALL
993			SELECT '[program_name]', 39
994			UNION ALL
995			SELECT '[additional_info]', 40
996			WHERE
997				@get_additional_info = 1
998			UNION ALL
999			SELECT '[start_time]', 41
1000			UNION ALL
1001			SELECT '[login_time]', 42
1002			UNION ALL
1003			SELECT '[request_id]', 43
1004			UNION ALL
1005			SELECT '[collection_time]', 44
1006		) AS x ON 
1007			x.column_name LIKE token ESCAPE '|'
1008	)
1009	SELECT
1010		@output_column_list =
1011			STUFF
1012			(
1013				(
1014					SELECT
1015						',' + column_name as [text()]
1016					FROM ordered_columns
1017					WHERE
1018						r = 1
1019					ORDER BY
1020						s
1021					FOR XML
1022						PATH('')
1023				),
1024				1,
1025				1,
1026				''
1027			);
1028	
1029	IF COALESCE(RTRIM(@output_column_list), '') = ''
1030	BEGIN;
1031		RAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1);
1032		RETURN;
1033	END;
1034	
1035	IF @destination_table <> ''
1036	BEGIN;
1037		SET @destination_table = 
1038			--database
1039			COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') +
1040			--schema
1041			COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') +
1042			--table
1043			COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), '');
1044			
1045		IF COALESCE(RTRIM(@destination_table), '') = ''
1046		BEGIN;
1047			RAISERROR('Destination table not properly formatted.', 16, 1);
1048			RETURN;
1049		END;
1050	END;
1051
1052	WITH
1053	a0 AS
1054	(SELECT 1 AS n UNION ALL SELECT 1),
1055	a1 AS
1056	(SELECT 1 AS n FROM a0 AS a, a0 AS b),
1057	a2 AS
1058	(SELECT 1 AS n FROM a1 AS a, a1 AS b),
1059	a3 AS
1060	(SELECT 1 AS n FROM a2 AS a, a2 AS b),
1061	a4 AS
1062	(SELECT 1 AS n FROM a3 AS a, a3 AS b),
1063	numbers AS
1064	(
1065		SELECT TOP(LEN(@sort_order))
1066			ROW_NUMBER() OVER
1067			(
1068				ORDER BY (SELECT NULL)
1069			) AS number
1070		FROM a4
1071		ORDER BY
1072			number
1073	),
1074	tokens AS
1075	(
1076		SELECT 
1077			'|[' +
1078				SUBSTRING
1079				(
1080					@sort_order,
1081					number + 1,
1082					CHARINDEX(']', @sort_order, number) - number - 1
1083				) + '|]' AS token,
1084			SUBSTRING
1085			(
1086				@sort_order,
1087				CHARINDEX(']', @sort_order, number) + 1,
1088				COALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number)
1089			) AS next_chunk,
1090			number
1091		FROM numbers
1092		WHERE
1093			SUBSTRING(@sort_order, number, 1) = '['
1094	),
1095	ordered_columns AS
1096	(
1097		SELECT
1098			x.column_name +
1099				CASE
1100					WHEN tokens.next_chunk LIKE '%asc%' THEN ' ASC'
1101					WHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC'
1102					ELSE ''
1103				END AS column_name,
1104			ROW_NUMBER() OVER
1105			(
1106				PARTITION BY
1107					x.column_name
1108				ORDER BY
1109					tokens.number
1110			) AS r,
1111			tokens.number
1112		FROM tokens
1113		JOIN
1114		(
1115			SELECT '[session_id]' AS column_name
1116			UNION ALL
1117			SELECT '[physical_io]'
1118			UNION ALL
1119			SELECT '[reads]'
1120			UNION ALL
1121			SELECT '[physical_reads]'
1122			UNION ALL
1123			SELECT '[writes]'
1124			UNION ALL
1125			SELECT '[tempdb_allocations]'
1126			UNION ALL
1127			SELECT '[tempdb_current]'
1128			UNION ALL
1129			SELECT '[CPU]'
1130			UNION ALL
1131			SELECT '[context_switches]'
1132			UNION ALL
1133			SELECT '[used_memory]'
1134			UNION ALL
1135			SELECT '[physical_io_delta]'
1136			UNION ALL
1137			SELECT '[reads_delta]'
1138			UNION ALL
1139			SELECT '[physical_reads_delta]'
1140			UNION ALL
1141			SELECT '[writes_delta]'
1142			UNION ALL
1143			SELECT '[tempdb_allocations_delta]'
1144			UNION ALL
1145			SELECT '[tempdb_current_delta]'
1146			UNION ALL
1147			SELECT '[CPU_delta]'
1148			UNION ALL
1149			SELECT '[context_switches_delta]'
1150			UNION ALL
1151			SELECT '[used_memory_delta]'
1152			UNION ALL
1153			SELECT '[tasks]'
1154			UNION ALL
1155			SELECT '[tran_start_time]'
1156			UNION ALL
1157			SELECT '[open_tran_count]'
1158			UNION ALL
1159			SELECT '[blocking_session_id]'
1160			UNION ALL
1161			SELECT '[blocked_session_count]'
1162			UNION ALL
1163			SELECT '[percent_complete]'
1164			UNION ALL
1165			SELECT '[host_name]'
1166			UNION ALL
1167			SELECT '[login_name]'
1168			UNION ALL
1169			SELECT '[database_name]'
1170			UNION ALL
1171			SELECT '[start_time]'
1172			UNION ALL
1173			SELECT '[login_time]'
1174		) AS x ON 
1175			x.column_name LIKE token ESCAPE '|'
1176	)
1177	SELECT
1178		@sort_order = COALESCE(z.sort_order, '')
1179	FROM
1180	(
1181		SELECT
1182			STUFF
1183			(
1184				(
1185					SELECT
1186						',' + column_name as [text()]
1187					FROM ordered_columns
1188					WHERE
1189						r = 1
1190					ORDER BY
1191						number
1192					FOR XML
1193						PATH('')
1194				),
1195				1,
1196				1,
1197				''
1198			) AS sort_order
1199	) AS z;
1200
1201	CREATE TABLE #sessions
1202	(
1203		recursion SMALLINT NOT NULL,
1204		session_id SMALLINT NOT NULL,
1205		request_id INT NOT NULL,
1206		session_number INT NOT NULL,
1207		elapsed_time INT NOT NULL,
1208		avg_elapsed_time INT NULL,
1209		physical_io BIGINT NULL,
1210		reads BIGINT NULL,
1211		physical_reads BIGINT NULL,
1212		writes BIGINT NULL,
1213		tempdb_allocations BIGINT NULL,
1214		tempdb_current BIGINT NULL,
1215		CPU INT NULL,
1216		thread_CPU_snapshot BIGINT NULL,
1217		context_switches BIGINT NULL,
1218		used_memory BIGINT NOT NULL, 
1219		tasks SMALLINT NULL,
1220		status VARCHAR(30) NOT NULL,
1221		wait_info NVARCHAR(4000) NULL,
1222		locks XML NULL,
1223		transaction_id BIGINT NULL,
1224		tran_start_time DATETIME NULL,
1225		tran_log_writes NVARCHAR(4000) NULL,
1226		open_tran_count SMALLINT NULL,
1227		sql_command XML NULL,
1228		sql_handle VARBINARY(64) NULL,
1229		statement_start_offset INT NULL,
1230		statement_end_offset INT NULL,
1231		sql_text XML NULL,
1232		plan_handle VARBINARY(64) NULL,
1233		query_plan XML NULL,
1234		blocking_session_id SMALLINT NULL,
1235		blocked_session_count SMALLINT NULL,
1236		percent_complete REAL NULL,
1237		host_name sysname NULL,
1238		login_name sysname NOT NULL,
1239		database_name sysname NULL,
1240		program_name sysname NULL,
1241		additional_info XML NULL,
1242		start_time DATETIME NOT NULL,
1243		login_time DATETIME NULL,
1244		last_request_start_time DATETIME NULL,
1245		PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),
1246		UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
1247	);
1248
1249	IF @return_schema = 0
1250	BEGIN;
1251		--Disable unnecessary autostats on the table
1252		CREATE STATISTICS s_session_id ON #sessions (session_id)
1253		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1254		CREATE STATISTICS s_request_id ON #sessions (request_id)
1255		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1256		CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)
1257		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1258		CREATE STATISTICS s_session_number ON #sessions (session_number)
1259		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1260		CREATE STATISTICS s_status ON #sessions (status)
1261		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1262		CREATE STATISTICS s_start_time ON #sessions (start_time)
1263		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1264		CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)
1265		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1266		CREATE STATISTICS s_recursion ON #sessions (recursion)
1267		WITH SAMPLE 0 ROWS, NORECOMPUTE;
1268
1269		DECLARE @recursion SMALLINT;
1270		SET @recursion = 
1271			CASE @delta_interval
1272				WHEN 0 THEN 1
1273				ELSE -1
1274			END;
1275
1276		DECLARE @first_collection_ms_ticks BIGINT;
1277		DECLARE @last_collection_start DATETIME;
1278
1279		--Used for the delta pull
1280		REDO:;
1281		
1282		IF 
1283			@get_locks = 1 
1284			AND @recursion = 1
1285			AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
1286		BEGIN;
1287			SELECT
1288				y.resource_type,
1289				y.database_name,
1290				y.object_id,
1291				y.file_id,
1292				y.page_type,
1293				y.hobt_id,
1294				y.allocation_unit_id,
1295				y.index_id,
1296				y.schema_id,
1297				y.principal_id,
1298				y.request_mode,
1299				y.request_status,
1300				y.session_id,
1301				y.resource_description,
1302				y.request_count,
1303				s.request_id,
1304				s.start_time,
1305				CONVERT(sysname, NULL) AS object_name,
1306				CONVERT(sysname, NULL) AS index_name,
1307				CONVERT(sysname, NULL) AS schema_name,
1308				CONVERT(sysname, NULL) AS principal_name,
1309				CONVERT(NVARCHAR(2048), NULL) AS query_error
1310			INTO #locks
1311			FROM
1312			(
1313				SELECT
1314					sp.spid AS session_id,
1315					CASE sp.status
1316						WHEN 'sleeping' THEN CONVERT(INT, 0)
1317						ELSE sp.request_id
1318					END AS request_id,
1319					CASE sp.status
1320						WHEN 'sleeping' THEN sp.last_batch
1321						ELSE COALESCE(req.start_time, sp.last_batch)
1322					END AS start_time,
1323					sp.dbid
1324				FROM sys.sysprocesses AS sp
1325				OUTER APPLY
1326				(
1327					SELECT TOP(1)
1328						CASE
1329							WHEN 
1330							(
1331								sp.hostprocess > ''
1332								OR r.total_elapsed_time < 0
1333							) THEN
1334								r.start_time
1335							ELSE
1336								DATEADD
1337								(
1338									ms, 
1339									1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())), 
1340									DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
1341								)
1342						END AS start_time
1343					FROM sys.dm_exec_requests AS r
1344					WHERE
1345						r.session_id = sp.spid
1346						AND r.request_id = sp.request_id
1347				) AS req
1348				WHERE
1349					--Process inclusive filter
1350					1 =
1351						CASE
1352							WHEN @filter <> '' THEN
1353								CASE @filter_type
1354									WHEN 'session' THEN
1355										CASE
1356											WHEN
1357												CONVERT(SMALLINT, @filter) = 0
1358												OR sp.spid = CONVERT(SMALLINT, @filter)
1359													THEN 1
1360											ELSE 0
1361										END
1362									WHEN 'program' THEN
1363										CASE
1364											WHEN sp.program_name LIKE @filter THEN 1
1365											ELSE 0
1366										END
1367									WHEN 'login' THEN
1368										CASE
1369											WHEN sp.loginame LIKE @filter THEN 1
1370											ELSE 0
1371										END
1372									WHEN 'host' THEN
1373										CASE
1374											WHEN sp.hostname LIKE @filter THEN 1
1375											ELSE 0
1376										END
1377									WHEN 'database' THEN
1378										CASE
1379											WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
1380											ELSE 0
1381										END
1382									ELSE 0
1383								END
1384							ELSE 1
1385						END
1386					--Process exclusive filter
1387					AND 0 =
1388						CASE
1389							WHEN @not_filter <> '' THEN
1390								CASE @not_filter_type
1391									WHEN 'session' THEN
1392										CASE
1393											WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
1394											ELSE 0
1395										END
1396									WHEN 'program' THEN
1397										CASE
1398											WHEN sp.program_name LIKE @not_filter THEN 1
1399											ELSE 0
1400										END
1401									WHEN 'login' THEN
1402										CASE
1403											WHEN sp.loginame LIKE @not_filter THEN 1
1404											ELSE 0
1405										END
1406									WHEN 'host' THEN
1407										CASE
1408											WHEN sp.hostname LIKE @not_filter THEN 1
1409											ELSE 0
1410										END
1411									WHEN 'database' THEN
1412										CASE
1413											WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
1414											ELSE 0
1415										END
1416									ELSE 0
1417								END
1418							ELSE 0
1419						END
1420					AND 
1421					(
1422						@show_own_spid = 1
1423						OR sp.spid <> @@SPID
1424					)
1425					AND 
1426					(
1427						@show_system_spids = 1
1428						OR sp.hostprocess > ''
1429					)
1430					AND sp.ecid = 0
1431			) AS s
1432			INNER HASH JOIN
1433			(
1434				SELECT
1435					x.resource_type,
1436					x.database_name,
1437					x.object_id,
1438					x.file_id,
1439					CASE
1440						WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
1441						WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
1442						WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1443						WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1444						WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1445						WHEN x.page_no IS NOT NULL THEN '*'
1446						ELSE NULL
1447					END AS page_type,
1448					x.hobt_id,
1449					x.allocation_unit_id,
1450					x.index_id,
1451					x.schema_id,
1452					x.principal_id,
1453					x.request_mode,
1454					x.request_status,
1455					x.session_id,
1456					x.request_id,
1457					CASE
1458						WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
1459						ELSE NULL
1460					END AS resource_description,
1461					COUNT(*) AS request_count
1462				FROM
1463				(
1464					SELECT
1465						tl.resource_type +
1466							CASE
1467								WHEN tl.resource_subtype = '' THEN ''
1468								ELSE '.' + tl.resource_subtype
1469							END AS resource_type,
1470						COALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name,
1471						CONVERT
1472						(
1473							INT,
1474							CASE
1475								WHEN tl.resource_type = 'OBJECT' THEN tl.resource_associated_entity_id
1476								WHEN tl.resource_description LIKE '%object_id = %' THEN
1477									(
1478										SUBSTRING
1479										(
1480											tl.resource_description, 
1481											(CHARINDEX('object_id = ', tl.resource_description) + 12), 
1482											COALESCE
1483											(
1484												NULLIF
1485												(
1486													CHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12),
1487													0
1488												), 
1489												DATALENGTH(tl.resource_description)+1
1490											) - (CHARINDEX('object_id = ', tl.resource_description) + 12)
1491										)
1492									)
1493								ELSE NULL
1494							END
1495						) AS object_id,
1496						CONVERT
1497						(
1498							INT,
1499							CASE 
1500								WHEN tl.resource_type = 'FILE' THEN CONVERT(INT, tl.resource_description)
1501								WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1)
1502								ELSE NULL
1503							END
1504						) AS file_id,
1505						CONVERT
1506						(
1507							INT,
1508							CASE
1509								WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN 
1510									SUBSTRING
1511									(
1512										tl.resource_description, 
1513										CHARINDEX(':', tl.resource_description) + 1, 
1514										COALESCE
1515										(
1516											NULLIF
1517											(
1518												CHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1), 
1519												0
1520											), 
1521											DATALENGTH(tl.resource_description)+1
1522										) - (CHARINDEX(':', tl.resource_description) + 1)
1523									)
1524								ELSE NULL
1525							END
1526						) AS page_no,
1527						CASE
1528							WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id
1529							ELSE NULL
1530						END AS hobt_id,
1531						CASE
1532							WHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id
1533							ELSE NULL
1534						END AS allocation_unit_id,
1535						CONVERT
1536						(
1537							INT,
1538							CASE
1539								WHEN
1540									/*TODO: Deal with server principals*/ 
1541									tl.resource_subtype <> 'SERVER_PRINCIPAL' 
1542									AND tl.resource_description LIKE '%index_id or stats_id = %' THEN
1543									(
1544										SUBSTRING
1545										(
1546											tl.resource_description, 
1547											(CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23), 
1548											COALESCE
1549											(
1550												NULLIF
1551												(
1552													CHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23), 
1553													0
1554												), 
1555												DATALENGTH(tl.resource_description)+1
1556											) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23)
1557										)
1558									)
1559								ELSE NULL
1560							END 
1561						) AS index_id,
1562						CONVERT
1563						(
1564							INT,
1565							CASE
1566								WHEN tl.resource_description LIKE '%schema_id = %' THEN
1567									(
1568										SUBSTRING
1569										(
1570											tl.resource_description, 
1571											(CHARINDEX('schema_id = ', tl.resource_description) + 12), 
1572											COALESCE
1573											(
1574												NULLIF
1575												(
1576													CHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12), 
1577													0
1578												), 
1579												DATALENGTH(tl.resource_description)+1
1580											) - (CHARINDEX('schema_id = ', tl.resource_description) + 12)
1581										)
1582									)
1583								ELSE NULL
1584							END 
1585						) AS schema_id,
1586						CONVERT
1587						(
1588							INT,
1589							CASE
1590								WHEN tl.resource_description LIKE '%principal_id = %' THEN
1591									(
1592										SUBSTRING
1593										(
1594											tl.resource_description, 
1595											(CHARINDEX('principal_id = ', tl.resource_description) + 15), 
1596											COALESCE
1597											(
1598												NULLIF
1599												(
1600													CHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15), 
1601													0
1602												), 
1603												DATALENGTH(tl.resource_description)+1
1604											) - (CHARINDEX('principal_id = ', tl.resource_description) + 15)
1605										)
1606									)
1607								ELSE NULL
1608							END
1609						) AS principal_id,
1610						tl.request_mode,
1611						tl.request_status,
1612						tl.request_session_id AS session_id,
1613						tl.request_request_id AS request_id,
1614
1615						/*TODO: Applocks, other resource_descriptions*/
1616						RTRIM(tl.resource_description) AS resource_description,
1617						tl.resource_associated_entity_id
1618						/*********************************************/
1619					FROM 
1620					(
1621						SELECT 
1622							request_session_id,
1623							CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,
1624							CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,
1625							resource_database_id,
1626							CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,
1627							resource_associated_entity_id,
1628							CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,
1629							CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,
1630							request_request_id
1631						FROM sys.dm_tran_locks
1632					) AS tl
1633				) AS x
1634				GROUP BY
1635					x.resource_type,
1636					x.database_name,
1637					x.object_id,
1638					x.file_id,
1639					CASE
1640						WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
1641						WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
1642						WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1643						WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1644						WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1645						WHEN x.page_no IS NOT NULL THEN '*'
1646						ELSE NULL
1647					END,
1648					x.hobt_id,
1649					x.allocation_unit_id,
1650					x.index_id,
1651					x.schema_id,
1652					x.principal_id,
1653					x.request_mode,
1654					x.request_status,
1655					x.session_id,
1656					x.request_id,
1657					CASE
1658						WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
1659						ELSE NULL
1660					END
1661			) AS y ON
1662				y.session_id = s.session_id
1663				AND y.request_id = s.request_id
1664			OPTION (HASH GROUP);
1665
1666			--Disable unnecessary autostats on the table
1667			CREATE STATISTICS s_database_name ON #locks (database_name)
1668			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1669			CREATE STATISTICS s_object_id ON #locks (object_id)
1670			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1671			CREATE STATISTICS s_hobt_id ON #locks (hobt_id)
1672			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1673			CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)
1674			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1675			CREATE STATISTICS s_index_id ON #locks (index_id)
1676			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1677			CREATE STATISTICS s_schema_id ON #locks (schema_id)
1678			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1679			CREATE STATISTICS s_principal_id ON #locks (principal_id)
1680			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1681			CREATE STATISTICS s_request_id ON #locks (request_id)
1682			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1683			CREATE STATISTICS s_start_time ON #locks (start_time)
1684			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1685			CREATE STATISTICS s_resource_type ON #locks (resource_type)
1686			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1687			CREATE STATISTICS s_object_name ON #locks (object_name)
1688			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1689			CREATE STATISTICS s_schema_name ON #locks (schema_name)
1690			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1691			CREATE STATISTICS s_page_type ON #locks (page_type)
1692			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1693			CREATE STATISTICS s_request_mode ON #locks (request_mode)
1694			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1695			CREATE STATISTICS s_request_status ON #locks (request_status)
1696			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1697			CREATE STATISTICS s_resource_description ON #locks (resource_description)
1698			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1699			CREATE STATISTICS s_index_name ON #locks (index_name)
1700			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1701			CREATE STATISTICS s_principal_name ON #locks (principal_name)
1702			WITH SAMPLE 0 ROWS, NORECOMPUTE;
1703		END;
1704		
1705		DECLARE 
1706			@sql VARCHAR(MAX), 
1707			@sql_n NVARCHAR(MAX);
1708
1709		SET @sql = 
1710			CONVERT(VARCHAR(MAX), '') +
1711			'DECLARE @blocker BIT;
1712			SET @blocker = 0;
1713			DECLARE @i INT;
1714			SET @i = 2147483647;
1715
1716			DECLARE @sessions TABLE
1717			(
1718				session_id SMALLINT NOT NULL,
1719				request_id INT NOT NULL,
1720				login_time DATETIME,
1721				last_request_end_time DATETIME,
1722				status VARCHAR(30),
1723				statement_start_offset INT,
1724				statement_end_offset INT,
1725				sql_handle BINARY(20),
1726				host_name NVARCHAR(128),
1727				login_name NVARCHAR(128),
1728				program_name NVARCHAR(128),
1729				database_id SMALLINT,
1730				memory_usage INT,
1731				open_tran_count SMALLINT, 
1732				' +
1733				CASE
1734					WHEN 
1735					(
1736						@get_task_info <> 0 
1737						OR @find_block_leaders = 1 
1738					) THEN
1739						'wait_type NVARCHAR(32),
1740						wait_resource NVARCHAR(256),
1741						wait_time BIGINT, 
1742						'
1743					ELSE 
1744						''
1745				END +
1746				'blocked SMALLINT,
1747				is_user_process BIT,
1748				cmd VARCHAR(32),
1749				PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
1750			);
1751
1752			DECLARE @blockers TABLE
1753			(
1754				session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
1755			);
1756
1757			BLOCKERS:;
1758
1759			INSERT @sessions
1760			(
1761				session_id,
1762				request_id,
1763				login_time,
1764				last_request_end_time,
1765				status,
1766				statement_start_offset,
1767				statement_end_offset,
1768				sql_handle,
1769				host_name,
1770				login_name,
1771				program_name,
1772				database_id,
1773				memory_usage,
1774				open_tran_count, 
1775				' +
1776				CASE
1777					WHEN 
1778					(
1779						@get_task_info <> 0
1780						OR @find_block_leaders = 1 
1781					) THEN
1782						'wait_type,
1783						wait_resource,
1784						wait_time, 
1785						'
1786					ELSE
1787						''
1788				END +
1789				'blocked,
1790				is_user_process,
1791				cmd 
1792			)
1793			SELECT TOP(@i)
1794				spy.session_id,
1795				spy.request_id,
1796				spy.login_time,
1797				spy.last_request_end_time,
1798				spy.status,
1799				spy.statement_start_offset,
1800				spy.statement_end_offset,
1801				spy.sql_handle,
1802				spy.host_name,
1803				spy.login_name,
1804				spy.program_name,
1805				spy.database_id,
1806				spy.memory_usage,
1807				spy.open_tran_count,
1808				' +
1809				CASE
1810					WHEN 
1811					(
1812						@get_task_info <> 0  
1813						OR @find_block_leaders = 1 
1814					) THEN
1815						'spy.wait_type,
1816						CASE
1817							WHEN
1818								spy.wait_type LIKE N''PAGE%LATCH_%''
1819								OR spy.wait_type = N''CXPACKET''
1820								OR spy.wait_type LIKE N''LATCH[_]%''
1821								OR spy.wait_type = N''OLEDB'' THEN
1822									spy.wait_resource
1823							ELSE
1824								NULL
1825						END AS wait_resource,
1826						spy.wait_time, 
1827						'
1828					ELSE
1829						''
1830				END +
1831				'spy.blocked,
1832				spy.is_user_process,
1833				spy.cmd
1834			FROM
1835			(
1836				SELECT TOP(@i)
1837					spx.*, 
1838					' +
1839					CASE
1840						WHEN 
1841						(
1842							@get_task_info <> 0 
1843							OR @find_block_leaders = 1 
1844						) THEN
1845							'ROW_NUMBER() OVER
1846							(
1847								PARTITION BY
1848									spx.session_id,
1849									spx.request_id
1850								ORDER BY
1851									CASE
1852										WHEN spx.wait_type LIKE N''LCK[_]%'' THEN 
1853											1
1854										ELSE
1855											99
1856									END,
1857									spx.wait_time DESC,
1858									spx.blocked DESC
1859							) AS r 
1860							'
1861						ELSE 
1862							'1 AS r 
1863							'
1864					END +
1865				'FROM
1866				(
1867					SELECT TOP(@i)
1868						sp0.session_id,
1869						sp0.request_id,
1870						sp0.login_time,
1871						sp0.last_request_end_time,
1872						LOWER(sp0.status) AS status,
1873						CASE
1874							WHEN sp0.cmd = ''CREATE INDEX'' THEN
1875								0
1876							ELSE
1877								sp0.stmt_start
1878						END AS statement_start_offset,
1879						CASE
1880							WHEN sp0.cmd = N''CREATE INDEX'' THEN
1881								-1
1882							ELSE
1883								COALESCE(NULLIF(sp0.stmt_end, 0), -1)
1884						END AS statement_end_offset,
1885						sp0.sql_handle,
1886						sp0.host_name,
1887						sp0.login_name,
1888						sp0.program_name,
1889						sp0.database_id,
1890						sp0.memory_usage,
1891						sp0.open_tran_count, 
1892						' +
1893						CASE
1894							WHEN 
1895							(
1896								@get_task_info <> 0 
1897								OR @find_block_leaders = 1 
1898							) THEN
1899								'CASE
1900									WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
1901										sp0.wait_type
1902									ELSE
1903										NULL
1904								END AS wait_type,
1905								CASE
1906									WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN 
1907										sp0.wait_resource
1908									ELSE
1909										NULL
1910								END AS wait_resource,
1911								CASE
1912									WHEN sp0.wait_type <> N''CXPACKET'' THEN
1913										sp0.wait_time
1914									ELSE
1915										0
1916								END AS wait_time, 
1917								'
1918							ELSE
1919								''
1920						END +
1921						'sp0.blocked,
1922						sp0.is_user_process,
1923						sp0.cmd
1924					FROM
1925					(
1926						SELECT TOP(@i)
1927							sp1.session_id,
1928							sp1.request_id,
1929							sp1.login_time,
1930							sp1.last_request_end_time,
1931							sp1.status,
1932							sp1.cmd,
1933							sp1.stmt_start,
1934							sp1.stmt_end,
1935							MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
1936							sp1.host_name,
1937							MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
1938							sp1.program_name,
1939							sp1.database_id,
1940							MAX(sp1.memory_usage)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
1941							MAX(sp1.open_tran_count)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
1942							sp1.wait_type,
1943							sp1.wait_resource,
1944							sp1.wait_time,
1945							sp1.blocked,
1946							sp1.hostprocess,
1947							sp1.is_user_process
1948						FROM
1949						(
1950							SELECT TOP(@i)
1951								sp2.spid AS session_id,
1952								CASE sp2.status
1953									WHEN ''sleeping'' THEN
1954										CONVERT(INT, 0)
1955									ELSE
1956										sp2.request_id
1957								END AS request_id,
1958								MAX(sp2.login_time) AS login_time,
1959								MAX(sp2.last_batch) AS last_request_end_time,
1960								MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
1961								MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
1962								MAX(sp2.stmt_start) AS stmt_start,
1963								MAX(sp2.stmt_end) AS stmt_end,
1964								MAX(sp2.sql_handle) AS sql_handle,
1965								MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
1966								MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
1967								MAX
1968								(
1969									CASE
1970										WHEN blk.queue_id IS NOT NULL THEN
1971											N''Service Broker
1972												database_id: '' + CONVERT(NVARCHAR, blk.database_id) +
1973												N'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id)
1974										ELSE
1975											CONVERT
1976											(
1977												sysname,
1978												RTRIM(sp2.program_name)
1979											)
1980									END COLLATE SQL_Latin1_General_CP1_CI_AS
1981								) AS program_name,
1982								MAX(sp2.dbid) AS database_id,
1983								MAX(sp2.memusage) AS memory_usage,
1984								MAX(sp2.open_tran) AS open_tran_count,
1985								RTRIM(sp2.lastwaittype) AS wait_type,
1986								RTRIM(sp2.waitresource) AS wait_resource,
1987								MAX(sp2.waittime) AS wait_time,
1988								COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
1989								MAX
1990								(
1991									CASE
1992										WHEN blk.session_id = sp2.spid THEN
1993											''blocker''
1994										ELSE
1995											RTRIM(sp2.hostprocess)
1996									END
1997								) AS hostprocess,
1998								CONVERT
1999								(
2000									BIT,
2001									MAX
2002									(
2003										CASE
2004											WHEN sp2.hostprocess > '''' THEN
2005												1
2006											ELSE
2007												0
2008										END
2009									)
2010								) AS is_user_process
2011							FROM
2012							(
2013								SELECT TOP(@i)
2014									session_id,
2015									CONVERT(INT, NULL) AS queue_id,
2016									CONVERT(INT, NULL) AS database_id
2017								FROM @blockers
2018
2019								UNION ALL
2020
2021								SELECT TOP(@i)
2022									CONVERT(SMALLINT, 0),
2023									CONVERT(INT, NULL) AS queue_id,
2024									CONVERT(INT, NULL) AS database_id
2025								WHERE
2026									@blocker = 0
2027
2028								UNION ALL
2029
2030								SELECT TOP(@i)
2031									CONVERT(SMALLINT, spid),
2032									queue_id,
2033									database_id
2034								FROM sys.dm_broker_activated_tasks
2035								WHERE
2036									@blocker = 0
2037							) AS blk
2038							INNER JOIN sys.sysprocesses AS sp2 ON
2039								sp2.spid = blk.session_id
2040								OR
2041								(
2042									blk.session_id = 0
2043									AND @blocker = 0
2044								)
2045							' +
2046							CASE 
2047								WHEN 
2048								(
2049									@get_task_info = 0 
2050									AND @find_block_leaders = 0
2051								) THEN
2052									'WHERE
2053										sp2.ecid = 0 
2054									' 
2055								ELSE
2056									''
2057							END +
2058							'GROUP BY
2059								sp2.spid,
2060								CASE sp2.status
2061									WHEN ''sleeping'' THEN
2062										CONVERT(INT, 0)
2063									ELSE
2064										sp2.request_id
2065								END,
2066								RTRIM(sp2.lastwaittype),
2067								RTRIM(sp2.waitresource),
2068								COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
2069						) AS sp1
2070					) AS sp0
2071					WHERE
2072						@blocker = 1
2073						OR
2074						(1=1 
2075						' +
2076							--inclusive filter
2077							CASE
2078								WHEN @filter <> '' THEN
2079									CASE @filter_type
2080										WHEN 'session' THEN
2081											CASE
2082												WHEN CONVERT(SMALLINT, @filter) <> 0 THEN
2083													'AND sp0.session_id = CONVERT(SMALLINT, @filter) 
2084													'
2085												ELSE
2086													''
2087											END
2088										WHEN 'program' THEN
2089											'AND sp0.program_name LIKE @filter 
2090											'
2091										WHEN 'login' THEN
2092											'AND sp0.login_name LIKE @filter 
2093											'
2094										WHEN 'host' THEN
2095											'AND sp0.host_name LIKE @filter 
2096											'
2097										WHEN 'database' THEN
2098											'AND DB_NAME(sp0.database_id) LIKE @filter 
2099											'
2100										ELSE
2101											''
2102									END
2103								ELSE
2104									''
2105							END +
2106							--exclusive filter
2107							CASE
2108								WHEN @not_filter <> '' THEN
2109									CASE @not_filter_type
2110										WHEN 'session' THEN
2111											CASE
2112												WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN
2113													'AND sp0.session_id <> CONVERT(SMALLINT, @not_filter) 
2114													'
2115												ELSE
2116													''
2117											END
2118										WHEN 'program' THEN
2119											'AND sp0.program_name NOT LIKE @not_filter 
2120											'
2121										WHEN 'login' THEN
2122											'AND sp0.login_name NOT LIKE @not_filter 
2123											'
2124										WHEN 'host' THEN
2125											'AND sp0.host_name NOT LIKE @not_filter 
2126											'
2127										WHEN 'database' THEN
2128											'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter 
2129											'
2130										ELSE
2131											''
2132									END
2133								ELSE
2134									''
2135							END +
2136							CASE @show_own_spid
2137								WHEN 1 THEN
2138									''
2139								ELSE
2140									'AND sp0.session_id <> @@spid 
2141									'
2142							END +
2143							CASE 
2144								WHEN @show_system_spids = 0 THEN
2145									'AND sp0.hostprocess > '''' 
2146									' 
2147								ELSE
2148									''
2149							END +
2150							CASE @show_sleeping_spids
2151								WHEN 0 THEN
2152									'AND sp0.status <> ''sleeping'' 
2153									'
2154								WHEN 1 THEN
2155									'AND
2156									(
2157										sp0.status <> ''sleeping''
2158										OR sp0.open_tran_count > 0
2159									)
2160									'
2161								ELSE
2162									''
2163							END +
2164						')
2165				) AS spx
2166			) AS spy
2167			WHERE
2168				spy.r = 1; 
2169			' + 
2170			CASE @recursion
2171				WHEN 1 THEN 
2172					'IF @@ROWCOUNT > 0
2173					BEGIN;
2174						INSERT @blockers
2175						(
2176							session_id
2177						)
2178						SELECT TOP(@i)
2179							blocked
2180						FROM @sessions
2181						WHERE
2182							NULLIF(blocked, 0) IS NOT NULL
2183
2184						EXCEPT
2185
2186						SELECT TOP(@i)
2187							session_id
2188						FROM @sessions; 
2189						' +
2190
2191						CASE
2192							WHEN
2193							(
2194								@get_task_info > 0
2195								OR @find_block_leaders = 1
2196							) THEN
2197								'IF @@ROWCOUNT > 0
2198								BEGIN;
2199									SET @blocker = 1;
2200									GOTO BLOCKERS;
2201								END; 
2202								'
2203							ELSE 
2204								''
2205						END +
2206					'END; 
2207					'
2208				ELSE 
2209					''
2210			END +
2211			'SELECT TOP(@i)
2212				@recursion AS recursion,
2213				x.session_id,
2214				x.request_id,
2215				DENSE_RANK() OVER
2216				(
2217					ORDER BY
2218						x.session_id
2219				) AS session_number,
2220				' +
2221				CASE
2222					WHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN 
2223						'x.elapsed_time '
2224					ELSE 
2225						'0 '
2226				END + 
2227					'AS elapsed_time, 
2228					' +
2229				CASE
2230					WHEN
2231						(
2232							@output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR 
2233							@output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|'
2234						)
2235						AND @recursion = 1
2236							THEN 
2237								'x.avg_elapsed_time / 1000 '
2238					ELSE 
2239						'NULL '
2240				END + 
2241					'AS avg_elapsed_time, 
2242					' +
2243				CASE
2244					WHEN 
2245						@output_column_list LIKE '%|[physical_io|]%' ESCAPE '|'
2246						OR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|'
2247							THEN 
2248								'x.physical_io '
2249					ELSE 
2250						'NULL '
2251				END + 
2252					'AS physical_io, 
2253					' +
2254				CASE
2255					WHEN 
2256						@output_column_list LIKE '%|[reads|]%' ESCAPE '|'
2257						OR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|'
2258							THEN 
2259								'x.reads '
2260					ELSE 
2261						'0 '
2262				END + 
2263					'AS reads, 
2264					' +
2265				CASE
2266					WHEN 
2267						@output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|'
2268						OR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|'
2269							THEN 
2270								'x.physical_reads '
2271					ELSE 
2272						'0 '
2273				END + 
2274					'AS physical_reads, 
2275					' +
2276				CASE
2277					WHEN 
2278						@output_column_list LIKE '%|[writes|]%' ESCAPE '|'
2279						OR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|'
2280							THEN 
2281								'x.writes '
2282					ELSE 
2283						'0 '
2284				END + 
2285					'AS writes, 
2286					' +
2287				CASE
2288					WHEN 
2289						@output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|'
2290						OR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|'
2291							THEN 
2292								'x.tempdb_allocations '
2293					ELSE 
2294						'0 '
2295				END + 
2296					'AS tempdb_allocations, 
2297					' +
2298				CASE
2299					WHEN 
2300						@output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|'
2301						OR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|'
2302							THEN 
2303								'x.tempdb_current '
2304					ELSE 
2305						'0 '
2306				END + 
2307					'AS tempdb_current, 
2308					' +
2309				CASE
2310					WHEN 
2311						@output_column_list LIKE '%|[CPU|]%' ESCAPE '|'
2312						OR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2313							THEN
2314								'x.CPU '
2315					ELSE
2316						'0 '
2317				END + 
2318					'AS CPU, 
2319					' +
2320				CASE
2321					WHEN 
2322						@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2323						AND @get_task_info = 2
2324							THEN 
2325								'x.thread_CPU_snapshot '
2326					ELSE 
2327						'0 '
2328				END + 
2329					'AS thread_CPU_snapshot, 
2330					' +
2331				CASE
2332					WHEN 
2333						@output_column_list LIKE '%|[context_switches|]%' ESCAPE '|'
2334						OR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|'
2335							THEN 
2336								'x.context_switches '
2337					ELSE 
2338						'NULL '
2339				END + 
2340					'AS context_switches, 
2341					' +
2342				CASE
2343					WHEN 
2344						@output_column_list LIKE '%|[used_memory|]%' ESCAPE '|'
2345						OR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|'
2346							THEN 
2347								'x.used_memory '
2348					ELSE 
2349						'0 '
2350				END + 
2351					'AS used_memory, 
2352					' +
2353				CASE
2354					WHEN 
2355						@output_column_list LIKE '%|[tasks|]%' ESCAPE '|'
2356						AND @recursion = 1
2357							THEN 
2358								'x.tasks '
2359					ELSE 
2360						'NULL '
2361				END + 
2362					'AS tasks, 
2363					' +
2364				CASE
2365					WHEN 
2366						(
2367							@output_column_list LIKE '%|[status|]%' ESCAPE '|' 
2368							OR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
2369						)
2370						AND @recursion = 1
2371							THEN 
2372								'x.status '
2373					ELSE 
2374						''''' '
2375				END + 
2376					'AS status, 
2377					' +
2378				CASE
2379					WHEN 
2380						@output_column_list LIKE '%|[wait_info|]%' ESCAPE '|' 
2381						AND @recursion = 1
2382							THEN 
2383								CASE @get_task_info
2384									WHEN 2 THEN
2385										'COALESCE(x.task_wait_info, x.sys_wait_info) '
2386									ELSE
2387										'x.sys_wait_info '
2388								END
2389					ELSE 
2390						'NULL '
2391				END + 
2392					'AS wait_info, 
2393					' +
2394				CASE
2395					WHEN 
2396						(
2397							@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|' 
2398							OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|' 
2399						)
2400						AND @recursion = 1
2401							THEN 
2402								'x.transaction_id '
2403					ELSE 
2404						'NULL '
2405				END + 
2406					'AS transaction_id, 
2407					' +
2408				CASE
2409					WHEN 
2410						@output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|' 
2411						AND @recursion = 1
2412							THEN 
2413								'x.open_tran_count '
2414					ELSE 
2415						'NULL '
2416				END + 
2417					'AS open_tran_count, 
2418					' +
2419				CASE
2420					WHEN 
2421						@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' 
2422						AND @recursion = 1
2423							THEN 
2424								'x.sql_handle '
2425					ELSE 
2426						'NULL '
2427				END + 
2428					'AS sql_handle, 
2429					' +
2430				CASE
2431					WHEN 
2432						(
2433							@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' 
2434							OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' 
2435						)
2436						AND @recursion = 1
2437							THEN 
2438								'x.statement_start_offset '
2439					ELSE 
2440						'NULL '
2441				END + 
2442					'AS statement_start_offset, 
2443					' +
2444				CASE
2445					WHEN 
2446						(
2447							@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' 
2448							OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' 
2449						)
2450						AND @recursion = 1
2451							THEN 
2452								'x.statement_end_offset '
2453					ELSE 
2454						'NULL '
2455				END + 
2456					'AS statement_end_offset, 
2457					' +
2458				'NULL AS sql_text, 
2459					' +
2460				CASE
2461					WHEN 
2462						@output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' 
2463						AND @recursion = 1
2464							THEN 
2465								'x.plan_handle '
2466					ELSE 
2467						'NULL '
2468				END + 
2469					'AS plan_handle, 
2470					' +
2471				CASE
2472					WHEN 
2473						@output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|' 
2474						AND @recursion = 1
2475							THEN 
2476								'NULLIF(x.blocking_session_id, 0) '
2477					ELSE 
2478						'NULL '
2479				END + 
2480					'AS blocking_session_id, 
2481					' +
2482				CASE
2483					WHEN 
2484						@output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|'
2485						AND @recursion = 1
2486							THEN 
2487								'x.percent_complete '
2488					ELSE 
2489						'NULL '
2490				END + 
2491					'AS percent_complete, 
2492					' +
2493				CASE
2494					WHEN 
2495						@output_column_list LIKE '%|[host_name|]%' ESCAPE '|' 
2496						AND @recursion = 1
2497							THEN 
2498								'x.host_name '
2499					ELSE 
2500						''''' '
2501				END + 
2502					'AS host_name, 
2503					' +
2504				CASE
2505					WHEN 
2506						@output_column_list LIKE '%|[login_name|]%' ESCAPE '|' 
2507						AND @recursion = 1
2508							THEN 
2509								'x.login_name '
2510					ELSE 
2511						''''' '
2512				END + 
2513					'AS login_name, 
2514					' +
2515				CASE
2516					WHEN 
2517						@output_column_list LIKE '%|[database_name|]%' ESCAPE '|' 
2518						AND @recursion = 1
2519							THEN 
2520								'DB_NAME(x.database_id) '
2521					ELSE 
2522						'NULL '
2523				END + 
2524					'AS database_name, 
2525					' +
2526				CASE
2527					WHEN 
2528						@output_column_list LIKE '%|[program_name|]%' ESCAPE '|' 
2529						AND @recursion = 1
2530							THEN 
2531								'x.program_name '
2532					ELSE 
2533						''''' '
2534				END + 
2535					'AS program_name, 
2536					' +
2537				CASE
2538					WHEN
2539						@output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
2540						AND @recursion = 1
2541							THEN
2542								'(
2543									SELECT TOP(@i)
2544										x.text_size,
2545										x.language,
2546										x.date_format,
2547										x.date_first,
2548										CASE x.quoted_identifier
2549											WHEN 0 THEN ''OFF''
2550											WHEN 1 THEN ''ON''
2551										END AS quoted_identifier,
2552										CASE x.arithabort
2553											WHEN 0 THEN ''OFF''
2554											WHEN 1 THEN ''ON''
2555										END AS arithabort,
2556										CASE x.ansi_null_dflt_on
2557											WHEN 0 THEN ''OFF''
2558											WHEN 1 THEN ''ON''
2559										END AS ansi_null_dflt_on,
2560										CASE x.ansi_defaults
2561											WHEN 0 THEN ''OFF''
2562											WHEN 1 THEN ''ON''
2563										END AS ansi_defaults,
2564										CASE x.ansi_warnings
2565											WHEN 0 THEN ''OFF''
2566											WHEN 1 THEN ''ON''
2567										END AS ansi_warnings,
2568										CASE x.ansi_padding
2569											WHEN 0 THEN ''OFF''
2570											WHEN 1 THEN ''ON''
2571										END AS ansi_padding,
2572										CASE ansi_nulls
2573											WHEN 0 THEN ''OFF''
2574											WHEN 1 THEN ''ON''
2575										END AS ansi_nulls,
2576										CASE x.concat_null_yields_null
2577											WHEN 0 THEN ''OFF''
2578											WHEN 1 THEN ''ON''
2579										END AS concat_null_yields_null,
2580										CASE x.transaction_isolation_level
2581											WHEN 0 THEN ''Unspecified''
2582											WHEN 1 THEN ''ReadUncomitted''
2583											WHEN 2 THEN ''ReadCommitted''
2584											WHEN 3 THEN ''Repeatable''
2585											WHEN 4 THEN ''Serializable''
2586											WHEN 5 THEN ''Snapshot''
2587										END AS transaction_isolation_level,
2588										x.lock_timeout,
2589										x.deadlock_priority,
2590										x.row_count,
2591										x.command_type, 
2592										master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
2593										master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,
2594										' +
2595										CASE
2596											WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN
2597												'(
2598													SELECT TOP(1)
2599														CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id,
2600														agent_info.step_id,
2601														(
2602															SELECT TOP(1)
2603																NULL
2604															FOR XML
2605																PATH(''job_name''),
2606																TYPE
2607														),
2608														(
2609															SELECT TOP(1)
2610																NULL
2611															FOR XML
2612																PATH(''step_name''),
2613																TYPE
2614														)
2615													FROM
2616													(
2617														SELECT TOP(1)
2618															SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,
2619															SUBSTRING(x.program_name, CHARINDEX('': Step '', x.program_name) + 7, CHARINDEX('')'', x.program_name, CHARINDEX('': Step '', x.program_name)) - (CHARINDEX('': Step '', x.program_name) + 7)) AS step_id
2620														WHERE
2621															x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''
2622													) AS agent_info
2623													FOR XML
2624														PATH(''agent_job_info''),
2625														TYPE
2626												),
2627												'
2628											ELSE ''
2629										END +
2630										CASE
2631											WHEN @get_task_info = 2 THEN
2632												'CONVERT(XML, x.block_info) AS block_info, 
2633												'
2634											ELSE
2635												''
2636										END +
2637										'x.host_process_id 
2638									FOR XML
2639										PATH(''additional_info''),
2640										TYPE
2641								) '
2642					ELSE
2643						'NULL '
2644				END + 
2645					'AS additional_info, 
2646				x.start_time, 
2647					' +
2648				CASE
2649					WHEN
2650						@output_column_list LIKE '%|[login_time|]%' ESCAPE '|'
2651						AND @recursion = 1
2652							THEN
2653								'x.login_time '
2654					ELSE 
2655						'NULL '
2656				END + 
2657					'AS login_time, 
2658				x.last_request_start_time
2659			FROM
2660			(
2661				SELECT TOP(@i)
2662					y.*,
2663					CASE
2664						WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
2665							DATEDIFF(second, GETDATE(), y.start_time)
2666						ELSE DATEDIFF(ms, y.start_time, GETDATE())
2667					END AS elapsed_time,
2668					COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
2669					COALESCE
2670					(
2671						CASE
2672							WHEN tempdb_info.tempdb_current < 0 THEN 0
2673							ELSE tempdb_info.tempdb_current
2674						END,
2675						0
2676					) AS tempdb_current, 
2677					' +
2678					CASE
2679						WHEN 
2680							(
2681								@get_task_info <> 0
2682								OR @find_block_leaders = 1
2683							) THEN
2684								'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +
2685									y.wait_type +
2686										CASE
2687											WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN
2688												N'':'' +
2689												COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +
2690												N'':'' +
2691												SUBSTRING(y.resource_description, CHARINDEX(N'':'', y.resource_description) + 1, LEN(y.resource_description) - CHARINDEX(N'':'', REVERSE(y.resource_description)) - CHARINDEX(N'':'', y.resource_description)) +
2692												N''('' +
2693													CASE
2694														WHEN
2695															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR
2696															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0
2697																THEN 
2698																	N''PFS''
2699														WHEN
2700															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR
2701															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0
2702																THEN 
2703																	N''GAM''
2704														WHEN
2705															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR
2706															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
2707																THEN
2708																	N''SGAM''
2709														WHEN
2710															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR
2711															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0 
2712																THEN 
2713																	N''DCM''
2714														WHEN
2715															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR
2716															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0 
2717																THEN 
2718																	N''BCM''
2719														ELSE 
2720															N''*''
2721													END +
2722												N'')''
2723											WHEN y.wait_type = N''CXPACKET'' THEN
2724												N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)
2725											WHEN y.wait_type LIKE N''LATCH[_]%'' THEN
2726												N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''
2727											WHEN
2728												y.wait_type = N''OLEDB''
2729												AND y.resource_description LIKE N''%(SPID=%)'' THEN
2730													N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +
2731														N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) + 6, CHARINDEX(N'')'', y.resource_description, (CHARINDEX(N''(SPID='', y.resource_description) + 6)) - (CHARINDEX(N''(SPID='', y.resource_description) + 6)) + '']''
2732											ELSE
2733												N''''
2734										END COLLATE Latin1_General_Bin2 AS sys_wait_info, 
2735										'
2736							ELSE
2737								''
2738						END +
2739						CASE
2740							WHEN @get_task_info = 2 THEN
2741								'tasks.physical_io,
2742								tasks.context_switches,
2743								tasks.tasks,
2744								tasks.block_info,
2745								tasks.wait_info AS task_wait_info,
2746								tasks.thread_CPU_snapshot,
2747								'
2748							ELSE
2749								'' 
2750					END +
2751					CASE 
2752						WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN
2753							'CONVERT(INT, NULL) '
2754						ELSE 
2755							'qs.total_elapsed_time / qs.execution_count '
2756					END + 
2757						'AS avg_elapsed_time 
2758				FROM
2759				(
2760					SELECT TOP(@i)
2761						sp.session_id,
2762						sp.request_id,
2763						COALESCE(r.logical_reads, s.logical_reads) AS reads,
2764						COALESCE(r.reads, s.reads) AS physical_reads,
2765						COALESCE(r.writes, s.writes) AS writes,
2766						COALESCE(r.CPU_time, s.CPU_time) AS CPU,
2767						sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
2768						LOWER(sp.status) AS status,
2769						COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
2770						COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
2771						COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
2772						' +
2773						CASE
2774							WHEN 
2775							(
2776								@get_task_info <> 0
2777								OR @find_block_leaders = 1 
2778							) THEN
2779								'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2780								sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
2781								sp.wait_time AS wait_duration_ms, 
2782								'
2783							ELSE
2784								''
2785						END +
2786						'NULLIF(sp.blocked, 0) AS blocking_session_id,
2787						r.plan_handle,
2788						NULLIF(r.percent_complete, 0) AS percent_complete,
2789						sp.host_name,
2790						sp.login_name,
2791						sp.program_name,
2792						s.host_process_id,
2793						COALESCE(r.text_size, s.text_size) AS text_size,
2794						COALESCE(r.language, s.language) AS language,
2795						COALESCE(r.date_format, s.date_format) AS date_format,
2796						COALESCE(r.date_first, s.date_first) AS date_first,
2797						COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
2798						COALESCE(r.arithabort, s.arithabort) AS arithabort,
2799						COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
2800						COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
2801						COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
2802						COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
2803						COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
2804						COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
2805						COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
2806						COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
2807						COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
2808						COALESCE(r.row_count, s.row_count) AS row_count,
2809						COALESCE(r.command, sp.cmd) AS command_type,
2810						COALESCE
2811						(
2812							CASE
2813								WHEN
2814								(
2815									s.is_user_process = 0
2816									AND r.total_elapsed_time >= 0
2817								) THEN
2818									DATEADD
2819									(
2820										ms,
2821										1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2822										DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2823									)
2824							END,
2825							NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),
2826							(
2827								SELECT TOP(1)
2828									DATEADD(second, -(ms_ticks / 1000), GETDATE())
2829								FROM sys.dm_os_sys_info
2830							)
2831						) AS start_time,
2832						sp.login_time,
2833						CASE
2834							WHEN s.is_user_process = 1 THEN
2835								s.last_request_start_time
2836							ELSE
2837								COALESCE
2838								(
2839									DATEADD
2840									(
2841										ms,
2842										1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2843										DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2844									),
2845									s.last_request_start_time
2846								)
2847						END AS last_request_start_time,
2848						r.transaction_id,
2849						sp.database_id,
2850						sp.open_tran_count
2851					FROM @sessions AS sp
2852					LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
2853						s.session_id = sp.session_id
2854						AND s.login_time = sp.login_time
2855					LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
2856						sp.status <> ''sleeping''
2857						AND r.session_id = sp.session_id
2858						AND r.request_id = sp.request_id
2859						AND
2860						(
2861							(
2862								s.is_user_process = 0
2863								AND sp.is_user_process = 0
2864							)
2865							OR
2866							(
2867								r.start_time = s.last_request_start_time
2868								AND s.last_request_end_time <= sp.last_request_end_time
2869							)
2870						)
2871				) AS y
2872				' + 
2873				CASE 
2874					WHEN @get_task_info = 2 THEN
2875						CONVERT(VARCHAR(MAX), '') +
2876						'LEFT OUTER HASH JOIN
2877						(
2878							SELECT TOP(@i)
2879								task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id,
2880								task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id,
2881								task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io,
2882								task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches,
2883								task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,
2884								task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info,
2885								task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,
2886								task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot
2887							FROM
2888							(
2889								SELECT TOP(@i)
2890									CONVERT
2891									(
2892										XML,
2893										REPLACE
2894										(
2895											CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
2896											N''</waits></tasks><tasks><waits>'',
2897											N'', ''
2898										)
2899									) AS task_xml
2900								FROM
2901								(
2902									SELECT TOP(@i)
2903										CASE waits.r
2904											WHEN 1 THEN
2905												waits.session_id
2906											ELSE
2907												NULL
2908										END AS [session_id],
2909										CASE waits.r
2910											WHEN 1 THEN
2911												waits.request_id
2912											ELSE
2913												NULL
2914										END AS [request_id],											
2915										CASE waits.r
2916											WHEN 1 THEN
2917												waits.physical_io
2918											ELSE
2919												NULL
2920										END AS [physical_io],
2921										CASE waits.r
2922											WHEN 1 THEN
2923												waits.context_switches
2924											ELSE
2925												NULL
2926										END AS [context_switches],
2927										CASE waits.r
2928											WHEN 1 THEN
2929												waits.thread_CPU_snapshot
2930											ELSE
2931												NULL
2932										END AS [thread_CPU_snapshot],
2933										CASE waits.r
2934											WHEN 1 THEN
2935												waits.tasks
2936											ELSE
2937												NULL
2938										END AS [tasks],
2939										CASE waits.r
2940											WHEN 1 THEN
2941												waits.block_info
2942											ELSE
2943												NULL
2944										END AS [block_info],
2945										REPLACE
2946										(
2947											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2948											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2949											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2950												CONVERT
2951												(
2952													NVARCHAR(MAX),
2953													N''('' +
2954														CONVERT(NVARCHAR, num_waits) + N''x: '' +
2955														CASE num_waits
2956															WHEN 1 THEN
2957																CONVERT(NVARCHAR, min_wait_time) + N''ms''
2958															WHEN 2 THEN
2959																CASE
2960																	WHEN min_wait_time <> max_wait_time THEN
2961																		CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2962																	ELSE
2963																		CONVERT(NVARCHAR, max_wait_time) + N''ms''
2964																END
2965															ELSE
2966																CASE
2967																	WHEN min_wait_time <> max_wait_time THEN
2968																		CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2969																	ELSE 
2970																		CONVERT(NVARCHAR, max_wait_time) + N''ms''
2971																END
2972														END +
2973													N'')'' + wait_type COLLATE Latin1_General_Bin2
2974												),
2975												NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
2976												NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
2977												NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
2978											NCHAR(0),
2979											N''''
2980										) AS [waits]
2981									FROM
2982									(
2983										SELECT TOP(@i)
2984											w1.*,
2985											ROW_NUMBER() OVER
2986											(
2987												PARTITION BY
2988													w1.session_id,
2989													w1.request_id
2990												ORDER BY
2991													w1.block_info DESC,
2992													w1.num_waits DESC,
2993													w1.wait_type
2994											) AS r
2995										FROM
2996										(
2997											SELECT TOP(@i)
2998												task_info.session_id,
2999												task_info.request_id,
3000												task_info.physical_io,
3001												task_info.context_switches,
3002												task_info.thread_CPU_snapshot,
3003												task_info.num_tasks AS tasks,
3004												CASE
3005													WHEN task_info.runnable_time IS NOT NULL THEN
3006														''RUNNABLE''
3007													ELSE
3008														wt2.wait_type
3009												END AS wait_type,
3010												NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
3011												MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
3012												AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
3013												MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
3014												MAX(wt2.block_info) AS block_info
3015											FROM
3016											(
3017												SELECT TOP(@i)
3018													t.session_id,
3019													t.request_id,
3020													SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
3021													SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches, 
3022													' +
3023													CASE
3024														WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
3025															THEN
3026																'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '
3027														ELSE
3028															'CONVERT(BIGINT, NULL) '
3029													END + 
3030														' AS thread_CPU_snapshot, 
3031													COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
3032													t.task_address,
3033													t.task_state,
3034													CASE
3035														WHEN
3036															t.task_state = ''RUNNABLE''
3037															AND w.runnable_time > 0 THEN
3038																w.runnable_time
3039														ELSE
3040															NULL
3041													END AS runnable_time
3042												FROM sys.dm_os_tasks AS t
3043												CROSS APPLY
3044												(
3045													SELECT TOP(1)
3046														sp2.session_id
3047													FROM @sessions AS sp2
3048													WHERE
3049														sp2.session_id = t.session_id
3050														AND sp2.request_id = t.request_id
3051														AND sp2.status <> ''sleeping''
3052												) AS sp20
3053												LEFT OUTER HASH JOIN
3054												(
3055													SELECT TOP(@i)
3056														(
3057															SELECT TOP(@i)
3058																ms_ticks
3059															FROM sys.dm_os_sys_info
3060														) -
3061															w0.wait_resumed_ms_ticks AS runnable_time,
3062														w0.worker_address,
3063														w0.thread_address,
3064														w0.task_bound_ms_ticks
3065													FROM sys.dm_os_workers AS w0
3066													WHERE
3067														w0.state = ''RUNNABLE''
3068														OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks
3069												) AS w ON
3070													w.worker_address = t.worker_address 
3071												' +
3072												CASE
3073													WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
3074														THEN
3075															'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
3076																tr.thread_address = w.thread_address
3077																AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
3078															'
3079													ELSE
3080														''
3081												END +
3082											') AS task_info
3083											LEFT OUTER HASH JOIN
3084											(
3085												SELECT TOP(@i)
3086													wt1.wait_type,
3087													wt1.waiting_task_address,
3088													MAX(wt1.wait_duration_ms) AS wait_duration_ms,
3089													MAX(wt1.block_info) AS block_info
3090												FROM
3091												(
3092													SELECT DISTINCT TOP(@i)
3093														wt.wait_type +
3094															CASE
3095																WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN
3096																	'':'' +
3097																	COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +
3098																	N'':'' +
3099																	SUBSTRING(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) + 1, LEN(wt.resource_description) - CHARINDEX(N'':'', REVERSE(wt.resource_description)) - CHARINDEX(N'':'', wt.resource_description)) +
3100																	N''('' +
3101																		CASE
3102																			WHEN
3103																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR
3104																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0
3105																					THEN 
3106																						N''PFS''
3107																			WHEN
3108																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR
3109																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0 
3110																					THEN 
3111																						N''GAM''
3112																			WHEN
3113																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR
3114																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0 
3115																					THEN 
3116																						N''SGAM''
3117																			WHEN
3118																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR
3119																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0 
3120																					THEN 
3121																						N''DCM''
3122																			WHEN
3123																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR
3124																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
3125																					THEN 
3126																						N''BCM''
3127																			ELSE
3128																				N''*''
3129																		END +
3130																	N'')''
3131																WHEN wt.wait_type = N''CXPACKET'' THEN
3132																	N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)
3133																WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN
3134																	N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''
3135																ELSE 
3136																	N''''
3137															END COLLATE Latin1_General_Bin2 AS wait_type,
3138														CASE
3139															WHEN
3140															(
3141																wt.blocking_session_id IS NOT NULL
3142																AND wt.wait_type LIKE N''LCK[_]%''
3143															) THEN
3144																(
3145																	SELECT TOP(@i)
3146																		x.lock_type,
3147																		REPLACE
3148																		(
3149																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3150																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3151																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3152																				DB_NAME
3153																				(
3154																					CONVERT
3155																					(
3156																						INT,
3157																						SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''dbid='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''dbid='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''dbid='', wt.resource_description) - 5)
3158																					)
3159																				),
3160																				NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
3161																				NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
3162																				NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
3163																			NCHAR(0),
3164																			N''''
3165																		) AS database_name,
3166																		CASE x.lock_type
3167																			WHEN N''objectlock'' THEN
3168																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''objid='', wt.resource_description), 0) + 6, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''objid='', wt.resource_description) + 6), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''objid='', wt.resource_description) - 6)
3169																			ELSE
3170																				NULL
3171																		END AS object_id,
3172																		CASE x.lock_type
3173																			WHEN N''filelock'' THEN
3174																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''fileid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''fileid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''fileid='', wt.resource_description) - 7)
3175																			ELSE
3176																				NULL
3177																		END AS file_id,
3178																		CASE
3179																			WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN
3180																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''associatedObjectId='', wt.resource_description), 0) + 19, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''associatedObjectId='', wt.resource_description) + 19), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''associatedObjectId='', wt.resource_description) - 19)
3181																			WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN
3182																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hobtid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hobtid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hobtid='', wt.resource_description) - 7)
3183																			ELSE
3184																				NULL
3185																		END AS hobt_id,
3186																		CASE x.lock_type
3187																			WHEN N''applicationlock'' THEN
3188																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hash='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hash='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hash='', wt.resource_description) - 5)
3189																			ELSE
3190																				NULL
3191																		END AS applock_hash,
3192																		CASE x.lock_type
3193																			WHEN N''metadatalock'' THEN
3194																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''subresource='', wt.resource_description), 0) + 12, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''subresource='', wt.resource_description) + 12), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''subresource='', wt.resource_description) - 12)
3195																			ELSE
3196																				NULL
3197																		END AS metadata_resource,
3198																		CASE x.lock_type
3199																			WHEN N''metadatalock'' THEN
3200																				SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''classid='', wt.resource_description), 0) + 8, COALESCE(NULLIF(CHARINDEX(N'' dbid='', wt.resource_description) - CHARINDEX(N''classid='', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 8)
3201																			ELSE
3202																				NULL
3203																		END AS metadata_class_id
3204																	FROM
3205																	(
3206																		SELECT TOP(1)
3207																			LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
3208																	) AS x
3209																	FOR XML
3210																		PATH('''')
3211																)
3212															ELSE NULL
3213														END AS block_info,
3214														wt.wait_duration_ms,
3215														wt.waiting_task_address
3216													FROM
3217													(
3218														SELECT TOP(@i)
3219															wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
3220															wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
3221															wt0.wait_duration_ms,
3222															wt0.waiting_task_address,
3223															CASE
3224																WHEN wt0.blocking_session_id = p.blocked THEN
3225																	wt0.blocking_session_id
3226																ELSE
3227																	NULL
3228															END AS blocking_session_id
3229														FROM sys.dm_os_waiting_tasks AS wt0
3230														CROSS APPLY
3231														(
3232															SELECT TOP(1)
3233																s0.blocked
3234															FROM @sessions AS s0
3235															WHERE
3236																s0.session_id = wt0.session_id
3237																AND COALESCE(s0.wait_type, N'''') <> N''OLEDB''
3238																AND wt0.wait_type <> N''OLEDB''
3239														) AS p
3240													) AS wt
3241												) AS wt1
3242												GROUP BY
3243													wt1.wait_type,
3244													wt1.waiting_task_address
3245											) AS wt2 ON
3246												wt2.waiting_task_address = task_info.task_address
3247												AND wt2.wait_duration_ms > 0
3248												AND task_info.runnable_time IS NULL
3249											GROUP BY
3250												task_info.session_id,
3251												task_info.request_id,
3252												task_info.physical_io,
3253												task_info.context_switches,
3254												task_info.thread_CPU_snapshot,
3255												task_info.num_tasks,
3256												CASE
3257													WHEN task_info.runnable_time IS NOT NULL THEN
3258														''RUNNABLE''
3259													ELSE
3260														wt2.wait_type
3261												END
3262										) AS w1
3263									) AS waits
3264									ORDER BY
3265										waits.session_id,
3266										waits.request_id,
3267										waits.r
3268									FOR XML
3269										PATH(N''tasks''),
3270										TYPE
3271								) AS tasks_raw (task_xml_raw)
3272							) AS tasks_final
3273							CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node)
3274							WHERE
3275								task_nodes.task_node.exist(N''session_id'') = 1
3276						) AS tasks ON
3277							tasks.session_id = y.session_id
3278							AND tasks.request_id = y.request_id 
3279						'
3280					ELSE
3281						''
3282				END +
3283				'LEFT OUTER HASH JOIN
3284				(
3285					SELECT TOP(@i)
3286						t_info.session_id,
3287						COALESCE(t_info.request_id, -1) AS request_id,
3288						SUM(t_info.tempdb_allocations) AS tempdb_allocations,
3289						SUM(t_info.tempdb_current) AS tempdb_current
3290					FROM
3291					(
3292						SELECT TOP(@i)
3293							tsu.session_id,
3294							tsu.request_id,
3295							tsu.user_objects_alloc_page_count +
3296								tsu.internal_objects_alloc_page_count AS tempdb_allocations,
3297							tsu.user_objects_alloc_page_count +
3298								tsu.internal_objects_alloc_page_count -
3299								tsu.user_objects_dealloc_page_count -
3300								tsu.internal_objects_dealloc_page_count AS tempdb_current
3301						FROM sys.dm_db_task_space_usage AS tsu
3302						CROSS APPLY
3303						(
3304							SELECT TOP(1)
3305								s0.session_id
3306							FROM @sessions AS s0
3307							WHERE
3308								s0.session_id = tsu.session_id
3309						) AS p
3310
3311						UNION ALL
3312
3313						SELECT TOP(@i)
3314							ssu.session_id,
3315							NULL AS request_id,
3316							ssu.user_objects_alloc_page_count +
3317								ssu.internal_objects_alloc_page_count AS tempdb_allocations,
3318							ssu.user_objects_alloc_page_count +
3319								ssu.internal_objects_alloc_page_count -
3320								ssu.user_objects_dealloc_page_count -
3321								ssu.internal_objects_dealloc_page_count AS tempdb_current
3322						FROM sys.dm_db_session_space_usage AS ssu
3323						CROSS APPLY
3324						(
3325							SELECT TOP(1)
3326								s0.session_id
3327							FROM @sessions AS s0
3328							WHERE
3329								s0.session_id = ssu.session_id
3330						) AS p
3331					) AS t_info
3332					GROUP BY
3333						t_info.session_id,
3334						COALESCE(t_info.request_id, -1)
3335				) AS tempdb_info ON
3336					tempdb_info.session_id = y.session_id
3337					AND tempdb_info.request_id =
3338						CASE
3339							WHEN y.status = N''sleeping'' THEN
3340								-1
3341							ELSE
3342								y.request_id
3343						END
3344				' +
3345				CASE 
3346					WHEN 
3347						NOT 
3348						(
3349							@get_avg_time = 1 
3350							AND @recursion = 1
3351						) THEN 
3352							''
3353					ELSE
3354						'LEFT OUTER HASH JOIN
3355						(
3356							SELECT TOP(@i)
3357								*
3358							FROM sys.dm_exec_query_stats
3359						) AS qs ON
3360							qs.sql_handle = y.sql_handle
3361							AND qs.plan_handle = y.plan_handle
3362							AND qs.statement_start_offset = y.statement_start_offset
3363							AND qs.statement_end_offset = y.statement_end_offset
3364						'
3365				END + 
3366			') AS x
3367			OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';
3368
3369		SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
3370
3371		SET @last_collection_start = GETDATE();
3372
3373		IF @recursion = -1
3374		BEGIN;
3375			SELECT
3376				@first_collection_ms_ticks = ms_ticks
3377			FROM sys.dm_os_sys_info;
3378		END;
3379
3380		INSERT #sessions
3381		(
3382			recursion,
3383			session_id,
3384			request_id,
3385			session_number,
3386			elapsed_time,
3387			avg_elapsed_time,
3388			physical_io,
3389			reads,
3390			physical_reads,
3391			writes,
3392			tempdb_allocations,
3393			tempdb_current,
3394			CPU,
3395			thread_CPU_snapshot,
3396			context_switches,
3397			used_memory,
3398			tasks,
3399			status,
3400			wait_info,
3401			transaction_id,
3402			open_tran_count,
3403			sql_handle,
3404			statement_start_offset,
3405			statement_end_offset,		
3406			sql_text,
3407			plan_handle,
3408			blocking_session_id,
3409			percent_complete,
3410			host_name,
3411			login_name,
3412			database_name,
3413			program_name,
3414			additional_info,
3415			start_time,
3416			login_time,
3417			last_request_start_time
3418		)
3419		EXEC sp_executesql 
3420			@sql_n,
3421			N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',
3422			@recursion, @filter, @not_filter, @first_collection_ms_ticks;
3423
3424		--Collect transaction information?
3425		IF
3426			@recursion = 1
3427			AND
3428			(
3429				@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
3430				OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|' 
3431			)
3432		BEGIN;	
3433			DECLARE @i INT;
3434			SET @i = 2147483647;
3435
3436			UPDATE s
3437			SET
3438				tran_start_time =
3439					CONVERT
3440					(
3441						DATETIME,
3442						LEFT
3443						(
3444							x.trans_info,
3445							NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
3446						),
3447						121
3448					),
3449				tran_log_writes =
3450					RIGHT
3451					(
3452						x.trans_info,
3453						LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
3454					)
3455			FROM
3456			(
3457				SELECT TOP(@i)
3458					trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id,
3459					COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id,
3460					trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info				
3461				FROM
3462				(
3463					SELECT TOP(@i)
3464						CONVERT
3465						(
3466							XML,
3467							REPLACE
3468							(
3469								CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2, 
3470								N'</trans_info></trans><trans><trans_info>', N''
3471							)
3472						)
3473					FROM
3474					(
3475						SELECT TOP(@i)
3476							CASE u_trans.r
3477								WHEN 1 THEN u_trans.session_id
3478								ELSE NULL
3479							END AS [session_id],
3480							CASE u_trans.r
3481								WHEN 1 THEN u_trans.request_id
3482								ELSE NULL
3483							END AS [request_id],
3484							CONVERT
3485							(
3486								NVARCHAR(MAX),
3487								CASE
3488									WHEN u_trans.database_id IS NOT NULL THEN
3489										CASE u_trans.r
3490											WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'')
3491											ELSE N''
3492										END + 
3493											REPLACE
3494											(
3495												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3496												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3497												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3498													CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),
3499													NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3500													NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3501													NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3502												NCHAR(0),
3503												N'?'
3504											) +
3505											N': ' +
3506										CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +
3507										N','
3508									ELSE
3509										N'N/A,'
3510								END COLLATE Latin1_General_Bin2
3511							) AS [trans_info]
3512						FROM
3513						(
3514							SELECT TOP(@i)
3515								trans.*,
3516								ROW_NUMBER() OVER
3517								(
3518									PARTITION BY
3519										trans.session_id,
3520										trans.request_id
3521									ORDER BY
3522										trans.transaction_start_time DESC
3523								) AS r
3524							FROM
3525							(
3526								SELECT TOP(@i)
3527									session_tran_map.session_id,
3528									session_tran_map.request_id,
3529									s_tran.database_id,
3530									COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,
3531									COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used,
3532									MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
3533								FROM
3534								(
3535									SELECT TOP(@i)
3536										*
3537									FROM sys.dm_tran_active_transactions
3538									WHERE
3539										transaction_begin_time <= @last_collection_start
3540								) AS a_tran
3541								INNER HASH JOIN
3542								(
3543									SELECT TOP(@i)
3544										*
3545									FROM sys.dm_tran_database_transactions
3546									WHERE
3547										database_id < 32767
3548								) AS s_tran ON
3549									s_tran.transaction_id = a_tran.transaction_id
3550								LEFT OUTER HASH JOIN
3551								(
3552									SELECT TOP(@i)
3553										*
3554									FROM sys.dm_tran_session_transactions
3555								) AS tst ON
3556									s_tran.transaction_id = tst.transaction_id
3557								CROSS APPLY
3558								(
3559									SELECT TOP(1)
3560										s3.session_id,
3561										s3.request_id
3562									FROM
3563									(
3564										SELECT TOP(1)
3565											s1.session_id,
3566											s1.request_id
3567										FROM #sessions AS s1
3568										WHERE
3569											s1.transaction_id = s_tran.transaction_id
3570											AND s1.recursion = 1
3571											
3572										UNION ALL
3573									
3574										SELECT TOP(1)
3575											s2.session_id,
3576											s2.request_id
3577										FROM #sessions AS s2
3578										WHERE
3579											s2.session_id = tst.session_id
3580											AND s2.recursion = 1
3581									) AS s3
3582									ORDER BY
3583										s3.request_id
3584								) AS session_tran_map
3585								GROUP BY
3586									session_tran_map.session_id,
3587									session_tran_map.request_id,
3588									s_tran.database_id
3589							) AS trans
3590						) AS u_trans
3591						FOR XML
3592							PATH('trans'),
3593							TYPE
3594					) AS trans_raw (trans_xml_raw)
3595				) AS trans_final (trans_xml)
3596				CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node)
3597			) AS x
3598			INNER HASH JOIN #sessions AS s ON
3599				s.session_id = x.session_id
3600				AND s.request_id = x.request_id
3601			OPTION (OPTIMIZE FOR (@i = 1));
3602		END;
3603
3604		--Variables for text and plan collection
3605		DECLARE	
3606			@session_id SMALLINT,
3607			@request_id INT,
3608			@sql_handle VARBINARY(64),
3609			@plan_handle VARBINARY(64),
3610			@statement_start_offset INT,
3611			@statement_end_offset INT,
3612			@start_time DATETIME,
3613			@database_name sysname;
3614
3615		IF 
3616			@recursion = 1
3617			AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
3618		BEGIN;
3619			DECLARE sql_cursor
3620			CURSOR LOCAL FAST_FORWARD
3621			FOR 
3622				SELECT 
3623					session_id,
3624					request_id,
3625					sql_handle,
3626					statement_start_offset,
3627					statement_end_offset
3628				FROM #sessions
3629				WHERE
3630					recursion = 1
3631					AND sql_handle IS NOT NULL
3632			OPTION (KEEPFIXED PLAN);
3633
3634			OPEN sql_cursor;
3635
3636			FETCH NEXT FROM sql_cursor
3637			INTO 
3638				@session_id,
3639				@request_id,
3640				@sql_handle,
3641				@statement_start_offset,
3642				@statement_end_offset;
3643
3644			--Wait up to 5 ms for the SQL text, then give up
3645			SET LOCK_TIMEOUT 5;
3646
3647			WHILE @@FETCH_STATUS = 0
3648			BEGIN;
3649				BEGIN TRY;
3650					UPDATE s
3651					SET
3652						s.sql_text =
3653						(
3654							SELECT
3655								REPLACE
3656								(
3657									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3658									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3659									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3660										N'--' + NCHAR(13) + NCHAR(10) +
3661										CASE 
3662											WHEN @get_full_inner_text = 1 THEN est.text
3663											WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
3664											WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
3665											ELSE
3666												CASE
3667													WHEN @statement_start_offset > 0 THEN
3668														SUBSTRING
3669														(
3670															est.text,
3671															((@statement_start_offset/2) + 1),
3672															(
3673																CASE
3674																	WHEN @statement_end_offset = -1 THEN 2147483647
3675																	ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
3676																END
3677															)
3678														)
3679													ELSE RTRIM(LTRIM(est.text))
3680												END
3681										END +
3682										NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
3683										NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3684										NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3685										NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3686									NCHAR(0),
3687									N''
3688								) AS [processing-instruction(query)]
3689							FOR XML
3690								PATH(''),
3691								TYPE
3692						),
3693						s.statement_start_offset = 
3694							CASE 
3695								WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
3696								WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
3697								ELSE @statement_start_offset
3698							END,
3699						s.statement_end_offset = 
3700							CASE 
3701								WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
3702								WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
3703								ELSE @statement_end_offset
3704							END
3705					FROM 
3706						#sessions AS s,
3707						(
3708							SELECT TOP(1)
3709								text
3710							FROM
3711							(
3712								SELECT 
3713									text, 
3714									0 AS row_num
3715								FROM sys.dm_exec_sql_text(@sql_handle)
3716								
3717								UNION ALL
3718								
3719								SELECT 
3720									NULL,
3721									1 AS row_num
3722							) AS est0
3723							ORDER BY
3724								row_num
3725						) AS est
3726					WHERE 
3727						s.session_id = @session_id
3728						AND s.request_id = @request_id
3729						AND s.recursion = 1
3730					OPTION (KEEPFIXED PLAN);
3731				END TRY
3732				BEGIN CATCH;
3733					UPDATE s
3734					SET
3735						s.sql_text = 
3736							CASE ERROR_NUMBER() 
3737								WHEN 1222 THEN '<timeout_exceeded />'
3738								ELSE '<error message="' + ERROR_MESSAGE() + '" />'
3739							END
3740					FROM #sessions AS s
3741					WHERE 
3742						s.session_id = @session_id
3743						AND s.request_id = @request_id
3744						AND s.recursion = 1
3745					OPTION (KEEPFIXED PLAN);
3746				END CATCH;
3747
3748				FETCH NEXT FROM sql_cursor
3749				INTO
3750					@session_id,
3751					@request_id,
3752					@sql_handle,
3753					@statement_start_offset,
3754					@statement_end_offset;
3755			END;
3756
3757			--Return this to the default
3758			SET LOCK_TIMEOUT -1;
3759
3760			CLOSE sql_cursor;
3761			DEALLOCATE sql_cursor;
3762		END;
3763
3764		IF 
3765			@get_outer_command = 1 
3766			AND @recursion = 1
3767			AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
3768		BEGIN;
3769			DECLARE @buffer_results TABLE
3770			(
3771				EventType VARCHAR(30),
3772				Parameters INT,
3773				EventInfo NVARCHAR(4000),
3774				start_time DATETIME,
3775				session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY
3776			);
3777
3778			DECLARE buffer_cursor
3779			CURSOR LOCAL FAST_FORWARD
3780			FOR 
3781				SELECT 
3782					session_id,
3783					MAX(start_time) AS start_time
3784				FROM #sessions
3785				WHERE
3786					recursion = 1
3787				GROUP BY
3788					session_id
3789				ORDER BY
3790					session_id
3791				OPTION (KEEPFIXED PLAN);
3792
3793			OPEN buffer_cursor;
3794
3795			FETCH NEXT FROM buffer_cursor
3796			INTO 
3797				@session_id,
3798				@start_time;
3799
3800			WHILE @@FETCH_STATUS = 0
3801			BEGIN;
3802				BEGIN TRY;
3803					--In SQL Server 2008, DBCC INPUTBUFFER will throw 
3804					--an exception if the session no longer exists
3805					INSERT @buffer_results
3806					(
3807						EventType,
3808						Parameters,
3809						EventInfo
3810					)
3811					EXEC sp_executesql
3812						N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
3813						N'@session_id SMALLINT',
3814						@session_id;
3815
3816					UPDATE br
3817					SET
3818						br.start_time = @start_time
3819					FROM @buffer_results AS br
3820					WHERE
3821						br.session_number = 
3822						(
3823							SELECT MAX(br2.session_number)
3824							FROM @buffer_results br2
3825						);
3826				END TRY
3827				BEGIN CATCH
3828				END CATCH;
3829
3830				FETCH NEXT FROM buffer_cursor
3831				INTO 
3832					@session_id,
3833					@start_time;
3834			END;
3835
3836			UPDATE s
3837			SET
3838				sql_command = 
3839				(
3840					SELECT 
3841						REPLACE
3842						(
3843							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3844							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3845							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3846								CONVERT
3847								(
3848									NVARCHAR(MAX),
3849									N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
3850								),
3851								NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3852								NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3853								NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3854							NCHAR(0),
3855							N''
3856						) AS [processing-instruction(query)]
3857					FROM @buffer_results AS br
3858					WHERE 
3859						br.session_number = s.session_number
3860						AND br.start_time = s.start_time
3861						AND 
3862						(
3863							(
3864								s.start_time = s.last_request_start_time
3865								AND EXISTS
3866								(
3867									SELECT *
3868									FROM sys.dm_exec_requests r2
3869									WHERE
3870										r2.session_id = s.session_id
3871										AND r2.request_id = s.request_id
3872										AND r2.start_time = s.start_time
3873								)
3874							)
3875							OR 
3876							(
3877								s.request_id = 0
3878								AND EXISTS
3879								(
3880									SELECT *
3881									FROM sys.dm_exec_sessions s2
3882									WHERE
3883										s2.session_id = s.session_id
3884										AND s2.last_request_start_time = s.last_request_start_time
3885								)
3886							)
3887						)
3888					FOR XML
3889						PATH(''),
3890						TYPE
3891				)
3892			FROM #sessions AS s
3893			WHERE
3894				recursion = 1
3895			OPTION (KEEPFIXED PLAN);
3896
3897			CLOSE buffer_cursor;
3898			DEALLOCATE buffer_cursor;
3899		END;
3900
3901		IF 
3902			@get_plans >= 1 
3903			AND @recursion = 1
3904			AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
3905		BEGIN;
3906			DECLARE plan_cursor
3907			CURSOR LOCAL FAST_FORWARD
3908			FOR 
3909				SELECT
3910					session_id,
3911					request_id,
3912					plan_handle,
3913					statement_start_offset,
3914					statement_end_offset
3915				FROM #sessions
3916				WHERE
3917					recursion = 1
3918					AND plan_handle IS NOT NULL
3919			OPTION (KEEPFIXED PLAN);
3920
3921			OPEN plan_cursor;
3922
3923			FETCH NEXT FROM plan_cursor
3924			INTO 
3925				@session_id,
3926				@request_id,
3927				@plan_handle,
3928				@statement_start_offset,
3929				@statement_end_offset;
3930
3931			--Wait up to 5 ms for a query plan, then give up
3932			SET LOCK_TIMEOUT 5;
3933
3934			WHILE @@FETCH_STATUS = 0
3935			BEGIN;
3936				BEGIN TRY;
3937					UPDATE s
3938					SET
3939						s.query_plan =
3940						(
3941							SELECT
3942								CONVERT(xml, query_plan)
3943							FROM sys.dm_exec_text_query_plan
3944							(
3945								@plan_handle, 
3946								CASE @get_plans
3947									WHEN 1 THEN
3948										@statement_start_offset
3949									ELSE
3950										0
3951								END, 
3952								CASE @get_plans
3953									WHEN 1 THEN
3954										@statement_end_offset
3955									ELSE
3956										-1
3957								END
3958							)
3959						)
3960					FROM #sessions AS s
3961					WHERE 
3962						s.session_id = @session_id
3963						AND s.request_id = @request_id
3964						AND s.recursion = 1
3965					OPTION (KEEPFIXED PLAN);
3966				END TRY
3967				BEGIN CATCH;
3968					IF ERROR_NUMBER() = 6335
3969					BEGIN;
3970						UPDATE s
3971						SET
3972							s.query_plan =
3973							(
3974								SELECT
3975									N'--' + NCHAR(13) + NCHAR(10) + 
3976									N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) + 
3977									N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +
3978									N'--' + NCHAR(13) + NCHAR(10) +
3979										REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') + 
3980										NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
3981								FROM sys.dm_exec_text_query_plan
3982								(
3983									@plan_handle, 
3984									CASE @get_plans
3985										WHEN 1 THEN
3986											@statement_start_offset
3987										ELSE
3988											0
3989									END, 
3990									CASE @get_plans
3991										WHEN 1 THEN
3992											@statement_end_offset
3993										ELSE
3994											-1
3995									END
3996								) AS qp
3997								FOR XML
3998									PATH(''),
3999									TYPE
4000							)
4001						FROM #sessions AS s
4002						WHERE 
4003							s.session_id = @session_id
4004							AND s.request_id = @request_id
4005							AND s.recursion = 1
4006						OPTION (KEEPFIXED PLAN);
4007					END;
4008					ELSE
4009					BEGIN;
4010						UPDATE s
4011						SET
4012							s.query_plan = 
4013								CASE ERROR_NUMBER() 
4014									WHEN 1222 THEN '<timeout_exceeded />'
4015									ELSE '<error message="' + ERROR_MESSAGE() + '" />'
4016								END
4017						FROM #sessions AS s
4018						WHERE 
4019							s.session_id = @session_id
4020							AND s.request_id = @request_id
4021							AND s.recursion = 1
4022						OPTION (KEEPFIXED PLAN);
4023					END;
4024				END CATCH;
4025
4026				FETCH NEXT FROM plan_cursor
4027				INTO
4028					@session_id,
4029					@request_id,
4030					@plan_handle,
4031					@statement_start_offset,
4032					@statement_end_offset;
4033			END;
4034
4035			--Return this to the default
4036			SET LOCK_TIMEOUT -1;
4037
4038			CLOSE plan_cursor;
4039			DEALLOCATE plan_cursor;
4040		END;
4041
4042		IF 
4043			@get_locks = 1 
4044			AND @recursion = 1
4045			AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
4046		BEGIN;
4047			DECLARE locks_cursor
4048			CURSOR LOCAL FAST_FORWARD
4049			FOR 
4050				SELECT DISTINCT
4051					database_name
4052				FROM #locks
4053				WHERE
4054					EXISTS
4055					(
4056						SELECT *
4057						FROM #sessions AS s
4058						WHERE
4059							s.session_id = #locks.session_id
4060							AND recursion = 1
4061					)
4062					AND database_name <> '(null)'
4063				OPTION (KEEPFIXED PLAN);
4064
4065			OPEN locks_cursor;
4066
4067			FETCH NEXT FROM locks_cursor
4068			INTO 
4069				@database_name;
4070
4071			WHILE @@FETCH_STATUS = 0
4072			BEGIN;
4073				BEGIN TRY;
4074					SET @sql_n = CONVERT(NVARCHAR(MAX), '') +
4075						'UPDATE l ' +
4076						'SET ' +
4077							'object_name = ' +
4078								'REPLACE ' +
4079								'( ' +
4080									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4081									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4082									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4083										'o.name COLLATE Latin1_General_Bin2, ' +
4084										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4085										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4086										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4087									'NCHAR(0), ' +
4088									N''''' ' +
4089								'), ' +
4090							'index_name = ' +
4091								'REPLACE ' +
4092								'( ' +
4093									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4094									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4095									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4096										'i.name COLLATE Latin1_General_Bin2, ' +
4097										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4098										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4099										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4100									'NCHAR(0), ' +
4101									N''''' ' +
4102								'), ' +
4103							'schema_name = ' +
4104								'REPLACE ' +
4105								'( ' +
4106									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4107									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4108									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4109										's.name COLLATE Latin1_General_Bin2, ' +
4110										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4111										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4112										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4113									'NCHAR(0), ' +
4114									N''''' ' +
4115								'), ' +
4116							'principal_name = ' + 
4117								'REPLACE ' +
4118								'( ' +
4119									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4120									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4121									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4122										'dp.name COLLATE Latin1_General_Bin2, ' +
4123										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4124										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4125										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4126									'NCHAR(0), ' +
4127									N''''' ' +
4128								') ' +
4129						'FROM #locks AS l ' +
4130						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +
4131							'au.allocation_unit_id = l.allocation_unit_id ' +
4132						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4133							'p.hobt_id = ' +
4134								'COALESCE ' +
4135								'( ' +
4136									'l.hobt_id, ' +
4137									'CASE ' +
4138										'WHEN au.type IN (1, 3) THEN au.container_id ' +
4139										'ELSE NULL ' +
4140									'END ' +
4141								') ' +
4142						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +
4143							'l.hobt_id IS NULL ' +
4144							'AND au.type = 2 ' +
4145							'AND p1.partition_id = au.container_id ' +
4146						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4147							'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
4148						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +
4149							'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
4150							'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +
4151						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4152							's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +
4153						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +
4154							'dp.principal_id = l.principal_id ' +
4155						'WHERE ' +
4156							'l.database_name = @database_name ' +
4157						'OPTION (KEEPFIXED PLAN); ';
4158					
4159					EXEC sp_executesql
4160						@sql_n,
4161						N'@database_name sysname',
4162						@database_name;
4163				END TRY
4164				BEGIN CATCH;
4165					UPDATE #locks
4166					SET
4167						query_error = 
4168							REPLACE
4169							(
4170								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4171								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4172								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4173									CONVERT
4174									(
4175										NVARCHAR(MAX), 
4176										ERROR_MESSAGE() COLLATE Latin1_General_Bin2
4177									),
4178									NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4179									NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4180									NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4181								NCHAR(0),
4182								N''
4183							)
4184					WHERE 
4185						database_name = @database_name
4186					OPTION (KEEPFIXED PLAN);
4187				END CATCH;
4188
4189				FETCH NEXT FROM locks_cursor
4190				INTO
4191					@database_name;
4192			END;
4193
4194			CLOSE locks_cursor;
4195			DEALLOCATE locks_cursor;
4196
4197			CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);
4198
4199			UPDATE s
4200			SET 
4201				s.locks =
4202				(
4203					SELECT 
4204						REPLACE
4205						(
4206							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4207							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4208							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4209								CONVERT
4210								(
4211									NVARCHAR(MAX), 
4212									l1.database_name COLLATE Latin1_General_Bin2
4213								),
4214								NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4215								NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4216								NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4217							NCHAR(0),
4218							N''
4219						) AS [Database/@name],
4220						MIN(l1.query_error) AS [Database/@query_error],
4221						(
4222							SELECT 
4223								l2.request_mode AS [Lock/@request_mode],
4224								l2.request_status AS [Lock/@request_status],
4225								COUNT(*) AS [Lock/@request_count]
4226							FROM #locks AS l2
4227							WHERE 
4228								l1.session_id = l2.session_id
4229								AND l1.request_id = l2.request_id
4230								AND l2.database_name = l1.database_name
4231								AND l2.resource_type = 'DATABASE'
4232							GROUP BY
4233								l2.request_mode,
4234								l2.request_status
4235							FOR XML
4236								PATH(''),
4237								TYPE
4238						) AS [Database/Locks],
4239						(
4240							SELECT
4241								COALESCE(l3.object_name, '(null)') AS [Object/@name],
4242								l3.schema_name AS [Object/@schema_name],
4243								(
4244									SELECT
4245										l4.resource_type AS [Lock/@resource_type],
4246										l4.page_type AS [Lock/@page_type],
4247										l4.index_name AS [Lock/@index_name],
4248										CASE 
4249											WHEN l4.object_name IS NULL THEN l4.schema_name
4250											ELSE NULL
4251										END AS [Lock/@schema_name],
4252										l4.principal_name AS [Lock/@principal_name],
4253										l4.resource_description AS [Lock/@resource_description],
4254										l4.request_mode AS [Lock/@request_mode],
4255										l4.request_status AS [Lock/@request_status],
4256										SUM(l4.request_count) AS [Lock/@request_count]
4257									FROM #locks AS l4
4258									WHERE 
4259										l4.session_id = l3.session_id
4260										AND l4.request_id = l3.request_id
4261										AND l3.database_name = l4.database_name
4262										AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')
4263										AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')
4264										AND l4.resource_type <> 'DATABASE'
4265									GROUP BY
4266										l4.resource_type,
4267										l4.page_type,
4268										l4.index_name,
4269										CASE 
4270											WHEN l4.object_name IS NULL THEN l4.schema_name
4271											ELSE NULL
4272										END,
4273										l4.principal_name,
4274										l4.resource_description,
4275										l4.request_mode,
4276										l4.request_status
4277									FOR XML
4278										PATH(''),
4279										TYPE
4280								) AS [Object/Locks]
4281							FROM #locks AS l3
4282							WHERE 
4283								l3.session_id = l1.session_id
4284								AND l3.request_id = l1.request_id
4285								AND l3.database_name = l1.database_name
4286								AND l3.resource_type <> 'DATABASE'
4287							GROUP BY 
4288								l3.session_id,
4289								l3.request_id,
4290								l3.database_name,
4291								COALESCE(l3.object_name, '(null)'),
4292								l3.schema_name
4293							FOR XML
4294								PATH(''),
4295								TYPE
4296						) AS [Database/Objects]
4297					FROM #locks AS l1
4298					WHERE
4299						l1.session_id = s.session_id
4300						AND l1.request_id = s.request_id
4301						AND l1.start_time IN (s.start_time, s.last_request_start_time)
4302						AND s.recursion = 1
4303					GROUP BY 
4304						l1.session_id,
4305						l1.request_id,
4306						l1.database_name
4307					FOR XML
4308						PATH(''),
4309						TYPE
4310				)
4311			FROM #sessions s
4312			OPTION (KEEPFIXED PLAN);
4313		END;
4314
4315		IF 
4316			@find_block_leaders = 1
4317			AND @recursion = 1
4318			AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'
4319		BEGIN;
4320			WITH
4321			blockers AS
4322			(
4323				SELECT
4324					session_id,
4325					session_id AS top_level_session_id,
4326					CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path
4327				FROM #sessions
4328				WHERE
4329					recursion = 1
4330
4331				UNION ALL
4332
4333				SELECT
4334					s.session_id,
4335					b.top_level_session_id,
4336					CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path
4337				FROM blockers AS b
4338				JOIN #sessions AS s ON
4339					s.blocking_session_id = b.session_id
4340					AND s.recursion = 1
4341					AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2
4342			)
4343			UPDATE s
4344			SET
4345				s.blocked_session_count = x.blocked_session_count
4346			FROM #sessions AS s
4347			JOIN
4348			(
4349				SELECT
4350					b.top_level_session_id AS session_id,
4351					COUNT(*) - 1 AS blocked_session_count
4352				FROM blockers AS b
4353				GROUP BY
4354					b.top_level_session_id
4355			) x ON
4356				s.session_id = x.session_id
4357			WHERE
4358				s.recursion = 1;
4359		END;
4360
4361		IF
4362			@get_task_info = 2
4363			AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
4364			AND @recursion = 1
4365		BEGIN;
4366			CREATE TABLE #blocked_requests
4367			(
4368				session_id SMALLINT NOT NULL,
4369				request_id INT NOT NULL,
4370				database_name sysname NOT NULL,
4371				object_id INT,
4372				hobt_id BIGINT,
4373				schema_id INT,
4374				schema_name sysname NULL,
4375				object_name sysname NULL,
4376				query_error NVARCHAR(2048),
4377				PRIMARY KEY (database_name, session_id, request_id)
4378			);
4379
4380			CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
4381			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4382			CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
4383			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4384			CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
4385			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4386			CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
4387			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4388		
4389			INSERT #blocked_requests
4390			(
4391				session_id,
4392				request_id,
4393				database_name,
4394				object_id,
4395				hobt_id,
4396				schema_id
4397			)
4398			SELECT
4399				session_id,
4400				request_id,
4401				database_name,
4402				object_id,
4403				hobt_id,
4404				CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id
4405			FROM
4406			(
4407				SELECT
4408					session_id,
4409					request_id,
4410					agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name,
4411					agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id,
4412					agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id,
4413					agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node
4414				FROM #sessions AS s
4415				CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node)
4416				WHERE
4417					s.recursion = 1
4418			) AS t
4419			WHERE
4420				t.database_name IS NOT NULL
4421				AND
4422				(
4423					t.object_id IS NOT NULL
4424					OR t.hobt_id IS NOT NULL
4425					OR t.schema_node IS NOT NULL
4426				);
4427			
4428			DECLARE blocks_cursor
4429			CURSOR LOCAL FAST_FORWARD
4430			FOR
4431				SELECT DISTINCT
4432					database_name
4433				FROM #blocked_requests;
4434				
4435			OPEN blocks_cursor;
4436			
4437			FETCH NEXT FROM blocks_cursor
4438			INTO 
4439				@database_name;
4440			
4441			WHILE @@FETCH_STATUS = 0
4442			BEGIN;
4443				BEGIN TRY;
4444					SET @sql_n = 
4445						CONVERT(NVARCHAR(MAX), '') +
4446						'UPDATE b ' +
4447						'SET ' +
4448							'b.schema_name = ' +
4449								'REPLACE ' +
4450								'( ' +
4451									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4452									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4453									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4454										's.name COLLATE Latin1_General_Bin2, ' +
4455										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4456										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4457										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4458									'NCHAR(0), ' +
4459									N''''' ' +
4460								'), ' +
4461							'b.object_name = ' +
4462								'REPLACE ' +
4463								'( ' +
4464									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4465									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4466									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4467										'o.name COLLATE Latin1_General_Bin2, ' +
4468										'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4469										'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4470										'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4471									'NCHAR(0), ' +
4472									N''''' ' +
4473								') ' +
4474						'FROM #blocked_requests AS b ' +
4475						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4476							'p.hobt_id = b.hobt_id ' +
4477						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4478							'o.object_id = COALESCE(p.object_id, b.object_id) ' +
4479						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4480							's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +
4481						'WHERE ' +
4482							'b.database_name = @database_name; ';
4483					
4484					EXEC sp_executesql
4485						@sql_n,
4486						N'@database_name sysname',
4487						@database_name;
4488				END TRY
4489				BEGIN CATCH;
4490					UPDATE #blocked_requests
4491					SET
4492						query_error = 
4493							REPLACE
4494							(
4495								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4496								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4497								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4498									CONVERT
4499									(
4500										NVARCHAR(MAX), 
4501										ERROR_MESSAGE() COLLATE Latin1_General_Bin2
4502									),
4503									NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4504									NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4505									NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4506								NCHAR(0),
4507								N''
4508							)
4509					WHERE
4510						database_name = @database_name;
4511				END CATCH;
4512
4513				FETCH NEXT FROM blocks_cursor
4514				INTO
4515					@database_name;
4516			END;
4517			
4518			CLOSE blocks_cursor;
4519			DEALLOCATE blocks_cursor;
4520			
4521			UPDATE s
4522			SET
4523				additional_info.modify
4524				('
4525					insert <schema_name>{sql:column("b.schema_name")}</schema_name>
4526					as last
4527					into (/additional_info/block_info)[1]
4528				')
4529			FROM #sessions AS s
4530			INNER JOIN #blocked_requests AS b ON
4531				b.session_id = s.session_id
4532				AND b.request_id = s.request_id
4533				AND s.recursion = 1
4534			WHERE
4535				b.schema_name IS NOT NULL;
4536
4537			UPDATE s
4538			SET
4539				additional_info.modify
4540				('
4541					insert <object_name>{sql:column("b.object_name")}</object_name>
4542					as last
4543					into (/additional_info/block_info)[1]
4544				')
4545			FROM #sessions AS s
4546			INNER JOIN #blocked_requests AS b ON
4547				b.session_id = s.session_id
4548				AND b.request_id = s.request_id
4549				AND s.recursion = 1
4550			WHERE
4551				b.object_name IS NOT NULL;
4552
4553			UPDATE s
4554			SET
4555				additional_info.modify
4556				('
4557					insert <query_error>{sql:column("b.query_error")}</query_error>
4558					as last
4559					into (/additional_info/block_info)[1]
4560				')
4561			FROM #sessions AS s
4562			INNER JOIN #blocked_requests AS b ON
4563				b.session_id = s.session_id
4564				AND b.request_id = s.request_id
4565				AND s.recursion = 1
4566			WHERE
4567				b.query_error IS NOT NULL;
4568		END;
4569
4570		IF
4571			@output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
4572			AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
4573			AND @recursion = 1
4574		BEGIN;
4575			DECLARE @job_id UNIQUEIDENTIFIER;
4576			DECLARE @step_id INT;
4577
4578			DECLARE agent_cursor
4579			CURSOR LOCAL FAST_FORWARD
4580			FOR 
4581				SELECT
4582					s.session_id,
4583					agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id,
4584					agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id
4585				FROM #sessions AS s
4586				CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes (agent_node)
4587				WHERE
4588					s.recursion = 1
4589			OPTION (KEEPFIXED PLAN);
4590			
4591			OPEN agent_cursor;
4592
4593			FETCH NEXT FROM agent_cursor
4594			INTO 
4595				@session_id,
4596				@job_id,
4597				@step_id;
4598
4599			WHILE @@FETCH_STATUS = 0
4600			BEGIN;
4601				BEGIN TRY;
4602					DECLARE @job_name sysname;
4603					SET @job_name = NULL;
4604					DECLARE @step_name sysname;
4605					SET @step_name = NULL;
4606					
4607					SELECT
4608						@job_name = 
4609							REPLACE
4610							(
4611								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4612								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4613								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4614									j.name,
4615									NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4616									NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4617									NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4618								NCHAR(0),
4619								N'?'
4620							),
4621						@step_name = 
4622							REPLACE
4623							(
4624								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4625								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4626								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4627									s.step_name,
4628									NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4629									NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4630									NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4631								NCHAR(0),
4632								N'?'
4633							)
4634					FROM msdb.dbo.sysjobs AS j
4635					INNER JOIN msdb..sysjobsteps AS s ON
4636						j.job_id = s.job_id
4637					WHERE
4638						j.job_id = @job_id
4639						AND s.step_id = @step_id;
4640
4641					IF @job_name IS NOT NULL
4642					BEGIN;
4643						UPDATE s
4644						SET
4645							additional_info.modify
4646							('
4647								insert text{sql:variable("@job_name")}
4648								into (/additional_info/agent_job_info/job_name)[1]
4649							')
4650						FROM #sessions AS s
4651						WHERE 
4652							s.session_id = @session_id
4653						OPTION (KEEPFIXED PLAN);
4654						
4655						UPDATE s
4656						SET
4657							additional_info.modify
4658							('
4659								insert text{sql:variable("@step_name")}
4660								into (/additional_info/agent_job_info/step_name)[1]
4661							')
4662						FROM #sessions AS s
4663						WHERE 
4664							s.session_id = @session_id
4665						OPTION (KEEPFIXED PLAN);
4666					END;
4667				END TRY
4668				BEGIN CATCH;
4669					DECLARE @msdb_error_message NVARCHAR(256);
4670					SET @msdb_error_message = ERROR_MESSAGE();
4671				
4672					UPDATE s
4673					SET
4674						additional_info.modify
4675						('
4676							insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
4677							as last
4678							into (/additional_info/agent_job_info)[1]
4679						')
4680					FROM #sessions AS s
4681					WHERE 
4682						s.session_id = @session_id
4683						AND s.recursion = 1
4684					OPTION (KEEPFIXED PLAN);
4685				END CATCH;
4686
4687				FETCH NEXT FROM agent_cursor
4688				INTO 
4689					@session_id,
4690					@job_id,
4691					@step_id;
4692			END;
4693
4694			CLOSE agent_cursor;
4695			DEALLOCATE agent_cursor;
4696		END; 
4697		
4698		IF 
4699			@delta_interval > 0 
4700			AND @recursion <> 1
4701		BEGIN;
4702			SET @recursion = 1;
4703
4704			DECLARE @delay_time CHAR(12);
4705			SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
4706			WAITFOR DELAY @delay_time;
4707
4708			GOTO REDO;
4709		END;
4710	END;
4711
4712	SET @sql = 
4713		--Outer column list
4714		CONVERT
4715		(
4716			VARCHAR(MAX),
4717			CASE
4718				WHEN 
4719					@destination_table <> '' 
4720					AND @return_schema = 0 
4721						THEN 'INSERT ' + @destination_table + ' '
4722				ELSE ''
4723			END +
4724			'SELECT ' +
4725				@output_column_list + ' ' +
4726			CASE @return_schema
4727				WHEN 1 THEN 'INTO #session_schema '
4728				ELSE ''
4729			END
4730		--End outer column list
4731		) + 
4732		--Inner column list
4733		CONVERT
4734		(
4735			VARCHAR(MAX),
4736			'FROM ' +
4737			'( ' +
4738				'SELECT ' +
4739					'session_id, ' +
4740					--[dd hh:mm:ss.mss]
4741					CASE
4742						WHEN @format_output IN (1, 2) THEN
4743							'CASE ' +
4744								'WHEN elapsed_time < 0 THEN ' +
4745									'RIGHT ' +
4746									'( ' +
4747										'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ' +
4748										'max_elapsed_length ' +
4749									') + ' +
4750										'RIGHT ' +
4751										'( ' +
4752											'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' +
4753											'9 ' +
4754										') + ' +
4755										'''.000'' ' +
4756								'ELSE ' +
4757									'RIGHT ' +
4758									'( ' +
4759										'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ' +
4760										'max_elapsed_length ' +
4761									') + ' +
4762										'RIGHT ' +
4763										'( ' +
4764											'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ' +
4765											'9 ' +
4766										') + ' +
4767										'''.'' + ' + 
4768										'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' +
4769							'END AS [dd hh:mm:ss.mss], '
4770						ELSE
4771							''
4772					END +
4773					--[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
4774					CASE 
4775						WHEN  @format_output IN (1, 2) THEN 
4776							'RIGHT ' +
4777							'( ' +
4778								'''00'' + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ' +
4779								'2 ' +
4780							') + ' +
4781								'RIGHT ' +
4782								'( ' +
4783									'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ' +
4784									'9 ' +
4785								') + ' +
4786								'''.'' + ' +
4787								'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], '
4788						ELSE
4789							'avg_elapsed_time, '
4790					END +
4791					--physical_io
4792					CASE @format_output
4793						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
4794						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
4795						ELSE ''
4796					END + 'physical_io, ' +
4797					--reads
4798					CASE @format_output
4799						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
4800						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
4801						ELSE ''
4802					END + 'reads, ' +
4803					--physical_reads
4804					CASE @format_output
4805						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
4806						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
4807						ELSE ''
4808					END + 'physical_reads, ' +
4809					--writes
4810					CASE @format_output
4811						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
4812						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
4813						ELSE ''
4814					END + 'writes, ' +
4815					--tempdb_allocations
4816					CASE @format_output
4817						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
4818						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
4819						ELSE ''
4820					END + 'tempdb_allocations, ' +
4821					--tempdb_current
4822					CASE @format_output
4823						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
4824						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
4825						ELSE ''
4826					END + 'tempdb_current, ' +
4827					--CPU
4828					CASE @format_output
4829						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
4830						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
4831						ELSE ''
4832					END + 'CPU, ' +
4833					--context_switches
4834					CASE @format_output
4835						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
4836						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
4837						ELSE ''
4838					END + 'context_switches, ' +
4839					--used_memory
4840					CASE @format_output
4841						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
4842						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
4843						ELSE ''
4844					END + 'used_memory, ' +
4845					CASE
4846						WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
4847							--physical_io_delta			
4848							'CASE ' +
4849								'WHEN ' +
4850									'first_request_start_time = last_request_start_time ' + 
4851									'AND num_events = 2 ' +
4852									'AND physical_io_delta >= 0 ' +
4853										'THEN ' +
4854										CASE @format_output
4855											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_io_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) ' 
4856											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
4857											ELSE 'physical_io_delta '
4858										END +
4859								'ELSE NULL ' +
4860							'END AS physical_io_delta, ' +
4861							--reads_delta
4862							'CASE ' +
4863								'WHEN ' +
4864									'first_request_start_time = last_request_start_time ' + 
4865									'AND num_events = 2 ' +
4866									'AND reads_delta >= 0 ' +
4867										'THEN ' +
4868										CASE @format_output
4869											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads_delta))) OVER() - LEN(CONVERT(VARCHAR, reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
4870											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
4871											ELSE 'reads_delta '
4872										END +
4873								'ELSE NULL ' +
4874							'END AS reads_delta, ' +
4875							--physical_reads_delta
4876							'CASE ' +
4877								'WHEN ' +
4878									'first_request_start_time = last_request_start_time ' + 
4879									'AND num_events = 2 ' +
4880									'AND physical_reads_delta >= 0 ' +
4881										'THEN ' +
4882										CASE @format_output
4883											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
4884											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
4885											ELSE 'physical_reads_delta '
4886										END + 
4887								'ELSE NULL ' +
4888							'END AS physical_reads_delta, ' +
4889							--writes_delta
4890							'CASE ' +
4891								'WHEN ' +
4892									'first_request_start_time = last_request_start_time ' + 
4893									'AND num_events = 2 ' +
4894									'AND writes_delta >= 0 ' +
4895										'THEN ' +
4896										CASE @format_output
4897											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes_delta))) OVER() - LEN(CONVERT(VARCHAR, writes_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
4898											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
4899											ELSE 'writes_delta '
4900										END + 
4901								'ELSE NULL ' +
4902							'END AS writes_delta, ' +
4903							--tempdb_allocations_delta
4904							'CASE ' +
4905								'WHEN ' +
4906									'first_request_start_time = last_request_start_time ' + 
4907									'AND num_events = 2 ' +
4908									'AND tempdb_allocations_delta >= 0 ' +
4909										'THEN ' +
4910										CASE @format_output
4911											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
4912											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
4913											ELSE 'tempdb_allocations_delta '
4914										END + 
4915								'ELSE NULL ' +
4916							'END AS tempdb_allocations_delta, ' +
4917							--tempdb_current_delta
4918							--this is the only one that can (legitimately) go negative 
4919							'CASE ' +
4920								'WHEN ' +
4921									'first_request_start_time = last_request_start_time ' + 
4922									'AND num_events = 2 ' +
4923										'THEN ' +
4924										CASE @format_output
4925											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
4926											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
4927											ELSE 'tempdb_current_delta '
4928										END + 
4929								'ELSE NULL ' +
4930							'END AS tempdb_current_delta, ' +
4931							--CPU_delta
4932							'CASE ' +
4933								'WHEN ' +
4934									'first_request_start_time = last_request_start_time ' + 
4935									'AND num_events = 2 ' +
4936										'THEN ' +
4937											'CASE ' +
4938												'WHEN ' +
4939													'thread_CPU_delta > CPU_delta ' +
4940													'AND thread_CPU_delta > 0 ' +
4941														'THEN ' +
4942															CASE @format_output
4943																WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, thread_CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
4944																WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
4945																ELSE 'thread_CPU_delta '
4946															END + 
4947												'WHEN CPU_delta >= 0 THEN ' +
4948													CASE @format_output
4949														WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
4950														WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
4951														ELSE 'CPU_delta '
4952													END + 
4953												'ELSE NULL ' +
4954											'END ' +
4955								'ELSE ' +
4956									'NULL ' +
4957							'END AS CPU_delta, ' +
4958							--context_switches_delta
4959							'CASE ' +
4960								'WHEN ' +
4961									'first_request_start_time = last_request_start_time ' + 
4962									'AND num_events = 2 ' +
4963									'AND context_switches_delta >= 0 ' +
4964										'THEN ' +
4965										CASE @format_output
4966											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches_delta))) OVER() - LEN(CONVERT(VARCHAR, context_switches_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
4967											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
4968											ELSE 'context_switches_delta '
4969										END + 
4970								'ELSE NULL ' +
4971							'END AS context_switches_delta, ' +
4972							--used_memory_delta
4973							'CASE ' +
4974								'WHEN ' +
4975									'first_request_start_time = last_request_start_time ' + 
4976									'AND num_events = 2 ' +
4977									'AND used_memory_delta >= 0 ' +
4978										'THEN ' +
4979										CASE @format_output
4980											WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory_delta))) OVER() - LEN(CONVERT(VARCHAR, used_memory_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
4981											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
4982											ELSE 'used_memory_delta '
4983										END + 
4984								'ELSE NULL ' +
4985							'END AS used_memory_delta, '
4986						ELSE ''
4987					END +
4988					--tasks
4989					CASE @format_output
4990						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tasks))) OVER() - LEN(CONVERT(VARCHAR, tasks))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) AS '
4991						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) '
4992						ELSE ''
4993					END + 'tasks, ' +
4994					'status, ' +
4995					'wait_info, ' +
4996					'locks, ' +
4997					'tran_start_time, ' +
4998					'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' +
4999					--open_tran_count
5000					CASE @format_output
5001						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, open_tran_count))) OVER() - LEN(CONVERT(VARCHAR, open_tran_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
5002						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
5003						ELSE ''
5004					END + 'open_tran_count, ' +
5005					--sql_command
5006					CASE @format_output 
5007						WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
5008						ELSE ''
5009					END + 'sql_command, ' +
5010					--sql_text
5011					CASE @format_output 
5012						WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
5013						ELSE ''
5014					END + 'sql_text, ' +
5015					'query_plan, ' +
5016					'blocking_session_id, ' +
5017					--blocked_session_count
5018					CASE @format_output
5019						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, blocked_session_count))) OVER() - LEN(CONVERT(VARCHAR, blocked_session_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
5020						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
5021						ELSE ''
5022					END + 'blocked_session_count, ' +
5023					--percent_complete
5024					CASE @format_output
5025						WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) OVER() - LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) + CONVERT(CHAR(22), CONVERT(MONEY, percent_complete), 2)) AS '
5026						WHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS '
5027						ELSE ''
5028					END + 'percent_complete, ' +
5029					'host_name, ' +
5030					'login_name, ' +
5031					'database_name, ' +
5032					'program_name, ' +
5033					'additional_info, ' +
5034					'start_time, ' +
5035					'login_time, ' +
5036					'CASE ' +
5037						'WHEN status = N''sleeping'' THEN NULL ' +
5038						'ELSE request_id ' +
5039					'END AS request_id, ' +
5040					'GETDATE() AS collection_time '
5041		--End inner column list
5042		) +
5043		--Derived table and INSERT specification
5044		CONVERT
5045		(
5046			VARCHAR(MAX),
5047				'FROM ' +
5048				'( ' +
5049					'SELECT TOP(2147483647) ' +
5050						'*, ' +
5051						'CASE ' +
5052							'MAX ' +
5053							'( ' +
5054								'LEN ' +
5055								'( ' +
5056									'CONVERT ' +
5057									'( ' +
5058										'VARCHAR, ' +
5059										'CASE ' +
5060											'WHEN elapsed_time < 0 THEN ' +
5061												'(-1 * elapsed_time) / 86400 ' +
5062											'ELSE ' +
5063												'elapsed_time / 86400000 ' +
5064										'END ' +
5065									') ' +
5066								') ' +
5067							') OVER () ' +
5068								'WHEN 1 THEN 2 ' +
5069								'ELSE ' +
5070									'MAX ' +
5071									'( ' +
5072										'LEN ' +
5073										'( ' +
5074											'CONVERT ' +
5075											'( ' +
5076												'VARCHAR, ' +
5077												'CASE ' +
5078													'WHEN elapsed_time < 0 THEN ' +
5079														'(-1 * elapsed_time) / 86400 ' +
5080													'ELSE ' +
5081														'elapsed_time / 86400000 ' +
5082												'END ' +
5083											') ' +
5084										') ' +
5085									') OVER () ' +
5086						'END AS max_elapsed_length, ' +
5087						CASE
5088							WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
5089								'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5090									'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' +
5091								'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5092									'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' +
5093								'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5094									'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' +
5095								'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5096									'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' +
5097								'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5098									'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' +
5099								'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5100									'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' +
5101								'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5102									'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' +
5103								'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5104									'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' +
5105								'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5106									'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' +
5107								'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5108									'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' +
5109								'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, '
5110							ELSE ''
5111						END +
5112						'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' +
5113					'FROM #sessions AS s1 ' +
5114					CASE 
5115						WHEN @sort_order = '' THEN ''
5116						ELSE
5117							'ORDER BY ' +
5118								@sort_order
5119					END +
5120				') AS s ' +
5121				'WHERE ' +
5122					's.recursion = 1 ' +
5123			') x ' +
5124			'OPTION (KEEPFIXED PLAN); ' +
5125			'' +
5126			CASE @return_schema
5127				WHEN 1 THEN
5128					'SET @schema = ' +
5129						'''CREATE TABLE <table_name> ( '' + ' +
5130							'STUFF ' +
5131							'( ' +
5132								'( ' +
5133									'SELECT ' +
5134										''','' + ' +
5135										'QUOTENAME(COLUMN_NAME) + '' '' + ' +
5136										'DATA_TYPE + ' + 
5137										'CASE ' +
5138											'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' +
5139											'ELSE '' '' ' +
5140										'END + ' +
5141										'CASE IS_NULLABLE ' +
5142											'WHEN ''NO'' THEN ''NOT '' ' +
5143											'ELSE '''' ' +
5144										'END + ''NULL'' AS [text()] ' +
5145									'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' +
5146									'WHERE ' +
5147										'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' +
5148										'ORDER BY ' +
5149											'ORDINAL_POSITION ' +
5150									'FOR XML ' +
5151										'PATH('''') ' +
5152								'), + ' +
5153								'1, ' +
5154								'1, ' +
5155								''''' ' +
5156							') + ' +
5157						''')''; ' 
5158				ELSE ''
5159			END
5160		--End derived table and INSERT specification
5161		);
5162
5163	SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
5164
5165	EXEC sp_executesql
5166		@sql_n,
5167		N'@schema VARCHAR(MAX) OUTPUT',
5168		@schema OUTPUT;
5169END;
5170GO