查看session的脚本

晨曦之光 发布于 2012/04/19 16:36
阅读 315
收藏 1
平时使用,国外论坛看见的!忘记出处了! 

 

USE master
GO


create PROC dbo.sp_WhoIsActive
    (
      @filter SYSNAME = '' ,
      @filter_type VARCHAR(10) = 'session' ,
      @not_filter SYSNAME = '' ,
      @not_filter_type VARCHAR(10) = 'session' ,
      @show_own_spid BIT = 0 ,
      @show_system_spids BIT = 0 ,
      @show_sleeping_spids TINYINT = 1 ,
      @get_full_inner_text BIT = 0 ,
      @get_plans TINYINT = 0 ,
      @get_outer_command BIT = 0 ,
      @get_transaction_info BIT = 0 ,
      @get_task_info TINYINT = 1 ,
      @get_locks BIT = 0 ,
      @get_avg_time BIT = 0 ,
      @get_additional_info BIT = 0 ,
      @find_block_leaders BIT = 0 ,
      @delta_interval TINYINT = 0 ,
      @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][%]' ,
      @sort_order VARCHAR(500) = '[start_time] ASC' ,
      @format_output TINYINT = 1 ,
      @destination_table VARCHAR(4000) = '' ,
      @return_schema BIT = 0 ,
      @schema VARCHAR(MAX) = NULL OUTPUT ,
      @help BIT = 0

    )
