常用SQL Server进行性能优化语句[通俗易懂]

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName  
from   sys.dm_tran_locks where resource_type="OBJECT"

代码100分

代码100分SELECT  DTL.[request_session_id] AS [session_id] ,
        DB_NAME(DTL.[resource_database_id]) AS [Database] ,
        DTL.resource_type ,
        CASE WHEN DTL.resource_type IN ( "DATABASE", "FILE", "METADATA" )
             THEN DTL.resource_type
             WHEN DTL.resource_type = "OBJECT"
             THEN OBJECT_NAME(DTL.resource_associated_entity_id,
                              DTL.[resource_database_id])
             WHEN DTL.resource_type IN ( "KEY", "PAGE", "RID" )
             THEN ( SELECT  OBJECT_NAME([object_id])
                    FROM    sys.partitions
                    WHERE   sys.partitions.hobt_id = DTL.resource_associated_entity_id
                  )
             ELSE "Unidentified"
        END AS [Parent Object] ,
        DTL.request_mode AS [Lock Type] ,
        DTL.request_status AS [Request Status] ,
        DER.[blocking_session_id] ,
        DES.[login_name] ,
        CASE DTL.request_lifetime
          WHEN 0 THEN DEST_R.TEXT
          ELSE DEST_C.TEXT
        END AS [Statement]
FROM    sys.dm_tran_locks DTL
        LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id]
        INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]
        OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C
        OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R
WHERE   DTL.[resource_database_id] = DB_ID()
        AND DTL.[resource_type] NOT IN ( "DATABASE", "METADATA" )
ORDER BY DTL.[request_session_id];

SELECT  [resource_type] ,
        DB_NAME([resource_database_id]) AS [Database Name] ,
        CASE WHEN DTL.resource_type IN ( "DATABASE", "FILE", "METADATA" )
             THEN DTL.resource_type
             WHEN DTL.resource_type = "OBJECT"
             THEN OBJECT_NAME(DTL.resource_associated_entity_id,
                              DTL.[resource_database_id])
             WHEN DTL.resource_type IN ( "KEY", "PAGE", "RID" )
             THEN ( SELECT  OBJECT_NAME([object_id])
                    FROM    sys.partitions
                    WHERE   sys.partitions.hobt_id = DTL.resource_associated_entity_id
                  )
             ELSE "Unidentified"
        END AS requested_object_name ,
        [request_mode] ,
        [resource_description]
FROM    sys.dm_tran_locks DTL
WHERE   DTL.[resource_type] <> "DATABASE";

代码100分SELECT  DTL.[resource_type] AS [resource type] ,
        CASE WHEN DTL.[resource_type] IN ( "DATABASE", "FILE", "METADATA" )
             THEN DTL.[resource_type]
             WHEN DTL.[resource_type] = "OBJECT"
             THEN OBJECT_NAME(DTL.resource_associated_entity_id)
             WHEN DTL.[resource_type] IN ( "KEY", "PAGE", "RID" )
             THEN ( SELECT  OBJECT_NAME([object_id])
                    FROM    sys.partitions
                    WHERE   sys.partitions.[hobt_id] = DTL.[resource_associated_entity_id]
                  )
             ELSE "Unidentified"
        END AS [Parent Object] ,
        DTL.[request_mode] AS [Lock Type] ,
        DTL.[request_status] AS [Request Status] ,
        DOWT.[wait_duration_ms] AS [wait duration ms] ,
        DOWT.[wait_type] AS [wait type] ,
        DOWT.[session_id] AS [blocked session id] ,
        DES_blocked.[login_name] AS [blocked_user] ,
        SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
                  ( CASE WHEN der.statement_end_offset = -1
                         THEN DATALENGTH(dest_blocked.text)
                         ELSE der.statement_end_offset
                    END - der.statement_start_offset ) / 2) AS [blocked_command] ,
        DOWT.[blocking_session_id] AS [blocking session id] ,
        DES_blocking.[login_name] AS [blocking user] ,
        DEST_blocking.[text] AS [blocking command] ,
        DOWT.resource_description AS [blocking resource detail]
FROM    sys.dm_tran_locks DTL
        INNER JOIN sys.dm_os_waiting_tasks DOWT ON DTL.lock_owner_address = DOWT.resource_address
        INNER JOIN sys.[dm_exec_requests] DER ON DOWT.[session_id] = DER.[session_id]
        INNER JOIN sys.dm_exec_sessions DES_blocked ON DOWT.[session_id] = DES_Blocked.[session_id]
        INNER JOIN sys.dm_exec_sessions DES_blocking ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking
        CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE   DTL.[resource_database_id] = DB_ID()

SELECT  "[" + DB_NAME(ddios.[database_id]) + "].[" + su.[name] + "].["
        + o.[name] + "]" AS [statement] ,
        i.[name] AS "index_name" ,
        ddios.[partition_number] ,
        ddios.[row_lock_count] ,
        ddios.[row_lock_wait_count] ,
        CAST (100.0 * ddios.[row_lock_wait_count] / ( ddios.[row_lock_count] ) AS DECIMAL(5,
                                                              2)) AS [%_times_blocked] ,
        ddios.[row_lock_wait_in_ms] ,
        CAST (1.0 * ddios.[row_lock_wait_in_ms] / ddios.[row_lock_wait_count] AS DECIMAL(15,
                                                              2)) AS [avg_row_lock_wait_in_ms]
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddios.[index_id]
        INNER JOIN sys.objects o ON ddios.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE   ddios.row_lock_wait_count > 0
        AND OBJECTPROPERTY(ddios.[object_id], "IsUserTable") = 1
        AND i.[index_id] > 0
ORDER BY ddios.[row_lock_wait_count] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

SELECT  "[" + DB_NAME() + "].[" + OBJECT_SCHEMA_NAME(ddios.[object_id])
        + "].[" + OBJECT_NAME(ddios.[object_id]) + "]" AS [object_name] ,
        i.[name] AS index_name ,
        ddios.page_io_latch_wait_count ,
        ddios.page_io_latch_wait_in_ms ,
        ( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count ) AS avg_page_io_latch_wait_in_ms
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]
                                    AND i.index_id = ddios.index_id
WHERE   ddios.page_io_latch_wait_count > 0
        AND OBJECTPROPERTY(i.object_id, "IsUserTable") = 1
ORDER BY ddios.page_io_latch_wait_count DESC ,
        avg_page_io_latch_wait_in_ms DESC

SELECT  OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.index_lock_promotion_attempt_count ,
        ddios.index_lock_promotion_count ,
        ( ddios.index_lock_promotion_attempt_count
          / ddios.index_lock_promotion_count ) AS percent_success
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
WHERE   ddios.index_lock_promotion_count > 0
ORDER BY index_lock_promotion_count DESC;

SELECT  OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.page_lock_wait_count ,
        ddios.page_lock_wait_in_ms ,
        CASE WHEN DDMID.database_id IS NULL THEN "N"
             ELSE "Y"
        END AS missing_index_identified
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    database_id ,
                                    object_id
                          FROM      sys.dm_db_missing_index_details
                        ) AS DDMID ON DDMID.database_id = ddios.database_id
                                      AND DDMID.object_id = ddios.object_id
WHERE   ddios.page_lock_wait_in_ms > 0
ORDER BY ddios.page_lock_wait_count DESC;

SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE   i.index_id NOT IN ( SELECT  ddius.index_id
                            FROM    sys.dm_db_index_usage_stats AS ddius
                            WHERE   ddius.[object_id] = i.[object_id]
                                    AND i.index_id = ddius.index_id
                                    AND database_id = DB_ID() )
        AND o.[type] = "U"
ORDER BY OBJECT_NAME(i.[object_id]) ASC;

SELECT  "[" + DB_NAME() + "].[" + su.[name] + "].[" + o.[name] + "]" AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        SUM(SP.rows) AS [total_rows]
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
                                        AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE   ddius.[database_id] = DB_ID() -- current database only
        AND OBJECTPROPERTY(ddius.[object_id], "IsUserTable") = 1
        AND ddius.[index_id] > 0
GROUP BY su.[name] ,
        o.[name] ,
        i.[name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
        ddius.[user_updates]
HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], "IsUserTable") = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC;

SELECT  "[" + DB_NAME() + "].[" + su.[name] + "].[" + o.[name] + "]" AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count]
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
                                        AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
        INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL,
                                                       NULL) AS ddios ON ddius.[index_id] = ddios.[index_id]
                                                              AND ddius.[object_id] = ddios.[object_id]
                                                              AND SP.[partition_number] = ddios.[partition_number]
                                                              AND ddius.[database_id] = ddios.[database_id]
WHERE   OBJECTPROPERTY(ddius.[object_id], "IsUserTable") = 1
        AND ddius.[index_id] > 0
        AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,
        dbmigs.last_user_seek ,
        dbmid.[statement] AS [Database.Schema.Table] ,
        dbmid.equality_columns ,
        dbmid.inequality_columns ,
        dbmid.included_columns ,
        dbmigs.unique_compiles ,
        dbmigs.user_seeks ,
        dbmigs.avg_total_user_cost ,
        dbmigs.avg_user_impact
FROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
        INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE   dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC;

SELECT  "[" + DB_NAME() + "].[" + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                     DB_ID()) + "].["
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + "]" AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc] ,
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, "limited") ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]

SELECT migs.group_handle, mid.* 
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON (migs.group_handle = mig.index_group_handle) 
INNER JOIN sys.dm_db_missing_index_details AS mid 
ON (mig.index_handle = mid.index_handle) 
WHERE migs.group_handle = 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB "USE [?]; INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,""IsMsShipped"") = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL" 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
INTO #TempUnusedIndexes 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB "USE [?]; INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ""IsMsShipped"") = 0 
AND s.user_seeks = 0 
AND s.user_scans = 0 
AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC" 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes

Select configuration_id ConfigurationId,
name Name,
description Description,
Cast(value as int) value,
Cast(minimum as int) Minimum,
Cast(maximum as int) Maximum,
Cast(value_in_use as int) ValueInUse,
is_dynamic IsDynamic,
is_advanced IsAdvanced
From sys.configurations
Order By is_advanced, name

select count(*) from sys.dm_os_workers

Select s.login_name LoginName,
s.host_name HostName,
s.transaction_isolation_level TransactionIsolationLevel,
Max(c.connect_time) LastConnectTime,
Count(*) ConnectionCount,
Sum(Cast(c.num_reads as BigInt)) TotalReads,
Sum(Cast(c.num_writes as BigInt)) TotalWrites
From sys.dm_exec_connections c
Join sys.dm_exec_sessions s
On c.most_recent_session_id = s.session_id
Group By s.login_name, s.host_name, s.transaction_isolation_level

Select DateAdd(s, (timestamp - (osi.cpu_ticks / Convert(Float, (osi.cpu_ticks / osi.ms_ticks)))) / 1000, GETDATE()) AS EventTime,
Record.value("(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]", "int") as SystemIdle,
Record.value("(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]", "int") as ProcessUtilization,
Record.value("(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]", "int") as MemoryUtilization
From (Select timestamp,
convert(xml, record) As Record
From sys.dm_os_ring_buffers
Where ring_buffer_type = N"RING_BUFFER_SCHEDULER_MONITOR"
And record Like "%<SystemHealth>%") x
Cross Join sys.dm_os_sys_info osi
Order By timestamp

SELECT  COUNT(*) * 8 / 1024 AS "Cached Size (MB)" ,
        CASE database_id
          WHEN 32767 THEN "ResourceDb"
          ELSE DB_NAME(database_id)
        END AS "Database"
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
        database_id
ORDER BY "Cached Size (MB)" DESC

SET STATISTICS IO ON
select top 10* from Table
SET STATISTICS IO OFF

CHECKPOINT;
GO
DBCC  FREEPROCCACHE      ---清空执行计划缓存
DBCC DROPCLEANBUFFERS;   --清空数据缓存
GO

DBCC INPUTBUFFER(51)

DBCC USEROPTIONS;

--In_Row_Data: 分别为存储行内数据的
    --LOB_Data: 存储Lob对象,Lob对象用于存储存在数据库的二进制文件
              --当这个类型的列出现时,原有的列会存储一个24字节的指针，而将具体的二进制数据存在LOB页中
    --Row_Overflow_data:存储溢出数据的,使用Varchar,nvarchar等数据类型时，当行的大小不超过8060字节时，全部存在行内In-row data
                    --当varchar中存储的数据过多使得整行超过8060字节时，会将额外的部分存于Row-overflow data页中，
                    --如果update这列使得行大小减少到小于8060字节，则这行又会全部回到in-row data页
                    --text,ntext和image类型来说，每一列只要不为null,即使占用很小的数据，也需要额外分配一个LOB页
DBCC IND ( Lawyer, [dbo.tb_Contract], -1)

SELECT TOP 10 TEXT AS "SQL Statement"
    ,last_execution_time AS "Last Execution Time"
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

with aa as (
SELECT  
--执行次数 
QS.execution_count, 
--查询语句 
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, 
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 
) AS statement_text, 
--执行文本 
ST.text, 
--执行计划 
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
QS.total_worker_time, 
QS.last_worker_time, 
QS.max_worker_time, 
QS.min_worker_time 
FROM 
sys.dm_exec_query_stats QS 
--关键字 
CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST 
WHERE 
QS.last_execution_time > "2016-02-14 00:00:00" and  execution_count > 500

-- AND ST.text LIKE "%%" 
--ORDER BY 
--QS.execution_count DESC

)
select text,max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time 
from aa
where [text] not  like "%sp_MSupd_%" and  [text] not like "%sp_MSins_%" and  [text] not like "%sp_MSdel_%" 
group by text
order by 2  desc

SELECT TOP ( 25 )
        P.name AS [SP Name] ,
        Deps.total_logical_reads AS [TotalLogicalReads] ,
        deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
        deps.execution_count ,
        ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
                                               GETDATE()), 0) AS [Calls/Second] ,
        deps.total_elapsed_time ,
        deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
        deps.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
WHERE   deps.Database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC;

SET STATISTICS PROFILE ON 
SELECT * FROM Demo
SET STATISTICS PROFILE OFF

SET STATISTICS Time ON 
SELECT * FROM Demo
SET STATISTICS TIME OFF

SET STATISTICS IO ON 
SELECT * FROM Demo
SET STATISTICS IO OFF

USE master
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = "sp_WhoIsActive")
    EXEC ("CREATE PROC dbo.sp_WhoIsActive AS SELECT ""stub version, to be replaced""")
GO

/*********************************************************************************************
Who Is Active? v11.17 (2016-10-18)
(C) 2007-2016, Adam Machanic

Feedback: mailto:amachanic@gmail.com
Updates: http://whoisactive.com

License: 
    Who is Active? is free to download and use for personal, educational, and internal 
    corporate purposes, provided that this header is preserved. Redistribution or sale 
    of Who is Active?, in whole or in part, is prohibited without the author"s express 
    written consent.
*********************************************************************************************/
ALTER PROC dbo.sp_WhoIsActive
(
--~
    --Filters--Both inclusive and exclusive
    --Set either filter to "" to disable
    --Valid filter types are: session, program, database, login, and host
    --Session is a session ID, and either 0 or "" can be used to indicate "all" sessions
    --All other filter types support % or _ as wildcards
    @filter sysname = "",
    @filter_type VARCHAR(10) = "session",
    @not_filter sysname = "",
    @not_filter_type VARCHAR(10) = "session",

    --Retrieve data about the calling session?
    @show_own_spid BIT = 0,

    --Retrieve data about system sessions?
    @show_system_spids BIT = 0,

    --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
    --0 does not pull any sleeping SPIDs
    --1 pulls only those sleeping SPIDs that also have an open transaction
    --2 pulls all sleeping SPIDs
    @show_sleeping_spids TINYINT = 1,

    --If 1, gets the full stored procedure or running batch, when available
    --If 0, gets only the actual statement that is currently running in the batch or procedure
    @get_full_inner_text BIT = 0,

    --Get associated query plans for running tasks, if available
    --If @get_plans = 1, gets the plan based on the request"s statement offset
    --If @get_plans = 2, gets the entire plan based on the request"s plan_handle
    @get_plans TINYINT = 0,

    --Get the associated outer ad hoc query or stored procedure call, if available
    @get_outer_command BIT = 0,

    --Enables pulling transaction log write info and transaction duration
    @get_transaction_info BIT = 0,

    --Get information on active tasks, based on three interest levels
    --Level 0 does not pull any task-related information
    --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
    --Level 2 pulls all available task-based metrics, including: 
    --number of active tasks, current wait stats, physical I/O, context switches, and blocker information
    @get_task_info TINYINT = 1,

    --Gets associated locks for each request, aggregated in an XML format
    @get_locks BIT = 0,

    --Get average time for past runs of an active query
    --(based on the combination of plan handle, sql handle, and offset)
    @get_avg_time BIT = 0,

    --Get additional non-performance-related information about the session or request
    --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, 
    --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, 
    --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
    --
    --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
    --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
    --
    --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
    --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id, 
    --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
    @get_additional_info BIT = 0,

    --Walk the blocking chain and count the number of 
    --total SPIDs blocked all the way down by a given session
    --Also enables task_info Level 1, if @get_task_info is set to 0
    @find_block_leaders BIT = 0,

    --Pull deltas on various metrics
    --Interval in seconds to wait before doing the second data pull
    @delta_interval TINYINT = 0,

    --List of desired output columns, in desired order
    --Note that the final output will be the intersection of all enabled features and all 
    --columns in the list. Therefore, only columns associated with enabled features will 
    --actually appear in the output. Likewise, removing columns from this list may effectively
    --disable features, even if they are turned on
    --
    --Each element in this list must be one of the valid output column names. Names must be
    --delimited by square brackets. White space, formatting, and additional characters are
    --allowed, as long as the list contains exact matches of delimited valid column names.
    @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][%]",

    --Column(s) by which to sort output, optionally with sort directions. 
        --Valid column choices:
        --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
        --tempdb_current, CPU, context_switches, used_memory, physical_io_delta, 
        --reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta, 
        --tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta, 
        --tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
        --percent_complete, host_name, login_name, database_name, start_time, login_time
        --
        --Note that column names in the list must be bracket-delimited. Commas and/or white
        --space are not required. 
    @sort_order VARCHAR(500) = "[start_time] ASC",

    --Formats some of the output columns in a more "human readable" form
    --0 disables outfput format
    --1 formats the output for variable-width fonts
    --2 formats the output for fixed-width fonts
    @format_output TINYINT = 1,

    --If set to a non-blank value, the script will attempt to insert into the specified 
    --destination table. Please note that the script will not verify that the table exists, 
    --or that it has the correct schema, before doing the insert.
    --Table can be specified in one, two, or three-part format
    @destination_table VARCHAR(4000) = "",

    --If set to 1, no data collection will happen and no result set will be returned; instead,
    --a CREATE TABLE statement will be returned via the @schema parameter, which will match 
    --the schema of the result set that would be returned by using the same collection of the
    --rest of the parameters. The CREATE TABLE statement will have a placeholder token of 
    --<table_name> in place of an actual table name.
    @return_schema BIT = 0,
    @schema VARCHAR(MAX) = NULL OUTPUT,

    --Help! What do I do?
    @help BIT = 0
