XneJXnNc

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.32 (2018-07-03)
15(C) 2007-2018, 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										x.statement_start_offset,
2614										x.statement_end_offset,
2615										' +
2616										CASE
2617											WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN
2618												'(
2619													SELECT TOP(1)
2620														CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id,
2621														agent_info.step_id,
2622														(
2623															SELECT TOP(1)
2624																NULL
2625															FOR XML
2626																PATH(''job_name''),
2627																TYPE
2628														),
2629														(
2630															SELECT TOP(1)
2631																NULL
2632															FOR XML
2633																PATH(''step_name''),
2634																TYPE
2635														)
2636													FROM
2637													(
2638														SELECT TOP(1)
2639															SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,
2640															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
2641														WHERE
2642															x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''
2643													) AS agent_info
2644													FOR XML
2645														PATH(''agent_job_info''),
2646														TYPE
2647												),
2648												'
2649											ELSE ''
2650										END +
2651										CASE
2652											WHEN @get_task_info = 2 THEN
2653												'CONVERT(XML, x.block_info) AS block_info, 
2654												'
2655											ELSE
2656												''
2657										END + '
2658										x.host_process_id,
2659										x.group_id
2660									FOR XML
2661										PATH(''additional_info''),
2662										TYPE
2663								) '
2664					ELSE
2665						'NULL '
2666				END + 
2667					'AS additional_info, 
2668				x.start_time, 
2669					' +
2670				CASE
2671					WHEN
2672						@output_column_list LIKE '%|[login_time|]%' ESCAPE '|'
2673						AND @recursion = 1
2674							THEN
2675								'x.login_time '
2676					ELSE 
2677						'NULL '
2678				END + 
2679					'AS login_time, 
2680				x.last_request_start_time
2681			FROM
2682			(
2683				SELECT TOP(@i)
2684					y.*,
2685					CASE
2686						WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
2687							DATEDIFF(second, GETDATE(), y.start_time)
2688						ELSE DATEDIFF(ms, y.start_time, GETDATE())
2689					END AS elapsed_time,
2690					COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
2691					COALESCE
2692					(
2693						CASE
2694							WHEN tempdb_info.tempdb_current < 0 THEN 0
2695							ELSE tempdb_info.tempdb_current
2696						END,
2697						0
2698					) AS tempdb_current, 
2699					' +
2700					CASE
2701						WHEN 
2702							(
2703								@get_task_info <> 0
2704								OR @find_block_leaders = 1
2705							) THEN
2706								'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +
2707									y.wait_type +
2708										CASE
2709											WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN
2710												N'':'' +
2711												COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +
2712												N'':'' +
2713												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)) +
2714												N''('' +
2715													CASE
2716														WHEN
2717															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR
2718															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0
2719																THEN 
2720																	N''PFS''
2721														WHEN
2722															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR
2723															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0
2724																THEN 
2725																	N''GAM''
2726														WHEN
2727															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR
2728															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
2729																THEN
2730																	N''SGAM''
2731														WHEN
2732															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR
2733															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0 
2734																THEN 
2735																	N''DCM''
2736														WHEN
2737															CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR
2738															(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0 
2739																THEN 
2740																	N''BCM''
2741														ELSE 
2742															N''*''
2743													END +
2744												N'')''
2745											WHEN y.wait_type = N''CXPACKET'' THEN
2746												N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)
2747											WHEN y.wait_type LIKE N''LATCH[_]%'' THEN
2748												N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''
2749											WHEN
2750												y.wait_type = N''OLEDB''
2751												AND y.resource_description LIKE N''%(SPID=%)'' THEN
2752													N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +
2753														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)) + '']''
2754											ELSE
2755												N''''
2756										END COLLATE Latin1_General_Bin2 AS sys_wait_info, 
2757										'
2758							ELSE
2759								''
2760						END +
2761						CASE
2762							WHEN @get_task_info = 2 THEN
2763								'tasks.physical_io,
2764								tasks.context_switches,
2765								tasks.tasks,
2766								tasks.block_info,
2767								tasks.wait_info AS task_wait_info,
2768								tasks.thread_CPU_snapshot,
2769								'
2770							ELSE
2771								'' 
2772					END +
2773					CASE 
2774						WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN
2775							'CONVERT(INT, NULL) '
2776						ELSE 
2777							'qs.total_elapsed_time / qs.execution_count '
2778					END + 
2779						'AS avg_elapsed_time 
2780				FROM
2781				(
2782					SELECT TOP(@i)
2783						sp.session_id,
2784						sp.request_id,
2785						COALESCE(r.logical_reads, s.logical_reads) AS reads,
2786						COALESCE(r.reads, s.reads) AS physical_reads,
2787						COALESCE(r.writes, s.writes) AS writes,
2788						COALESCE(r.CPU_time, s.CPU_time) AS CPU,
2789						sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
2790						LOWER(sp.status) AS status,
2791						COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
2792						COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
2793						COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
2794						' +
2795						CASE
2796							WHEN 
2797							(
2798								@get_task_info <> 0
2799								OR @find_block_leaders = 1 
2800							) THEN
2801								'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2802								sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
2803								sp.wait_time AS wait_duration_ms, 
2804								'
2805							ELSE
2806								''
2807						END +
2808						'NULLIF(sp.blocked, 0) AS blocking_session_id,
2809						r.plan_handle,
2810						NULLIF(r.percent_complete, 0) AS percent_complete,
2811						sp.host_name,
2812						sp.login_name,
2813						sp.program_name,
2814						s.host_process_id,
2815						COALESCE(r.text_size, s.text_size) AS text_size,
2816						COALESCE(r.language, s.language) AS language,
2817						COALESCE(r.date_format, s.date_format) AS date_format,
2818						COALESCE(r.date_first, s.date_first) AS date_first,
2819						COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
2820						COALESCE(r.arithabort, s.arithabort) AS arithabort,
2821						COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
2822						COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
2823						COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
2824						COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
2825						COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
2826						COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
2827						COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
2828						COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
2829						COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
2830						COALESCE(r.row_count, s.row_count) AS row_count,
2831						COALESCE(r.command, sp.cmd) AS command_type,
2832						COALESCE
2833						(
2834							CASE
2835								WHEN
2836								(
2837									s.is_user_process = 0
2838									AND r.total_elapsed_time >= 0
2839								) THEN
2840									DATEADD
2841									(
2842										ms,
2843										1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2844										DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2845									)
2846							END,
2847							NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),
2848							sp.login_time
2849						) AS start_time,
2850						sp.login_time,
2851						CASE
2852							WHEN s.is_user_process = 1 THEN
2853								s.last_request_start_time
2854							ELSE
2855								COALESCE
2856								(
2857									DATEADD
2858									(
2859										ms,
2860										1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2861										DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2862									),
2863									s.last_request_start_time
2864								)
2865						END AS last_request_start_time,
2866						r.transaction_id,
2867						sp.database_id,
2868						sp.open_tran_count,
2869						' +
2870							CASE
2871								WHEN EXISTS
2872								(
2873									SELECT
2874										*
2875									FROM sys.all_columns AS ac
2876									WHERE
2877										ac.object_id = OBJECT_ID('sys.dm_exec_sessions')
2878										AND ac.name = 'group_id'
2879								)
2880									THEN 's.group_id'
2881								ELSE 'CONVERT(INT, NULL) AS group_id'
2882							END + '
2883					FROM @sessions AS sp
2884					LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
2885						s.session_id = sp.session_id
2886						AND s.login_time = sp.login_time
2887					LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
2888						sp.status <> ''sleeping''
2889						AND r.session_id = sp.session_id
2890						AND r.request_id = sp.request_id
2891						AND
2892						(
2893							(
2894								s.is_user_process = 0
2895								AND sp.is_user_process = 0
2896							)
2897							OR
2898							(
2899								r.start_time = s.last_request_start_time
2900								AND s.last_request_end_time <= sp.last_request_end_time
2901							)
2902						)
2903				) AS y
2904				' + 
2905				CASE 
2906					WHEN @get_task_info = 2 THEN
2907						CONVERT(VARCHAR(MAX), '') +
2908						'LEFT OUTER HASH JOIN
2909						(
2910							SELECT TOP(@i)
2911								task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id,
2912								task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id,
2913								task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io,
2914								task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches,
2915								task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,
2916								task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info,
2917								task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,
2918								task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot
2919							FROM
2920							(
2921								SELECT TOP(@i)
2922									CONVERT
2923									(
2924										XML,
2925										REPLACE
2926										(
2927											CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
2928											N''</waits></tasks><tasks><waits>'',
2929											N'', ''
2930										)
2931									) AS task_xml
2932								FROM
2933								(
2934									SELECT TOP(@i)
2935										CASE waits.r
2936											WHEN 1 THEN
2937												waits.session_id
2938											ELSE
2939												NULL
2940										END AS [session_id],
2941										CASE waits.r
2942											WHEN 1 THEN
2943												waits.request_id
2944											ELSE
2945												NULL
2946										END AS [request_id],											
2947										CASE waits.r
2948											WHEN 1 THEN
2949												waits.physical_io
2950											ELSE
2951												NULL
2952										END AS [physical_io],
2953										CASE waits.r
2954											WHEN 1 THEN
2955												waits.context_switches
2956											ELSE
2957												NULL
2958										END AS [context_switches],
2959										CASE waits.r
2960											WHEN 1 THEN
2961												waits.thread_CPU_snapshot
2962											ELSE
2963												NULL
2964										END AS [thread_CPU_snapshot],
2965										CASE waits.r
2966											WHEN 1 THEN
2967												waits.tasks
2968											ELSE
2969												NULL
2970										END AS [tasks],
2971										CASE waits.r
2972											WHEN 1 THEN
2973												waits.block_info
2974											ELSE
2975												NULL
2976										END AS [block_info],
2977										REPLACE
2978										(
2979											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2980											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2981											REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2982												CONVERT
2983												(
2984													NVARCHAR(MAX),
2985													N''('' +
2986														CONVERT(NVARCHAR, num_waits) + N''x: '' +
2987														CASE num_waits
2988															WHEN 1 THEN
2989																CONVERT(NVARCHAR, min_wait_time) + N''ms''
2990															WHEN 2 THEN
2991																CASE
2992																	WHEN min_wait_time <> max_wait_time THEN
2993																		CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2994																	ELSE
2995																		CONVERT(NVARCHAR, max_wait_time) + N''ms''
2996																END
2997															ELSE
2998																CASE
2999																	WHEN min_wait_time <> max_wait_time THEN
3000																		CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
3001																	ELSE 
3002																		CONVERT(NVARCHAR, max_wait_time) + N''ms''
3003																END
3004														END +
3005													N'')'' + wait_type COLLATE Latin1_General_Bin2
3006												),
3007												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''?''),
3008												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''?''),
3009												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''?''),
3010											NCHAR(0),
3011											N''''
3012										) AS [waits]
3013									FROM
3014									(
3015										SELECT TOP(@i)
3016											w1.*,
3017											ROW_NUMBER() OVER
3018											(
3019												PARTITION BY
3020													w1.session_id,
3021													w1.request_id
3022												ORDER BY
3023													w1.block_info DESC,
3024													w1.num_waits DESC,
3025													w1.wait_type
3026											) AS r
3027										FROM
3028										(
3029											SELECT TOP(@i)
3030												task_info.session_id,
3031												task_info.request_id,
3032												task_info.physical_io,
3033												task_info.context_switches,
3034												task_info.thread_CPU_snapshot,
3035												task_info.num_tasks AS tasks,
3036												CASE
3037													WHEN task_info.runnable_time IS NOT NULL THEN
3038														''RUNNABLE''
3039													ELSE
3040														wt2.wait_type
3041												END AS wait_type,
3042												NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
3043												MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
3044												AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
3045												MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
3046												MAX(wt2.block_info) AS block_info
3047											FROM
3048											(
3049												SELECT TOP(@i)
3050													t.session_id,
3051													t.request_id,
3052													SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
3053													SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches, 
3054													' +
3055													CASE
3056														WHEN 
3057															@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
3058															AND @sys_info = 1
3059															THEN
3060																'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '
3061														ELSE
3062															'CONVERT(BIGINT, NULL) '
3063													END + 
3064														' AS thread_CPU_snapshot, 
3065													COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
3066													t.task_address,
3067													t.task_state,
3068													CASE
3069														WHEN
3070															t.task_state = ''RUNNABLE''
3071															AND w.runnable_time > 0 THEN
3072																w.runnable_time
3073														ELSE
3074															NULL
3075													END AS runnable_time
3076												FROM sys.dm_os_tasks AS t
3077												CROSS APPLY
3078												(
3079													SELECT TOP(1)
3080														sp2.session_id
3081													FROM @sessions AS sp2
3082													WHERE
3083														sp2.session_id = t.session_id
3084														AND sp2.request_id = t.request_id
3085														AND sp2.status <> ''sleeping''
3086												) AS sp20
3087												LEFT OUTER HASH JOIN
3088												( 
3089												' +
3090													CASE
3091														WHEN @sys_info = 1 THEN
3092															'SELECT TOP(@i)
3093																(
3094																	SELECT TOP(@i)
3095																		ms_ticks
3096																	FROM sys.dm_os_sys_info
3097																) -
3098																	w0.wait_resumed_ms_ticks AS runnable_time,
3099																w0.worker_address,
3100																w0.thread_address,
3101																w0.task_bound_ms_ticks
3102															FROM sys.dm_os_workers AS w0
3103															WHERE
3104																w0.state = ''RUNNABLE''
3105																OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks'
3106														ELSE
3107															'SELECT
3108																CONVERT(BIGINT, NULL) AS runnable_time,
3109																CONVERT(VARBINARY(8), NULL) AS worker_address,
3110																CONVERT(VARBINARY(8), NULL) AS thread_address,
3111																CONVERT(BIGINT, NULL) AS task_bound_ms_ticks
3112															WHERE
3113																1 = 0'
3114														END +
3115												'
3116												) AS w ON
3117													w.worker_address = t.worker_address 
3118												' +
3119												CASE
3120													WHEN
3121														@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
3122														AND @sys_info = 1
3123														THEN
3124															'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
3125																tr.thread_address = w.thread_address
3126																AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
3127															'
3128													ELSE
3129														''
3130												END +
3131											') AS task_info
3132											LEFT OUTER HASH JOIN
3133											(
3134												SELECT TOP(@i)
3135													wt1.wait_type,
3136													wt1.waiting_task_address,
3137													MAX(wt1.wait_duration_ms) AS wait_duration_ms,
3138													MAX(wt1.block_info) AS block_info
3139												FROM
3140												(
3141													SELECT DISTINCT TOP(@i)
3142														wt.wait_type +
3143															CASE
3144																WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN
3145																	'':'' +
3146																	COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +
3147																	N'':'' +
3148																	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)) +
3149																	N''('' +
3150																		CASE
3151																			WHEN
3152																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR
3153																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0
3154																					THEN 
3155																						N''PFS''
3156																			WHEN
3157																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR
3158																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0 
3159																					THEN 
3160																						N''GAM''
3161																			WHEN
3162																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR
3163																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0 
3164																					THEN 
3165																						N''SGAM''
3166																			WHEN
3167																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR
3168																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0 
3169																					THEN 
3170																						N''DCM''
3171																			WHEN
3172																				CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR
3173																				(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
3174																					THEN 
3175																						N''BCM''
3176																			ELSE
3177																				N''*''
3178																		END +
3179																	N'')''
3180																WHEN wt.wait_type = N''CXPACKET'' THEN
3181																	N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)
3182																WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN
3183																	N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''
3184																ELSE 
3185																	N''''
3186															END COLLATE Latin1_General_Bin2 AS wait_type,
3187														CASE
3188															WHEN
3189															(
3190																wt.blocking_session_id IS NOT NULL
3191																AND wt.wait_type LIKE N''LCK[_]%''
3192															) THEN
3193																(
3194																	SELECT TOP(@i)
3195																		x.lock_type,
3196																		REPLACE
3197																		(
3198																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3199																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3200																			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3201																				DB_NAME
3202																				(
3203																					CONVERT
3204																					(
3205																						INT,
3206																						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)
3207																					)
3208																				),
3209																				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''?''),
3210																				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''?''),
3211																				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''?''),
3212																			NCHAR(0),
3213																			N''''
3214																		) AS database_name,
3215																		CASE x.lock_type
3216																			WHEN N''objectlock'' THEN
3217																				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)
3218																			ELSE
3219																				NULL
3220																		END AS object_id,
3221																		CASE x.lock_type
3222																			WHEN N''filelock'' THEN
3223																				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)
3224																			ELSE
3225																				NULL
3226																		END AS file_id,
3227																		CASE
3228																			WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN
3229																				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)
3230																			WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN
3231																				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)
3232																			ELSE
3233																				NULL
3234																		END AS hobt_id,
3235																		CASE x.lock_type
3236																			WHEN N''applicationlock'' THEN
3237																				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)
3238																			ELSE
3239																				NULL
3240																		END AS applock_hash,
3241																		CASE x.lock_type
3242																			WHEN N''metadatalock'' THEN
3243																				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)
3244																			ELSE
3245																				NULL
3246																		END AS metadata_resource,
3247																		CASE x.lock_type
3248																			WHEN N''metadatalock'' THEN
3249																				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)
3250																			ELSE
3251																				NULL
3252																		END AS metadata_class_id
3253																	FROM
3254																	(
3255																		SELECT TOP(1)
3256																			LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
3257																	) AS x
3258																	FOR XML
3259																		PATH('''')
3260																)
3261															ELSE NULL
3262														END AS block_info,
3263														wt.wait_duration_ms,
3264														wt.waiting_task_address
3265													FROM
3266													(
3267														SELECT TOP(@i)
3268															wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
3269															wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
3270															wt0.wait_duration_ms,
3271															wt0.waiting_task_address,
3272															CASE
3273																WHEN wt0.blocking_session_id = p.blocked THEN
3274																	wt0.blocking_session_id
3275																ELSE
3276																	NULL
3277															END AS blocking_session_id
3278														FROM sys.dm_os_waiting_tasks AS wt0
3279														CROSS APPLY
3280														(
3281															SELECT TOP(1)
3282																s0.blocked
3283															FROM @sessions AS s0
3284															WHERE
3285																s0.session_id = wt0.session_id
3286																AND COALESCE(s0.wait_type, N'''') <> N''OLEDB''
3287																AND wt0.wait_type <> N''OLEDB''
3288														) AS p
3289													) AS wt
3290												) AS wt1
3291												GROUP BY
3292													wt1.wait_type,
3293													wt1.waiting_task_address
3294											) AS wt2 ON
3295												wt2.waiting_task_address = task_info.task_address
3296												AND wt2.wait_duration_ms > 0
3297												AND task_info.runnable_time IS NULL
3298											GROUP BY
3299												task_info.session_id,
3300												task_info.request_id,
3301												task_info.physical_io,
3302												task_info.context_switches,
3303												task_info.thread_CPU_snapshot,
3304												task_info.num_tasks,
3305												CASE
3306													WHEN task_info.runnable_time IS NOT NULL THEN
3307														''RUNNABLE''
3308													ELSE
3309														wt2.wait_type
3310												END
3311										) AS w1
3312									) AS waits
3313									ORDER BY
3314										waits.session_id,
3315										waits.request_id,
3316										waits.r
3317									FOR XML
3318										PATH(N''tasks''),
3319										TYPE
3320								) AS tasks_raw (task_xml_raw)
3321							) AS tasks_final
3322							CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node)
3323							WHERE
3324								task_nodes.task_node.exist(N''session_id'') = 1
3325						) AS tasks ON
3326							tasks.session_id = y.session_id
3327							AND tasks.request_id = y.request_id 
3328						'
3329					ELSE
3330						''
3331				END +
3332				'LEFT OUTER HASH JOIN
3333				(
3334					SELECT TOP(@i)
3335						t_info.session_id,
3336						COALESCE(t_info.request_id, -1) AS request_id,
3337						SUM(t_info.tempdb_allocations) AS tempdb_allocations,
3338						SUM(t_info.tempdb_current) AS tempdb_current
3339					FROM
3340					(
3341						SELECT TOP(@i)
3342							tsu.session_id,
3343							tsu.request_id,
3344							tsu.user_objects_alloc_page_count +
3345								tsu.internal_objects_alloc_page_count AS tempdb_allocations,
3346							tsu.user_objects_alloc_page_count +
3347								tsu.internal_objects_alloc_page_count -
3348								tsu.user_objects_dealloc_page_count -
3349								tsu.internal_objects_dealloc_page_count AS tempdb_current
3350						FROM sys.dm_db_task_space_usage AS tsu
3351						CROSS APPLY
3352						(
3353							SELECT TOP(1)
3354								s0.session_id
3355							FROM @sessions AS s0
3356							WHERE
3357								s0.session_id = tsu.session_id
3358						) AS p
3359
3360						UNION ALL
3361
3362						SELECT TOP(@i)
3363							ssu.session_id,
3364							NULL AS request_id,
3365							ssu.user_objects_alloc_page_count +
3366								ssu.internal_objects_alloc_page_count AS tempdb_allocations,
3367							ssu.user_objects_alloc_page_count +
3368								ssu.internal_objects_alloc_page_count -
3369								ssu.user_objects_dealloc_page_count -
3370								ssu.internal_objects_dealloc_page_count AS tempdb_current
3371						FROM sys.dm_db_session_space_usage AS ssu
3372						CROSS APPLY
3373						(
3374							SELECT TOP(1)
3375								s0.session_id
3376							FROM @sessions AS s0
3377							WHERE
3378								s0.session_id = ssu.session_id
3379						) AS p
3380					) AS t_info
3381					GROUP BY
3382						t_info.session_id,
3383						COALESCE(t_info.request_id, -1)
3384				) AS tempdb_info ON
3385					tempdb_info.session_id = y.session_id
3386					AND tempdb_info.request_id =
3387						CASE
3388							WHEN y.status = N''sleeping'' THEN
3389								-1
3390							ELSE
3391								y.request_id
3392						END
3393				' +
3394				CASE 
3395					WHEN 
3396						NOT 
3397						(
3398							@get_avg_time = 1 
3399							AND @recursion = 1
3400						) THEN 
3401							''
3402					ELSE
3403						'LEFT OUTER HASH JOIN
3404						(
3405							SELECT TOP(@i)
3406								*
3407							FROM sys.dm_exec_query_stats
3408						) AS qs ON
3409							qs.sql_handle = y.sql_handle
3410							AND qs.plan_handle = y.plan_handle
3411							AND qs.statement_start_offset = y.statement_start_offset
3412							AND qs.statement_end_offset = y.statement_end_offset
3413						'
3414				END + 
3415			') AS x
3416			OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';
3417
3418		SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
3419
3420		SET @last_collection_start = GETDATE();
3421
3422		IF 
3423			@recursion = -1
3424			AND @sys_info = 1
3425		BEGIN;
3426			SELECT
3427				@first_collection_ms_ticks = ms_ticks
3428			FROM sys.dm_os_sys_info;
3429		END;
3430
3431		INSERT #sessions
3432		(
3433			recursion,
3434			session_id,
3435			request_id,
3436			session_number,
3437			elapsed_time,
3438			avg_elapsed_time,
3439			physical_io,
3440			reads,
3441			physical_reads,
3442			writes,
3443			tempdb_allocations,
3444			tempdb_current,
3445			CPU,
3446			thread_CPU_snapshot,
3447			context_switches,
3448			used_memory,
3449			tasks,
3450			status,
3451			wait_info,
3452			transaction_id,
3453			open_tran_count,
3454			sql_handle,
3455			statement_start_offset,
3456			statement_end_offset,		
3457			sql_text,
3458			plan_handle,
3459			blocking_session_id,
3460			percent_complete,
3461			host_name,
3462			login_name,
3463			database_name,
3464			program_name,
3465			additional_info,
3466			start_time,
3467			login_time,
3468			last_request_start_time
3469		)
3470		EXEC sp_executesql 
3471			@sql_n,
3472			N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',
3473			@recursion, @filter, @not_filter, @first_collection_ms_ticks;
3474
3475		--Collect transaction information?
3476		IF
3477			@recursion = 1
3478			AND
3479			(
3480				@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
3481				OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|' 
3482			)
3483		BEGIN;	
3484			DECLARE @i INT;
3485			SET @i = 2147483647;
3486
3487			UPDATE s
3488			SET
3489				tran_start_time =
3490					CONVERT
3491					(
3492						DATETIME,
3493						LEFT
3494						(
3495							x.trans_info,
3496							NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
3497						),
3498						121
3499					),
3500				tran_log_writes =
3501					RIGHT
3502					(
3503						x.trans_info,
3504						LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
3505					)
3506			FROM
3507			(
3508				SELECT TOP(@i)
3509					trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id,
3510					COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id,
3511					trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info				
3512				FROM
3513				(
3514					SELECT TOP(@i)
3515						CONVERT
3516						(
3517							XML,
3518							REPLACE
3519							(
3520								CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2, 
3521								N'</trans_info></trans><trans><trans_info>', N''
3522							)
3523						)
3524					FROM
3525					(
3526						SELECT TOP(@i)
3527							CASE u_trans.r
3528								WHEN 1 THEN u_trans.session_id
3529								ELSE NULL
3530							END AS [session_id],
3531							CASE u_trans.r
3532								WHEN 1 THEN u_trans.request_id
3533								ELSE NULL
3534							END AS [request_id],
3535							CONVERT
3536							(
3537								NVARCHAR(MAX),
3538								CASE
3539									WHEN u_trans.database_id IS NOT NULL THEN
3540										CASE u_trans.r
3541											WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'')
3542											ELSE N''
3543										END + 
3544											REPLACE
3545											(
3546												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3547												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3548												REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3549													CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),
3550													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'?'),
3551													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'?'),
3552													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'?'),
3553												NCHAR(0),
3554												N'?'
3555											) +
3556											N': ' +
3557										CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +
3558										N','
3559									ELSE
3560										N'N/A,'
3561								END COLLATE Latin1_General_Bin2
3562							) AS [trans_info]
3563						FROM
3564						(
3565							SELECT TOP(@i)
3566								trans.*,
3567								ROW_NUMBER() OVER
3568								(
3569									PARTITION BY
3570										trans.session_id,
3571										trans.request_id
3572									ORDER BY
3573										trans.transaction_start_time DESC
3574								) AS r
3575							FROM
3576							(
3577								SELECT TOP(@i)
3578									session_tran_map.session_id,
3579									session_tran_map.request_id,
3580									s_tran.database_id,
3581									COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,
3582									COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used,
3583									MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
3584								FROM
3585								(
3586									SELECT TOP(@i)
3587										*
3588									FROM sys.dm_tran_active_transactions
3589									WHERE
3590										transaction_begin_time <= @last_collection_start
3591								) AS a_tran
3592								INNER HASH JOIN
3593								(
3594									SELECT TOP(@i)
3595										*
3596									FROM sys.dm_tran_database_transactions
3597									WHERE
3598										database_id < 32767
3599								) AS s_tran ON
3600									s_tran.transaction_id = a_tran.transaction_id
3601								LEFT OUTER HASH JOIN
3602								(
3603									SELECT TOP(@i)
3604										*
3605									FROM sys.dm_tran_session_transactions
3606								) AS tst ON
3607									s_tran.transaction_id = tst.transaction_id
3608								CROSS APPLY
3609								(
3610									SELECT TOP(1)
3611										s3.session_id,
3612										s3.request_id
3613									FROM
3614									(
3615										SELECT TOP(1)
3616											s1.session_id,
3617											s1.request_id
3618										FROM #sessions AS s1
3619										WHERE
3620											s1.transaction_id = s_tran.transaction_id
3621											AND s1.recursion = 1
3622											
3623										UNION ALL
3624									
3625										SELECT TOP(1)
3626											s2.session_id,
3627											s2.request_id
3628										FROM #sessions AS s2
3629										WHERE
3630											s2.session_id = tst.session_id
3631											AND s2.recursion = 1
3632									) AS s3
3633									ORDER BY
3634										s3.request_id
3635								) AS session_tran_map
3636								GROUP BY
3637									session_tran_map.session_id,
3638									session_tran_map.request_id,
3639									s_tran.database_id
3640							) AS trans
3641						) AS u_trans
3642						FOR XML
3643							PATH('trans'),
3644							TYPE
3645					) AS trans_raw (trans_xml_raw)
3646				) AS trans_final (trans_xml)
3647				CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node)
3648			) AS x
3649			INNER HASH JOIN #sessions AS s ON
3650				s.session_id = x.session_id
3651				AND s.request_id = x.request_id
3652			OPTION (OPTIMIZE FOR (@i = 1));
3653		END;
3654
3655		--Variables for text and plan collection
3656		DECLARE	
3657			@session_id SMALLINT,
3658			@request_id INT,
3659			@sql_handle VARBINARY(64),
3660			@plan_handle VARBINARY(64),
3661			@statement_start_offset INT,
3662			@statement_end_offset INT,
3663			@start_time DATETIME,
3664			@database_name sysname;
3665
3666		IF 
3667			@recursion = 1
3668			AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
3669		BEGIN;
3670			DECLARE sql_cursor
3671			CURSOR LOCAL FAST_FORWARD
3672			FOR 
3673				SELECT 
3674					session_id,
3675					request_id,
3676					sql_handle,
3677					statement_start_offset,
3678					statement_end_offset
3679				FROM #sessions
3680				WHERE
3681					recursion = 1
3682					AND sql_handle IS NOT NULL
3683			OPTION (KEEPFIXED PLAN);
3684
3685			OPEN sql_cursor;
3686
3687			FETCH NEXT FROM sql_cursor
3688			INTO 
3689				@session_id,
3690				@request_id,
3691				@sql_handle,
3692				@statement_start_offset,
3693				@statement_end_offset;
3694
3695			--Wait up to 5 ms for the SQL text, then give up
3696			SET LOCK_TIMEOUT 5;
3697
3698			WHILE @@FETCH_STATUS = 0
3699			BEGIN;
3700				BEGIN TRY;
3701					UPDATE s
3702					SET
3703						s.sql_text =
3704						(
3705							SELECT
3706								REPLACE
3707								(
3708									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3709									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3710									REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3711										N'--' + NCHAR(13) + NCHAR(10) +
3712										CASE 
3713											WHEN @get_full_inner_text = 1 THEN est.text
3714											WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
3715											WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
3716											ELSE
3717												CASE
3718													WHEN @statement_start_offset > 0 THEN
3719														SUBSTRING
3720														(
3721															est.text,
3722															((@statement_start_offset/2) + 1),
3723															(
3724																CASE
3725																	WHEN @statement_end_offset = -1 THEN 2147483647
3726																	ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
3727																END
3728															)
3729														)
3730													ELSE RTRIM(LTRIM(est.text))
3731												END
3732										END +
3733										NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
3734										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'?'),
3735										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'?'),
3736										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'?'),
3737									NCHAR(0),
3738									N''
3739								) AS [processing-instruction(query)]
3740							FOR XML
3741								PATH(''),
3742								TYPE
3743						),
3744						s.statement_start_offset = 
3745							CASE 
3746								WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
3747								WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
3748								ELSE @statement_start_offset
3749							END,
3750						s.statement_end_offset = 
3751							CASE 
3752								WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
3753								WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
3754								ELSE @statement_end_offset
3755							END
3756					FROM 
3757						#sessions AS s,
3758						(
3759							SELECT TOP(1)
3760								text
3761							FROM
3762							(
3763								SELECT 
3764									text, 
3765									0 AS row_num
3766								FROM sys.dm_exec_sql_text(@sql_handle)
3767								
3768								UNION ALL
3769								
3770								SELECT 
3771									NULL,
3772									1 AS row_num
3773							) AS est0
3774							ORDER BY
3775								row_num
3776						) AS est
3777					WHERE 
3778						s.session_id = @session_id
3779						AND s.request_id = @request_id
3780						AND s.recursion = 1
3781					OPTION (KEEPFIXED PLAN);
3782				END TRY
3783				BEGIN CATCH;
3784					UPDATE s
3785					SET
3786						s.sql_text = 
3787							CASE ERROR_NUMBER() 
3788								WHEN 1222 THEN '<timeout_exceeded />'
3789								ELSE '<error message="' + ERROR_MESSAGE() + '" />'
3790							END
3791					FROM #sessions AS s
3792					WHERE 
3793						s.session_id = @session_id
3794						AND s.request_id = @request_id
3795						AND s.recursion = 1
3796					OPTION (KEEPFIXED PLAN);
3797				END CATCH;
3798
3799				FETCH NEXT FROM sql_cursor
3800				INTO
3801					@session_id,
3802					@request_id,
3803					@sql_handle,
3804					@statement_start_offset,
3805					@statement_end_offset;
3806			END;
3807
3808			--Return this to the default
3809			SET LOCK_TIMEOUT -1;
3810
3811			CLOSE sql_cursor;
3812			DEALLOCATE sql_cursor;
3813		END;
3814
3815		IF 
3816			@get_outer_command = 1 
3817			AND @recursion = 1
3818			AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
3819		BEGIN;
3820			DECLARE @buffer_results TABLE
3821			(
3822				EventType VARCHAR(30),
3823				Parameters INT,
3824				EventInfo NVARCHAR(4000),
3825				start_time DATETIME,
3826				session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY
3827			);
3828
3829			DECLARE buffer_cursor
3830			CURSOR LOCAL FAST_FORWARD
3831			FOR 
3832				SELECT 
3833					session_id,
3834					MAX(start_time) AS start_time
3835				FROM #sessions
3836				WHERE
3837					recursion = 1
3838				GROUP BY
3839					session_id
3840				ORDER BY
3841					session_id
3842				OPTION (KEEPFIXED PLAN);
3843
3844			OPEN buffer_cursor;
3845
3846			FETCH NEXT FROM buffer_cursor
3847			INTO 
3848				@session_id,
3849				@start_time;
3850
3851			WHILE @@FETCH_STATUS = 0
3852			BEGIN;
3853				BEGIN TRY;
3854					--In SQL Server 2008, DBCC INPUTBUFFER will throw 
3855					--an exception if the session no longer exists
3856					INSERT @buffer_results
3857					(
3858						EventType,
3859						Parameters,
3860						EventInfo
3861					)
3862					EXEC sp_executesql
3863						N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
3864						N'@session_id SMALLINT',
3865						@session_id;
3866
3867					UPDATE br
3868					SET
3869						br.start_time = @start_time
3870					FROM @buffer_results AS br
3871					WHERE
3872						br.session_number = 
3873						(
3874							SELECT MAX(br2.session_number)
3875							FROM @buffer_results br2
3876						);
3877				END TRY
3878				BEGIN CATCH
3879				END CATCH;
3880
3881				FETCH NEXT FROM buffer_cursor
3882				INTO 
3883					@session_id,
3884					@start_time;
3885			END;
3886
3887			UPDATE s
3888			SET
3889				sql_command = 
3890				(
3891					SELECT 
3892						REPLACE
3893						(
3894							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3895							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3896							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3897								CONVERT
3898								(
3899									NVARCHAR(MAX),
3900									N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
3901								),
3902								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'?'),
3903								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'?'),
3904								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'?'),
3905							NCHAR(0),
3906							N''
3907						) AS [processing-instruction(query)]
3908					FROM @buffer_results AS br
3909					WHERE 
3910						br.session_number = s.session_number
3911						AND br.start_time = s.start_time
3912						AND 
3913						(
3914							(
3915								s.start_time = s.last_request_start_time
3916								AND EXISTS
3917								(
3918									SELECT *
3919									FROM sys.dm_exec_requests r2
3920									WHERE
3921										r2.session_id = s.session_id
3922										AND r2.request_id = s.request_id
3923										AND r2.start_time = s.start_time
3924								)
3925							)
3926							OR 
3927							(
3928								s.request_id = 0
3929								AND EXISTS
3930								(
3931									SELECT *
3932									FROM sys.dm_exec_sessions s2
3933									WHERE
3934										s2.session_id = s.session_id
3935										AND s2.last_request_start_time = s.last_request_start_time
3936								)
3937							)
3938						)
3939					FOR XML
3940						PATH(''),
3941						TYPE
3942				)
3943			FROM #sessions AS s
3944			WHERE
3945				recursion = 1
3946			OPTION (KEEPFIXED PLAN);
3947
3948			CLOSE buffer_cursor;
3949			DEALLOCATE buffer_cursor;
3950		END;
3951
3952		IF 
3953			@get_plans >= 1 
3954			AND @recursion = 1
3955			AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
3956		BEGIN;
3957			DECLARE @live_plan BIT;
3958			SET @live_plan = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_exec_query_statistics_xml'))), 0)
3959
3960			DECLARE plan_cursor
3961			CURSOR LOCAL FAST_FORWARD
3962			FOR 
3963				SELECT
3964					session_id,
3965					request_id,
3966					plan_handle,
3967					statement_start_offset,
3968					statement_end_offset
3969				FROM #sessions
3970				WHERE
3971					recursion = 1
3972					AND plan_handle IS NOT NULL
3973			OPTION (KEEPFIXED PLAN);
3974
3975			OPEN plan_cursor;
3976
3977			FETCH NEXT FROM plan_cursor
3978			INTO 
3979				@session_id,
3980				@request_id,
3981				@plan_handle,
3982				@statement_start_offset,
3983				@statement_end_offset;
3984
3985			--Wait up to 5 ms for a query plan, then give up
3986			SET LOCK_TIMEOUT 5;
3987
3988			WHILE @@FETCH_STATUS = 0
3989			BEGIN;
3990				DECLARE @query_plan XML;
3991				SET @query_plan = NULL;
3992
3993				IF @live_plan = 1
3994				BEGIN;
3995					BEGIN TRY;
3996						SELECT
3997							@query_plan = x.query_plan
3998						FROM sys.dm_exec_query_statistics_xml(@session_id) AS x;
3999
4000						IF 
4001							@query_plan IS NOT NULL
4002							AND EXISTS
4003							(
4004								SELECT
4005									*
4006								FROM sys.dm_exec_requests AS r
4007								WHERE
4008									r.session_id = @session_id
4009									AND r.request_id = @request_id
4010									AND r.plan_handle = @plan_handle
4011									AND r.statement_start_offset = @statement_start_offset
4012									AND r.statement_end_offset = @statement_end_offset
4013							)
4014						BEGIN;
4015							UPDATE s
4016							SET
4017								s.query_plan = @query_plan
4018							FROM #sessions AS s
4019							WHERE 
4020								s.session_id = @session_id
4021								AND s.request_id = @request_id
4022								AND s.recursion = 1
4023							OPTION (KEEPFIXED PLAN);
4024						END;
4025					END TRY
4026					BEGIN CATCH;
4027						SET @query_plan = NULL;
4028					END CATCH;
4029				END;
4030
4031				IF @query_plan IS NULL
4032				BEGIN;
4033					BEGIN TRY;
4034						UPDATE s
4035						SET
4036							s.query_plan =
4037							(
4038								SELECT
4039									CONVERT(xml, query_plan)
4040								FROM sys.dm_exec_text_query_plan
4041								(
4042									@plan_handle, 
4043									CASE @get_plans
4044										WHEN 1 THEN
4045											@statement_start_offset
4046										ELSE
4047											0
4048									END, 
4049									CASE @get_plans
4050										WHEN 1 THEN
4051											@statement_end_offset
4052										ELSE
4053											-1
4054									END
4055								)
4056							)
4057						FROM #sessions AS s
4058						WHERE 
4059							s.session_id = @session_id
4060							AND s.request_id = @request_id
4061							AND s.recursion = 1
4062						OPTION (KEEPFIXED PLAN);
4063					END TRY
4064					BEGIN CATCH;
4065						IF ERROR_NUMBER() = 6335
4066						BEGIN;
4067							UPDATE s
4068							SET
4069								s.query_plan =
4070								(
4071									SELECT
4072										N'--' + NCHAR(13) + NCHAR(10) + 
4073										N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) + 
4074										N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +
4075										N'--' + NCHAR(13) + NCHAR(10) +
4076											REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') + 
4077											NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
4078									FROM sys.dm_exec_text_query_plan
4079									(
4080										@plan_handle, 
4081										CASE @get_plans
4082											WHEN 1 THEN
4083												@statement_start_offset
4084											ELSE
4085												0
4086										END, 
4087										CASE @get_plans
4088											WHEN 1 THEN
4089												@statement_end_offset
4090											ELSE
4091												-1
4092										END
4093									) AS qp
4094									FOR XML
4095										PATH(''),
4096										TYPE
4097								)
4098							FROM #sessions AS s
4099							WHERE 
4100								s.session_id = @session_id
4101								AND s.request_id = @request_id
4102								AND s.recursion = 1
4103							OPTION (KEEPFIXED PLAN);
4104						END;
4105						ELSE
4106						BEGIN;
4107							UPDATE s
4108							SET
4109								s.query_plan = 
4110									CASE ERROR_NUMBER() 
4111										WHEN 1222 THEN '<timeout_exceeded />'
4112										ELSE '<error message="' + ERROR_MESSAGE() + '" />'
4113									END
4114							FROM #sessions AS s
4115							WHERE 
4116								s.session_id = @session_id
4117								AND s.request_id = @request_id
4118								AND s.recursion = 1
4119							OPTION (KEEPFIXED PLAN);
4120						END;
4121					END CATCH;
4122				END;
4123
4124				FETCH NEXT FROM plan_cursor
4125				INTO
4126					@session_id,
4127					@request_id,
4128					@plan_handle,
4129					@statement_start_offset,
4130					@statement_end_offset;
4131			END;
4132
4133			--Return this to the default
4134			SET LOCK_TIMEOUT -1;
4135
4136			CLOSE plan_cursor;
4137			DEALLOCATE plan_cursor;
4138		END;
4139
4140		IF 
4141			@get_locks = 1 
4142			AND @recursion = 1
4143			AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
4144		BEGIN;
4145			DECLARE locks_cursor
4146			CURSOR LOCAL FAST_FORWARD
4147			FOR 
4148				SELECT DISTINCT
4149					database_name
4150				FROM #locks
4151				WHERE
4152					EXISTS
4153					(
4154						SELECT *
4155						FROM #sessions AS s
4156						WHERE
4157							s.session_id = #locks.session_id
4158							AND recursion = 1
4159					)
4160					AND database_name <> '(null)'
4161				OPTION (KEEPFIXED PLAN);
4162
4163			OPEN locks_cursor;
4164
4165			FETCH NEXT FROM locks_cursor
4166			INTO 
4167				@database_name;
4168
4169			WHILE @@FETCH_STATUS = 0
4170			BEGIN;
4171				BEGIN TRY;
4172					SET @sql_n = CONVERT(NVARCHAR(MAX), '') +
4173						'UPDATE l ' +
4174						'SET ' +
4175							'object_name = ' +
4176								'REPLACE ' +
4177								'( ' +
4178									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4179									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4180									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4181										'o.name COLLATE Latin1_General_Bin2, ' +
4182										'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''?''), ' +
4183										'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''?''), ' +
4184										'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''?''), ' +
4185									'NCHAR(0), ' +
4186									N''''' ' +
4187								'), ' +
4188							'index_name = ' +
4189								'REPLACE ' +
4190								'( ' +
4191									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4192									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4193									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4194										'i.name COLLATE Latin1_General_Bin2, ' +
4195										'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''?''), ' +
4196										'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''?''), ' +
4197										'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''?''), ' +
4198									'NCHAR(0), ' +
4199									N''''' ' +
4200								'), ' +
4201							'schema_name = ' +
4202								'REPLACE ' +
4203								'( ' +
4204									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4205									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4206									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4207										's.name COLLATE Latin1_General_Bin2, ' +
4208										'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''?''), ' +
4209										'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''?''), ' +
4210										'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''?''), ' +
4211									'NCHAR(0), ' +
4212									N''''' ' +
4213								'), ' +
4214							'principal_name = ' + 
4215								'REPLACE ' +
4216								'( ' +
4217									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4218									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4219									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4220										'dp.name COLLATE Latin1_General_Bin2, ' +
4221										'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''?''), ' +
4222										'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''?''), ' +
4223										'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''?''), ' +
4224									'NCHAR(0), ' +
4225									N''''' ' +
4226								') ' +
4227						'FROM #locks AS l ' +
4228						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +
4229							'au.allocation_unit_id = l.allocation_unit_id ' +
4230						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4231							'p.hobt_id = ' +
4232								'COALESCE ' +
4233								'( ' +
4234									'l.hobt_id, ' +
4235									'CASE ' +
4236										'WHEN au.type IN (1, 3) THEN au.container_id ' +
4237										'ELSE NULL ' +
4238									'END ' +
4239								') ' +
4240						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +
4241							'l.hobt_id IS NULL ' +
4242							'AND au.type = 2 ' +
4243							'AND p1.partition_id = au.container_id ' +
4244						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4245							'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
4246						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +
4247							'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
4248							'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +
4249						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4250							's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +
4251						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +
4252							'dp.principal_id = l.principal_id ' +
4253						'WHERE ' +
4254							'l.database_name = @database_name ' +
4255						'OPTION (KEEPFIXED PLAN); ';
4256					
4257					EXEC sp_executesql
4258						@sql_n,
4259						N'@database_name sysname',
4260						@database_name;
4261				END TRY
4262				BEGIN CATCH;
4263					UPDATE #locks
4264					SET
4265						query_error = 
4266							REPLACE
4267							(
4268								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4269								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4270								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4271									CONVERT
4272									(
4273										NVARCHAR(MAX), 
4274										ERROR_MESSAGE() COLLATE Latin1_General_Bin2
4275									),
4276									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'?'),
4277									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'?'),
4278									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'?'),
4279								NCHAR(0),
4280								N''
4281							)
4282					WHERE 
4283						database_name = @database_name
4284					OPTION (KEEPFIXED PLAN);
4285				END CATCH;
4286
4287				FETCH NEXT FROM locks_cursor
4288				INTO
4289					@database_name;
4290			END;
4291
4292			CLOSE locks_cursor;
4293			DEALLOCATE locks_cursor;
4294
4295			CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);
4296
4297			UPDATE s
4298			SET 
4299				s.locks =
4300				(
4301					SELECT 
4302						REPLACE
4303						(
4304							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4305							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4306							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4307								CONVERT
4308								(
4309									NVARCHAR(MAX), 
4310									l1.database_name COLLATE Latin1_General_Bin2
4311								),
4312								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'?'),
4313								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'?'),
4314								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'?'),
4315							NCHAR(0),
4316							N''
4317						) AS [Database/@name],
4318						MIN(l1.query_error) AS [Database/@query_error],
4319						(
4320							SELECT 
4321								l2.request_mode AS [Lock/@request_mode],
4322								l2.request_status AS [Lock/@request_status],
4323								COUNT(*) AS [Lock/@request_count]
4324							FROM #locks AS l2
4325							WHERE 
4326								l1.session_id = l2.session_id
4327								AND l1.request_id = l2.request_id
4328								AND l2.database_name = l1.database_name
4329								AND l2.resource_type = 'DATABASE'
4330							GROUP BY
4331								l2.request_mode,
4332								l2.request_status
4333							FOR XML
4334								PATH(''),
4335								TYPE
4336						) AS [Database/Locks],
4337						(
4338							SELECT
4339								COALESCE(l3.object_name, '(null)') AS [Object/@name],
4340								l3.schema_name AS [Object/@schema_name],
4341								(
4342									SELECT
4343										l4.resource_type AS [Lock/@resource_type],
4344										l4.page_type AS [Lock/@page_type],
4345										l4.index_name AS [Lock/@index_name],
4346										CASE 
4347											WHEN l4.object_name IS NULL THEN l4.schema_name
4348											ELSE NULL
4349										END AS [Lock/@schema_name],
4350										l4.principal_name AS [Lock/@principal_name],
4351										l4.resource_description AS [Lock/@resource_description],
4352										l4.request_mode AS [Lock/@request_mode],
4353										l4.request_status AS [Lock/@request_status],
4354										SUM(l4.request_count) AS [Lock/@request_count]
4355									FROM #locks AS l4
4356									WHERE 
4357										l4.session_id = l3.session_id
4358										AND l4.request_id = l3.request_id
4359										AND l3.database_name = l4.database_name
4360										AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')
4361										AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')
4362										AND l4.resource_type <> 'DATABASE'
4363									GROUP BY
4364										l4.resource_type,
4365										l4.page_type,
4366										l4.index_name,
4367										CASE 
4368											WHEN l4.object_name IS NULL THEN l4.schema_name
4369											ELSE NULL
4370										END,
4371										l4.principal_name,
4372										l4.resource_description,
4373										l4.request_mode,
4374										l4.request_status
4375									FOR XML
4376										PATH(''),
4377										TYPE
4378								) AS [Object/Locks]
4379							FROM #locks AS l3
4380							WHERE 
4381								l3.session_id = l1.session_id
4382								AND l3.request_id = l1.request_id
4383								AND l3.database_name = l1.database_name
4384								AND l3.resource_type <> 'DATABASE'
4385							GROUP BY 
4386								l3.session_id,
4387								l3.request_id,
4388								l3.database_name,
4389								COALESCE(l3.object_name, '(null)'),
4390								l3.schema_name
4391							FOR XML
4392								PATH(''),
4393								TYPE
4394						) AS [Database/Objects]
4395					FROM #locks AS l1
4396					WHERE
4397						l1.session_id = s.session_id
4398						AND l1.request_id = s.request_id
4399						AND l1.start_time IN (s.start_time, s.last_request_start_time)
4400						AND s.recursion = 1
4401					GROUP BY 
4402						l1.session_id,
4403						l1.request_id,
4404						l1.database_name
4405					FOR XML
4406						PATH(''),
4407						TYPE
4408				)
4409			FROM #sessions s
4410			OPTION (KEEPFIXED PLAN);
4411		END;
4412
4413		IF 
4414			@find_block_leaders = 1
4415			AND @recursion = 1
4416			AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'
4417		BEGIN;
4418			WITH
4419			blockers AS
4420			(
4421				SELECT
4422					session_id,
4423					session_id AS top_level_session_id,
4424					CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path
4425				FROM #sessions
4426				WHERE
4427					recursion = 1
4428
4429				UNION ALL
4430
4431				SELECT
4432					s.session_id,
4433					b.top_level_session_id,
4434					CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path
4435				FROM blockers AS b
4436				JOIN #sessions AS s ON
4437					s.blocking_session_id = b.session_id
4438					AND s.recursion = 1
4439					AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2
4440			)
4441			UPDATE s
4442			SET
4443				s.blocked_session_count = x.blocked_session_count
4444			FROM #sessions AS s
4445			JOIN
4446			(
4447				SELECT
4448					b.top_level_session_id AS session_id,
4449					COUNT(*) - 1 AS blocked_session_count
4450				FROM blockers AS b
4451				GROUP BY
4452					b.top_level_session_id
4453			) x ON
4454				s.session_id = x.session_id
4455			WHERE
4456				s.recursion = 1;
4457		END;
4458
4459		IF
4460			@get_task_info = 2
4461			AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
4462			AND @recursion = 1
4463		BEGIN;
4464			CREATE TABLE #blocked_requests
4465			(
4466				session_id SMALLINT NOT NULL,
4467				request_id INT NOT NULL,
4468				database_name sysname NOT NULL,
4469				object_id INT,
4470				hobt_id BIGINT,
4471				schema_id INT,
4472				schema_name sysname NULL,
4473				object_name sysname NULL,
4474				query_error NVARCHAR(2048),
4475				PRIMARY KEY (database_name, session_id, request_id)
4476			);
4477
4478			CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
4479			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4480			CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
4481			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4482			CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
4483			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4484			CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
4485			WITH SAMPLE 0 ROWS, NORECOMPUTE;
4486		
4487			INSERT #blocked_requests
4488			(
4489				session_id,
4490				request_id,
4491				database_name,
4492				object_id,
4493				hobt_id,
4494				schema_id
4495			)
4496			SELECT
4497				session_id,
4498				request_id,
4499				database_name,
4500				object_id,
4501				hobt_id,
4502				CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id
4503			FROM
4504			(
4505				SELECT
4506					session_id,
4507					request_id,
4508					agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name,
4509					agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id,
4510					agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id,
4511					agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node
4512				FROM #sessions AS s
4513				CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node)
4514				WHERE
4515					s.recursion = 1
4516			) AS t
4517			WHERE
4518				t.database_name IS NOT NULL
4519				AND
4520				(
4521					t.object_id IS NOT NULL
4522					OR t.hobt_id IS NOT NULL
4523					OR t.schema_node IS NOT NULL
4524				);
4525			
4526			DECLARE blocks_cursor
4527			CURSOR LOCAL FAST_FORWARD
4528			FOR
4529				SELECT DISTINCT
4530					database_name
4531				FROM #blocked_requests;
4532				
4533			OPEN blocks_cursor;
4534			
4535			FETCH NEXT FROM blocks_cursor
4536			INTO 
4537				@database_name;
4538			
4539			WHILE @@FETCH_STATUS = 0
4540			BEGIN;
4541				BEGIN TRY;
4542					SET @sql_n = 
4543						CONVERT(NVARCHAR(MAX), '') +
4544						'UPDATE b ' +
4545						'SET ' +
4546							'b.schema_name = ' +
4547								'REPLACE ' +
4548								'( ' +
4549									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4550									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4551									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4552										's.name COLLATE Latin1_General_Bin2, ' +
4553										'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''?''), ' +
4554										'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''?''), ' +
4555										'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''?''), ' +
4556									'NCHAR(0), ' +
4557									N''''' ' +
4558								'), ' +
4559							'b.object_name = ' +
4560								'REPLACE ' +
4561								'( ' +
4562									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4563									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4564									'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4565										'o.name COLLATE Latin1_General_Bin2, ' +
4566										'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''?''), ' +
4567										'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''?''), ' +
4568										'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''?''), ' +
4569									'NCHAR(0), ' +
4570									N''''' ' +
4571								') ' +
4572						'FROM #blocked_requests AS b ' +
4573						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4574							'p.hobt_id = b.hobt_id ' +
4575						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4576							'o.object_id = COALESCE(p.object_id, b.object_id) ' +
4577						'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4578							's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +
4579						'WHERE ' +
4580							'b.database_name = @database_name; ';
4581					
4582					EXEC sp_executesql
4583						@sql_n,
4584						N'@database_name sysname',
4585						@database_name;
4586				END TRY
4587				BEGIN CATCH;
4588					UPDATE #blocked_requests
4589					SET
4590						query_error = 
4591							REPLACE
4592							(
4593								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4594								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4595								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4596									CONVERT
4597									(
4598										NVARCHAR(MAX), 
4599										ERROR_MESSAGE() COLLATE Latin1_General_Bin2
4600									),
4601									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'?'),
4602									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'?'),
4603									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'?'),
4604								NCHAR(0),
4605								N''
4606							)
4607					WHERE
4608						database_name = @database_name;
4609				END CATCH;
4610
4611				FETCH NEXT FROM blocks_cursor
4612				INTO
4613					@database_name;
4614			END;
4615			
4616			CLOSE blocks_cursor;
4617			DEALLOCATE blocks_cursor;
4618			
4619			UPDATE s
4620			SET
4621				additional_info.modify
4622				('
4623					insert <schema_name>{sql:column("b.schema_name")}</schema_name>
4624					as last
4625					into (/additional_info/block_info)[1]
4626				')
4627			FROM #sessions AS s
4628			INNER JOIN #blocked_requests AS b ON
4629				b.session_id = s.session_id
4630				AND b.request_id = s.request_id
4631				AND s.recursion = 1
4632			WHERE
4633				b.schema_name IS NOT NULL;
4634
4635			UPDATE s
4636			SET
4637				additional_info.modify
4638				('
4639					insert <object_name>{sql:column("b.object_name")}</object_name>
4640					as last
4641					into (/additional_info/block_info)[1]
4642				')
4643			FROM #sessions AS s
4644			INNER JOIN #blocked_requests AS b ON
4645				b.session_id = s.session_id
4646				AND b.request_id = s.request_id
4647				AND s.recursion = 1
4648			WHERE
4649				b.object_name IS NOT NULL;
4650
4651			UPDATE s
4652			SET
4653				additional_info.modify
4654				('
4655					insert <query_error>{sql:column("b.query_error")}</query_error>
4656					as last
4657					into (/additional_info/block_info)[1]
4658				')
4659			FROM #sessions AS s
4660			INNER JOIN #blocked_requests AS b ON
4661				b.session_id = s.session_id
4662				AND b.request_id = s.request_id
4663				AND s.recursion = 1
4664			WHERE
4665				b.query_error IS NOT NULL;
4666		END;
4667
4668		IF
4669			@output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
4670			AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
4671			AND @recursion = 1
4672			AND DB_ID('msdb') IS NOT NULL
4673		BEGIN;
4674			SET @sql_n =
4675				N'BEGIN TRY;
4676					DECLARE @job_name sysname;
4677					SET @job_name = NULL;
4678					DECLARE @step_name sysname;
4679					SET @step_name = NULL;
4680
4681					SELECT
4682						@job_name = 
4683							REPLACE
4684							(
4685								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4686								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4687								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4688									j.name,
4689									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''?''),
4690									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''?''),
4691									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''?''),
4692								NCHAR(0),
4693								N''?''
4694							),
4695						@step_name = 
4696							REPLACE
4697							(
4698								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4699								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4700								REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4701									s.step_name,
4702									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''?''),
4703									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''?''),
4704									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''?''),
4705								NCHAR(0),
4706								N''?''
4707							)
4708					FROM msdb.dbo.sysjobs AS j
4709					INNER JOIN msdb.dbo.sysjobsteps AS s ON
4710						j.job_id = s.job_id
4711					WHERE
4712						j.job_id = @job_id
4713						AND s.step_id = @step_id;
4714
4715					IF @job_name IS NOT NULL
4716					BEGIN;
4717						UPDATE s
4718						SET
4719							additional_info.modify
4720							(''
4721								insert text{sql:variable("@job_name")}
4722								into (/additional_info/agent_job_info/job_name)[1]
4723							'')
4724						FROM #sessions AS s
4725						WHERE 
4726							s.session_id = @session_id
4727							AND s.recursion = 1
4728						OPTION (KEEPFIXED PLAN);
4729						
4730						UPDATE s
4731						SET
4732							additional_info.modify
4733							(''
4734								insert text{sql:variable("@step_name")}
4735								into (/additional_info/agent_job_info/step_name)[1]
4736							'')
4737						FROM #sessions AS s
4738						WHERE 
4739							s.session_id = @session_id
4740							AND s.recursion = 1
4741						OPTION (KEEPFIXED PLAN);
4742					END;
4743				END TRY
4744				BEGIN CATCH;
4745					DECLARE @msdb_error_message NVARCHAR(256);
4746					SET @msdb_error_message = ERROR_MESSAGE();
4747				
4748					UPDATE s
4749					SET
4750						additional_info.modify
4751						(''
4752							insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
4753							as last
4754							into (/additional_info/agent_job_info)[1]
4755						'')
4756					FROM #sessions AS s
4757					WHERE 
4758						s.session_id = @session_id
4759						AND s.recursion = 1
4760					OPTION (KEEPFIXED PLAN);
4761				END CATCH;'
4762
4763			DECLARE @job_id UNIQUEIDENTIFIER;
4764			DECLARE @step_id INT;
4765
4766			DECLARE agent_cursor
4767			CURSOR LOCAL FAST_FORWARD
4768			FOR 
4769				SELECT
4770					s.session_id,
4771					agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id,
4772					agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id
4773				FROM #sessions AS s
4774				CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes (agent_node)
4775				WHERE
4776					s.recursion = 1
4777			OPTION (KEEPFIXED PLAN);
4778			
4779			OPEN agent_cursor;
4780
4781			FETCH NEXT FROM agent_cursor
4782			INTO 
4783				@session_id,
4784				@job_id,
4785				@step_id;
4786
4787			WHILE @@FETCH_STATUS = 0
4788			BEGIN;
4789				EXEC sp_executesql
4790					@sql_n,
4791					N'@job_id UNIQUEIDENTIFIER, @step_id INT, @session_id SMALLINT',
4792					@job_id, @step_id, @session_id
4793
4794				FETCH NEXT FROM agent_cursor
4795				INTO 
4796					@session_id,
4797					@job_id,
4798					@step_id;
4799			END;
4800
4801			CLOSE agent_cursor;
4802			DEALLOCATE agent_cursor;
4803		END; 
4804		
4805		IF 
4806			@delta_interval > 0 
4807			AND @recursion <> 1
4808		BEGIN;
4809			SET @recursion = 1;
4810
4811			DECLARE @delay_time CHAR(12);
4812			SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
4813			WAITFOR DELAY @delay_time;
4814
4815			GOTO REDO;
4816		END;
4817	END;
4818
4819	SET @sql = 
4820		--Outer column list
4821		CONVERT
4822		(
4823			VARCHAR(MAX),
4824			CASE
4825				WHEN 
4826					@destination_table <> '' 
4827					AND @return_schema = 0 
4828						THEN 'INSERT ' + @destination_table + ' '
4829				ELSE ''
4830			END +
4831			'SELECT ' +
4832				@output_column_list + ' ' +
4833			CASE @return_schema
4834				WHEN 1 THEN 'INTO #session_schema '
4835				ELSE ''
4836			END
4837		--End outer column list
4838		) + 
4839		--Inner column list
4840		CONVERT
4841		(
4842			VARCHAR(MAX),
4843			'FROM ' +
4844			'( ' +
4845				'SELECT ' +
4846					'session_id, ' +
4847					--[dd hh:mm:ss.mss]
4848					CASE
4849						WHEN @format_output IN (1, 2) THEN
4850							'CASE ' +
4851								'WHEN elapsed_time < 0 THEN ' +
4852									'RIGHT ' +
4853									'( ' +
4854										'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ' +
4855										'max_elapsed_length ' +
4856									') + ' +
4857										'RIGHT ' +
4858										'( ' +
4859											'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' +
4860											'9 ' +
4861										') + ' +
4862										'''.000'' ' +
4863								'ELSE ' +
4864									'RIGHT ' +
4865									'( ' +
4866										'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ' +
4867										'max_elapsed_length ' +
4868									') + ' +
4869										'RIGHT ' +
4870										'( ' +
4871											'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ' +
4872											'9 ' +
4873										') + ' +
4874										'''.'' + ' + 
4875										'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' +
4876							'END AS [dd hh:mm:ss.mss], '
4877						ELSE
4878							''
4879					END +
4880					--[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
4881					CASE 
4882						WHEN  @format_output IN (1, 2) THEN 
4883							'RIGHT ' +
4884							'( ' +
4885								'''00'' + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ' +
4886								'2 ' +
4887							') + ' +
4888								'RIGHT ' +
4889								'( ' +
4890									'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ' +
4891									'9 ' +
4892								') + ' +
4893								'''.'' + ' +
4894								'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], '
4895						ELSE
4896							'avg_elapsed_time, '
4897					END +
4898					--physical_io
4899					CASE @format_output
4900						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 '
4901						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
4902						ELSE ''
4903					END + 'physical_io, ' +
4904					--reads
4905					CASE @format_output
4906						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 '
4907						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
4908						ELSE ''
4909					END + 'reads, ' +
4910					--physical_reads
4911					CASE @format_output
4912						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 '
4913						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
4914						ELSE ''
4915					END + 'physical_reads, ' +
4916					--writes
4917					CASE @format_output
4918						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 '
4919						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
4920						ELSE ''
4921					END + 'writes, ' +
4922					--tempdb_allocations
4923					CASE @format_output
4924						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 '
4925						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
4926						ELSE ''
4927					END + 'tempdb_allocations, ' +
4928					--tempdb_current
4929					CASE @format_output
4930						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 '
4931						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
4932						ELSE ''
4933					END + 'tempdb_current, ' +
4934					--CPU
4935					CASE @format_output
4936						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 '
4937						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
4938						ELSE ''
4939					END + 'CPU, ' +
4940					--context_switches
4941					CASE @format_output
4942						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 '
4943						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
4944						ELSE ''
4945					END + 'context_switches, ' +
4946					--used_memory
4947					CASE @format_output
4948						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 '
4949						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
4950						ELSE ''
4951					END + 'used_memory, ' +
4952					CASE
4953						WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
4954							--physical_io_delta			
4955							'CASE ' +
4956								'WHEN ' +
4957									'first_request_start_time = last_request_start_time ' + 
4958									'AND num_events = 2 ' +
4959									'AND physical_io_delta >= 0 ' +
4960										'THEN ' +
4961										CASE @format_output
4962											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)) ' 
4963											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
4964											ELSE 'physical_io_delta '
4965										END +
4966								'ELSE NULL ' +
4967							'END AS physical_io_delta, ' +
4968							--reads_delta
4969							'CASE ' +
4970								'WHEN ' +
4971									'first_request_start_time = last_request_start_time ' + 
4972									'AND num_events = 2 ' +
4973									'AND reads_delta >= 0 ' +
4974										'THEN ' +
4975										CASE @format_output
4976											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)) '
4977											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
4978											ELSE 'reads_delta '
4979										END +
4980								'ELSE NULL ' +
4981							'END AS reads_delta, ' +
4982							--physical_reads_delta
4983							'CASE ' +
4984								'WHEN ' +
4985									'first_request_start_time = last_request_start_time ' + 
4986									'AND num_events = 2 ' +
4987									'AND physical_reads_delta >= 0 ' +
4988										'THEN ' +
4989										CASE @format_output
4990											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)) '
4991											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
4992											ELSE 'physical_reads_delta '
4993										END + 
4994								'ELSE NULL ' +
4995							'END AS physical_reads_delta, ' +
4996							--writes_delta
4997							'CASE ' +
4998								'WHEN ' +
4999									'first_request_start_time = last_request_start_time ' + 
5000									'AND num_events = 2 ' +
5001									'AND writes_delta >= 0 ' +
5002										'THEN ' +
5003										CASE @format_output
5004											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)) '
5005											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
5006											ELSE 'writes_delta '
5007										END + 
5008								'ELSE NULL ' +
5009							'END AS writes_delta, ' +
5010							--tempdb_allocations_delta
5011							'CASE ' +
5012								'WHEN ' +
5013									'first_request_start_time = last_request_start_time ' + 
5014									'AND num_events = 2 ' +
5015									'AND tempdb_allocations_delta >= 0 ' +
5016										'THEN ' +
5017										CASE @format_output
5018											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)) '
5019											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
5020											ELSE 'tempdb_allocations_delta '
5021										END + 
5022								'ELSE NULL ' +
5023							'END AS tempdb_allocations_delta, ' +
5024							--tempdb_current_delta
5025							--this is the only one that can (legitimately) go negative 
5026							'CASE ' +
5027								'WHEN ' +
5028									'first_request_start_time = last_request_start_time ' + 
5029									'AND num_events = 2 ' +
5030										'THEN ' +
5031										CASE @format_output
5032											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)) '
5033											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
5034											ELSE 'tempdb_current_delta '
5035										END + 
5036								'ELSE NULL ' +
5037							'END AS tempdb_current_delta, ' +
5038							--CPU_delta
5039							'CASE ' +
5040								'WHEN ' +
5041									'first_request_start_time = last_request_start_time ' + 
5042									'AND num_events = 2 ' +
5043										'THEN ' +
5044											'CASE ' +
5045												'WHEN ' +
5046													'thread_CPU_delta > CPU_delta ' +
5047													'AND thread_CPU_delta > 0 ' +
5048														'THEN ' +
5049															CASE @format_output
5050																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)) '
5051																WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
5052																ELSE 'thread_CPU_delta '
5053															END + 
5054												'WHEN CPU_delta >= 0 THEN ' +
5055													CASE @format_output
5056														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)) '
5057														WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
5058														ELSE 'CPU_delta '
5059													END + 
5060												'ELSE NULL ' +
5061											'END ' +
5062								'ELSE ' +
5063									'NULL ' +
5064							'END AS CPU_delta, ' +
5065							--context_switches_delta
5066							'CASE ' +
5067								'WHEN ' +
5068									'first_request_start_time = last_request_start_time ' + 
5069									'AND num_events = 2 ' +
5070									'AND context_switches_delta >= 0 ' +
5071										'THEN ' +
5072										CASE @format_output
5073											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)) '
5074											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
5075											ELSE 'context_switches_delta '
5076										END + 
5077								'ELSE NULL ' +
5078							'END AS context_switches_delta, ' +
5079							--used_memory_delta
5080							'CASE ' +
5081								'WHEN ' +
5082									'first_request_start_time = last_request_start_time ' + 
5083									'AND num_events = 2 ' +
5084									'AND used_memory_delta >= 0 ' +
5085										'THEN ' +
5086										CASE @format_output
5087											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)) '
5088											WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
5089											ELSE 'used_memory_delta '
5090										END + 
5091								'ELSE NULL ' +
5092							'END AS used_memory_delta, '
5093						ELSE ''
5094					END +
5095					--tasks
5096					CASE @format_output
5097						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 '
5098						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) '
5099						ELSE ''
5100					END + 'tasks, ' +
5101					'status, ' +
5102					'wait_info, ' +
5103					'locks, ' +
5104					'tran_start_time, ' +
5105					'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' +
5106					--open_tran_count
5107					CASE @format_output
5108						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 '
5109						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
5110						ELSE ''
5111					END + 'open_tran_count, ' +
5112					--sql_command
5113					CASE @format_output 
5114						WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
5115						ELSE ''
5116					END + 'sql_command, ' +
5117					--sql_text
5118					CASE @format_output 
5119						WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
5120						ELSE ''
5121					END + 'sql_text, ' +
5122					'query_plan, ' +
5123					'blocking_session_id, ' +
5124					--blocked_session_count
5125					CASE @format_output
5126						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 '
5127						WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
5128						ELSE ''
5129					END + 'blocked_session_count, ' +
5130					--percent_complete
5131					CASE @format_output
5132						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 '
5133						WHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS '
5134						ELSE ''
5135					END + 'percent_complete, ' +
5136					'host_name, ' +
5137					'login_name, ' +
5138					'database_name, ' +
5139					'program_name, ' +
5140					'additional_info, ' +
5141					'start_time, ' +
5142					'login_time, ' +
5143					'CASE ' +
5144						'WHEN status = N''sleeping'' THEN NULL ' +
5145						'ELSE request_id ' +
5146					'END AS request_id, ' +
5147					'GETDATE() AS collection_time '
5148		--End inner column list
5149		) +
5150		--Derived table and INSERT specification
5151		CONVERT
5152		(
5153			VARCHAR(MAX),
5154				'FROM ' +
5155				'( ' +
5156					'SELECT TOP(2147483647) ' +
5157						'*, ' +
5158						'CASE ' +
5159							'MAX ' +
5160							'( ' +
5161								'LEN ' +
5162								'( ' +
5163									'CONVERT ' +
5164									'( ' +
5165										'VARCHAR, ' +
5166										'CASE ' +
5167											'WHEN elapsed_time < 0 THEN ' +
5168												'(-1 * elapsed_time) / 86400 ' +
5169											'ELSE ' +
5170												'elapsed_time / 86400000 ' +
5171										'END ' +
5172									') ' +
5173								') ' +
5174							') OVER () ' +
5175								'WHEN 1 THEN 2 ' +
5176								'ELSE ' +
5177									'MAX ' +
5178									'( ' +
5179										'LEN ' +
5180										'( ' +
5181											'CONVERT ' +
5182											'( ' +
5183												'VARCHAR, ' +
5184												'CASE ' +
5185													'WHEN elapsed_time < 0 THEN ' +
5186														'(-1 * elapsed_time) / 86400 ' +
5187													'ELSE ' +
5188														'elapsed_time / 86400000 ' +
5189												'END ' +
5190											') ' +
5191										') ' +
5192									') OVER () ' +
5193						'END AS max_elapsed_length, ' +
5194						CASE
5195							WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
5196								'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5197									'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' +
5198								'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5199									'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' +
5200								'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5201									'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' +
5202								'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5203									'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' +
5204								'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5205									'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' +
5206								'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5207									'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' +
5208								'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5209									'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' +
5210								'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5211									'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' +
5212								'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5213									'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' +
5214								'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' +
5215									'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' +
5216								'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, '
5217							ELSE ''
5218						END +
5219						'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' +
5220					'FROM #sessions AS s1 ' +
5221					CASE 
5222						WHEN @sort_order = '' THEN ''
5223						ELSE
5224							'ORDER BY ' +
5225								@sort_order
5226					END +
5227				') AS s ' +
5228				'WHERE ' +
5229					's.recursion = 1 ' +
5230			') x ' +
5231			'OPTION (KEEPFIXED PLAN); ' +
5232			'' +
5233			CASE @return_schema
5234				WHEN 1 THEN
5235					'SET @schema = ' +
5236						'''CREATE TABLE <table_name> ( '' + ' +
5237							'STUFF ' +
5238							'( ' +
5239								'( ' +
5240									'SELECT ' +
5241										''','' + ' +
5242										'QUOTENAME(COLUMN_NAME) + '' '' + ' +
5243										'DATA_TYPE + ' + 
5244										'CASE ' +
5245											'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' +
5246											'ELSE '' '' ' +
5247										'END + ' +
5248										'CASE IS_NULLABLE ' +
5249											'WHEN ''NO'' THEN ''NOT '' ' +
5250											'ELSE '''' ' +
5251										'END + ''NULL'' AS [text()] ' +
5252									'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' +
5253									'WHERE ' +
5254										'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' +
5255										'ORDER BY ' +
5256											'ORDINAL_POSITION ' +
5257									'FOR XML ' +
5258										'PATH('''') ' +
5259								'), + ' +
5260								'1, ' +
5261								'1, ' +
5262								''''' ' +
5263							') + ' +
5264						''')''; ' 
5265				ELSE ''
5266			END
5267		--End derived table and INSERT specification
5268		);
5269
5270	SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
5271
5272	EXEC sp_executesql
5273		@sql_n,
5274		N'@schema VARCHAR(MAX) OUTPUT',
5275		@schema OUTPUT;
5276END;
5277GO
5278