AS
    BEGIN
        SET NOCOUNT ON ;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
        SET QUOTED_IDENTIFIER ON ;
        SET ANSI_PADDING 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('输入参数不能为空', 16, 1) ;
                RETURN ;
            END ;
 
        IF @filter_type NOT IN ( 'session', 'program', 'database', 'login',
                                 'host' )
            BEGIN;
                RAISERROR('验证过滤类型,只能是下列内容: session, program, database, login, host', 16, 1) ;
                RETURN ;
            END ;
 
        IF @filter_type = 'session'
            AND @filter LIKE '%[^0123456789]%'
            BEGIN;
                RAISERROR('会话过滤必须是有效的整数', 16, 1) ;
                RETURN ;
            END ;
 
        IF @not_filter_type NOT IN ( 'session', 'program', 'database', 'login',
                                     'host' )
            BEGIN;
                RAISERROR('有效的过滤类型: session, program, database, login, host', 16, 1) ;
                RETURN ;
            END ;
 
        IF @not_filter_type = 'session'
            AND @not_filter LIKE '%[^0123456789]%'
            BEGIN;
                RAISERROR('会话过滤必须是有效的整数', 16, 1) ;
                RETURN ;
            END ;
 
        IF @show_sleeping_spids NOT IN ( 0, 1, 2 )
            BEGIN;
                RAISERROR('针对@show_sleeping_spids的有效值必须是: 0, 1, or 2', 16, 1) ;
                RETURN ;
            END ;
 
        IF @get_plans NOT IN ( 0, 1, 2 )
            BEGIN;
                RAISERROR('针对 @get_plans的有效值必须是: 0, 1, or 2', 16, 1) ;
                RETURN ;
            END ;

        IF @get_task_info NOT IN ( 0, 1, 2 )
            BEGIN;
                RAISERROR('针对@get_task_info的有效值必须是: 0, 1, or 2', 16, 1) ;
                RETURN ;
            END ;

        IF @format_output NOT IN ( 0, 1, 2 )
            BEGIN;
                RAISERROR('针对@format_output的有效值必须是: 0, 1, or 2', 16, 1) ;
                RETURN ;
            END ;
 
        IF @help = 1
            BEGIN;
                DECLARE @params VARCHAR(MAX) ,
                    @outputs VARCHAR(MAX) ;

                SELECT  @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)),
                                                     ' ', '') + 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(@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),
                                                              @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),
                                                              @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)
                             ),
                        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),
                                                              column_info) + 1,
                                                       CHARINDEX(']',
                                                              column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info) + 2)
                                                       - CHARINDEX(CHAR(255),
                                                              column_info))
                                        ELSE SUBSTRING(column_info,
                                                       CHARINDEX(CHAR(255),
                                                              column_info) + 2,
                                                       CHARINDEX(']',
                                                              column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info) + 2)
                                                       - CHARINDEX(CHAR(255),
                                                              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),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info))
                                                              + 1, 1) = '<'
                                                  THEN SUBSTRING(column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info))
                                                              + 1,
                                                              CHARINDEX('>',
                                                              column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info))
                                                              + 1)
                                                              - CHARINDEX(CHAR(255),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info)))
                                                  ELSE SUBSTRING(column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info))
                                                              + 1,
                                                              CHARINDEX(']',
                                                              column_info,
                                                              CHARINDEX(CHAR(255),
                                                              column_info,
                                                              CHARINDEX('Non-Formatted:',
                                                              column_info))
                                                              + 1)
                                                              - CHARINDEX(CHAR(255),
                                                              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),
                                                              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), '')
                            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🇲🇲ss.mss]' ,
                                                2
                                       WHERE    @format_output = 1
                                       UNION ALL
                                       SELECT   '[dd hh🇲🇲ss.mss (avg)]' ,
                                                3
                                       WHERE    @format_output = 1
                                                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   '[request_id]' ,
                                                42
                                       UNION ALL
                                       SELECT   '[collection_time]' ,
                                                43
                                     ) 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]'
                                     ) AS x ON x.column_name LIKE token ESCAPE '|'
                     )
            SELECT  @sort_order = COALESCE(STUFF(( SELECT   ',' + column_name AS [text()]
                                                   FROM     ordered_columns
                                                   WHERE    r = 1
                                                   ORDER BY number
                                                 FOR
                                                   XML PATH('')
                                                 ), 1, 1, ''), '') ;

        CREATE TABLE #sessions
            (
              recursion SMALLINT NOT NULL ,
              session_id SMALLINT NOT NULL ,
              request_id INT NULL ,
              session_number INT NOT NULL ,
              elapsed_time INT NOT NULL ,
              avg_elapsed_time INT NULL ,
              physical_io BIGINT NULL ,
              reads BIGINT NOT NULL ,
              physical_reads BIGINT NOT NULL ,
              writes BIGINT NOT NULL ,
              tempdb_allocations BIGINT NOT NULL ,
              tempdb_current BIGINT NOT NULL ,
              CPU INT NOT 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 ,
              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 ,
              last_request_start_time DATETIME NOT NULL ,
              UNIQUE CLUSTERED ( 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_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 ;

  --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.db_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 ,
                                COALESCE(s.request_id, -1) AS 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
                        INTO    #locks
                        FROM    ( SELECT    sp.spid AS session_id ,
                                            CASE sp.status
                                              WHEN 'sleeping'
                                              THEN CONVERT(INT, NULL)
                                              ELSE sp.request_id
                                            END AS request_id ,
                                            CASE sp.status
                                              WHEN 'sleeping'
                                              THEN sp.login_time
                                              ELSE COALESCE(( SELECT
                                                              r.start_time
                                                              FROM
                                                              sys.dm_exec_requests
                                                              AS r
                                                              WHERE
                                                              r.session_id = sp.spid
                                                              AND r.request_id = sp.request_id
                                                            ), sp.login_time)
                                            END AS start_time ,
                                            sp.dbid
                                  FROM      sys.sysprocesses AS sp
                                  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.db_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
                                                              % 511233 = 0
                                                              THEN 'SGAM'
                                                              WHEN x.page_no = 6
                                                              OR x.page_no
                                                              % 511238 = 0
                                                              THEN 'DCM'
                                                              WHEN x.page_no = 7
                                                              OR x.page_no
                                                              % 511239 = 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 db_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.db_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
                                                              % 511233 = 0
                                                              THEN 'SGAM'
                                                              WHEN x.page_no = 6
                                                              OR x.page_no
                                                              % 511238 = 0
                                                              THEN 'DCM'
                                                              WHEN x.page_no = 7
                                                              OR x.page_no
                                                              % 511239 = 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 = COALESCE(s.request_id,
                                                              0)
                        OPTION  ( HASH GROUP ) ;
   
   --Disable unnecessary autostats on the table
                        CREATE STATISTICS s_db_name ON #locks (db_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 =
   --Column list
                    CONVERT
   (VARCHAR(MAX), 'DECLARE @blocker BIT; '
                    + 'SET @blocker = 0; ' + 'DECLARE @i INT; '
                    + 'SET @i = 2147483647; ' + ''
                    + 'DECLARE @sessions TABLE ' + '( '
                    + 'session_id SMALLINT NOT NULL, '
                    + 'kpid SMALLINT NOT NULL, ' + 'ecid SMALLINT NOT NULL, '
                    + 'request_id INT, ' + 'login_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 = 1
                                OR @find_block_leaders = 1
                           THEN 'wait_type NVARCHAR(32), '
                                + 'wait_resource NVARCHAR(256), '
                                + 'wait_time BIGINT, '
                           ELSE ''
                      END + 'blocked SMALLINT, '
                    + 'UNIQUE CLUSTERED (session_id, kpid, ecid)  WITH (IGNORE_DUP_KEY = ON) '
                    + '); ' + '' + 'DECLARE @blockers TABLE ' + '( '
                    + 'session_id INT NOT NULL PRIMARY KEY ' + '); ' + ''
                    + 'BLOCKERS:; ' + '' + 'INSERT @sessions ' + '( '
                    + 'session_id, ' + 'kpid, ' + 'ecid, ' + 'request_id, '
                    + 'login_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 = 1
                                OR @find_block_leaders = 1
                           THEN 'wait_type, ' + 'wait_resource, '
                                + 'wait_time, '
                           ELSE ''
                      END + 'blocked ' + ') ' + 'SELECT TOP(@i) '
                    + 'sp0.session_id, ' + 'sp0.kpid, ' + 'sp0.ecid, '
                    + 'sp0.request_id, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.login_time ' + 'ELSE NULL '
                    + 'END AS login_time, ' + 'LOWER(sp0.status) AS status, '
                    + 'CASE sp0.ecid ' + 'WHEN 0 THEN ' + 'CASE '
                    + 'WHEN sp0.cmd = ''CREATE INDEX'' THEN 0 '
                    + 'ELSE sp0.stmt_start ' + 'END ' + 'ELSE NULL '
                    + 'END AS statement_start_offset, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN ' + 'CASE '
                    + 'WHEN sp0.cmd = ''CREATE INDEX'' THEN -1 '
                    + 'ELSE COALESCE(NULLIF(sp0.stmt_end, 0), -1) ' + 'END '
                    + 'ELSE NULL ' + 'END AS statement_end_offset, '
                    + 'CASE sp0.ecid ' + 'WHEN 0 THEN sp0.sql_handle '
                    + 'ELSE NULL ' + 'END AS sql_handle, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.host_name ' + 'ELSE NULL '
                    + 'END AS host_name, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.login_name ' + 'ELSE NULL '
                    + 'END AS login_name, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.program_name ' + 'ELSE NULL '
                    + 'END AS program_name, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.database_id ' + 'ELSE NULL '
                    + 'END AS database_id, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.memory_usage ' + 'ELSE NULL '
                    + 'END AS memory_usage, ' + 'CASE sp0.ecid '
                    + 'WHEN 0 THEN sp0.open_tran_count ' + 'ELSE NULL '
                    + 'END AS open_tran_count, '
                    + CASE WHEN @get_task_info = 1
                                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 ' + 'FROM ' + '( '
                    + 'SELECT TOP(@i) ' + 'sp1.spid AS session_id, '
                    + 'sp1.kpid, ' + 'sp1.ecid, ' + 'CASE sp1.status '
                    + 'WHEN ''sleeping'' THEN CONVERT(INT, NULL) '
                    + 'ELSE sp1.request_id ' + 'END AS request_id, '
                    + 'sp1.login_time, '
                    + 'CONVERT(VARCHAR(30), RTRIM(sp1.status)) COLLATE Latin1_General_Bin2 AS status, '
                    + 'CONVERT(VARCHAR(16), RTRIM(sp1.cmd)) COLLATE Latin1_General_Bin2 AS cmd, '
                    + 'sp1.stmt_start, ' + 'sp1.stmt_end, '
                    + 'sp1.sql_handle, '
                    + 'CONVERT(sysname, RTRIM(sp1.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS AS host_name, '
                    + CASE WHEN @filter_type = 'login'
                                OR @not_filter_type = 'login'
                           THEN 'MAX(CONVERT(sysname, RTRIM(sp1.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) OVER (PARTITION BY sp1.spid, sp1.request_id) AS login_name, '
                           ELSE 'CONVERT(sysname, RTRIM(sp1.loginame)) AS login_name, '
                      END
                    + 'CONVERT(sysname, RTRIM(sp1.program_name)) COLLATE SQL_Latin1_General_CP1_CI_AS AS program_name, '
                    + 'sp1.dbid AS database_id, '
                    + 'sp1.memusage AS memory_usage, '
                    + 'sp1.open_tran AS open_tran_count, '
                    + 'RTRIM(sp1.lastwaittype) AS wait_type, '
                    + 'RTRIM(sp1.waitresource) AS wait_resource, '
                    + 'sp1.waittime AS wait_time, '
                    + 'COALESCE(NULLIF(sp1.blocked, sp1.spid), 0) AS blocked, '
                    + 'sp1.hostprocess ' + 'FROM ' + '( ' + 'SELECT TOP(@i) '
                    + 'session_id ' + 'FROM @blockers ' + '' + 'UNION ALL '
                    + '' + 'SELECT TOP(@i) ' + '0 ' + 'WHERE '
                    + '@blocker = 0 ' + ') AS blk (session_id) '
                    + 'INNER LOOP JOIN sys.sysprocesses AS sp1 ON '
                    + 'sp1.spid = blk.session_id ' + 'OR @blocker = 0 '
                    + CASE WHEN ( @get_task_info = 0
                                  AND @find_block_leaders = 0
                                ) THEN 'WHERE ' + 'sp1.ecid = 0 '
                           ELSE ''
                      END + ') 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 + '); ' + CASE @recursion
                                            WHEN 1
                                            THEN 'IF @blocker = 0 '
                                                 + 'BEGIN; '
                                                 + 'INSERT @blockers ' + '( '
                                                 + 'session_id ' + ') '
                                                 + 'SELECT TOP(@i) '
                                                 + 'blocked '
                                                 + 'FROM @sessions ' + ''
                                                 + 'EXCEPT ' + ''
                                                 + 'SELECT TOP(@i) '
                                                 + 'session_id '
                                                 + 'FROM @sessions; ' + ''
                                                 + 'IF @@ROWCOUNT > 0 '
                                                 + 'BEGIN; '
                                                 + 'SET @blocker = 1; '
                                                 + 'GOTO BLOCKERS; ' + '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🇲🇲ss.mss|]%'
                                ESCAPE '|' THEN 'x.elapsed_time '
                           ELSE '0 '
                      END + 'AS elapsed_time, '
                    + CASE WHEN ( @output_column_list LIKE '%|[dd hh🇲🇲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 '%|[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 'x.wait_info '
                           ELSE 'NULL '
                      END + 'AS wait_info, '
                    + CASE WHEN @output_column_list LIKE '%|[tran_start_time|]%'
                                ESCAPE '|'
                                AND @recursion = 1
                           THEN 'CONVERT ' + '( ' + 'DATETIME, ' + 'LEFT '
                                + '( ' + 'x.tran_log_writes, '
                                + 'NULLIF(CHARINDEX(NCHAR(254), x.tran_log_writes) - 1, -1) '
                                + ') ' + ') '
                           ELSE 'NULL '
                      END + 'AS tran_start_time, '
                    + CASE WHEN @output_column_list LIKE '%|[tran_log_writes|]%'
                                ESCAPE '|'
                                AND @recursion = 1
                           THEN 'RIGHT ' + '( ' + 'x.tran_log_writes, '
                                + 'LEN(x.tran_log_writes) - CHARINDEX(NCHAR(254), x.tran_log_writes) '
                                + ') '
                           ELSE 'NULL '
                      END + 'AS tran_log_writes, '
                    + 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) ' + 'text_size, '
                                + 'language, ' + 'date_format, '
                                + 'date_first, ' + 'CASE quoted_identifier '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS quoted_identifier, '
                                + 'CASE arithabort ' + 'WHEN 0 THEN ''OFF'' '
                                + 'ELSE ''ON'' ' + 'END AS arithabort, '
                                + 'CASE ansi_null_dflt_on '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS ansi_null_dflt_on, '
                                + 'CASE ansi_defaults '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS ansi_defaults, '
                                + 'CASE ansi_warnings '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS ansi_warnings, '
                                + 'CASE ansi_padding '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS ansi_padding, ' + 'CASE ansi_nulls '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS ansi_nulls, '
                                + 'CASE concat_null_yields_null '
                                + 'WHEN 0 THEN ''OFF'' ' + 'ELSE ''ON'' '
                                + 'END AS concat_null_yields_null, '
                                + 'CASE 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, '
                                + 'lock_timeout, ' + 'deadlock_priority, '
                                + 'row_count, ' + 'original_login_name '
                                + 'FOR XML ' + 'PATH(''additional_info''), '
                                + 'TYPE ' + ') '
                           ELSE 'NULL '
                      END + 'AS additional_info, ' + 'x.start_time, '
                    + 'x.last_request_start_time '--End column list
   ) + --Derived table "y"
   CONVERT
   (VARCHAR(MAX), 'FROM ' + '( ' + 'SELECT TOP(@i) ' + 'y.*, '
                    + 'CASE '
                    + --if there are more than 24 days, return a negative number of seconds rather than
       --positive milliseconds, in order to avoid overflow errors
                    'WHEN DATEDIFF(day, y.start_time, GETDATE()) > 24 THEN '
                    + 'DATEDIFF(second, GETDATE(), y.start_time) '
                    + 'ELSE DATEDIFF(ms, y.start_time, GETDATE()) '
                    + 'END AS elapsed_time, ' + 'tasks.physical_io, '
                    + '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, ' + 'tasks.context_switches, '
                    + 'tasks.tasks, ' + 'tasks.wait_info, '
                    + 'tasks.blocking_session_id, '
                    + 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, ' + 'sp.sql_handle, '
                    + 'r.sql_handle AS request_sql_handle, '
                    + 'sp.statement_start_offset, '
                    + 'sp.statement_end_offset, ' + 'r.plan_handle, '
                    + 'NULLIF(r.percent_complete, 0) AS percent_complete, '
                    + 'sp.host_name, ' + 'sp.login_name, '
                    + 'sp.program_name, '
                    + '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, '
                    + 's.original_login_name, '
                    + 'COALESCE(r.start_time, sp.login_time) AS start_time, '
                    + 'COALESCE(r.start_time, s.last_request_start_time) AS last_request_start_time, '
                    + 'r.transaction_id, ' + 'sp.database_id, '
                    + 'sp.open_tran_count, ' + '( '
                    + CASE WHEN NOT ( @get_transaction_info = 1
                                      AND @recursion = 1
                                    )
                           THEN 'SELECT CONVERT(NVARCHAR(4000), NULL) '
                           ELSE CONVERT
          (VARCHAR(MAX), '( '
                                + 'SELECT TOP(@i) ' + '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 [text()] ' + 'FROM ' + '( '
                                + 'SELECT TOP(@i) ' + 'trans.*, '
                                + 'ROW_NUMBER() OVER (ORDER BY trans.transaction_start_time DESC) AS r '
                                + 'FROM ' + '( ' + 'SELECT TOP(@i) '
                                + '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_database_transactions '
                                + ') AS s_tran ' + 'LEFT OUTER JOIN ' + '( '
                                + 'SELECT TOP(@i) ' + '* '
                                + 'FROM sys.dm_tran_session_transactions '
                                + ') AS tst ON '
                                + 's_tran.transaction_id = tst.transaction_id '
                                + 'AND s_tran.database_id < 32767 ' + 'WHERE '
                                + 's_tran.transaction_id = r.transaction_id '
                                + 'OR ' + '( '
                                + 'COALESCE(sp.request_id, 0) = 0 '
                                + 'AND sp.session_id = tst.session_id ' + ') '
                                + 'GROUP BY ' + 's_tran.database_id '
                                + ') AS trans ' + ') AS u_trans '
                                + 'FOR XML PATH(''''), TYPE '
                                + ').value(''.'', ''NVARCHAR(MAX)'') ')
                      END
                    + ') COLLATE Latin1_General_Bin2 AS tran_log_writes '
                    + 'FROM @sessions AS sp '
                    + '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 '
                    + 'LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON '
                    + 's.session_id = sp.session_id '
                    + 'AND r.request_id IS NULL ' + 'WHERE ' + 'sp.ecid = 0 '
                    + 'AND ' + '( ' + '( ' + 'COALESCE(sp.request_id, 0) = 0 '
                    + 'AND ' + '( ' + 'r.request_id IS NOT NULL '
                    + 'OR s.session_id IS NOT NULL ' + ') ' + ') ' + 'OR '
                    + '( ' + 'COALESCE(sp.request_id, 0) > 0 '
                    + 'AND r.request_id IS NOT NULL ' + ') ' + ') '
                    + ') AS y '--End derived table "y"
    ) + --Derived table "x"
    CONVERT
    (VARCHAR(MAX), CASE WHEN ( @get_task_info = 0
                                                   AND @find_block_leaders = 0
                                                 )
                                            THEN 'CROSS JOIN ' + '( '
                                                 + 'SELECT TOP(@i) '
                                                 + 'CONVERT(BIGINT, NULL) AS physical_io, '
                                                 + 'CONVERT(BIGINT, NULL) AS context_switches, '
                                                 + 'CONVERT(INT, NULL) AS tasks, '
                                                 + 'CONVERT(SMALLINT, NULL) AS blocking_session_id, '
                                                 + 'CONVERT(NVARCHAR(4000), NULL) AS wait_info '
                                                 + ') AS tasks '
                                            WHEN @get_task_info = 2
                                            THEN '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(''(blocking_session_id/text())[1]'', ''SMALLINT'') AS blocking_session_id, '
                                                 + 'task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info '
                                                 + '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.tasks '
                                                 + 'ELSE NULL '
                                                 + 'END AS [tasks], '
                                                 + 'CASE waits.r '
                                                 + 'WHEN 1 THEN waits.blocking_session_id '
                                                 + 'ELSE NULL '
                                                 + 'END AS [blocking_session_id], '
                                                 + '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.blocking_session_id 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.num_tasks AS tasks, '
                                                 + 'wt2.wait_type, '
                                                 + 'NULLIF(COUNT(wt2.waiting_task_address), 0) AS num_waits, '
                                                 + 'MIN(wt2.wait_duration_ms) AS min_wait_time, '
                                                 + 'AVG(wt2.wait_duration_ms) AS avg_wait_time, '
                                                 + 'MAX(wt2.wait_duration_ms) AS max_wait_time, '
                                                 + 'MAX(wt2.blocking_session_id) AS blocking_session_id '
                                                 + 'FROM ' + '( '
                                                 + 'SELECT TOP(@i) '
                                                 + 'sp2.session_id, '
                                                 + 'sp2.request_id, '
                                                 + 'SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY sp2.session_id, sp2.request_id) AS physical_io, '
                                                 + 'SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY sp2.session_id, sp2.request_id) AS context_switches, '
                                                 + 'COUNT(*) OVER (PARTITION BY sp2.session_id, sp2.request_id) AS num_tasks, '
                                                 + 't.task_address, '
                                                 + 't.task_state '
                                                 + 'FROM sys.dm_os_tasks AS t '
                                                 + 'INNER LOOP JOIN @sessions AS sp2 ON '
                                                 + 'sp2.session_id = t.session_id '
                                                 + 'AND sp2.status <> ''sleeping'' '
                                                 + 'INNER HASH JOIN ' + '( '
                                                 + 'SELECT TOP(@i) ' + '* '
                                                 + 'FROM sys.dm_os_threads '
                                                 + ') AS th ON '
                                                 + 'th.os_thread_id = sp2.kpid '
                                                 + 'INNER HASH JOIN ' + '( '
                                                 + 'SELECT TOP(@i) ' + '* '
                                                 + 'FROM sys.dm_os_workers '
                                                 + ') AS w ON '
                                                 + 'w.thread_address = th.thread_address '
                                                 + 'AND w.worker_address = t.worker_address '
                                                 + ') 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.blocking_session_id) AS blocking_session_id '
                                                 + 'FROM ' + '( '
                                                 + 'SELECT DISTINCT TOP(@i) '
                                                 + 'wt.wait_type + '
                                                 + --TODO: What else can be pulled from the resource_description?
                                                 'CASE '
                                                 + 'WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN '
                                                 + ''':'' + '
                                                 + --database name
                                                 'COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') + '
                                                 + 'N'':'' + '
                                                 + --file id
                                                 '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)) + '
                                                 + --page # for special pages
                                                 '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)) % 511233 = 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)) % 511238 = 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)) % 511239 = 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, '
                                                 + 'wt.wait_duration_ms, '
                                                 + 'wt.waiting_task_address, '
                                                 + 'wt.blocking_session_id '
                                                 + '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 <> wt0.session_id 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.session_id '
                                                 + 'FROM @sessions AS s0 '
                                                 + 'WHERE '
                                                 + 's0.session_id = wt0.session_id '
                                                 + ') AS p ' + ') AS wt '
                                                 + ') AS wt1 ' + 'GROUP BY '
                                                 + 'wt1.wait_type, '
                                                 + 'wt1.waiting_task_address '
                                                 + '' + 'UNION ALL ' + ''
                                                 + 'SELECT TOP(@i) '
                                                 + 'state, '
                                                 + 'task_address, ' + '( '
                                                 + 'SELECT TOP(@i) '
                                                 + 'ms_ticks '
                                                 + 'FROM sys.dm_os_sys_info '
                                                 + ') - '
                                                 + 'wait_resumed_ms_ticks, '
                                                 + 'NULL '
                                                 + 'FROM sys.dm_os_workers '
                                                 + 'WHERE '
                                                 + 'state = ''RUNNABLE'' '
                                                 + ') AS wt2 ON '
                                                 + 'wt2.waiting_task_address = task_info.task_address '
                                                 + 'AND wt2.wait_duration_ms > 0 '
                                                 + 'AND ' + '( '
                                                 + 'task_info.task_state <> ''RUNNABLE'' '
                                                 + 'OR ' + '( '
                                                 + 'task_info.task_state = ''RUNNABLE'' '
                                                 + 'AND wt2.wait_type = ''RUNNABLE'' '
                                                 + ') ' + ') ' + 'GROUP BY '
                                                 + 'task_info.session_id, '
                                                 + 'task_info.request_id, '
                                                 + 'task_info.physical_io, '
                                                 + 'task_info.context_switches, '
                                                 + 'task_info.num_tasks, '
                                                 + 'wt2.wait_type '
                                                 + ') 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 --@get_task_info = 1 OR @find_block_leaders = 1
                                                 'LEFT OUTER HASH JOIN '
                                                 + '( ' + 'SELECT TOP(@i) '
                                                 + 'w1.session_id, '
                                                 + 'w1.request_id, '
                                                 + 'w1.blocking_session_id, '
                                                 + 'N''('' + CONVERT(NVARCHAR, w1.wait_duration_ms) + N''ms)'' + '
                                                 + 'w1.wait_type + '
                                                 + --TODO: What else can be pulled from the resource_description?
                                                 'CASE '
                                                 + 'WHEN w1.wait_type LIKE N''PAGE%LATCH_%'' THEN '
                                                 + 'N'':'' + '
                                                 + --database name
                                                 'COALESCE(DB_NAME(CONVERT(INT, LEFT(w1.resource_description, CHARINDEX(N'':'', w1.resource_description) - 1))), N''(null)'') + '
                                                 + 'N'':'' + '
                                                 + --file id
                                                 'SUBSTRING(w1.resource_description, CHARINDEX(N'':'', w1.resource_description) + 1, LEN(w1.resource_description) - CHARINDEX(N'':'', REVERSE(w1.resource_description)) - CHARINDEX(N'':'', w1.resource_description)) + '
                                                 + --page # for special pages
                                                 'N''('' + ' + 'CASE '
                                                 + 'WHEN '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) = 1 OR '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) % 8088 = 0 THEN N''PFS'' '
                                                 + 'WHEN '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) = 2 OR '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) % 511232 = 0 THEN N''GAM'' '
                                                 + 'WHEN '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) = 3 OR '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) % 511233 = 0 THEN N''SGAM'' '
                                                 + 'WHEN '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) = 6 OR '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) % 511238 = 0 THEN N''DCM'' '
                                                 + 'WHEN '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) = 7 OR '
                                                 + 'CONVERT(INT, RIGHT(w1.resource_description, CHARINDEX(N'':'', REVERSE(w1.resource_description)) - 1)) % 511239 = 0 THEN N''BCM'' '
                                                 + 'ELSE N''*'' ' + 'END + '
                                                 + 'N'')'' '
                                                 + 'WHEN w1.wait_type = N''CXPACKET'' THEN '
                                                 + 'N'':'' + SUBSTRING(w1.resource_description, CHARINDEX(N''nodeId'', w1.resource_description) + 7, 4)'
                                                 + 'WHEN w1.wait_type LIKE N''LATCH[_]%'' THEN '
                                                 + 'N'' ['' + LEFT(w1.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', w1.resource_description), 0), LEN(w1.resource_description) + 1) - 1) + N'']'' '
                                                 + 'ELSE N'''' '
                                                 + 'END COLLATE Latin1_General_Bin2 AS wait_info, '
                                                 + 'CONVERT(BIGINT, NULL) AS physical_io, '
                                                 + 'CONVERT(BIGINT, NULL) AS context_switches, '
                                                 + 'CONVERT(INT, NULL) AS tasks '
                                                 + 'FROM ' + '( '
                                                 + 'SELECT TOP(@i) '
                                                 + 'sp2.session_id, '
                                                 + 'sp2.request_id, '
                                                 + 'sp2.wait_type COLLATE Latin1_General_Bin2 AS wait_type, '
                                                 + 'sp2.wait_resource COLLATE Latin1_General_Bin2 AS resource_description, '
                                                 + 'sp2.wait_time AS wait_duration_ms, '
                                                 + 'NULLIF(sp2.blocked, 0) AS blocking_session_id, '
                                                 + 'ROW_NUMBER() OVER ' + '( '
                                                 + 'PARTITION BY sp2.session_id, sp2.request_id '
                                                 + 'ORDER BY sp2.blocked DESC, sp2.wait_time DESC '
                                                 + ') AS r '
                                                 + 'FROM @sessions AS sp2 '
                                                 + ') AS w1 ' + 'WHERE '
                                                 + 'w1.r = 1 '
                                                 + ') AS tasks ON '
                                                 + 'tasks.session_id = y.session_id '
                                                 + 'AND tasks.request_id = y.request_id '
                                       END + CONVERT
     (VARCHAR(MAX), 'LEFT OUTER HASH JOIN ' + '( '
                    + 'SELECT TOP(@i) ' + 't_info.session_id, '
                    + 't_info.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, '
                    + 't_info.request_id ' + ') AS tempdb_info ON '
                    + 'tempdb_info.session_id = y.session_id '
                    + 'AND COALESCE(tempdb_info.request_id, -1) = COALESCE(y.request_id, -1) ')
                    + CASE WHEN NOT ( @get_avg_time = 1
                                      AND @recursion = 1
                                    ) THEN CONVERT(VARCHAR(MAX), '')
                           ELSE CONVERT(VARCHAR(MAX), '')
                                + 'LEFT OUTER HASH JOIN ' + '( '
                                + 'SELECT TOP(@i) ' + '* '
                                + 'FROM sys.dm_exec_query_stats '
                                + ') AS qs ON '
                                + 'qs.sql_handle = y.request_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)); '--End derived table "x"
   ) ;

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

                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 ,
                          context_switches ,
                          used_memory ,
                          tasks ,
                          status ,
                          wait_info ,
                          tran_start_time ,
                          tran_log_writes ,
                          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 ,
                          last_request_start_time
              )
                        EXEC sp_executesql @sql_n,
                            N'@recursion SMALLINT, @filter sysname, @not_filter sysname',
                            @recursion, @filter, @not_filter ;

  --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 ;

                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
                        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 COALESCE(s.request_id, -1) = COALESCE(@request_id,
                                                              -1)
                                            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 COALESCE(s.request_id, -1) = COALESCE(@request_id,
                                                              -1)
                                            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 ( COALESCE(s.request_id,
                                                              0) = 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 FORWARD_ONLY DYNAMIC OPTIMISTIC
                        FOR
                            SELECT  plan_handle ,
                                    statement_start_offset ,
                                    statement_end_offset
                            FROM    #sessions
                            WHERE   recursion = 1 FOR UPDATE OF
    query_plan OPTION ( KEEPFIXED PLAN ) ;

                        OPEN plan_cursor ;

                        FETCH NEXT FROM plan_cursor
   INTO @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,
                                                              @statement_start_offset,
                                                              @statement_end_offset)
                                                             WHERE
                                                              @get_plans = 1
                                                             UNION ALL
                                                             SELECT
                                                              query_plan
                                                             FROM
                                                              sys.dm_exec_query_plan(@plan_handle)
                                                             WHERE
                                                              @get_plans = 2
                                                           )
                                    FROM    #sessions AS s
                                    WHERE
      CURRENT OF plan_cursor
                                    OPTION  ( KEEPFIXED PLAN ) ;
                                END TRY
                                BEGIN CATCH;
                                    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
      CURRENT OF plan_cursor
                                    OPTION  ( KEEPFIXED PLAN ) ;
                                END CATCH ;

                                FETCH NEXT FROM plan_cursor
    INTO @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 @DB_NAME SYSNAME ;

                        DECLARE locks_cursor CURSOR LOCAL FAST_FORWARD
                        FOR
                            SELECT DISTINCT
                                    db_name
                            FROM    #locks
                            WHERE   EXISTS ( SELECT *
                                             FROM   #sessions AS s
                                             WHERE  s.session_id = #locks.session_id
                                                    AND recursion = 1 )
                                    AND db_name <> '(null)'
                        OPTION  ( KEEPFIXED PLAN ) ;

                        OPEN locks_cursor ;

                        FETCH NEXT  FROM locks_cursor
   INTO @DB_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(@DB_NAME)
                                        + '.sys.allocation_units AS au ON '
                                        + 'au.allocation_unit_id = l.allocation_unit_id '
                                        + 'LEFT OUTER JOIN '
                                        + QUOTENAME(@DB_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(@DB_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(@DB_NAME)
                                        + '.sys.objects AS o ON '
                                        + 'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) '
                                        + 'LEFT OUTER JOIN '
                                        + QUOTENAME(@DB_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(@DB_NAME)
                                        + '.sys.schemas AS s ON '
                                        + 's.schema_id = COALESCE(l.schema_id, o.schema_id) '
                                        + 'LEFT OUTER JOIN '
                                        + QUOTENAME(@DB_NAME)
                                        + '.sys.database_principals AS dp ON '
                                        + 'dp.principal_id = l.principal_id '
                                        + 'WHERE ' + 'l.db_name = @DB_NAME '
                                        + 'OPTION (KEEPFIXED PLAN); ' ;
     
                                    EXEC sp_executesql @sql_n,
                                        N'@DB_NAME sysname', @DB_NAME ;
                                END TRY
                                BEGIN CATCH;
                                    UPDATE  #locks
                                    SET     object_name = '(db_inaccessible)'
                                    WHERE   db_name = @DB_NAME
                                    OPTION  ( KEEPFIXED PLAN ) ;
                                END CATCH ;

                                FETCH NEXT  FROM locks_cursor
    INTO @DB_NAME ;
                            END ;

                        CLOSE locks_cursor ;
                        DEALLOCATE locks_cursor ;

                        CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, db_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.db_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] ,
                                                    ( 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.db_name = l1.db_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.db_name = l4.db_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.db_name = l1.db_name
                                                              AND l3.resource_type <> 'DATABASE'
                                                      GROUP BY l3.session_id ,
                                                              l3.request_id ,
                                                              l3.db_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 = COALESCE(s.request_id,
                                                              -1)
                                                    AND ( ( s.request_id IS NULL
                                                            AND l1.start_time = s.start_time
                                                          )
                                                          OR ( s.request_id IS NOT NULL
                                                              AND l1.start_time = s.last_request_start_time
                                                             )
                                                        )
                                                    AND s.recursion = 1
                                            GROUP BY l1.session_id ,
                                                    l1.request_id ,
                                                    l1.db_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
                                       FROM     #sessions
                                       WHERE    recursion = 1
                                       UNION ALL
                                       SELECT   s.session_id ,
                                                b.top_level_session_id
                                       FROM     blockers AS b
                                                JOIN #sessions AS s ON s.blocking_session_id = b.session_id
                                                              AND s.recursion = 1
                                     )
                            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 @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🇲🇲ss.mss]
     CASE @format_output
                                WHEN 1
                                THEN 'CASE ' + 'WHEN elapsed_time < 0 THEN '
                                     + 'RIGHT ' + '( '
                                     + '''00'' + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), '
                                     + '2 ' + ') + ' + 'RIGHT ' + '( '
                                     + 'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), '
                                     + '9 ' + ') + ' + '''.000'' ' + 'ELSE '
                                     + 'RIGHT ' + '( '
                                     + '''00'' + CONVERT(VARCHAR, elapsed_time / 86400000), '
                                     + '2 ' + ') + ' + 'RIGHT ' + '( '
                                     + 'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), '
                                     + '9 ' + ') + ' + '''.'' + '
                                     + 'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) '
                                     + 'END AS [dd hh🇲🇲ss.mss], '
                                ELSE ''
                              END
            + --[dd hh🇲🇲ss.mss (avg)] / avg_elapsed_time
     CASE @format_output
                                WHEN 1
                                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🇲🇲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, '
            + --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 ' + 'AND CPU_delta >= 0 ' + 'THEN '
            + CASE @format_output
                WHEN 1
                THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, 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 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, '
            + --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, ' + 'request_id, '
            + 'GETDATE() AS collection_time '--End inner column list
  ) + --Derived table and INSERT specification
  CONVERT
  (VARCHAR(MAX), 'FROM ' + '( ' + 'SELECT TOP(2147483647) ' + '*, '
            + '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(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, '
            + '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


--EXEC dbo.sp_WhoIsActive


原文链接:http://blog.csdn.net/yangzhawen/article/details/7353116
加载中
返回顶部
顶部