--~
)
/*
OUTPUT COLUMNS
--------------
Formatted/Non:    [session_id] [smallint] NOT NULL
    Session ID (a.k.a. SPID)

Formatted:        [dd hh:mm:ss.mss] [varchar](15) NULL
Non-Formatted:    <not returned>
    For an active request, time the query has been running
    For a sleeping session, time since the last batch completed

Formatted:        [dd hh:mm:ss.mss (avg)] [varchar](15) NULL
Non-Formatted:    [avg_elapsed_time] [int] NULL
    (Requires @get_avg_time option)
    How much time has the active portion of the query taken in the past, on average?

Formatted:        [physical_io] [varchar](30) NULL
Non-Formatted:    [physical_io] [bigint] NULL
    Shows the number of physical I/Os, for active requests

Formatted:        [reads] [varchar](30) NULL
Non-Formatted:    [reads] [bigint] NULL
    For an active request, number of reads done for the current query
    For a sleeping session, total number of reads done over the lifetime of the session

Formatted:        [physical_reads] [varchar](30) NULL
Non-Formatted:    [physical_reads] [bigint] NULL
    For an active request, number of physical reads done for the current query
    For a sleeping session, total number of physical reads done over the lifetime of the session

Formatted:        [writes] [varchar](30) NULL
Non-Formatted:    [writes] [bigint] NULL
    For an active request, number of writes done for the current query
    For a sleeping session, total number of writes done over the lifetime of the session

Formatted:        [tempdb_allocations] [varchar](30) NULL
Non-Formatted:    [tempdb_allocations] [bigint] NULL
    For an active request, number of TempDB writes done for the current query
    For a sleeping session, total number of TempDB writes done over the lifetime of the session

Formatted:        [tempdb_current] [varchar](30) NULL
Non-Formatted:    [tempdb_current] [bigint] NULL
    For an active request, number of TempDB pages currently allocated for the query
    For a sleeping session, number of TempDB pages currently allocated for the session

Formatted:        [CPU] [varchar](30) NULL
Non-Formatted:    [CPU] [int] NULL
    For an active request, total CPU time consumed by the current query
    For a sleeping session, total CPU time consumed over the lifetime of the session

Formatted:        [context_switches] [varchar](30) NULL
Non-Formatted:    [context_switches] [bigint] NULL
    Shows the number of context switches, for active requests

Formatted:        [used_memory] [varchar](30) NOT NULL
Non-Formatted:    [used_memory] [bigint] NOT NULL
    For an active request, total memory consumption for the current query
    For a sleeping session, total current memory consumption

Formatted:        [physical_io_delta] [varchar](30) NULL
Non-Formatted:    [physical_io_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of physical I/Os reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [reads_delta] [varchar](30) NULL
Non-Formatted:    [reads_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of reads reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [physical_reads_delta] [varchar](30) NULL
Non-Formatted:    [physical_reads_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of physical reads reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [writes_delta] [varchar](30) NULL
Non-Formatted:    [writes_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of writes reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [tempdb_allocations_delta] [varchar](30) NULL
Non-Formatted:    [tempdb_allocations_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of TempDB writes reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [tempdb_current_delta] [varchar](30) NULL
Non-Formatted:    [tempdb_current_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the number of allocated TempDB pages reported on the first and second 
    collections. If the request started after the first collection, the value will be NULL

Formatted:        [CPU_delta] [varchar](30) NULL
Non-Formatted:    [CPU_delta] [int] NULL
    (Requires @delta_interval option)
    Difference between the CPU time reported on the first and second collections. 
    If the request started after the first collection, the value will be NULL

Formatted:        [context_switches_delta] [varchar](30) NULL
Non-Formatted:    [context_switches_delta] [bigint] NULL
    (Requires @delta_interval option)
    Difference between the context switches count reported on the first and second collections
    If the request started after the first collection, the value will be NULL

Formatted:        [used_memory_delta] [varchar](30) NULL
Non-Formatted:    [used_memory_delta] [bigint] NULL
    Difference between the memory usage reported on the first and second collections
    If the request started after the first collection, the value will be NULL

Formatted:        [tasks] [varchar](30) NULL
Non-Formatted:    [tasks] [smallint] NULL
    Number of worker tasks currently allocated, for active requests

Formatted/Non:    [status] [varchar](30) NOT NULL
    Activity status for the session (running, sleeping, etc)

Formatted/Non:    [wait_info] [nvarchar](4000) NULL
    Aggregates wait information, in the following format:
        (Ax: Bms/Cms/Dms)E
    A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
    times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
    If two tasks are waiting, each of their wait times will be shown (B/C). If three or more 
    tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
    If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM), 
    the page type will be identified.
    If wait type E is CXPACKET, the nodeId from the query plan will be identified

Formatted/Non:    [locks] [xml] NULL
    (Requires @get_locks option)
    Aggregates lock information, in XML format.
    The lock XML includes the lock mode, locked object, and aggregates the number of requests. 
    Attempts are made to identify locked objects by name

Formatted/Non:    [tran_start_time] [datetime] NULL
    (Requires @get_transaction_info option)
    Date and time that the first transaction opened by a session caused a transaction log 
    write to occur.

Formatted/Non:    [tran_log_writes] [nvarchar](4000) NULL
    (Requires @get_transaction_info option)
    Aggregates transaction log write information, in the following format:
    A:wB (C kB)
    A is a database that has been touched by an active transaction
    B is the number of log writes that have been made in the database as a result of the transaction
    C is the number of log kilobytes consumed by the log records

Formatted:        [open_tran_count] [varchar](30) NULL
Non-Formatted:    [open_tran_count] [smallint] NULL
    Shows the number of open transactions the session has open

Formatted:        [sql_command] [xml] NULL
Non-Formatted:    [sql_command] [nvarchar](max) NULL
    (Requires @get_outer_command option)
    Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server, 
    if available

Formatted:        [sql_text] [xml] NULL
Non-Formatted:    [sql_text] [nvarchar](max) NULL
    Shows the SQL text for active requests or the last statement executed
    for sleeping sessions, if available in either case.
    If @get_full_inner_text option is set, shows the full text of the batch.
    Otherwise, shows only the active statement within the batch.
    If the query text is locked, a special timeout message will be sent, in the following format:
        <timeout_exceeded />
    If an error occurs, an error message will be sent, in the following format:
        <error message="message" />

Formatted/Non:    [query_plan] [xml] NULL
    (Requires @get_plans option)
    Shows the query plan for the request, if available.
    If the plan is locked, a special timeout message will be sent, in the following format:
        <timeout_exceeded />
    If an error occurs, an error message will be sent, in the following format:
        <error message="message" />

Formatted/Non:    [blocking_session_id] [smallint] NULL
    When applicable, shows the blocking SPID

Formatted:        [blocked_session_count] [varchar](30) NULL
Non-Formatted:    [blocked_session_count] [smallint] NULL
    (Requires @find_block_leaders option)
    The total number of SPIDs blocked by this session,
    all the way down the blocking chain.

Formatted:        [percent_complete] [varchar](30) NULL
Non-Formatted:    [percent_complete] [real] NULL
    When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)

Formatted/Non:    [host_name] [sysname] NOT NULL
    Shows the host name for the connection

Formatted/Non:    [login_name] [sysname] NOT NULL
    Shows the login name for the connection

Formatted/Non:    [database_name] [sysname] NULL
    Shows the connected database

Formatted/Non:    [program_name] [sysname] NULL
    Shows the reported program/application name

Formatted/Non:    [additional_info] [xml] NULL
    (Requires @get_additional_info option)
    Returns additional non-performance-related session/request information
    If the script finds a SQL Agent job running, the name of the job and job step will be reported
    If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported

Formatted/Non:    [start_time] [datetime] NOT NULL
    For active requests, shows the time the request started
    For sleeping sessions, shows the time the last batch completed

Formatted/Non:    [login_time] [datetime] NOT NULL
    Shows the time that the session connected

Formatted/Non:    [request_id] [int] NULL
    For active requests, shows the request_id
    Should be 0 unless MARS is being used

Formatted/Non:    [collection_time] [datetime] NOT NULL
    Time that this script"s final SELECT ran
*/
AS
BEGIN;
    SET NOCOUNT ON; 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_PADDING ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET ANSI_WARNINGS ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET ARITHABORT ON;

    IF
        @filter IS NULL
        OR @filter_type IS NULL
        OR @not_filter IS NULL
        OR @not_filter_type IS NULL
        OR @show_own_spid IS NULL
        OR @show_system_spids IS NULL
        OR @show_sleeping_spids IS NULL
        OR @get_full_inner_text IS NULL
        OR @get_plans IS NULL
        OR @get_outer_command IS NULL
        OR @get_transaction_info IS NULL
        OR @get_task_info IS NULL
        OR @get_locks IS NULL
        OR @get_avg_time IS NULL
        OR @get_additional_info IS NULL
        OR @find_block_leaders IS NULL
        OR @delta_interval IS NULL
        OR @format_output IS NULL
        OR @output_column_list IS NULL
        OR @sort_order IS NULL
        OR @return_schema IS NULL
        OR @destination_table IS NULL
        OR @help IS NULL
    BEGIN;
        RAISERROR("Input parameters cannot be NULL", 16, 1);
        RETURN;
    END;
    
    IF @filter_type NOT IN ("session", "program", "database", "login", "host")
    BEGIN;
        RAISERROR("Valid filter types are: session, program, database, login, host", 16, 1);
        RETURN;
    END;
    
    IF @filter_type = "session" AND @filter LIKE "%[^0123456789]%"
    BEGIN;
        RAISERROR("Session filters must be valid integers", 16, 1);
        RETURN;
    END;
    
    IF @not_filter_type NOT IN ("session", "program", "database", "login", "host")
    BEGIN;
        RAISERROR("Valid filter types are: session, program, database, login, host", 16, 1);
        RETURN;
    END;
    
    IF @not_filter_type = "session" AND @not_filter LIKE "%[^0123456789]%"
    BEGIN;
        RAISERROR("Session filters must be valid integers", 16, 1);
        RETURN;
    END;
    
    IF @show_sleeping_spids NOT IN (0, 1, 2)
    BEGIN;
        RAISERROR("Valid values for @show_sleeping_spids are: 0, 1, or 2", 16, 1);
        RETURN;
    END;
    
    IF @get_plans NOT IN (0, 1, 2)
    BEGIN;
        RAISERROR("Valid values for @get_plans are: 0, 1, or 2", 16, 1);
        RETURN;
    END;

    IF @get_task_info NOT IN (0, 1, 2)
    BEGIN;
        RAISERROR("Valid values for @get_task_info are: 0, 1, or 2", 16, 1);
        RETURN;
    END;

    IF @format_output NOT IN (0, 1, 2)
    BEGIN;
        RAISERROR("Valid values for @format_output are: 0, 1, or 2", 16, 1);
        RETURN;
    END;
    
    IF @help = 1
    BEGIN;
        DECLARE 
            @header VARCHAR(MAX),
            @params VARCHAR(MAX),
            @outputs VARCHAR(MAX);

        SELECT 
            @header =
                REPLACE
                (
                    REPLACE
                    (
                        CONVERT
                        (
                            VARCHAR(MAX),
                            SUBSTRING
                            (
                                t.text, 
                                CHARINDEX("/" + REPLICATE("*", 93), t.text) + 94,
                                CHARINDEX(REPLICATE("*", 93) + "/", t.text) - (CHARINDEX("/" + REPLICATE("*", 93), t.text) + 94)
                            )
                        ),
                        CHAR(13)+CHAR(10),
                        CHAR(13)
                    ),
                    "    ",
                    ""
                ),
            @params =
                CHAR(13) +
                    REPLACE
                    (
                        REPLACE
                        (
                            CONVERT
                            (
                                VARCHAR(MAX),
                                SUBSTRING
                                (
                                    t.text, 
                                    CHARINDEX("--~", t.text) + 5, 
                                    CHARINDEX("--~", t.text, CHARINDEX("--~", t.text) + 5) - (CHARINDEX("--~", t.text) + 5)
                                )
                            ),
                            CHAR(13)+CHAR(10),
                            CHAR(13)
                        ),
                        "    ",
                        ""
                    ),
                @outputs = 
                    CHAR(13) +
                        REPLACE
                        (
                            REPLACE
                            (
                                REPLACE
                                (
                                    CONVERT
                                    (
                                        VARCHAR(MAX),
                                        SUBSTRING
                                        (
                                            t.text, 
                                            CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32,
                                            CHARINDEX("*/", t.text, CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32) - (CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32)
                                        )
                                    ),
                                    CHAR(9),
                                    CHAR(255)
                                ),
                                CHAR(13)+CHAR(10),
                                CHAR(13)
                            ),
                            "    ",
                            ""
                        ) +
                        CHAR(13)
        FROM sys.dm_exec_requests AS r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
        WHERE
            r.session_id = @@SPID;

        WITH
        a0 AS
        (SELECT 1 AS n UNION ALL SELECT 1),
        a1 AS
        (SELECT 1 AS n FROM a0 AS a, a0 AS b),
        a2 AS
        (SELECT 1 AS n FROM a1 AS a, a1 AS b),
        a3 AS
        (SELECT 1 AS n FROM a2 AS a, a2 AS b),
        a4 AS
        (SELECT 1 AS n FROM a3 AS a, a3 AS b),
        numbers AS
        (
            SELECT TOP(LEN(@header) - 1)
                ROW_NUMBER() OVER
                (
                    ORDER BY (SELECT NULL)
                ) AS number
            FROM a4
            ORDER BY
                number
        )
        SELECT
            RTRIM(LTRIM(
                SUBSTRING
                (
                    @header,
                    number + 1,
                    CHARINDEX(CHAR(13), @header, number + 1) - number - 1
                )
            )) AS [------header---------------------------------------------------------------------------------------------------------------]
        FROM numbers
        WHERE
            SUBSTRING(@header, number, 1) = CHAR(13);

        WITH
        a0 AS
        (SELECT 1 AS n UNION ALL SELECT 1),
        a1 AS
        (SELECT 1 AS n FROM a0 AS a, a0 AS b),
        a2 AS
        (SELECT 1 AS n FROM a1 AS a, a1 AS b),
        a3 AS
        (SELECT 1 AS n FROM a2 AS a, a2 AS b),
        a4 AS
        (SELECT 1 AS n FROM a3 AS a, a3 AS b),
        numbers AS
        (
            SELECT TOP(LEN(@params) - 1)
                ROW_NUMBER() OVER
                (
                    ORDER BY (SELECT NULL)
                ) AS number
            FROM a4
            ORDER BY
                number
        ),
        tokens AS
        (
            SELECT 
                RTRIM(LTRIM(
                    SUBSTRING
                    (
                        @params,
                        number + 1,
                        CHARINDEX(CHAR(13), @params, number + 1) - number - 1
                    )
                )) AS token,
                number,
                CASE
                    WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
                    ELSE COALESCE(NULLIF(CHARINDEX("," + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) 
                END AS param_group,
                ROW_NUMBER() OVER
                (
                    PARTITION BY
                        CHARINDEX("," + CHAR(13) + CHAR(13), @params, number),
                        SUBSTRING(@params, number+1, 1)
                    ORDER BY 
                        number
                ) AS group_order
            FROM numbers
            WHERE
                SUBSTRING(@params, number, 1) = CHAR(13)
        ),
        parsed_tokens AS
        (
            SELECT
                MIN
                (
                    CASE
                        WHEN token LIKE "@%" THEN token
                        ELSE NULL
                    END
                ) AS parameter,
                MIN
                (
                    CASE
                        WHEN token LIKE "--%" THEN RIGHT(token, LEN(token) - 2)
                        ELSE NULL
                    END
                ) AS description,
                param_group,
                group_order
            FROM tokens
            WHERE
                NOT 
                (
                    token = "" 
                    AND group_order > 1
                )
            GROUP BY
                param_group,
                group_order
        )
        SELECT
            CASE
                WHEN description IS NULL AND parameter IS NULL THEN "-------------------------------------------------------------------------"
                WHEN param_group = MAX(param_group) OVER() THEN parameter
                ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), "")
            END AS [------parameter----------------------------------------------------------],
            CASE
                WHEN description IS NULL AND parameter IS NULL THEN "----------------------------------------------------------------------------------------------------------------------"
                ELSE COALESCE(description, "")
            END AS [------description-----------------------------------------------------------------------------------------------------]
        FROM parsed_tokens
        ORDER BY
            param_group, 
            group_order;
        
        WITH
        a0 AS
        (SELECT 1 AS n UNION ALL SELECT 1),
        a1 AS
        (SELECT 1 AS n FROM a0 AS a, a0 AS b),
        a2 AS
        (SELECT 1 AS n FROM a1 AS a, a1 AS b),
        a3 AS
        (SELECT 1 AS n FROM a2 AS a, a2 AS b),
        a4 AS
        (SELECT 1 AS n FROM a3 AS a, a3 AS b),
        numbers AS
        (
            SELECT TOP(LEN(@outputs) - 1)
                ROW_NUMBER() OVER
                (
                    ORDER BY (SELECT NULL)
                ) AS number
            FROM a4
            ORDER BY
                number
        ),
        tokens AS
        (
            SELECT 
                RTRIM(LTRIM(
                    SUBSTRING
                    (
                        @outputs,
                        number + 1,
                        CASE
                            WHEN 
                                COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) < 
                                COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))
                                THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) - number - 1
                            ELSE
                                COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1
                        END
                    )
                )) AS token,
                number,
                COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) AS output_group,
                ROW_NUMBER() OVER
                (
                    PARTITION BY 
                        COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs))
                    ORDER BY
                        number
                ) AS output_group_order
            FROM numbers
            WHERE
                SUBSTRING(@outputs, number, 10) = CHAR(13) + "Formatted"
                OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
        ),
        output_tokens AS
        (
            SELECT 
                *,
                CASE output_group_order
                    WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)
                    ELSE ""
                END COLLATE Latin1_General_Bin2 AS column_info
            FROM tokens
        )
        SELECT
            CASE output_group_order
                WHEN 1 THEN "-----------------------------------"
                WHEN 2 THEN 
                    CASE
                        WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN
                            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))
                        ELSE
                            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)
                    END
                ELSE ""
            END AS formatted_column_name,
            CASE output_group_order
                WHEN 1 THEN "-----------------------------------"
                WHEN 2 THEN 
                    CASE
                        WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN
                            SUBSTRING(column_info, CHARINDEX("]", column_info)+2, LEN(column_info))
                        ELSE
                            SUBSTRING(column_info, CHARINDEX("]", column_info)+2, CHARINDEX("Non-Formatted:", column_info, CHARINDEX("]", column_info)+2) - CHARINDEX("]", column_info)-3)
                    END
                ELSE ""
            END AS formatted_column_type,
            CASE output_group_order
                WHEN 1 THEN "---------------------------------------"
                WHEN 2 THEN 
                    CASE
                        WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN ""
                        ELSE
                            CASE
                                WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, 1) = "<" THEN
                                    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)))
                                ELSE
                                    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)))
                            END
                    END
                ELSE ""
            END AS unformatted_column_name,
            CASE output_group_order
                WHEN 1 THEN "---------------------------------------"
                WHEN 2 THEN 
                    CASE
                        WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN ""
                        ELSE
                            CASE
                                WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, 1) = "<" THEN ""
                                ELSE
                                    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)
                            END
                    END
                ELSE ""
            END AS unformatted_column_type,
            CASE output_group_order
                WHEN 1 THEN "----------------------------------------------------------------------------------------------------------------------"
                ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, "")
            END AS [------description-----------------------------------------------------------------------------------------------------]
        FROM output_tokens
        WHERE
            NOT 
            (
                output_group_order = 1 
                AND output_group = LEN(@outputs)
            )
        ORDER BY
            output_group,
            CASE output_group_order
                WHEN 1 THEN 99
                ELSE output_group_order
            END;

        RETURN;
    END;

    WITH
    a0 AS
    (SELECT 1 AS n UNION ALL SELECT 1),
    a1 AS
    (SELECT 1 AS n FROM a0 AS a, a0 AS b),
    a2 AS
    (SELECT 1 AS n FROM a1 AS a, a1 AS b),
    a3 AS
    (SELECT 1 AS n FROM a2 AS a, a2 AS b),
    a4 AS
    (SELECT 1 AS n FROM a3 AS a, a3 AS b),
    numbers AS
    (
        SELECT TOP(LEN(@output_column_list))
            ROW_NUMBER() OVER
            (
                ORDER BY (SELECT NULL)
            ) AS number
        FROM a4
        ORDER BY
            number
    ),
    tokens AS
    (
        SELECT 
            "|[" +
                SUBSTRING
                (
                    @output_column_list,
                    number + 1,
                    CHARINDEX("]", @output_column_list, number) - number - 1
                ) + "|]" AS token,
            number
        FROM numbers
        WHERE
            SUBSTRING(@output_column_list, number, 1) = "["
    ),
    ordered_columns AS
    (
        SELECT
            x.column_name,
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    x.column_name
                ORDER BY
                    tokens.number,
                    x.default_order
            ) AS r,
            ROW_NUMBER() OVER
            (
                ORDER BY
                    tokens.number,
                    x.default_order
            ) AS s
        FROM tokens
        JOIN
        (
            SELECT "[session_id]" AS column_name, 1 AS default_order
            UNION ALL
            SELECT "[dd hh:mm:ss.mss]", 2
            WHERE
                @format_output IN (1, 2)
            UNION ALL
            SELECT "[dd hh:mm:ss.mss (avg)]", 3
            WHERE
                @format_output IN (1, 2)
                AND @get_avg_time = 1
            UNION ALL
            SELECT "[avg_elapsed_time]", 4
            WHERE
                @format_output = 0
                AND @get_avg_time = 1
            UNION ALL
            SELECT "[physical_io]", 5
            WHERE
                @get_task_info = 2
            UNION ALL
            SELECT "[reads]", 6
            UNION ALL
            SELECT "[physical_reads]", 7
            UNION ALL
            SELECT "[writes]", 8
            UNION ALL
            SELECT "[tempdb_allocations]", 9
            UNION ALL
            SELECT "[tempdb_current]", 10
            UNION ALL
            SELECT "[CPU]", 11
            UNION ALL
            SELECT "[context_switches]", 12
            WHERE
                @get_task_info = 2
            UNION ALL
            SELECT "[used_memory]", 13
            UNION ALL
            SELECT "[physical_io_delta]", 14
            WHERE
                @delta_interval > 0    
                AND @get_task_info = 2
            UNION ALL
            SELECT "[reads_delta]", 15
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[physical_reads_delta]", 16
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[writes_delta]", 17
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[tempdb_allocations_delta]", 18
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[tempdb_current_delta]", 19
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[CPU_delta]", 20
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[context_switches_delta]", 21
            WHERE
                @delta_interval > 0
                AND @get_task_info = 2
            UNION ALL
            SELECT "[used_memory_delta]", 22
            WHERE
                @delta_interval > 0
            UNION ALL
            SELECT "[tasks]", 23
            WHERE
                @get_task_info = 2
            UNION ALL
            SELECT "[status]", 24
            UNION ALL
            SELECT "[wait_info]", 25
            WHERE
                @get_task_info > 0
                OR @find_block_leaders = 1
            UNION ALL
            SELECT "[locks]", 26
            WHERE
                @get_locks = 1
            UNION ALL
            SELECT "[tran_start_time]", 27
            WHERE
                @get_transaction_info = 1
            UNION ALL
            SELECT "[tran_log_writes]", 28
            WHERE
                @get_transaction_info = 1
            UNION ALL
            SELECT "[open_tran_count]", 29
            UNION ALL
            SELECT "[sql_command]", 30
            WHERE
                @get_outer_command = 1
            UNION ALL
            SELECT "[sql_text]", 31
            UNION ALL
            SELECT "[query_plan]", 32
            WHERE
                @get_plans >= 1
            UNION ALL
            SELECT "[blocking_session_id]", 33
            WHERE
                @get_task_info > 0
                OR @find_block_leaders = 1
            UNION ALL
            SELECT "[blocked_session_count]", 34
            WHERE
                @find_block_leaders = 1
            UNION ALL
            SELECT "[percent_complete]", 35
            UNION ALL
            SELECT "[host_name]", 36
            UNION ALL
            SELECT "[login_name]", 37
            UNION ALL
            SELECT "[database_name]", 38
            UNION ALL
            SELECT "[program_name]", 39
            UNION ALL
            SELECT "[additional_info]", 40
            WHERE
                @get_additional_info = 1
            UNION ALL
            SELECT "[start_time]", 41
            UNION ALL
            SELECT "[login_time]", 42
            UNION ALL
            SELECT "[request_id]", 43
            UNION ALL
            SELECT "[collection_time]", 44
        ) AS x ON 
            x.column_name LIKE token ESCAPE "|"
    )
    SELECT
        @output_column_list =
            STUFF
            (
                (
                    SELECT
                        "," + column_name as [text()]
                    FROM ordered_columns
                    WHERE
                        r = 1
                    ORDER BY
                        s
                    FOR XML
                        PATH("")
                ),
                1,
                1,
                ""
            );
    
    IF COALESCE(RTRIM(@output_column_list), "") = ""
    BEGIN;
        RAISERROR("No valid column matches found in @output_column_list or no columns remain due to selected options.", 16, 1);
        RETURN;
    END;
    
    IF @destination_table <> ""
    BEGIN;
        SET @destination_table = 
            --database
            COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + ".", "") +
            --schema
            COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + ".", "") +
            --table
            COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), "");
            
        IF COALESCE(RTRIM(@destination_table), "") = ""
        BEGIN;
            RAISERROR("Destination table not properly formatted.", 16, 1);
            RETURN;
        END;
    END;

    WITH
    a0 AS
    (SELECT 1 AS n UNION ALL SELECT 1),
    a1 AS
    (SELECT 1 AS n FROM a0 AS a, a0 AS b),
    a2 AS
    (SELECT 1 AS n FROM a1 AS a, a1 AS b),
    a3 AS
    (SELECT 1 AS n FROM a2 AS a, a2 AS b),
    a4 AS
    (SELECT 1 AS n FROM a3 AS a, a3 AS b),
    numbers AS
    (
        SELECT TOP(LEN(@sort_order))
            ROW_NUMBER() OVER
            (
                ORDER BY (SELECT NULL)
            ) AS number
        FROM a4
        ORDER BY
            number
    ),
    tokens AS
    (
        SELECT 
            "|[" +
                SUBSTRING
                (
                    @sort_order,
                    number + 1,
                    CHARINDEX("]", @sort_order, number) - number - 1
                ) + "|]" AS token,
            SUBSTRING
            (
                @sort_order,
                CHARINDEX("]", @sort_order, number) + 1,
                COALESCE(NULLIF(CHARINDEX("[", @sort_order, CHARINDEX("]", @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX("]", @sort_order, number)
            ) AS next_chunk,
            number
        FROM numbers
        WHERE
            SUBSTRING(@sort_order, number, 1) = "["
    ),
    ordered_columns AS
    (
        SELECT
            x.column_name +
                CASE
                    WHEN tokens.next_chunk LIKE "%asc%" THEN " ASC"
                    WHEN tokens.next_chunk LIKE "%desc%" THEN " DESC"
                    ELSE ""
                END AS column_name,
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    x.column_name
                ORDER BY
                    tokens.number
            ) AS r,
            tokens.number
        FROM tokens
        JOIN
        (
            SELECT "[session_id]" AS column_name
            UNION ALL
            SELECT "[physical_io]"
            UNION ALL
            SELECT "[reads]"
            UNION ALL
            SELECT "[physical_reads]"
            UNION ALL
            SELECT "[writes]"
            UNION ALL
            SELECT "[tempdb_allocations]"
            UNION ALL
            SELECT "[tempdb_current]"
            UNION ALL
            SELECT "[CPU]"
            UNION ALL
            SELECT "[context_switches]"
            UNION ALL
            SELECT "[used_memory]"
            UNION ALL
            SELECT "[physical_io_delta]"
            UNION ALL
            SELECT "[reads_delta]"
            UNION ALL
            SELECT "[physical_reads_delta]"
            UNION ALL
            SELECT "[writes_delta]"
            UNION ALL
            SELECT "[tempdb_allocations_delta]"
            UNION ALL
            SELECT "[tempdb_current_delta]"
            UNION ALL
            SELECT "[CPU_delta]"
            UNION ALL
            SELECT "[context_switches_delta]"
            UNION ALL
            SELECT "[used_memory_delta]"
            UNION ALL
            SELECT "[tasks]"
            UNION ALL
            SELECT "[tran_start_time]"
            UNION ALL
            SELECT "[open_tran_count]"
            UNION ALL
            SELECT "[blocking_session_id]"
            UNION ALL
            SELECT "[blocked_session_count]"
            UNION ALL
            SELECT "[percent_complete]"
            UNION ALL
            SELECT "[host_name]"
            UNION ALL
            SELECT "[login_name]"
            UNION ALL
            SELECT "[database_name]"
            UNION ALL
            SELECT "[start_time]"
            UNION ALL
            SELECT "[login_time]"
        ) AS x ON 
            x.column_name LIKE token ESCAPE "|"
    )
    SELECT
        @sort_order = COALESCE(z.sort_order, "")
    FROM
    (
        SELECT
            STUFF
            (
                (
                    SELECT
                        "," + column_name as [text()]
                    FROM ordered_columns
                    WHERE
                        r = 1
                    ORDER BY
                        number
                    FOR XML
                        PATH("")
                ),
                1,
                1,
                ""
            ) AS sort_order
    ) AS z;

    CREATE TABLE #sessions
    (
        recursion SMALLINT NOT NULL,
        session_id SMALLINT NOT NULL,
        request_id INT NOT NULL,
        session_number INT NOT NULL,
        elapsed_time INT NOT NULL,
        avg_elapsed_time INT NULL,
        physical_io BIGINT NULL,
        reads BIGINT NULL,
        physical_reads BIGINT NULL,
        writes BIGINT NULL,
        tempdb_allocations BIGINT NULL,
        tempdb_current BIGINT NULL,
        CPU INT NULL,
        thread_CPU_snapshot BIGINT NULL,
        context_switches BIGINT NULL,
        used_memory BIGINT NOT NULL, 
        tasks SMALLINT NULL,
        status VARCHAR(30) NOT NULL,
        wait_info NVARCHAR(4000) NULL,
        locks XML NULL,
        transaction_id BIGINT NULL,
        tran_start_time DATETIME NULL,
        tran_log_writes NVARCHAR(4000) NULL,
        open_tran_count SMALLINT NULL,
        sql_command XML NULL,
        sql_handle VARBINARY(64) NULL,
        statement_start_offset INT NULL,
        statement_end_offset INT NULL,
        sql_text XML NULL,
        plan_handle VARBINARY(64) NULL,
        query_plan XML NULL,
        blocking_session_id SMALLINT NULL,
        blocked_session_count SMALLINT NULL,
        percent_complete REAL NULL,
        host_name sysname NULL,
        login_name sysname NOT NULL,
        database_name sysname NULL,
        program_name sysname NULL,
        additional_info XML NULL,
        start_time DATETIME NOT NULL,
        login_time DATETIME NULL,
        last_request_start_time DATETIME NULL,
        PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),
        UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
    );

    IF @return_schema = 0
    BEGIN;
        --Disable unnecessary autostats on the table
        CREATE STATISTICS s_session_id ON #sessions (session_id)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_request_id ON #sessions (request_id)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_session_number ON #sessions (session_number)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_status ON #sessions (status)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_start_time ON #sessions (start_time)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;
        CREATE STATISTICS s_recursion ON #sessions (recursion)
        WITH SAMPLE 0 ROWS, NORECOMPUTE;

        DECLARE @recursion SMALLINT;
        SET @recursion = 
            CASE @delta_interval
                WHEN 0 THEN 1
                ELSE -1
            END;

        DECLARE @first_collection_ms_ticks BIGINT;
        DECLARE @last_collection_start DATETIME;

        --Used for the delta pull
        REDO:;
        
        IF 
            @get_locks = 1 
            AND @recursion = 1
            AND @output_column_list LIKE "%|[locks|]%" ESCAPE "|"
        BEGIN;
            SELECT
                y.resource_type,
                y.database_name,
                y.object_id,
                y.file_id,
                y.page_type,
                y.hobt_id,
                y.allocation_unit_id,
                y.index_id,
                y.schema_id,
                y.principal_id,
                y.request_mode,
                y.request_status,
                y.session_id,
                y.resource_description,
                y.request_count,
                s.request_id,
                s.start_time,
                CONVERT(sysname, NULL) AS object_name,
                CONVERT(sysname, NULL) AS index_name,
                CONVERT(sysname, NULL) AS schema_name,
                CONVERT(sysname, NULL) AS principal_name,
                CONVERT(NVARCHAR(2048), NULL) AS query_error
            INTO #locks
            FROM
            (
                SELECT
                    sp.spid AS session_id,
                    CASE sp.status
                        WHEN "sleeping" THEN CONVERT(INT, 0)
                        ELSE sp.request_id
                    END AS request_id,
                    CASE sp.status
                        WHEN "sleeping" THEN sp.last_batch
                        ELSE COALESCE(req.start_time, sp.last_batch)
                    END AS start_time,
                    sp.dbid
                FROM sys.sysprocesses AS sp
                OUTER APPLY
                (
                    SELECT TOP(1)
                        CASE
                            WHEN 
                            (
                                sp.hostprocess > ""
                                OR r.total_elapsed_time < 0
                            ) THEN
                                r.start_time
                            ELSE
                                DATEADD
                                (
                                    ms, 
                                    1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())), 
                                    DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
                                )
                        END AS start_time
                    FROM sys.dm_exec_requests AS r
                    WHERE
                        r.session_id = sp.spid
                        AND r.request_id = sp.request_id
                ) AS req
                WHERE
                    --Process inclusive filter
                    1 =
                        CASE
                            WHEN @filter <> "" THEN
                                CASE @filter_type
                                    WHEN "session" THEN
                                        CASE
                                            WHEN
                                                CONVERT(SMALLINT, @filter) = 0
                                                OR sp.spid = CONVERT(SMALLINT, @filter)
                                                    THEN 1
                                            ELSE 0
                                        END
                                    WHEN "program" THEN
                                        CASE
                                            WHEN sp.program_name LIKE @filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "login" THEN
                                        CASE
                                            WHEN sp.loginame LIKE @filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "host" THEN
                                        CASE
                                            WHEN sp.hostname LIKE @filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "database" THEN
                                        CASE
                                            WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
                                            ELSE 0
                                        END
                                    ELSE 0
                                END
                            ELSE 1
                        END
                    --Process exclusive filter
                    AND 0 =
                        CASE
                            WHEN @not_filter <> "" THEN
                                CASE @not_filter_type
                                    WHEN "session" THEN
                                        CASE
                                            WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
                                            ELSE 0
                                        END
                                    WHEN "program" THEN
                                        CASE
                                            WHEN sp.program_name LIKE @not_filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "login" THEN
                                        CASE
                                            WHEN sp.loginame LIKE @not_filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "host" THEN
                                        CASE
                                            WHEN sp.hostname LIKE @not_filter THEN 1
                                            ELSE 0
                                        END
                                    WHEN "database" THEN
                                        CASE
                                            WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
                                            ELSE 0
                                        END
                                    ELSE 0
                                END
                            ELSE 0
                        END
                    AND 
                    (
                        @show_own_spid = 1
                        OR sp.spid <> @@SPID
                    )
                    AND 
                    (
                        @show_system_spids = 1
                        OR sp.hostprocess > ""
                    )
                    AND sp.ecid = 0
            ) AS s
            INNER HASH JOIN
            (
                SELECT
                    x.resource_type,
                    x.database_name,
                    x.object_id,
                    x.file_id,
                    CASE
                        WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN "PFS"
                        WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN "GAM"
                        WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN "SGAM"
                        WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN "DCM"
                        WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN "BCM"
                        WHEN x.page_no IS NOT NULL THEN "*"
                        ELSE NULL
                    END AS page_type,
                    x.hobt_id,
                    x.allocation_unit_id,
                    x.index_id,
                    x.schema_id,
                    x.principal_id,
                    x.request_mode,
                    x.request_status,
                    x.session_id,
                    x.request_id,
                    CASE
                        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, "")
                        ELSE NULL
                    END AS resource_description,
                    COUNT(*) AS request_count
                FROM
                (
                    SELECT
                        tl.resource_type +
                            CASE
                                WHEN tl.resource_subtype = "" THEN ""
                                ELSE "." + tl.resource_subtype
                            END AS resource_type,
                        COALESCE(DB_NAME(tl.resource_database_id), N"(null)") AS database_name,
                        CONVERT
                        (
                            INT,
                            CASE
                                WHEN tl.resource_type = "OBJECT" THEN tl.resource_associated_entity_id
                                WHEN tl.resource_description LIKE "%object_id = %" THEN
                                    (
                                        SUBSTRING
                                        (
                                            tl.resource_description, 
                                            (CHARINDEX("object_id = ", tl.resource_description) + 12), 
                                            COALESCE
                                            (
                                                NULLIF
                                                (
                                                    CHARINDEX(",", tl.resource_description, CHARINDEX("object_id = ", tl.resource_description) + 12),
                                                    0
                                                ), 
                                                DATALENGTH(tl.resource_description)+1
                                            ) - (CHARINDEX("object_id = ", tl.resource_description) + 12)
                                        )
                                    )
                                ELSE NULL
                            END
                        ) AS object_id,
                        CONVERT
                        (
                            INT,
                            CASE 
                                WHEN tl.resource_type = "FILE" THEN CONVERT(INT, tl.resource_description)
                                WHEN tl.resource_type IN ("PAGE", "EXTENT", "RID") THEN LEFT(tl.resource_description, CHARINDEX(":", tl.resource_description)-1)
                                ELSE NULL
                            END
                        ) AS file_id,
                        CONVERT
                        (
                            INT,
                            CASE
                                WHEN tl.resource_type IN ("PAGE", "EXTENT", "RID") THEN 
                                    SUBSTRING
                                    (
                                        tl.resource_description, 
                                        CHARINDEX(":", tl.resource_description) + 1, 
                                        COALESCE
                                        (
                                            NULLIF
                                            (
                                                CHARINDEX(":", tl.resource_description, CHARINDEX(":", tl.resource_description) + 1), 
                                                0
                                            ), 
                                            DATALENGTH(tl.resource_description)+1
                                        ) - (CHARINDEX(":", tl.resource_description) + 1)
                                    )
                                ELSE NULL
                            END
                        ) AS page_no,
                        CASE
                            WHEN tl.resource_type IN ("PAGE", "KEY", "RID", "HOBT") THEN tl.resource_associated_entity_id
                            ELSE NULL
                        END AS hobt_id,
                        CASE
                            WHEN tl.resource_type = "ALLOCATION_UNIT" THEN tl.resource_associated_entity_id
                            ELSE NULL
                        END AS allocation_unit_id,
                        CONVERT
                        (
                            INT,
                            CASE
                                WHEN
                                    /*TODO: Deal with server principals*/ 
                                    tl.resource_subtype <> "SERVER_PRINCIPAL" 
                                    AND tl.resource_description LIKE "%index_id or stats_id = %" THEN
                                    (
                                        SUBSTRING
                                        (
                                            tl.resource_description, 
                                            (CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23), 
                                            COALESCE
                                            (
                                                NULLIF
                                                (
                                                    CHARINDEX(",", tl.resource_description, CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23), 
                                                    0
                                                ), 
                                                DATALENGTH(tl.resource_description)+1
                                            ) - (CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23)
                                        )
                                    )
                                ELSE NULL
                            END 
                        ) AS index_id,
                        CONVERT
                        (
                            INT,
                            CASE
                                WHEN tl.resource_description LIKE "%schema_id = %" THEN
                                    (
                                        SUBSTRING
                                        (
                                            tl.resource_description, 
                                            (CHARINDEX("schema_id = ", tl.resource_description) + 12), 
                                            COALESCE
                                            (
                                                NULLIF
                                                (
                                                    CHARINDEX(",", tl.resource_description, CHARINDEX("schema_id = ", tl.resource_description) + 12), 
                                                    0
                                                ), 
                                                DATALENGTH(tl.resource_description)+1
                                            ) - (CHARINDEX("schema_id = ", tl.resource_description) + 12)
                                        )
                                    )
                                ELSE NULL
                            END 
                        ) AS schema_id,
                        CONVERT
                        (
                            INT,
                            CASE
                                WHEN tl.resource_description LIKE "%principal_id = %" THEN
                                    (
                                        SUBSTRING
                                        (
                                            tl.resource_description, 
                                            (CHARINDEX("principal_id = ", tl.resource_description) + 15), 
                                            COALESCE
                                            (
                                                NULLIF
                                                (
                                                    CHARINDEX(",", tl.resource_description, CHARINDEX("principal_id = ", tl.resource_description) + 15), 
                                                    0
                                                ), 
                                                DATALENGTH(tl.resource_description)+1
                                            ) - (CHARINDEX("principal_id = ", tl.resource_description) + 15)
                                        )
                                    )
                                ELSE NULL
                            END
                        ) AS principal_id,
                        tl.request_mode,
                        tl.request_status,
                        tl.request_session_id AS session_id,
                        tl.request_request_id AS request_id,

                        /*TODO: Applocks, other resource_descriptions*/
                        RTRIM(tl.resource_description) AS resource_description,
                        tl.resource_associated_entity_id
                        /*********************************************/
                    FROM 
                    (
                        SELECT 
                            request_session_id,
                            CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,
                            CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,
                            resource_database_id,
                            CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,
                            resource_associated_entity_id,
                            CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,
                            CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,
                            request_request_id
                        FROM sys.dm_tran_locks
                    ) AS tl
                ) AS x
                GROUP BY
                    x.resource_type,
                    x.database_name,
                    x.object_id,
                    x.file_id,
                    CASE
                        WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN "PFS"
                        WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN "GAM"
                        WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN "SGAM"
                        WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN "DCM"
                        WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN "BCM"
                        WHEN x.page_no IS NOT NULL THEN "*"
                        ELSE NULL
                    END,
                    x.hobt_id,
                    x.allocation_unit_id,
                    x.index_id,
                    x.schema_id,
                    x.principal_id,
                    x.request_mode,
                    x.request_status,
                    x.session_id,
                    x.request_id,
                    CASE
                        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, "")
                        ELSE NULL
                    END
            ) AS y ON
                y.session_id = s.session_id
                AND y.request_id = s.request_id
            OPTION (HASH GROUP);

            --Disable unnecessary autostats on the table
            CREATE STATISTICS s_database_name ON #locks (database_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_object_id ON #locks (object_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_hobt_id ON #locks (hobt_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_index_id ON #locks (index_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_schema_id ON #locks (schema_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_principal_id ON #locks (principal_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_request_id ON #locks (request_id)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_start_time ON #locks (start_time)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_resource_type ON #locks (resource_type)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_object_name ON #locks (object_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_schema_name ON #locks (schema_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_page_type ON #locks (page_type)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_request_mode ON #locks (request_mode)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_request_status ON #locks (request_status)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_resource_description ON #locks (resource_description)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_index_name ON #locks (index_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_principal_name ON #locks (principal_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
        END;
        
        DECLARE 
            @sql VARCHAR(MAX), 
            @sql_n NVARCHAR(MAX);

        SET @sql = 
            CONVERT(VARCHAR(MAX), "") +
            "DECLARE @blocker BIT;
            SET @blocker = 0;
            DECLARE @i INT;
            SET @i = 2147483647;

            DECLARE @sessions TABLE
            (
                session_id SMALLINT NOT NULL,
                request_id INT NOT NULL,
                login_time DATETIME,
                last_request_end_time DATETIME,
                status VARCHAR(30),
                statement_start_offset INT,
                statement_end_offset INT,
                sql_handle BINARY(20),
                host_name NVARCHAR(128),
                login_name NVARCHAR(128),
                program_name NVARCHAR(128),
                database_id SMALLINT,
                memory_usage INT,
                open_tran_count SMALLINT, 
                " +
                CASE
                    WHEN 
                    (
                        @get_task_info <> 0 
                        OR @find_block_leaders = 1 
                    ) THEN
                        "wait_type NVARCHAR(32),
                        wait_resource NVARCHAR(256),
                        wait_time BIGINT, 
                        "
                    ELSE 
                        ""
                END +
                "blocked SMALLINT,
                is_user_process BIT,
                cmd VARCHAR(32),
                PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
            );

            DECLARE @blockers TABLE
            (
                session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
            );

            BLOCKERS:;

            INSERT @sessions
            (
                session_id,
                request_id,
                login_time,
                last_request_end_time,
                status,
                statement_start_offset,
                statement_end_offset,
                sql_handle,
                host_name,
                login_name,
                program_name,
                database_id,
                memory_usage,
                open_tran_count, 
                " +
                CASE
                    WHEN 
                    (
                        @get_task_info <> 0
                        OR @find_block_leaders = 1 
                    ) THEN
                        "wait_type,
                        wait_resource,
                        wait_time, 
                        "
                    ELSE
                        ""
                END +
                "blocked,
                is_user_process,
                cmd 
            )
            SELECT TOP(@i)
                spy.session_id,
                spy.request_id,
                spy.login_time,
                spy.last_request_end_time,
                spy.status,
                spy.statement_start_offset,
                spy.statement_end_offset,
                spy.sql_handle,
                spy.host_name,
                spy.login_name,
                spy.program_name,
                spy.database_id,
                spy.memory_usage,
                spy.open_tran_count,
                " +
                CASE
                    WHEN 
                    (
                        @get_task_info <> 0  
                        OR @find_block_leaders = 1 
                    ) THEN
                        "spy.wait_type,
                        CASE
                            WHEN
                                spy.wait_type LIKE N""PAGE%LATCH_%""
                                OR spy.wait_type = N""CXPACKET""
                                OR spy.wait_type LIKE N""LATCH[_]%""
                                OR spy.wait_type = N""OLEDB"" THEN
                                    spy.wait_resource
                            ELSE
                                NULL
                        END AS wait_resource,
                        spy.wait_time, 
                        "
                    ELSE
                        ""
                END +
                "spy.blocked,
                spy.is_user_process,
                spy.cmd
            FROM
            (
                SELECT TOP(@i)
                    spx.*, 
                    " +
                    CASE
                        WHEN 
                        (
                            @get_task_info <> 0 
                            OR @find_block_leaders = 1 
                        ) THEN
                            "ROW_NUMBER() OVER
                            (
                                PARTITION BY
                                    spx.session_id,
                                    spx.request_id
                                ORDER BY
                                    CASE
                                        WHEN spx.wait_type LIKE N""LCK[_]%"" THEN 
                                            1
                                        ELSE
                                            99
                                    END,
                                    spx.wait_time DESC,
                                    spx.blocked DESC
                            ) AS r 
                            "
                        ELSE 
                            "1 AS r 
                            "
                    END +
                "FROM
                (
                    SELECT TOP(@i)
                        sp0.session_id,
                        sp0.request_id,
                        sp0.login_time,
                        sp0.last_request_end_time,
                        LOWER(sp0.status) AS status,
                        CASE
                            WHEN sp0.cmd = ""CREATE INDEX"" THEN
                                0
                            ELSE
                                sp0.stmt_start
                        END AS statement_start_offset,
                        CASE
                            WHEN sp0.cmd = N""CREATE INDEX"" THEN
                                -1
                            ELSE
                                COALESCE(NULLIF(sp0.stmt_end, 0), -1)
                        END AS statement_end_offset,
                        sp0.sql_handle,
                        sp0.host_name,
                        sp0.login_name,
                        sp0.program_name,
                        sp0.database_id,
                        sp0.memory_usage,
                        sp0.open_tran_count, 
                        " +
                        CASE
                            WHEN 
                            (
                                @get_task_info <> 0 
                                OR @find_block_leaders = 1 
                            ) THEN
                                "CASE
                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N""CXPACKET"" THEN
                                        sp0.wait_type
                                    ELSE
                                        NULL
                                END AS wait_type,
                                CASE
                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N""CXPACKET"" THEN 
                                        sp0.wait_resource
                                    ELSE
                                        NULL
                                END AS wait_resource,
                                CASE
                                    WHEN sp0.wait_type <> N""CXPACKET"" THEN
                                        sp0.wait_time
                                    ELSE
                                        0
                                END AS wait_time, 
                                "
                            ELSE
                                ""
                        END +
                        "sp0.blocked,
                        sp0.is_user_process,
                        sp0.cmd
                    FROM
                    (
                        SELECT TOP(@i)
                            sp1.session_id,
                            sp1.request_id,
                            sp1.login_time,
                            sp1.last_request_end_time,
                            sp1.status,
                            sp1.cmd,
                            sp1.stmt_start,
                            sp1.stmt_end,
                            MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
                            sp1.host_name,
                            MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
                            sp1.program_name,
                            sp1.database_id,
                            MAX(sp1.memory_usage)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
                            MAX(sp1.open_tran_count)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
                            sp1.wait_type,
                            sp1.wait_resource,
                            sp1.wait_time,
                            sp1.blocked,
                            sp1.hostprocess,
                            sp1.is_user_process
                        FROM
                        (
                            SELECT TOP(@i)
                                sp2.spid AS session_id,
                                CASE sp2.status
                                    WHEN ""sleeping"" THEN
                                        CONVERT(INT, 0)
                                    ELSE
                                        sp2.request_id
                                END AS request_id,
                                MAX(sp2.login_time) AS login_time,
                                MAX(sp2.last_batch) AS last_request_end_time,
                                MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
                                MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
                                MAX(sp2.stmt_start) AS stmt_start,
                                MAX(sp2.stmt_end) AS stmt_end,
                                MAX(sp2.sql_handle) AS sql_handle,
                                MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
                                MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
                                MAX
                                (
                                    CASE
                                        WHEN blk.queue_id IS NOT NULL THEN
                                            N""Service Broker
                                                database_id: "" + CONVERT(NVARCHAR, blk.database_id) +
                                                N"" queue_id: "" + CONVERT(NVARCHAR, blk.queue_id)
                                        ELSE
                                            CONVERT
                                            (
                                                sysname,
                                                RTRIM(sp2.program_name)
                                            )
                                    END COLLATE SQL_Latin1_General_CP1_CI_AS
                                ) AS program_name,
                                MAX(sp2.dbid) AS database_id,
                                MAX(sp2.memusage) AS memory_usage,
                                MAX(sp2.open_tran) AS open_tran_count,
                                RTRIM(sp2.lastwaittype) AS wait_type,
                                RTRIM(sp2.waitresource) AS wait_resource,
                                MAX(sp2.waittime) AS wait_time,
                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
                                MAX
                                (
                                    CASE
                                        WHEN blk.session_id = sp2.spid THEN
                                            ""blocker""
                                        ELSE
                                            RTRIM(sp2.hostprocess)
                                    END
                                ) AS hostprocess,
                                CONVERT
                                (
                                    BIT,
                                    MAX
                                    (
                                        CASE
                                            WHEN sp2.hostprocess > """" THEN
                                                1
                                            ELSE
                                                0
                                        END
                                    )
                                ) AS is_user_process
                            FROM
                            (
                                SELECT TOP(@i)
                                    session_id,
                                    CONVERT(INT, NULL) AS queue_id,
                                    CONVERT(INT, NULL) AS database_id
                                FROM @blockers

                                UNION ALL

                                SELECT TOP(@i)
                                    CONVERT(SMALLINT, 0),
                                    CONVERT(INT, NULL) AS queue_id,
                                    CONVERT(INT, NULL) AS database_id
                                WHERE
                                    @blocker = 0

                                UNION ALL

                                SELECT TOP(@i)
                                    CONVERT(SMALLINT, spid),
                                    queue_id,
                                    database_id
                                FROM sys.dm_broker_activated_tasks
                                WHERE
                                    @blocker = 0
                            ) AS blk
                            INNER JOIN sys.sysprocesses AS sp2 ON
                                sp2.spid = blk.session_id
                                OR
                                (
                                    blk.session_id = 0
                                    AND @blocker = 0
                                )
                            " +
                            CASE 
                                WHEN 
                                (
                                    @get_task_info = 0 
                                    AND @find_block_leaders = 0
                                ) THEN
                                    "WHERE
                                        sp2.ecid = 0 
                                    " 
                                ELSE
                                    ""
                            END +
                            "GROUP BY
                                sp2.spid,
                                CASE sp2.status
                                    WHEN ""sleeping"" THEN
                                        CONVERT(INT, 0)
                                    ELSE
                                        sp2.request_id
                                END,
                                RTRIM(sp2.lastwaittype),
                                RTRIM(sp2.waitresource),
                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
                        ) AS sp1
                    ) AS sp0
                    WHERE
                        @blocker = 1
                        OR
                        (1=1 
                        " +
                            --inclusive filter
                            CASE
                                WHEN @filter <> "" THEN
                                    CASE @filter_type
                                        WHEN "session" THEN
                                            CASE
                                                WHEN CONVERT(SMALLINT, @filter) <> 0 THEN
                                                    "AND sp0.session_id = CONVERT(SMALLINT, @filter) 
                                                    "
                                                ELSE
                                                    ""
                                            END
                                        WHEN "program" THEN
                                            "AND sp0.program_name LIKE @filter 
                                            "
                                        WHEN "login" THEN
                                            "AND sp0.login_name LIKE @filter 
                                            "
                                        WHEN "host" THEN
                                            "AND sp0.host_name LIKE @filter 
                                            "
                                        WHEN "database" THEN
                                            "AND DB_NAME(sp0.database_id) LIKE @filter 
                                            "
                                        ELSE
                                            ""
                                    END
                                ELSE
                                    ""
                            END +
                            --exclusive filter
                            CASE
                                WHEN @not_filter <> "" THEN
                                    CASE @not_filter_type
                                        WHEN "session" THEN
                                            CASE
                                                WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN
                                                    "AND sp0.session_id <> CONVERT(SMALLINT, @not_filter) 
                                                    "
                                                ELSE
                                                    ""
                                            END
                                        WHEN "program" THEN
                                            "AND sp0.program_name NOT LIKE @not_filter 
                                            "
                                        WHEN "login" THEN
                                            "AND sp0.login_name NOT LIKE @not_filter 
                                            "
                                        WHEN "host" THEN
                                            "AND sp0.host_name NOT LIKE @not_filter 
                                            "
                                        WHEN "database" THEN
                                            "AND DB_NAME(sp0.database_id) NOT LIKE @not_filter 
                                            "
                                        ELSE
                                            ""
                                    END
                                ELSE
                                    ""
                            END +
                            CASE @show_own_spid
                                WHEN 1 THEN
                                    ""
                                ELSE
                                    "AND sp0.session_id <> @@spid 
                                    "
                            END +
                            CASE 
                                WHEN @show_system_spids = 0 THEN
                                    "AND sp0.hostprocess > """" 
                                    " 
                                ELSE
                                    ""
                            END +
                            CASE @show_sleeping_spids
                                WHEN 0 THEN
                                    "AND sp0.status <> ""sleeping"" 
                                    "
                                WHEN 1 THEN
                                    "AND
                                    (
                                        sp0.status <> ""sleeping""
                                        OR sp0.open_tran_count > 0
                                    )
                                    "
                                ELSE
                                    ""
                            END +
                        ")
                ) AS spx
            ) AS spy
            WHERE
                spy.r = 1; 
            " + 
            CASE @recursion
                WHEN 1 THEN 
                    "IF @@ROWCOUNT > 0
                    BEGIN;
                        INSERT @blockers
                        (
                            session_id
                        )
                        SELECT TOP(@i)
                            blocked
                        FROM @sessions
                        WHERE
                            NULLIF(blocked, 0) IS NOT NULL

                        EXCEPT

                        SELECT TOP(@i)
                            session_id
                        FROM @sessions; 
                        " +

                        CASE
                            WHEN
                            (
                                @get_task_info > 0
                                OR @find_block_leaders = 1
                            ) THEN
                                "IF @@ROWCOUNT > 0
                                BEGIN;
                                    SET @blocker = 1;
                                    GOTO BLOCKERS;
                                END; 
                                "
                            ELSE 
                                ""
                        END +
                    "END; 
                    "
                ELSE 
                    ""
            END +
            "SELECT TOP(@i)
                @recursion AS recursion,
                x.session_id,
                x.request_id,
                DENSE_RANK() OVER
                (
                    ORDER BY
                        x.session_id
                ) AS session_number,
                " +
                CASE
                    WHEN @output_column_list LIKE "%|[dd hh:mm:ss.mss|]%" ESCAPE "|" THEN 
                        "x.elapsed_time "
                    ELSE 
                        "0 "
                END + 
                    "AS elapsed_time, 
                    " +
                CASE
                    WHEN
                        (
                            @output_column_list LIKE "%|[dd hh:mm:ss.mss (avg)|]%" ESCAPE "|" OR 
                            @output_column_list LIKE "%|[avg_elapsed_time|]%" ESCAPE "|"
                        )
                        AND @recursion = 1
                            THEN 
                                "x.avg_elapsed_time / 1000 "
                    ELSE 
                        "NULL "
                END + 
                    "AS avg_elapsed_time, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[physical_io|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[physical_io_delta|]%" ESCAPE "|"
                            THEN 
                                "x.physical_io "
                    ELSE 
                        "NULL "
                END + 
                    "AS physical_io, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[reads|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[reads_delta|]%" ESCAPE "|"
                            THEN 
                                "x.reads "
                    ELSE 
                        "0 "
                END + 
                    "AS reads, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[physical_reads|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[physical_reads_delta|]%" ESCAPE "|"
                            THEN 
                                "x.physical_reads "
                    ELSE 
                        "0 "
                END + 
                    "AS physical_reads, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[writes|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[writes_delta|]%" ESCAPE "|"
                            THEN 
                                "x.writes "
                    ELSE 
                        "0 "
                END + 
                    "AS writes, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[tempdb_allocations|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[tempdb_allocations_delta|]%" ESCAPE "|"
                            THEN 
                                "x.tempdb_allocations "
                    ELSE 
                        "0 "
                END + 
                    "AS tempdb_allocations, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[tempdb_current|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[tempdb_current_delta|]%" ESCAPE "|"
                            THEN 
                                "x.tempdb_current "
                    ELSE 
                        "0 "
                END + 
                    "AS tempdb_current, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[CPU|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
                            THEN
                                "x.CPU "
                    ELSE
                        "0 "
                END + 
                    "AS CPU, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
                        AND @get_task_info = 2
                            THEN 
                                "x.thread_CPU_snapshot "
                    ELSE 
                        "0 "
                END + 
                    "AS thread_CPU_snapshot, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[context_switches|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[context_switches_delta|]%" ESCAPE "|"
                            THEN 
                                "x.context_switches "
                    ELSE 
                        "NULL "
                END + 
                    "AS context_switches, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[used_memory|]%" ESCAPE "|"
                        OR @output_column_list LIKE "%|[used_memory_delta|]%" ESCAPE "|"
                            THEN 
                                "x.used_memory "
                    ELSE 
                        "0 "
                END + 
                    "AS used_memory, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[tasks|]%" ESCAPE "|"
                        AND @recursion = 1
                            THEN 
                                "x.tasks "
                    ELSE 
                        "NULL "
                END + 
                    "AS tasks, 
                    " +
                CASE
                    WHEN 
                        (
                            @output_column_list LIKE "%|[status|]%" ESCAPE "|" 
                            OR @output_column_list LIKE "%|[sql_command|]%" ESCAPE "|"
                        )
                        AND @recursion = 1
                            THEN 
                                "x.status "
                    ELSE 
                        """"" "
                END + 
                    "AS status, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[wait_info|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                CASE @get_task_info
                                    WHEN 2 THEN
                                        "COALESCE(x.task_wait_info, x.sys_wait_info) "
                                    ELSE
                                        "x.sys_wait_info "
                                END
                    ELSE 
                        "NULL "
                END + 
                    "AS wait_info, 
                    " +
                CASE
                    WHEN 
                        (
                            @output_column_list LIKE "%|[tran_start_time|]%" ESCAPE "|" 
                            OR @output_column_list LIKE "%|[tran_log_writes|]%" ESCAPE "|" 
                        )
                        AND @recursion = 1
                            THEN 
                                "x.transaction_id "
                    ELSE 
                        "NULL "
                END + 
                    "AS transaction_id, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[open_tran_count|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.open_tran_count "
                    ELSE 
                        "NULL "
                END + 
                    "AS open_tran_count, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[sql_text|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.sql_handle "
                    ELSE 
                        "NULL "
                END + 
                    "AS sql_handle, 
                    " +
                CASE
                    WHEN 
                        (
                            @output_column_list LIKE "%|[sql_text|]%" ESCAPE "|" 
                            OR @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|" 
                        )
                        AND @recursion = 1
                            THEN 
                                "x.statement_start_offset "
                    ELSE 
                        "NULL "
                END + 
                    "AS statement_start_offset, 
                    " +
                CASE
                    WHEN 
                        (
                            @output_column_list LIKE "%|[sql_text|]%" ESCAPE "|" 
                            OR @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|" 
                        )
                        AND @recursion = 1
                            THEN 
                                "x.statement_end_offset "
                    ELSE 
                        "NULL "
                END + 
                    "AS statement_end_offset, 
                    " +
                "NULL AS sql_text, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.plan_handle "
                    ELSE 
                        "NULL "
                END + 
                    "AS plan_handle, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[blocking_session_id|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "NULLIF(x.blocking_session_id, 0) "
                    ELSE 
                        "NULL "
                END + 
                    "AS blocking_session_id, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[percent_complete|]%" ESCAPE "|"
                        AND @recursion = 1
                            THEN 
                                "x.percent_complete "
                    ELSE 
                        "NULL "
                END + 
                    "AS percent_complete, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[host_name|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.host_name "
                    ELSE 
                        """"" "
                END + 
                    "AS host_name, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[login_name|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.login_name "
                    ELSE 
                        """"" "
                END + 
                    "AS login_name, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[database_name|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "DB_NAME(x.database_id) "
                    ELSE 
                        "NULL "
                END + 
                    "AS database_name, 
                    " +
                CASE
                    WHEN 
                        @output_column_list LIKE "%|[program_name|]%" ESCAPE "|" 
                        AND @recursion = 1
                            THEN 
                                "x.program_name "
                    ELSE 
                        """"" "
                END + 
                    "AS program_name, 
                    " +
                CASE
                    WHEN
                        @output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
                        AND @recursion = 1
                            THEN
                                "(
                                    SELECT TOP(@i)
                                        x.text_size,
                                        x.language,
                                        x.date_format,
                                        x.date_first,
                                        CASE x.quoted_identifier
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS quoted_identifier,
                                        CASE x.arithabort
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS arithabort,
                                        CASE x.ansi_null_dflt_on
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS ansi_null_dflt_on,
                                        CASE x.ansi_defaults
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS ansi_defaults,
                                        CASE x.ansi_warnings
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS ansi_warnings,
                                        CASE x.ansi_padding
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS ansi_padding,
                                        CASE ansi_nulls
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS ansi_nulls,
                                        CASE x.concat_null_yields_null
                                            WHEN 0 THEN ""OFF""
                                            WHEN 1 THEN ""ON""
                                        END AS concat_null_yields_null,
                                        CASE x.transaction_isolation_level
                                            WHEN 0 THEN ""Unspecified""
                                            WHEN 1 THEN ""ReadUncomitted""
                                            WHEN 2 THEN ""ReadCommitted""
                                            WHEN 3 THEN ""Repeatable""
                                            WHEN 4 THEN ""Serializable""
                                            WHEN 5 THEN ""Snapshot""
                                        END AS transaction_isolation_level,
                                        x.lock_timeout,
                                        x.deadlock_priority,
                                        x.row_count,
                                        x.command_type, 
                                        master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
                                        master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,
                                        " +
                                        CASE
                                            WHEN @output_column_list LIKE "%|[program_name|]%" ESCAPE "|" THEN
                                                "(
                                                    SELECT TOP(1)
                                                        CONVERT(uniqueidentifier, CONVERT(XML, """").value(""xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )"", ""binary(16)"")) AS job_id,
                                                        agent_info.step_id,
                                                        (
                                                            SELECT TOP(1)
                                                                NULL
                                                            FOR XML
                                                                PATH(""job_name""),
                                                                TYPE
                                                        ),
                                                        (
                                                            SELECT TOP(1)
                                                                NULL
                                                            FOR XML
                                                                PATH(""step_name""),
                                                                TYPE
                                                        )
                                                    FROM
                                                    (
                                                        SELECT TOP(1)
                                                            SUBSTRING(x.program_name, CHARINDEX(""0x"", x.program_name) + 2, 32) AS job_id_string,
                                                            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
                                                        WHERE
                                                            x.program_name LIKE N""SQLAgent - TSQL JobStep (Job 0x%""
                                                    ) AS agent_info
                                                    FOR XML
                                                        PATH(""agent_job_info""),
                                                        TYPE
                                                ),
                                                "
                                            ELSE ""
                                        END +
                                        CASE
                                            WHEN @get_task_info = 2 THEN
                                                "CONVERT(XML, x.block_info) AS block_info, 
                                                "
                                            ELSE
                                                ""
                                        END +
                                        "x.host_process_id 
                                    FOR XML
                                        PATH(""additional_info""),
                                        TYPE
                                ) "
                    ELSE
                        "NULL "
                END + 
                    "AS additional_info, 
                x.start_time, 
                    " +
                CASE
                    WHEN
                        @output_column_list LIKE "%|[login_time|]%" ESCAPE "|"
                        AND @recursion = 1
                            THEN
                                "x.login_time "
                    ELSE 
                        "NULL "
                END + 
                    "AS login_time, 
                x.last_request_start_time
            FROM
            (
                SELECT TOP(@i)
                    y.*,
                    CASE
                        WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
                            DATEDIFF(second, GETDATE(), y.start_time)
                        ELSE DATEDIFF(ms, y.start_time, GETDATE())
                    END AS elapsed_time,
                    COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
                    COALESCE
                    (
                        CASE
                            WHEN tempdb_info.tempdb_current < 0 THEN 0
                            ELSE tempdb_info.tempdb_current
                        END,
                        0
                    ) AS tempdb_current, 
                    " +
                    CASE
                        WHEN 
                            (
                                @get_task_info <> 0
                                OR @find_block_leaders = 1
                            ) THEN
                                "N""("" + CONVERT(NVARCHAR, y.wait_duration_ms) + N""ms)"" +
                                    y.wait_type +
                                        CASE
                                            WHEN y.wait_type LIKE N""PAGE%LATCH_%"" THEN
                                                N"":"" +
                                                COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N"":"", y.resource_description) - 1))), N""(null)"") +
                                                N"":"" +
                                                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)) +
                                                N""("" +
                                                    CASE
                                                        WHEN
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 1 OR
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) % 8088 = 0
                                                                THEN 
                                                                    N""PFS""
                                                        WHEN
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 2 OR
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) % 511232 = 0
                                                                THEN 
                                                                    N""GAM""
                                                        WHEN
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 3 OR
                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
                                                                THEN
                                                                    N""SGAM""
                                                        WHEN
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 6 OR
                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0 
                                                                THEN 
                                                                    N""DCM""
                                                        WHEN
                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 7 OR
                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0 
                                                                THEN 
                                                                    N""BCM""
                                                        ELSE 
                                                            N""*""
                                                    END +
                                                N"")""
                                            WHEN y.wait_type = N""CXPACKET"" THEN
                                                N"":"" + SUBSTRING(y.resource_description, CHARINDEX(N""nodeId"", y.resource_description) + 7, 4)
                                            WHEN y.wait_type LIKE N""LATCH[_]%"" THEN
                                                N"" ["" + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N"" "", y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N""]""
                                            WHEN
                                                y.wait_type = N""OLEDB""
                                                AND y.resource_description LIKE N""%(SPID=%)"" THEN
                                                    N""["" + LEFT(y.resource_description, CHARINDEX(N""(SPID="", y.resource_description) - 2) +
                                                        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)) + ""]""
                                            ELSE
                                                N""""
                                        END COLLATE Latin1_General_Bin2 AS sys_wait_info, 
                                        "
                            ELSE
                                ""
                        END +
                        CASE
                            WHEN @get_task_info = 2 THEN
                                "tasks.physical_io,
                                tasks.context_switches,
                                tasks.tasks,
                                tasks.block_info,
                                tasks.wait_info AS task_wait_info,
                                tasks.thread_CPU_snapshot,
                                "
                            ELSE
                                "" 
                    END +
                    CASE 
                        WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN
                            "CONVERT(INT, NULL) "
                        ELSE 
                            "qs.total_elapsed_time / qs.execution_count "
                    END + 
                        "AS avg_elapsed_time 
                FROM
                (
                    SELECT TOP(@i)
                        sp.session_id,
                        sp.request_id,
                        COALESCE(r.logical_reads, s.logical_reads) AS reads,
                        COALESCE(r.reads, s.reads) AS physical_reads,
                        COALESCE(r.writes, s.writes) AS writes,
                        COALESCE(r.CPU_time, s.CPU_time) AS CPU,
                        sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
                        LOWER(sp.status) AS status,
                        COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
                        COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
                        COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
                        " +
                        CASE
                            WHEN 
                            (
                                @get_task_info <> 0
                                OR @find_block_leaders = 1 
                            ) THEN
                                "sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
                                sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
                                sp.wait_time AS wait_duration_ms, 
                                "
                            ELSE
                                ""
                        END +
                        "NULLIF(sp.blocked, 0) AS blocking_session_id,
                        r.plan_handle,
                        NULLIF(r.percent_complete, 0) AS percent_complete,
                        sp.host_name,
                        sp.login_name,
                        sp.program_name,
                        s.host_process_id,
                        COALESCE(r.text_size, s.text_size) AS text_size,
                        COALESCE(r.language, s.language) AS language,
                        COALESCE(r.date_format, s.date_format) AS date_format,
                        COALESCE(r.date_first, s.date_first) AS date_first,
                        COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
                        COALESCE(r.arithabort, s.arithabort) AS arithabort,
                        COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
                        COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
                        COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
                        COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
                        COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
                        COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
                        COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
                        COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
                        COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
                        COALESCE(r.row_count, s.row_count) AS row_count,
                        COALESCE(r.command, sp.cmd) AS command_type,
                        COALESCE
                        (
                            CASE
                                WHEN
                                (
                                    s.is_user_process = 0
                                    AND r.total_elapsed_time >= 0
                                ) THEN
                                    DATEADD
                                    (
                                        ms,
                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
                                    )
                            END,
                            NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ""19000101"", 112)),
                            (
                                SELECT TOP(1)
                                    DATEADD(second, -(ms_ticks / 1000), GETDATE())
                                FROM sys.dm_os_sys_info
                            )
                        ) AS start_time,
                        sp.login_time,
                        CASE
                            WHEN s.is_user_process = 1 THEN
                                s.last_request_start_time
                            ELSE
                                COALESCE
                                (
                                    DATEADD
                                    (
                                        ms,
                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
                                    ),
                                    s.last_request_start_time
                                )
                        END AS last_request_start_time,
                        r.transaction_id,
                        sp.database_id,
                        sp.open_tran_count
                    FROM @sessions AS sp
                    LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
                        s.session_id = sp.session_id
                        AND s.login_time = sp.login_time
                    LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
                        sp.status <> ""sleeping""
                        AND r.session_id = sp.session_id
                        AND r.request_id = sp.request_id
                        AND
                        (
                            (
                                s.is_user_process = 0
                                AND sp.is_user_process = 0
                            )
                            OR
                            (
                                r.start_time = s.last_request_start_time
                                AND s.last_request_end_time <= sp.last_request_end_time
                            )
                        )
                ) AS y
                " + 
                CASE 
                    WHEN @get_task_info = 2 THEN
                        CONVERT(VARCHAR(MAX), "") +
                        "LEFT OUTER HASH JOIN
                        (
                            SELECT TOP(@i)
                                task_nodes.task_node.value(""(session_id/text())[1]"", ""SMALLINT"") AS session_id,
                                task_nodes.task_node.value(""(request_id/text())[1]"", ""INT"") AS request_id,
                                task_nodes.task_node.value(""(physical_io/text())[1]"", ""BIGINT"") AS physical_io,
                                task_nodes.task_node.value(""(context_switches/text())[1]"", ""BIGINT"") AS context_switches,
                                task_nodes.task_node.value(""(tasks/text())[1]"", ""INT"") AS tasks,
                                task_nodes.task_node.value(""(block_info/text())[1]"", ""NVARCHAR(4000)"") AS block_info,
                                task_nodes.task_node.value(""(waits/text())[1]"", ""NVARCHAR(4000)"") AS wait_info,
                                task_nodes.task_node.value(""(thread_CPU_snapshot/text())[1]"", ""BIGINT"") AS thread_CPU_snapshot
                            FROM
                            (
                                SELECT TOP(@i)
                                    CONVERT
                                    (
                                        XML,
                                        REPLACE
                                        (
                                            CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
                                            N""</waits></tasks><tasks><waits>"",
                                            N"", ""
                                        )
                                    ) AS task_xml
                                FROM
                                (
                                    SELECT TOP(@i)
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.session_id
                                            ELSE
                                                NULL
                                        END AS [session_id],
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.request_id
                                            ELSE
                                                NULL
                                        END AS [request_id],                                            
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.physical_io
                                            ELSE
                                                NULL
                                        END AS [physical_io],
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.context_switches
                                            ELSE
                                                NULL
                                        END AS [context_switches],
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.thread_CPU_snapshot
                                            ELSE
                                                NULL
                                        END AS [thread_CPU_snapshot],
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.tasks
                                            ELSE
                                                NULL
                                        END AS [tasks],
                                        CASE waits.r
                                            WHEN 1 THEN
                                                waits.block_info
                                            ELSE
                                                NULL
                                        END AS [block_info],
                                        REPLACE
                                        (
                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                CONVERT
                                                (
                                                    NVARCHAR(MAX),
                                                    N""("" +
                                                        CONVERT(NVARCHAR, num_waits) + N""x: "" +
                                                        CASE num_waits
                                                            WHEN 1 THEN
                                                                CONVERT(NVARCHAR, min_wait_time) + N""ms""
                                                            WHEN 2 THEN
                                                                CASE
                                                                    WHEN min_wait_time <> max_wait_time THEN
                                                                        CONVERT(NVARCHAR, min_wait_time) + N""/"" + CONVERT(NVARCHAR, max_wait_time) + N""ms""
                                                                    ELSE
                                                                        CONVERT(NVARCHAR, max_wait_time) + N""ms""
                                                                END
                                                            ELSE
                                                                CASE
                                                                    WHEN min_wait_time <> max_wait_time THEN
                                                                        CONVERT(NVARCHAR, min_wait_time) + N""/"" + CONVERT(NVARCHAR, avg_wait_time) + N""/"" + CONVERT(NVARCHAR, max_wait_time) + N""ms""
                                                                    ELSE 
                                                                        CONVERT(NVARCHAR, max_wait_time) + N""ms""
                                                                END
                                                        END +
                                                    N"")"" + wait_type COLLATE Latin1_General_Bin2
                                                ),
                                                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""?""),
                                                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""?""),
                                                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""?""),
                                            NCHAR(0),
                                            N""""
                                        ) AS [waits]
                                    FROM
                                    (
                                        SELECT TOP(@i)
                                            w1.*,
                                            ROW_NUMBER() OVER
                                            (
                                                PARTITION BY
                                                    w1.session_id,
                                                    w1.request_id
                                                ORDER BY
                                                    w1.block_info DESC,
                                                    w1.num_waits DESC,
                                                    w1.wait_type
                                            ) AS r
                                        FROM
                                        (
                                            SELECT TOP(@i)
                                                task_info.session_id,
                                                task_info.request_id,
                                                task_info.physical_io,
                                                task_info.context_switches,
                                                task_info.thread_CPU_snapshot,
                                                task_info.num_tasks AS tasks,
                                                CASE
                                                    WHEN task_info.runnable_time IS NOT NULL THEN
                                                        ""RUNNABLE""
                                                    ELSE
                                                        wt2.wait_type
                                                END AS wait_type,
                                                NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
                                                MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
                                                AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
                                                MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
                                                MAX(wt2.block_info) AS block_info
                                            FROM
                                            (
                                                SELECT TOP(@i)
                                                    t.session_id,
                                                    t.request_id,
                                                    SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
                                                    SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches, 
                                                    " +
                                                    CASE
                                                        WHEN @output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
                                                            THEN
                                                                "SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) "
                                                        ELSE
                                                            "CONVERT(BIGINT, NULL) "
                                                    END + 
                                                        " AS thread_CPU_snapshot, 
                                                    COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
                                                    t.task_address,
                                                    t.task_state,
                                                    CASE
                                                        WHEN
                                                            t.task_state = ""RUNNABLE""
                                                            AND w.runnable_time > 0 THEN
                                                                w.runnable_time
                                                        ELSE
                                                            NULL
                                                    END AS runnable_time
                                                FROM sys.dm_os_tasks AS t
                                                CROSS APPLY
                                                (
                                                    SELECT TOP(1)
                                                        sp2.session_id
                                                    FROM @sessions AS sp2
                                                    WHERE
                                                        sp2.session_id = t.session_id
                                                        AND sp2.request_id = t.request_id
                                                        AND sp2.status <> ""sleeping""
                                                ) AS sp20
                                                LEFT OUTER HASH JOIN
                                                (
                                                    SELECT TOP(@i)
                                                        (
                                                            SELECT TOP(@i)
                                                                ms_ticks
                                                            FROM sys.dm_os_sys_info
                                                        ) -
                                                            w0.wait_resumed_ms_ticks AS runnable_time,
                                                        w0.worker_address,
                                                        w0.thread_address,
                                                        w0.task_bound_ms_ticks
                                                    FROM sys.dm_os_workers AS w0
                                                    WHERE
                                                        w0.state = ""RUNNABLE""
                                                        OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks
                                                ) AS w ON
                                                    w.worker_address = t.worker_address 
                                                " +
                                                CASE
                                                    WHEN @output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
                                                        THEN
                                                            "LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
                                                                tr.thread_address = w.thread_address
                                                                AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
                                                            "
                                                    ELSE
                                                        ""
                                                END +
                                            ") AS task_info
                                            LEFT OUTER HASH JOIN
                                            (
                                                SELECT TOP(@i)
                                                    wt1.wait_type,
                                                    wt1.waiting_task_address,
                                                    MAX(wt1.wait_duration_ms) AS wait_duration_ms,
                                                    MAX(wt1.block_info) AS block_info
                                                FROM
                                                (
                                                    SELECT DISTINCT TOP(@i)
                                                        wt.wait_type +
                                                            CASE
                                                                WHEN wt.wait_type LIKE N""PAGE%LATCH_%"" THEN
                                                                    "":"" +
                                                                    COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N"":"", wt.resource_description) - 1))), N""(null)"") +
                                                                    N"":"" +
                                                                    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)) +
                                                                    N""("" +
                                                                        CASE
                                                                            WHEN
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) = 1 OR
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) % 8088 = 0
                                                                                    THEN 
                                                                                        N""PFS""
                                                                            WHEN
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) = 2 OR
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) % 511232 = 0 
                                                                                    THEN 
                                                                                        N""GAM""
                                                                            WHEN
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) = 3 OR
                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0 
                                                                                    THEN 
                                                                                        N""SGAM""
                                                                            WHEN
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) = 6 OR
                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0 
                                                                                    THEN 
                                                                                        N""DCM""
                                                                            WHEN
                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) = 7 OR
                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N"":"", REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
                                                                                    THEN 
                                                                                        N""BCM""
                                                                            ELSE
                                                                                N""*""
                                                                        END +
                                                                    N"")""
                                                                WHEN wt.wait_type = N""CXPACKET"" THEN
                                                                    N"":"" + SUBSTRING(wt.resource_description, CHARINDEX(N""nodeId"", wt.resource_description) + 7, 4)
                                                                WHEN wt.wait_type LIKE N""LATCH[_]%"" THEN
                                                                    N"" ["" + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N"" "", wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N""]""
                                                                ELSE 
                                                                    N""""
                                                            END COLLATE Latin1_General_Bin2 AS wait_type,
                                                        CASE
                                                            WHEN
                                                            (
                                                                wt.blocking_session_id IS NOT NULL
                                                                AND wt.wait_type LIKE N""LCK[_]%""
                                                            ) THEN
                                                                (
                                                                    SELECT TOP(@i)
                                                                        x.lock_type,
                                                                        REPLACE
                                                                        (
                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                                                DB_NAME
                                                                                (
                                                                                    CONVERT
                                                                                    (
                                                                                        INT,
                                                                                        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)
                                                                                    )
                                                                                ),
                                                                                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""?""),
                                                                                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""?""),
                                                                                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""?""),
                                                                            NCHAR(0),
                                                                            N""""
                                                                        ) AS database_name,
                                                                        CASE x.lock_type
                                                                            WHEN N""objectlock"" THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS object_id,
                                                                        CASE x.lock_type
                                                                            WHEN N""filelock"" THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS file_id,
                                                                        CASE
                                                                            WHEN x.lock_type in (N""pagelock"", N""extentlock"", N""ridlock"") THEN
                                                                                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)
                                                                            WHEN x.lock_type in (N""keylock"", N""hobtlock"", N""allocunitlock"") THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS hobt_id,
                                                                        CASE x.lock_type
                                                                            WHEN N""applicationlock"" THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS applock_hash,
                                                                        CASE x.lock_type
                                                                            WHEN N""metadatalock"" THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS metadata_resource,
                                                                        CASE x.lock_type
                                                                            WHEN N""metadatalock"" THEN
                                                                                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)
                                                                            ELSE
                                                                                NULL
                                                                        END AS metadata_class_id
                                                                    FROM
                                                                    (
                                                                        SELECT TOP(1)
                                                                            LEFT(wt.resource_description, CHARINDEX(N"" "", wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
                                                                    ) AS x
                                                                    FOR XML
                                                                        PATH("""")
                                                                )
                                                            ELSE NULL
                                                        END AS block_info,
                                                        wt.wait_duration_ms,
                                                        wt.waiting_task_address
                                                    FROM
                                                    (
                                                        SELECT TOP(@i)
                                                            wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
                                                            wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
                                                            wt0.wait_duration_ms,
                                                            wt0.waiting_task_address,
                                                            CASE
                                                                WHEN wt0.blocking_session_id = p.blocked THEN
                                                                    wt0.blocking_session_id
                                                                ELSE
                                                                    NULL
                                                            END AS blocking_session_id
                                                        FROM sys.dm_os_waiting_tasks AS wt0
                                                        CROSS APPLY
                                                        (
                                                            SELECT TOP(1)
                                                                s0.blocked
                                                            FROM @sessions AS s0
                                                            WHERE
                                                                s0.session_id = wt0.session_id
                                                                AND COALESCE(s0.wait_type, N"""") <> N""OLEDB""
                                                                AND wt0.wait_type <> N""OLEDB""
                                                        ) AS p
                                                    ) AS wt
                                                ) AS wt1
                                                GROUP BY
                                                    wt1.wait_type,
                                                    wt1.waiting_task_address
                                            ) AS wt2 ON
                                                wt2.waiting_task_address = task_info.task_address
                                                AND wt2.wait_duration_ms > 0
                                                AND task_info.runnable_time IS NULL
                                            GROUP BY
                                                task_info.session_id,
                                                task_info.request_id,
                                                task_info.physical_io,
                                                task_info.context_switches,
                                                task_info.thread_CPU_snapshot,
                                                task_info.num_tasks,
                                                CASE
                                                    WHEN task_info.runnable_time IS NOT NULL THEN
                                                        ""RUNNABLE""
                                                    ELSE
                                                        wt2.wait_type
                                                END
                                        ) AS w1
                                    ) AS waits
                                    ORDER BY
                                        waits.session_id,
                                        waits.request_id,
                                        waits.r
                                    FOR XML
                                        PATH(N""tasks""),
                                        TYPE
                                ) AS tasks_raw (task_xml_raw)
                            ) AS tasks_final
                            CROSS APPLY tasks_final.task_xml.nodes(N""/tasks"") AS task_nodes (task_node)
                            WHERE
                                task_nodes.task_node.exist(N""session_id"") = 1
                        ) AS tasks ON
                            tasks.session_id = y.session_id
                            AND tasks.request_id = y.request_id 
                        "
                    ELSE
                        ""
                END +
                "LEFT OUTER HASH JOIN
                (
                    SELECT TOP(@i)
                        t_info.session_id,
                        COALESCE(t_info.request_id, -1) AS request_id,
                        SUM(t_info.tempdb_allocations) AS tempdb_allocations,
                        SUM(t_info.tempdb_current) AS tempdb_current
                    FROM
                    (
                        SELECT TOP(@i)
                            tsu.session_id,
                            tsu.request_id,
                            tsu.user_objects_alloc_page_count +
                                tsu.internal_objects_alloc_page_count AS tempdb_allocations,
                            tsu.user_objects_alloc_page_count +
                                tsu.internal_objects_alloc_page_count -
                                tsu.user_objects_dealloc_page_count -
                                tsu.internal_objects_dealloc_page_count AS tempdb_current
                        FROM sys.dm_db_task_space_usage AS tsu
                        CROSS APPLY
                        (
                            SELECT TOP(1)
                                s0.session_id
                            FROM @sessions AS s0
                            WHERE
                                s0.session_id = tsu.session_id
                        ) AS p

                        UNION ALL

                        SELECT TOP(@i)
                            ssu.session_id,
                            NULL AS request_id,
                            ssu.user_objects_alloc_page_count +
                                ssu.internal_objects_alloc_page_count AS tempdb_allocations,
                            ssu.user_objects_alloc_page_count +
                                ssu.internal_objects_alloc_page_count -
                                ssu.user_objects_dealloc_page_count -
                                ssu.internal_objects_dealloc_page_count AS tempdb_current
                        FROM sys.dm_db_session_space_usage AS ssu
                        CROSS APPLY
                        (
                            SELECT TOP(1)
                                s0.session_id
                            FROM @sessions AS s0
                            WHERE
                                s0.session_id = ssu.session_id
                        ) AS p
                    ) AS t_info
                    GROUP BY
                        t_info.session_id,
                        COALESCE(t_info.request_id, -1)
                ) AS tempdb_info ON
                    tempdb_info.session_id = y.session_id
                    AND tempdb_info.request_id =
                        CASE
                            WHEN y.status = N""sleeping"" THEN
                                -1
                            ELSE
                                y.request_id
                        END
                " +
                CASE 
                    WHEN 
                        NOT 
                        (
                            @get_avg_time = 1 
                            AND @recursion = 1
                        ) THEN 
                            ""
                    ELSE
                        "LEFT OUTER HASH JOIN
                        (
                            SELECT TOP(@i)
                                *
                            FROM sys.dm_exec_query_stats
                        ) AS qs ON
                            qs.sql_handle = y.sql_handle
                            AND qs.plan_handle = y.plan_handle
                            AND qs.statement_start_offset = y.statement_start_offset
                            AND qs.statement_end_offset = y.statement_end_offset
                        "
                END + 
            ") AS x
            OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ";

        SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);

        SET @last_collection_start = GETDATE();

        IF @recursion = -1
        BEGIN;
            SELECT
                @first_collection_ms_ticks = ms_ticks
            FROM sys.dm_os_sys_info;
        END;

        INSERT #sessions
        (
            recursion,
            session_id,
            request_id,
            session_number,
            elapsed_time,
            avg_elapsed_time,
            physical_io,
            reads,
            physical_reads,
            writes,
            tempdb_allocations,
            tempdb_current,
            CPU,
            thread_CPU_snapshot,
            context_switches,
            used_memory,
            tasks,
            status,
            wait_info,
            transaction_id,
            open_tran_count,
            sql_handle,
            statement_start_offset,
            statement_end_offset,        
            sql_text,
            plan_handle,
            blocking_session_id,
            percent_complete,
            host_name,
            login_name,
            database_name,
            program_name,
            additional_info,
            start_time,
            login_time,
            last_request_start_time
        )
        EXEC sp_executesql 
            @sql_n,
            N"@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT",
            @recursion, @filter, @not_filter, @first_collection_ms_ticks;

        --Collect transaction information?
        IF
            @recursion = 1
            AND
            (
                @output_column_list LIKE "%|[tran_start_time|]%" ESCAPE "|"
                OR @output_column_list LIKE "%|[tran_log_writes|]%" ESCAPE "|" 
            )
        BEGIN;    
            DECLARE @i INT;
            SET @i = 2147483647;

            UPDATE s
            SET
                tran_start_time =
                    CONVERT
                    (
                        DATETIME,
                        LEFT
                        (
                            x.trans_info,
                            NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
                        ),
                        121
                    ),
                tran_log_writes =
                    RIGHT
                    (
                        x.trans_info,
                        LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
                    )
            FROM
            (
                SELECT TOP(@i)
                    trans_nodes.trans_node.value("(session_id/text())[1]", "SMALLINT") AS session_id,
                    COALESCE(trans_nodes.trans_node.value("(request_id/text())[1]", "INT"), 0) AS request_id,
                    trans_nodes.trans_node.value("(trans_info/text())[1]", "NVARCHAR(4000)") AS trans_info                
                FROM
                (
                    SELECT TOP(@i)
                        CONVERT
                        (
                            XML,
                            REPLACE
                            (
                                CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2, 
                                N"</trans_info></trans><trans><trans_info>", N""
                            )
                        )
                    FROM
                    (
                        SELECT TOP(@i)
                            CASE u_trans.r
                                WHEN 1 THEN u_trans.session_id
                                ELSE NULL
                            END AS [session_id],
                            CASE u_trans.r
                                WHEN 1 THEN u_trans.request_id
                                ELSE NULL
                            END AS [request_id],
                            CONVERT
                            (
                                NVARCHAR(MAX),
                                CASE
                                    WHEN u_trans.database_id IS NOT NULL THEN
                                        CASE u_trans.r
                                            WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N"")
                                            ELSE N""
                                        END + 
                                            REPLACE
                                            (
                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                                    CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N"(null)")),
                                                    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"?"),
                                                    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"?"),
                                                    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"?"),
                                                NCHAR(0),
                                                N"?"
                                            ) +
                                            N": " +
                                        CONVERT(NVARCHAR, u_trans.log_record_count) + N" (" + CONVERT(NVARCHAR, u_trans.log_kb_used) + N" kB)" +
                                        N","
                                    ELSE
                                        N"N/A,"
                                END COLLATE Latin1_General_Bin2
                            ) AS [trans_info]
                        FROM
                        (
                            SELECT TOP(@i)
                                trans.*,
                                ROW_NUMBER() OVER
                                (
                                    PARTITION BY
                                        trans.session_id,
                                        trans.request_id
                                    ORDER BY
                                        trans.transaction_start_time DESC
                                ) AS r
                            FROM
                            (
                                SELECT TOP(@i)
                                    session_tran_map.session_id,
                                    session_tran_map.request_id,
                                    s_tran.database_id,
                                    COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,
                                    COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used,
                                    MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
                                FROM
                                (
                                    SELECT TOP(@i)
                                        *
                                    FROM sys.dm_tran_active_transactions
                                    WHERE
                                        transaction_begin_time <= @last_collection_start
                                ) AS a_tran
                                INNER HASH JOIN
                                (
                                    SELECT TOP(@i)
                                        *
                                    FROM sys.dm_tran_database_transactions
                                    WHERE
                                        database_id < 32767
                                ) AS s_tran ON
                                    s_tran.transaction_id = a_tran.transaction_id
                                LEFT OUTER HASH JOIN
                                (
                                    SELECT TOP(@i)
                                        *
                                    FROM sys.dm_tran_session_transactions
                                ) AS tst ON
                                    s_tran.transaction_id = tst.transaction_id
                                CROSS APPLY
                                (
                                    SELECT TOP(1)
                                        s3.session_id,
                                        s3.request_id
                                    FROM
                                    (
                                        SELECT TOP(1)
                                            s1.session_id,
                                            s1.request_id
                                        FROM #sessions AS s1
                                        WHERE
                                            s1.transaction_id = s_tran.transaction_id
                                            AND s1.recursion = 1
                                            
                                        UNION ALL
                                    
                                        SELECT TOP(1)
                                            s2.session_id,
                                            s2.request_id
                                        FROM #sessions AS s2
                                        WHERE
                                            s2.session_id = tst.session_id
                                            AND s2.recursion = 1
                                    ) AS s3
                                    ORDER BY
                                        s3.request_id
                                ) AS session_tran_map
                                GROUP BY
                                    session_tran_map.session_id,
                                    session_tran_map.request_id,
                                    s_tran.database_id
                            ) AS trans
                        ) AS u_trans
                        FOR XML
                            PATH("trans"),
                            TYPE
                    ) AS trans_raw (trans_xml_raw)
                ) AS trans_final (trans_xml)
                CROSS APPLY trans_final.trans_xml.nodes("/trans") AS trans_nodes (trans_node)
            ) AS x
            INNER HASH JOIN #sessions AS s ON
                s.session_id = x.session_id
                AND s.request_id = x.request_id
            OPTION (OPTIMIZE FOR (@i = 1));
        END;

        --Variables for text and plan collection
        DECLARE    
            @session_id SMALLINT,
            @request_id INT,
            @sql_handle VARBINARY(64),
            @plan_handle VARBINARY(64),
            @statement_start_offset INT,
            @statement_end_offset INT,
            @start_time DATETIME,
            @database_name sysname;

        IF 
            @recursion = 1
            AND @output_column_list LIKE "%|[sql_text|]%" ESCAPE "|"
        BEGIN;
            DECLARE sql_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR 
                SELECT 
                    session_id,
                    request_id,
                    sql_handle,
                    statement_start_offset,
                    statement_end_offset
                FROM #sessions
                WHERE
                    recursion = 1
                    AND sql_handle IS NOT NULL
            OPTION (KEEPFIXED PLAN);

            OPEN sql_cursor;

            FETCH NEXT FROM sql_cursor
            INTO 
                @session_id,
                @request_id,
                @sql_handle,
                @statement_start_offset,
                @statement_end_offset;

            --Wait up to 5 ms for the SQL text, then give up
            SET LOCK_TIMEOUT 5;

            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    UPDATE s
                    SET
                        s.sql_text =
                        (
                            SELECT
                                REPLACE
                                (
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        N"--" + NCHAR(13) + NCHAR(10) +
                                        CASE 
                                            WHEN @get_full_inner_text = 1 THEN est.text
                                            WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
                                            WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N"[a-zA-Z0-9][a-zA-Z0-9]" THEN est.text
                                            ELSE
                                                CASE
                                                    WHEN @statement_start_offset > 0 THEN
                                                        SUBSTRING
                                                        (
                                                            est.text,
                                                            ((@statement_start_offset/2) + 1),
                                                            (
                                                                CASE
                                                                    WHEN @statement_end_offset = -1 THEN 2147483647
                                                                    ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
                                                                END
                                                            )
                                                        )
                                                    ELSE RTRIM(LTRIM(est.text))
                                                END
                                        END +
                                        NCHAR(13) + NCHAR(10) + N"--" COLLATE Latin1_General_Bin2,
                                        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"?"),
                                        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"?"),
                                        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"?"),
                                    NCHAR(0),
                                    N""
                                ) AS [processing-instruction(query)]
                            FOR XML
                                PATH(""),
                                TYPE
                        ),
                        s.statement_start_offset = 
                            CASE 
                                WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE "[a-zA-Z0-9][a-zA-Z0-9]" THEN 0
                                ELSE @statement_start_offset
                            END,
                        s.statement_end_offset = 
                            CASE 
                                WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE "[a-zA-Z0-9][a-zA-Z0-9]" THEN -1
                                ELSE @statement_end_offset
                            END
                    FROM 
                        #sessions AS s,
                        (
                            SELECT TOP(1)
                                text
                            FROM
                            (
                                SELECT 
                                    text, 
                                    0 AS row_num
                                FROM sys.dm_exec_sql_text(@sql_handle)
                                
                                UNION ALL
                                
                                SELECT 
                                    NULL,
                                    1 AS row_num
                            ) AS est0
                            ORDER BY
                                row_num
                        ) AS est
                    WHERE 
                        s.session_id = @session_id
                        AND s.request_id = @request_id
                        AND s.recursion = 1
                    OPTION (KEEPFIXED PLAN);
                END TRY
                BEGIN CATCH;
                    UPDATE s
                    SET
                        s.sql_text = 
                            CASE ERROR_NUMBER() 
                                WHEN 1222 THEN "<timeout_exceeded />"
                                ELSE "<error message="" + ERROR_MESSAGE() + "" />"
                            END
                    FROM #sessions AS s
                    WHERE 
                        s.session_id = @session_id
                        AND s.request_id = @request_id
                        AND s.recursion = 1
                    OPTION (KEEPFIXED PLAN);
                END CATCH;

                FETCH NEXT FROM sql_cursor
                INTO
                    @session_id,
                    @request_id,
                    @sql_handle,
                    @statement_start_offset,
                    @statement_end_offset;
            END;

            --Return this to the default
            SET LOCK_TIMEOUT -1;

            CLOSE sql_cursor;
            DEALLOCATE sql_cursor;
        END;

        IF 
            @get_outer_command = 1 
            AND @recursion = 1
            AND @output_column_list LIKE "%|[sql_command|]%" ESCAPE "|"
        BEGIN;
            DECLARE @buffer_results TABLE
            (
                EventType VARCHAR(30),
                Parameters INT,
                EventInfo NVARCHAR(4000),
                start_time DATETIME,
                session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY
            );

            DECLARE buffer_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR 
                SELECT 
                    session_id,
                    MAX(start_time) AS start_time
                FROM #sessions
                WHERE
                    recursion = 1
                GROUP BY
                    session_id
                ORDER BY
                    session_id
                OPTION (KEEPFIXED PLAN);

            OPEN buffer_cursor;

            FETCH NEXT FROM buffer_cursor
            INTO 
                @session_id,
                @start_time;

            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    --In SQL Server 2008, DBCC INPUTBUFFER will throw 
                    --an exception if the session no longer exists
                    INSERT @buffer_results
                    (
                        EventType,
                        Parameters,
                        EventInfo
                    )
                    EXEC sp_executesql
                        N"DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;",
                        N"@session_id SMALLINT",
                        @session_id;

                    UPDATE br
                    SET
                        br.start_time = @start_time
                    FROM @buffer_results AS br
                    WHERE
                        br.session_number = 
                        (
                            SELECT MAX(br2.session_number)
                            FROM @buffer_results br2
                        );
                END TRY
                BEGIN CATCH
                END CATCH;

                FETCH NEXT FROM buffer_cursor
                INTO 
                    @session_id,
                    @start_time;
            END;

            UPDATE s
            SET
                sql_command = 
                (
                    SELECT 
                        REPLACE
                        (
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                CONVERT
                                (
                                    NVARCHAR(MAX),
                                    N"--" + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N"--" COLLATE Latin1_General_Bin2
                                ),
                                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"?"),
                                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"?"),
                                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"?"),
                            NCHAR(0),
                            N""
                        ) AS [processing-instruction(query)]
                    FROM @buffer_results AS br
                    WHERE 
                        br.session_number = s.session_number
                        AND br.start_time = s.start_time
                        AND 
                        (
                            (
                                s.start_time = s.last_request_start_time
                                AND EXISTS
                                (
                                    SELECT *
                                    FROM sys.dm_exec_requests r2
                                    WHERE
                                        r2.session_id = s.session_id
                                        AND r2.request_id = s.request_id
                                        AND r2.start_time = s.start_time
                                )
                            )
                            OR 
                            (
                                s.request_id = 0
                                AND EXISTS
                                (
                                    SELECT *
                                    FROM sys.dm_exec_sessions s2
                                    WHERE
                                        s2.session_id = s.session_id
                                        AND s2.last_request_start_time = s.last_request_start_time
                                )
                            )
                        )
                    FOR XML
                        PATH(""),
                        TYPE
                )
            FROM #sessions AS s
            WHERE
                recursion = 1
            OPTION (KEEPFIXED PLAN);

            CLOSE buffer_cursor;
            DEALLOCATE buffer_cursor;
        END;

        IF 
            @get_plans >= 1 
            AND @recursion = 1
            AND @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|"
        BEGIN;
            DECLARE plan_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR 
                SELECT
                    session_id,
                    request_id,
                    plan_handle,
                    statement_start_offset,
                    statement_end_offset
                FROM #sessions
                WHERE
                    recursion = 1
                    AND plan_handle IS NOT NULL
            OPTION (KEEPFIXED PLAN);

            OPEN plan_cursor;

            FETCH NEXT FROM plan_cursor
            INTO 
                @session_id,
                @request_id,
                @plan_handle,
                @statement_start_offset,
                @statement_end_offset;

            --Wait up to 5 ms for a query plan, then give up
            SET LOCK_TIMEOUT 5;

            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    UPDATE s
                    SET
                        s.query_plan =
                        (
                            SELECT
                                CONVERT(xml, query_plan)
                            FROM sys.dm_exec_text_query_plan
                            (
                                @plan_handle, 
                                CASE @get_plans
                                    WHEN 1 THEN
                                        @statement_start_offset
                                    ELSE
                                        0
                                END, 
                                CASE @get_plans
                                    WHEN 1 THEN
                                        @statement_end_offset
                                    ELSE
                                        -1
                                END
                            )
                        )
                    FROM #sessions AS s
                    WHERE 
                        s.session_id = @session_id
                        AND s.request_id = @request_id
                        AND s.recursion = 1
                    OPTION (KEEPFIXED PLAN);
                END TRY
                BEGIN CATCH;
                    IF ERROR_NUMBER() = 6335
                    BEGIN;
                        UPDATE s
                        SET
                            s.query_plan =
                            (
                                SELECT
                                    N"--" + NCHAR(13) + NCHAR(10) + 
                                    N"-- Could not render showplan due to XML data type limitations. " + NCHAR(13) + NCHAR(10) + 
                                    N"-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS." + NCHAR(13) + NCHAR(10) +
                                    N"--" + NCHAR(13) + NCHAR(10) +
                                        REPLACE(qp.query_plan, N"<RelOp", NCHAR(13)+NCHAR(10)+N"<RelOp") + 
                                        NCHAR(13) + NCHAR(10) + N"--" COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
                                FROM sys.dm_exec_text_query_plan
                                (
                                    @plan_handle, 
                                    CASE @get_plans
                                        WHEN 1 THEN
                                            @statement_start_offset
                                        ELSE
                                            0
                                    END, 
                                    CASE @get_plans
                                        WHEN 1 THEN
                                            @statement_end_offset
                                        ELSE
                                            -1
                                    END
                                ) AS qp
                                FOR XML
                                    PATH(""),
                                    TYPE
                            )
                        FROM #sessions AS s
                        WHERE 
                            s.session_id = @session_id
                            AND s.request_id = @request_id
                            AND s.recursion = 1
                        OPTION (KEEPFIXED PLAN);
                    END;
                    ELSE
                    BEGIN;
                        UPDATE s
                        SET
                            s.query_plan = 
                                CASE ERROR_NUMBER() 
                                    WHEN 1222 THEN "<timeout_exceeded />"
                                    ELSE "<error message="" + ERROR_MESSAGE() + "" />"
                                END
                        FROM #sessions AS s
                        WHERE 
                            s.session_id = @session_id
                            AND s.request_id = @request_id
                            AND s.recursion = 1
                        OPTION (KEEPFIXED PLAN);
                    END;
                END CATCH;

                FETCH NEXT FROM plan_cursor
                INTO
                    @session_id,
                    @request_id,
                    @plan_handle,
                    @statement_start_offset,
                    @statement_end_offset;
            END;

            --Return this to the default
            SET LOCK_TIMEOUT -1;

            CLOSE plan_cursor;
            DEALLOCATE plan_cursor;
        END;

        IF 
            @get_locks = 1 
            AND @recursion = 1
            AND @output_column_list LIKE "%|[locks|]%" ESCAPE "|"
        BEGIN;
            DECLARE locks_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR 
                SELECT DISTINCT
                    database_name
                FROM #locks
                WHERE
                    EXISTS
                    (
                        SELECT *
                        FROM #sessions AS s
                        WHERE
                            s.session_id = #locks.session_id
                            AND recursion = 1
                    )
                    AND database_name <> "(null)"
                OPTION (KEEPFIXED PLAN);

            OPEN locks_cursor;

            FETCH NEXT FROM locks_cursor
            INTO 
                @database_name;

            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    SET @sql_n = CONVERT(NVARCHAR(MAX), "") +
                        "UPDATE l " +
                        "SET " +
                            "object_name = " +
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "o.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                "), " +
                            "index_name = " +
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "i.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                "), " +
                            "schema_name = " +
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "s.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                "), " +
                            "principal_name = " + 
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "dp.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                ") " +
                        "FROM #locks AS l " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.allocation_units AS au ON " +
                            "au.allocation_unit_id = l.allocation_unit_id " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p ON " +
                            "p.hobt_id = " +
                                "COALESCE " +
                                "( " +
                                    "l.hobt_id, " +
                                    "CASE " +
                                        "WHEN au.type IN (1, 3) THEN au.container_id " +
                                        "ELSE NULL " +
                                    "END " +
                                ") " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p1 ON " +
                            "l.hobt_id IS NULL " +
                            "AND au.type = 2 " +
                            "AND p1.partition_id = au.container_id " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.objects AS o ON " +
                            "o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.indexes AS i ON " +
                            "i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) " +
                            "AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.schemas AS s ON " +
                            "s.schema_id = COALESCE(l.schema_id, o.schema_id) " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.database_principals AS dp ON " +
                            "dp.principal_id = l.principal_id " +
                        "WHERE " +
                            "l.database_name = @database_name " +
                        "OPTION (KEEPFIXED PLAN); ";
                    
                    EXEC sp_executesql
                        @sql_n,
                        N"@database_name sysname",
                        @database_name;
                END TRY
                BEGIN CATCH;
                    UPDATE #locks
                    SET
                        query_error = 
                            REPLACE
                            (
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    CONVERT
                                    (
                                        NVARCHAR(MAX), 
                                        ERROR_MESSAGE() COLLATE Latin1_General_Bin2
                                    ),
                                    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"?"),
                                    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"?"),
                                    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"?"),
                                NCHAR(0),
                                N""
                            )
                    WHERE 
                        database_name = @database_name
                    OPTION (KEEPFIXED PLAN);
                END CATCH;

                FETCH NEXT FROM locks_cursor
                INTO
                    @database_name;
            END;

            CLOSE locks_cursor;
            DEALLOCATE locks_cursor;

            CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);

            UPDATE s
            SET 
                s.locks =
                (
                    SELECT 
                        REPLACE
                        (
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                CONVERT
                                (
                                    NVARCHAR(MAX), 
                                    l1.database_name COLLATE Latin1_General_Bin2
                                ),
                                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"?"),
                                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"?"),
                                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"?"),
                            NCHAR(0),
                            N""
                        ) AS [Database/@name],
                        MIN(l1.query_error) AS [Database/@query_error],
                        (
                            SELECT 
                                l2.request_mode AS [Lock/@request_mode],
                                l2.request_status AS [Lock/@request_status],
                                COUNT(*) AS [Lock/@request_count]
                            FROM #locks AS l2
                            WHERE 
                                l1.session_id = l2.session_id
                                AND l1.request_id = l2.request_id
                                AND l2.database_name = l1.database_name
                                AND l2.resource_type = "DATABASE"
                            GROUP BY
                                l2.request_mode,
                                l2.request_status
                            FOR XML
                                PATH(""),
                                TYPE
                        ) AS [Database/Locks],
                        (
                            SELECT
                                COALESCE(l3.object_name, "(null)") AS [Object/@name],
                                l3.schema_name AS [Object/@schema_name],
                                (
                                    SELECT
                                        l4.resource_type AS [Lock/@resource_type],
                                        l4.page_type AS [Lock/@page_type],
                                        l4.index_name AS [Lock/@index_name],
                                        CASE 
                                            WHEN l4.object_name IS NULL THEN l4.schema_name
                                            ELSE NULL
                                        END AS [Lock/@schema_name],
                                        l4.principal_name AS [Lock/@principal_name],
                                        l4.resource_description AS [Lock/@resource_description],
                                        l4.request_mode AS [Lock/@request_mode],
                                        l4.request_status AS [Lock/@request_status],
                                        SUM(l4.request_count) AS [Lock/@request_count]
                                    FROM #locks AS l4
                                    WHERE 
                                        l4.session_id = l3.session_id
                                        AND l4.request_id = l3.request_id
                                        AND l3.database_name = l4.database_name
                                        AND COALESCE(l3.object_name, "(null)") = COALESCE(l4.object_name, "(null)")
                                        AND COALESCE(l3.schema_name, "") = COALESCE(l4.schema_name, "")
                                        AND l4.resource_type <> "DATABASE"
                                    GROUP BY
                                        l4.resource_type,
                                        l4.page_type,
                                        l4.index_name,
                                        CASE 
                                            WHEN l4.object_name IS NULL THEN l4.schema_name
                                            ELSE NULL
                                        END,
                                        l4.principal_name,
                                        l4.resource_description,
                                        l4.request_mode,
                                        l4.request_status
                                    FOR XML
                                        PATH(""),
                                        TYPE
                                ) AS [Object/Locks]
                            FROM #locks AS l3
                            WHERE 
                                l3.session_id = l1.session_id
                                AND l3.request_id = l1.request_id
                                AND l3.database_name = l1.database_name
                                AND l3.resource_type <> "DATABASE"
                            GROUP BY 
                                l3.session_id,
                                l3.request_id,
                                l3.database_name,
                                COALESCE(l3.object_name, "(null)"),
                                l3.schema_name
                            FOR XML
                                PATH(""),
                                TYPE
                        ) AS [Database/Objects]
                    FROM #locks AS l1
                    WHERE
                        l1.session_id = s.session_id
                        AND l1.request_id = s.request_id
                        AND l1.start_time IN (s.start_time, s.last_request_start_time)
                        AND s.recursion = 1
                    GROUP BY 
                        l1.session_id,
                        l1.request_id,
                        l1.database_name
                    FOR XML
                        PATH(""),
                        TYPE
                )
            FROM #sessions s
            OPTION (KEEPFIXED PLAN);
        END;

        IF 
            @find_block_leaders = 1
            AND @recursion = 1
            AND @output_column_list LIKE "%|[blocked_session_count|]%" ESCAPE "|"
        BEGIN;
            WITH
            blockers AS
            (
                SELECT
                    session_id,
                    session_id AS top_level_session_id,
                    CONVERT(VARCHAR(8000), "." + CONVERT(VARCHAR(8000), session_id) + ".") AS the_path
                FROM #sessions
                WHERE
                    recursion = 1

                UNION ALL

                SELECT
                    s.session_id,
                    b.top_level_session_id,
                    CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + ".") AS the_path
                FROM blockers AS b
                JOIN #sessions AS s ON
                    s.blocking_session_id = b.session_id
                    AND s.recursion = 1
                    AND b.the_path NOT LIKE "%." + CONVERT(VARCHAR(8000), s.session_id) + ".%" COLLATE Latin1_General_Bin2
            )
            UPDATE s
            SET
                s.blocked_session_count = x.blocked_session_count
            FROM #sessions AS s
            JOIN
            (
                SELECT
                    b.top_level_session_id AS session_id,
                    COUNT(*) - 1 AS blocked_session_count
                FROM blockers AS b
                GROUP BY
                    b.top_level_session_id
            ) x ON
                s.session_id = x.session_id
            WHERE
                s.recursion = 1;
        END;

        IF
            @get_task_info = 2
            AND @output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
            AND @recursion = 1
        BEGIN;
            CREATE TABLE #blocked_requests
            (
                session_id SMALLINT NOT NULL,
                request_id INT NOT NULL,
                database_name sysname NOT NULL,
                object_id INT,
                hobt_id BIGINT,
                schema_id INT,
                schema_name sysname NULL,
                object_name sysname NULL,
                query_error NVARCHAR(2048),
                PRIMARY KEY (database_name, session_id, request_id)
            );

            CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
            CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
            WITH SAMPLE 0 ROWS, NORECOMPUTE;
        
            INSERT #blocked_requests
            (
                session_id,
                request_id,
                database_name,
                object_id,
                hobt_id,
                schema_id
            )
            SELECT
                session_id,
                request_id,
                database_name,
                object_id,
                hobt_id,
                CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(" = ", schema_node) + 3, LEN(schema_node))) AS schema_id
            FROM
            (
                SELECT
                    session_id,
                    request_id,
                    agent_nodes.agent_node.value("(database_name/text())[1]", "sysname") AS database_name,
                    agent_nodes.agent_node.value("(object_id/text())[1]", "int") AS object_id,
                    agent_nodes.agent_node.value("(hobt_id/text())[1]", "bigint") AS hobt_id,
                    agent_nodes.agent_node.value("(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]", "varchar(100)") AS schema_node
                FROM #sessions AS s
                CROSS APPLY s.additional_info.nodes("//block_info") AS agent_nodes (agent_node)
                WHERE
                    s.recursion = 1
            ) AS t
            WHERE
                t.database_name IS NOT NULL
                AND
                (
                    t.object_id IS NOT NULL
                    OR t.hobt_id IS NOT NULL
                    OR t.schema_node IS NOT NULL
                );
            
            DECLARE blocks_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR
                SELECT DISTINCT
                    database_name
                FROM #blocked_requests;
                
            OPEN blocks_cursor;
            
            FETCH NEXT FROM blocks_cursor
            INTO 
                @database_name;
            
            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    SET @sql_n = 
                        CONVERT(NVARCHAR(MAX), "") +
                        "UPDATE b " +
                        "SET " +
                            "b.schema_name = " +
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "s.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                "), " +
                            "b.object_name = " +
                                "REPLACE " +
                                "( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                    "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
                                        "o.name COLLATE Latin1_General_Bin2, " +
                                        "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""?""), " +
                                        "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""?""), " +
                                        "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""?""), " +
                                    "NCHAR(0), " +
                                    N""""" " +
                                ") " +
                        "FROM #blocked_requests AS b " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p ON " +
                            "p.hobt_id = b.hobt_id " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.objects AS o ON " +
                            "o.object_id = COALESCE(p.object_id, b.object_id) " +
                        "LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.schemas AS s ON " +
                            "s.schema_id = COALESCE(o.schema_id, b.schema_id) " +
                        "WHERE " +
                            "b.database_name = @database_name; ";
                    
                    EXEC sp_executesql
                        @sql_n,
                        N"@database_name sysname",
                        @database_name;
                END TRY
                BEGIN CATCH;
                    UPDATE #blocked_requests
                    SET
                        query_error = 
                            REPLACE
                            (
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    CONVERT
                                    (
                                        NVARCHAR(MAX), 
                                        ERROR_MESSAGE() COLLATE Latin1_General_Bin2
                                    ),
                                    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"?"),
                                    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"?"),
                                    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"?"),
                                NCHAR(0),
                                N""
                            )
                    WHERE
                        database_name = @database_name;
                END CATCH;

                FETCH NEXT FROM blocks_cursor
                INTO
                    @database_name;
            END;
            
            CLOSE blocks_cursor;
            DEALLOCATE blocks_cursor;
            
            UPDATE s
            SET
                additional_info.modify
                ("
                    insert <schema_name>{sql:column("b.schema_name")}</schema_name>
                    as last
                    into (/additional_info/block_info)[1]
                ")
            FROM #sessions AS s
            INNER JOIN #blocked_requests AS b ON
                b.session_id = s.session_id
                AND b.request_id = s.request_id
                AND s.recursion = 1
            WHERE
                b.schema_name IS NOT NULL;

            UPDATE s
            SET
                additional_info.modify
                ("
                    insert <object_name>{sql:column("b.object_name")}</object_name>
                    as last
                    into (/additional_info/block_info)[1]
                ")
            FROM #sessions AS s
            INNER JOIN #blocked_requests AS b ON
                b.session_id = s.session_id
                AND b.request_id = s.request_id
                AND s.recursion = 1
            WHERE
                b.object_name IS NOT NULL;

            UPDATE s
            SET
                additional_info.modify
                ("
                    insert <query_error>{sql:column("b.query_error")}</query_error>
                    as last
                    into (/additional_info/block_info)[1]
                ")
            FROM #sessions AS s
            INNER JOIN #blocked_requests AS b ON
                b.session_id = s.session_id
                AND b.request_id = s.request_id
                AND s.recursion = 1
            WHERE
                b.query_error IS NOT NULL;
        END;

        IF
            @output_column_list LIKE "%|[program_name|]%" ESCAPE "|"
            AND @output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
            AND @recursion = 1
        BEGIN;
            DECLARE @job_id UNIQUEIDENTIFIER;
            DECLARE @step_id INT;

            DECLARE agent_cursor
            CURSOR LOCAL FAST_FORWARD
            FOR 
                SELECT
                    s.session_id,
                    agent_nodes.agent_node.value("(job_id/text())[1]", "uniqueidentifier") AS job_id,
                    agent_nodes.agent_node.value("(step_id/text())[1]", "int") AS step_id
                FROM #sessions AS s
                CROSS APPLY s.additional_info.nodes("//agent_job_info") AS agent_nodes (agent_node)
                WHERE
                    s.recursion = 1
            OPTION (KEEPFIXED PLAN);
            
            OPEN agent_cursor;

            FETCH NEXT FROM agent_cursor
            INTO 
                @session_id,
                @job_id,
                @step_id;

            WHILE @@FETCH_STATUS = 0
            BEGIN;
                BEGIN TRY;
                    DECLARE @job_name sysname;
                    SET @job_name = NULL;
                    DECLARE @step_name sysname;
                    SET @step_name = NULL;
                    
                    SELECT
                        @job_name = 
                            REPLACE
                            (
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    j.name,
                                    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"?"),
                                    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"?"),
                                    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"?"),
                                NCHAR(0),
                                N"?"
                            ),
                        @step_name = 
                            REPLACE
                            (
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    s.step_name,
                                    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"?"),
                                    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"?"),
                                    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"?"),
                                NCHAR(0),
                                N"?"
                            )
                    FROM msdb.dbo.sysjobs AS j
                    INNER JOIN msdb..sysjobsteps AS s ON
                        j.job_id = s.job_id
                    WHERE
                        j.job_id = @job_id
                        AND s.step_id = @step_id;

                    IF @job_name IS NOT NULL
                    BEGIN;
                        UPDATE s
                        SET
                            additional_info.modify
                            ("
                                insert text{sql:variable("@job_name")}
                                into (/additional_info/agent_job_info/job_name)[1]
                            ")
                        FROM #sessions AS s
                        WHERE 
                            s.session_id = @session_id
                        OPTION (KEEPFIXED PLAN);
                        
                        UPDATE s
                        SET
                            additional_info.modify
                            ("
                                insert text{sql:variable("@step_name")}
                                into (/additional_info/agent_job_info/step_name)[1]
                            ")
                        FROM #sessions AS s
                        WHERE 
                            s.session_id = @session_id
                        OPTION (KEEPFIXED PLAN);
                    END;
                END TRY
                BEGIN CATCH;
                    DECLARE @msdb_error_message NVARCHAR(256);
                    SET @msdb_error_message = ERROR_MESSAGE();
                
                    UPDATE s
                    SET
                        additional_info.modify
                        ("
                            insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
                            as last
                            into (/additional_info/agent_job_info)[1]
                        ")
                    FROM #sessions AS s
                    WHERE 
                        s.session_id = @session_id
                        AND s.recursion = 1
                    OPTION (KEEPFIXED PLAN);
                END CATCH;

                FETCH NEXT FROM agent_cursor
                INTO 
                    @session_id,
                    @job_id,
                    @step_id;
            END;

            CLOSE agent_cursor;
            DEALLOCATE agent_cursor;
        END; 
        
        IF 
            @delta_interval > 0 
            AND @recursion <> 1
        BEGIN;
            SET @recursion = 1;

            DECLARE @delay_time CHAR(12);
            SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
            WAITFOR DELAY @delay_time;

            GOTO REDO;
        END;
    END;

    SET @sql = 
        --Outer column list
        CONVERT
        (
            VARCHAR(MAX),
            CASE
                WHEN 
                    @destination_table <> "" 
                    AND @return_schema = 0 
                        THEN "INSERT " + @destination_table + " "
                ELSE ""
            END +
            "SELECT " +
                @output_column_list + " " +
            CASE @return_schema
                WHEN 1 THEN "INTO #session_schema "
                ELSE ""
            END
        --End outer column list
        ) + 
        --Inner column list
        CONVERT
        (
            VARCHAR(MAX),
            "FROM " +
            "( " +
                "SELECT " +
                    "session_id, " +
                    --[dd hh:mm:ss.mss]
                    CASE
                        WHEN @format_output IN (1, 2) THEN
                            "CASE " +
                                "WHEN elapsed_time < 0 THEN " +
                                    "RIGHT " +
                                    "( " +
                                        "REPLICATE(""0"", max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), " +
                                        "max_elapsed_length " +
                                    ") + " +
                                        "RIGHT " +
                                        "( " +
                                            "CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), " +
                                            "9 " +
                                        ") + " +
                                        """.000"" " +
                                "ELSE " +
                                    "RIGHT " +
                                    "( " +
                                        "REPLICATE(""0"", max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), " +
                                        "max_elapsed_length " +
                                    ") + " +
                                        "RIGHT " +
                                        "( " +
                                            "CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), " +
                                            "9 " +
                                        ") + " +
                                        """."" + " + 
                                        "RIGHT(""000"" + CONVERT(VARCHAR, elapsed_time % 1000), 3) " +
                            "END AS [dd hh:mm:ss.mss], "
                        ELSE
                            ""
                    END +
                    --[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
                    CASE 
                        WHEN  @format_output IN (1, 2) THEN 
                            "RIGHT " +
                            "( " +
                                """00"" + CONVERT(VARCHAR, avg_elapsed_time / 86400000), " +
                                "2 " +
                            ") + " +
                                "RIGHT " +
                                "( " +
                                    "CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), " +
                                    "9 " +
                                ") + " +
                                """."" + " +
                                "RIGHT(""000"" + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], "
                        ELSE
                            "avg_elapsed_time, "
                    END +
                    --physical_io
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS "
                        ELSE ""
                    END + "physical_io, " +
                    --reads
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS "
                        ELSE ""
                    END + "reads, " +
                    --physical_reads
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS "
                        ELSE ""
                    END + "physical_reads, " +
                    --writes
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS "
                        ELSE ""
                    END + "writes, " +
                    --tempdb_allocations
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS "
                        ELSE ""
                    END + "tempdb_allocations, " +
                    --tempdb_current
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS "
                        ELSE ""
                    END + "tempdb_current, " +
                    --CPU
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS "
                        ELSE ""
                    END + "CPU, " +
                    --context_switches
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS "
                        ELSE ""
                    END + "context_switches, " +
                    --used_memory
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS "
                        ELSE ""
                    END + "used_memory, " +
                    CASE
                        WHEN @output_column_list LIKE "%|_delta|]%" ESCAPE "|" THEN
                            --physical_io_delta            
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND physical_io_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) " 
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) "
                                            ELSE "physical_io_delta "
                                        END +
                                "ELSE NULL " +
                            "END AS physical_io_delta, " +
                            --reads_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND reads_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) "
                                            ELSE "reads_delta "
                                        END +
                                "ELSE NULL " +
                            "END AS reads_delta, " +
                            --physical_reads_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND physical_reads_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) "
                                            ELSE "physical_reads_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS physical_reads_delta, " +
                            --writes_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND writes_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) "
                                            ELSE "writes_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS writes_delta, " +
                            --tempdb_allocations_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND tempdb_allocations_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) "
                                            ELSE "tempdb_allocations_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS tempdb_allocations_delta, " +
                            --tempdb_current_delta
                            --this is the only one that can (legitimately) go negative 
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) "
                                            ELSE "tempdb_current_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS tempdb_current_delta, " +
                            --CPU_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                        "THEN " +
                                            "CASE " +
                                                "WHEN " +
                                                    "thread_CPU_delta > CPU_delta " +
                                                    "AND thread_CPU_delta > 0 " +
                                                        "THEN " +
                                                            CASE @format_output
                                                                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)) "
                                                                WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) "
                                                                ELSE "thread_CPU_delta "
                                                            END + 
                                                "WHEN CPU_delta >= 0 THEN " +
                                                    CASE @format_output
                                                        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)) "
                                                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) "
                                                        ELSE "CPU_delta "
                                                    END + 
                                                "ELSE NULL " +
                                            "END " +
                                "ELSE " +
                                    "NULL " +
                            "END AS CPU_delta, " +
                            --context_switches_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND context_switches_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) "
                                            ELSE "context_switches_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS context_switches_delta, " +
                            --used_memory_delta
                            "CASE " +
                                "WHEN " +
                                    "first_request_start_time = last_request_start_time " + 
                                    "AND num_events = 2 " +
                                    "AND used_memory_delta >= 0 " +
                                        "THEN " +
                                        CASE @format_output
                                            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)) "
                                            WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) "
                                            ELSE "used_memory_delta "
                                        END + 
                                "ELSE NULL " +
                            "END AS used_memory_delta, "
                        ELSE ""
                    END +
                    --tasks
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) "
                        ELSE ""
                    END + "tasks, " +
                    "status, " +
                    "wait_info, " +
                    "locks, " +
                    "tran_start_time, " +
                    "LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, " +
                    --open_tran_count
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS "
                        ELSE ""
                    END + "open_tran_count, " +
                    --sql_command
                    CASE @format_output 
                        WHEN 0 THEN "REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ""<?query --""+CHAR(13)+CHAR(10), """"), CHAR(13)+CHAR(10)+""--?>"", """") AS "
                        ELSE ""
                    END + "sql_command, " +
                    --sql_text
                    CASE @format_output 
                        WHEN 0 THEN "REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ""<?query --""+CHAR(13)+CHAR(10), """"), CHAR(13)+CHAR(10)+""--?>"", """") AS "
                        ELSE ""
                    END + "sql_text, " +
                    "query_plan, " +
                    "blocking_session_id, " +
                    --blocked_session_count
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS "
                        ELSE ""
                    END + "blocked_session_count, " +
                    --percent_complete
                    CASE @format_output
                        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 "
                        WHEN 2 THEN "CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS "
                        ELSE ""
                    END + "percent_complete, " +
                    "host_name, " +
                    "login_name, " +
                    "database_name, " +
                    "program_name, " +
                    "additional_info, " +
                    "start_time, " +
                    "login_time, " +
                    "CASE " +
                        "WHEN status = N""sleeping"" THEN NULL " +
                        "ELSE request_id " +
                    "END AS request_id, " +
                    "GETDATE() AS collection_time "
        --End inner column list
        ) +
        --Derived table and INSERT specification
        CONVERT
        (
            VARCHAR(MAX),
                "FROM " +
                "( " +
                    "SELECT TOP(2147483647) " +
                        "*, " +
                        "CASE " +
                            "MAX " +
                            "( " +
                                "LEN " +
                                "( " +
                                    "CONVERT " +
                                    "( " +
                                        "VARCHAR, " +
                                        "CASE " +
                                            "WHEN elapsed_time < 0 THEN " +
                                                "(-1 * elapsed_time) / 86400 " +
                                            "ELSE " +
                                                "elapsed_time / 86400000 " +
                                        "END " +
                                    ") " +
                                ") " +
                            ") OVER () " +
                                "WHEN 1 THEN 2 " +
                                "ELSE " +
                                    "MAX " +
                                    "( " +
                                        "LEN " +
                                        "( " +
                                            "CONVERT " +
                                            "( " +
                                                "VARCHAR, " +
                                                "CASE " +
                                                    "WHEN elapsed_time < 0 THEN " +
                                                        "(-1 * elapsed_time) / 86400 " +
                                                    "ELSE " +
                                                        "elapsed_time / 86400000 " +
                                                "END " +
                                            ") " +
                                        ") " +
                                    ") OVER () " +
                        "END AS max_elapsed_length, " +
                        CASE
                            WHEN @output_column_list LIKE "%|_delta|]%" ESCAPE "|" THEN
                                "MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, " +
                                "MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, " +
                                "MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, " +
                                "MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, " +
                                "MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, " +
                                "MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, " +
                                "MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, " +
                                "MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, " +
                                "MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, " +
                                "MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + " +
                                    "MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, " +
                                "MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, "
                            ELSE ""
                        END +
                        "COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events " +
                    "FROM #sessions AS s1 " +
                    CASE 
                        WHEN @sort_order = "" THEN ""
                        ELSE
                            "ORDER BY " +
                                @sort_order
                    END +
                ") AS s " +
                "WHERE " +
                    "s.recursion = 1 " +
            ") x " +
            "OPTION (KEEPFIXED PLAN); " +
            "" +
            CASE @return_schema
                WHEN 1 THEN
                    "SET @schema = " +
                        """CREATE TABLE <table_name> ( "" + " +
                            "STUFF " +
                            "( " +
                                "( " +
                                    "SELECT " +
                                        ""","" + " +
                                        "QUOTENAME(COLUMN_NAME) + "" "" + " +
                                        "DATA_TYPE + " + 
                                        "CASE " +
                                            "WHEN DATA_TYPE LIKE ""%char"" THEN ""("" + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ""-1""), ""max"") + "") "" " +
                                            "ELSE "" "" " +
                                        "END + " +
                                        "CASE IS_NULLABLE " +
                                            "WHEN ""NO"" THEN ""NOT "" " +
                                            "ELSE """" " +
                                        "END + ""NULL"" AS [text()] " +
                                    "FROM tempdb.INFORMATION_SCHEMA.COLUMNS " +
                                    "WHERE " +
                                        "TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(""tempdb..#session_schema"")) " +
                                        "ORDER BY " +
                                            "ORDINAL_POSITION " +
                                    "FOR XML " +
                                        "PATH("""") " +
                                "), + " +
                                "1, " +
                                "1, " +
                                """"" " +
                            ") + " +
                        """)""; " 
                ELSE ""
            END
        --End derived table and INSERT specification
        );

    SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);

    EXEC sp_executesql
        @sql_n,
        N"@schema VARCHAR(MAX) OUTPUT",
        @schema OUTPUT;
END;
GO