HXQVdDWV

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