SELECT sc.[name] AS [schema]    ,o.[name] AS [table_name]    ,o.type_desc    ,obd.[file_id]    ,obd.page_id    ,obd.page_level    ,obd.row_count    ,obd.free_space_in_bytes    ,obd.is_modified    ,obd.numa_nodeFROM sys.dm_os_buffer_descriptors AS obdJOIN sys.allocation_units AS au ON obd.allocation_unit_id = au.allocation_unit_idJOIN sys.partitions AS p ON au.container_id = p.partition_idJOIN sys.objects AS o ON p.[object_id] = o.[object_id]JOIN sys.schemas AS sc ON o.[schema_id] = sc.[schema_id]WHERE database_id = DB_ID()    AND o.is_ms_shipped = 0ORDER BY obd.page_id    ,o.[name]

SELECT TOP (100) qs.execution_count    ,qs.total_rows    ,qs.last_rows    ,qs.min_rows    ,qs.max_rows    ,qs.last_elapsed_time    ,qs.min_elapsed_time    ,qs.max_elapsed_time    ,total_worker_time    ,total_logical_reads    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (            CASE                 WHEN qs.statement_end_offset = - 1                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2                ELSE qs.statement_end_offset                END - qs.statement_start_offset            ) / 2) AS query_textFROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.execution_count DESCOPTION (RECOMPILE);

SELECT TOP (100) qs.execution_count    ,qs.last_elapsed_time    ,qs.min_elapsed_time    ,qs.max_elapsed_time    ,total_worker_time    ,total_logical_reads    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (            CASE                 WHEN qs.statement_end_offset = - 1                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2                ELSE qs.statement_end_offset                END - qs.statement_start_offset            ) / 2) AS query_textFROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.execution_count DESCOPTION (RECOMPILE);

SELECT TOP (50) [text] AS [QueryText]    ,cp.cacheobjtype    ,cp.objtype    ,cp.size_in_bytes / 1024 AS [Plan Size in KB]FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE cp.cacheobjtype = N‘Compiled Plan‘    AND cp.objtype IN (        N‘Adhoc‘        ,N‘Prepared‘        )    AND cp.usecounts = 1ORDER BY cp.size_in_bytes DESCOPTION (RECOMPILE);

SELECT dm_ws.wait_duration_ms    ,dm_ws.wait_type    ,dm_es.STATUS    ,dm_t.TEXT    ,dm_qp.query_plan    ,dm_ws.session_ID    ,dm_es.cpu_time    ,dm_es.memory_usage    ,dm_es.logical_reads    ,dm_es.total_elapsed_time    ,dm_es.program_name    ,DB_NAME(dm_r.database_id) DatabaseName    ,dm_ws.blocking_session_id    ,dm_r.wait_resource    ,dm_es.login_name    ,dm_r.command    ,dm_r.last_wait_typeFROM sys.dm_os_waiting_tasks dm_wsINNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_idINNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_idCROSS APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_tCROSS APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qpWHERE dm_es.is_user_process = 1;

CREATE TABLE #sp_who2 (    SPID INT    ,STATUS VARCHAR(255)    ,LOGIN VARCHAR(255)    ,HostName VARCHAR(255)    ,BlkBy VARCHAR(255)    ,DBName VARCHAR(255)    ,Command VARCHAR(255)    ,CPUTime INT    ,DiskIO INT    ,LastBatch VARCHAR(255)    ,ProgramName VARCHAR(255)    ,SPID2 INT    ,REQUESTID INT    )INSERT INTO #sp_who2EXEC sp_who2SELECT *FROM #sp_who2 w--WHERE w.ProgramName = ‘xxx‘DROP TABLE #sp_who2

SELECT spid    ,a.[status]    ,hostname    ,program_name    ,cmd    ,cpu    ,physical_io    ,blocked    ,b.[name]    ,loginameFROM master.dbo.sysprocesses aINNER JOIN master.dbo.sysdatabases b ON a.dbid = b.dbidwhere hostname != ‘‘ORDER BY program_name

DECLARE @current_sql_handle BINARY (20);DECLARE @sql_text_list TABLE (    sql_handle BINARY (20)    ,TEXT NVARCHAR(max)    );DECLARE sql_handle_cursor CURSORFORSELECT sp.sql_handleFROM sys.sysprocesses spWHERE sp.sql_handle != 0x0000000000000000000000000000000000000000    --AND sp.program_name = ‘xxxx‘    ;OPEN sql_handle_cursorFETCH NEXTFROM sql_handle_cursorINTO @current_sql_handleWHILE @@FETCH_STATUS = 0BEGIN    INSERT INTO @sql_text_list (        sql_handle        ,TEXT        )    SELECT @current_sql_handle        ,est.TEXT    FROM sys.dm_exec_query_stats qs    CROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est;    FETCH NEXT    FROM sql_handle_cursor    INTO @current_sql_handleENDSELECT DISTINCT *FROM @sql_text_list tlWHERE tl.TEXT NOT LIKE ‘%statement_start_offset%‘;CLOSE sql_handle_cursorDEALLOCATE sql_handle_cursor

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,last_execution_time    ,total_elapsed_time / execution_count avg_elapsed_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,execution_count    ,total_worker_time    ,total_elapsed_time    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())    AND st.TEXT NOT LIKE ‘%statement_start_offset%‘    AND total_elapsed_time / execution_count >= 300ORDER BY last_execution_time DESC;

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,last_execution_time    ,total_elapsed_time / execution_count avg_elapsed_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,execution_count    ,total_worker_time    ,total_elapsed_time    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())    AND st.TEXT NOT LIKE ‘%statement_start_offset%‘    AND execution_count < 100    AND total_elapsed_time / execution_count > 100    AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) NOT LIKE ‘SELECT%‘ORDER BY last_execution_time DESC;

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,total_elapsed_time / execution_count / 1000 AS avg_elapsed_time_by_ms    ,last_execution_time    ,total_elapsed_time    ,execution_count    ,total_worker_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE qs.execution_count > 1000    AND last_execution_time > DATEADD(SECOND, - 60, GETDATE())    --AND (    --    st.TEXT LIKE ‘%[[]AAA]%‘    --    OR st.TEXT LIKE ‘%[[]BBB]%‘    --    OR st.TEXT LIKE ‘%[[]CCC]%‘    --    )ORDER BY total_elapsed_time / execution_count DESC;

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

USE masterGOIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ‘sp_WhoIsActive‘)    EXEC (‘CREATE PROC dbo.sp_WhoIsActive AS SELECT ‘‘stub version, to be replaced‘‘‘)GO/*********************************************************************************************Who Is Active? v11.11 (2012-03-22)(C) 2007-2012, Adam MachanicFeedback: mailto:amachanic@gmail.comUpdates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx"Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspxDonate! Support this project: http://tinyurl.com/WhoIsActiveDonateLicense:     Who is Active? is free to download and use for personal, educational, and internal     corporate purposes, provided that this header is preserved. Redistribution or sale     of Who is Active?, in whole or in part, is prohibited without the author‘s express     written consent.*********************************************************************************************/ALTER PROC dbo.sp_WhoIsActive(--~    --Filters--Both inclusive and exclusive    --Set either filter to ‘‘ to disable    --Valid filter types are: session, program, database, login, and host    --Session is a session ID, and either 0 or ‘‘ can be used to indicate "all" sessions    --All other filter types support % or _ as wildcards    @filter sysname = ‘‘,    @filter_type VARCHAR(10) = ‘session‘,    @not_filter sysname = ‘‘,    @not_filter_type VARCHAR(10) = ‘session‘,    --Retrieve data about the calling session?    @show_own_spid BIT = 0,    --Retrieve data about system sessions?    @show_system_spids BIT = 0,    --Controls how sleeping SPIDs are handled, based on the idea of levels of interest    --0 does not pull any sleeping SPIDs    --1 pulls only those sleeping SPIDs that also have an open transaction    --2 pulls all sleeping SPIDs    @show_sleeping_spids TINYINT = 1,    --If 1, gets the full stored procedure or running batch, when available    --If 0, gets only the actual statement that is currently running in the batch or procedure    @get_full_inner_text BIT = 0,    --Get associated query plans for running tasks, if available    --If @get_plans = 1, gets the plan based on the request‘s statement offset    --If @get_plans = 2, gets the entire plan based on the request‘s plan_handle    @get_plans TINYINT = 0,    --Get the associated outer ad hoc query or stored procedure call, if available    @get_outer_command BIT = 0,    --Enables pulling transaction log write info and transaction duration    @get_transaction_info BIT = 0,    --Get information on active tasks, based on three interest levels    --Level 0 does not pull any task-related information    --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers    --Level 2 pulls all available task-based metrics, including:     --number of active tasks, current wait stats, physical I/O, context switches, and blocker information    @get_task_info TINYINT = 1,    --Gets associated locks for each request, aggregated in an XML format    @get_locks BIT = 0,    --Get average time for past runs of an active query    --(based on the combination of plan handle, sql handle, and offset)    @get_avg_time BIT = 0,    --Get additional non-performance-related information about the session or request    --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,     --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,     --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type    --    --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of    --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)    --    --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be    --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,     --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name    @get_additional_info BIT = 0,    --Walk the blocking chain and count the number of     --total SPIDs blocked all the way down by a given session    --Also enables task_info Level 1, if @get_task_info is set to 0    @find_block_leaders BIT = 0,    --Pull deltas on various metrics    --Interval in seconds to wait before doing the second data pull    @delta_interval TINYINT = 0,    --List of desired output columns, in desired order    --Note that the final output will be the intersection of all enabled features and all     --columns in the list. Therefore, only columns associated with enabled features will     --actually appear in the output. Likewise, removing columns from this list may effectively    --disable features, even if they are turned on    --    --Each element in this list must be one of the valid output column names. Names must be    --delimited by square brackets. White space, formatting, and additional characters are    --allowed, as long as the list contains exact matches of delimited valid column names.    @output_column_list VARCHAR(8000) = ‘[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]‘,    --Column(s) by which to sort output, optionally with sort directions.         --Valid column choices:        --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,        --tempdb_current, CPU, context_switches, used_memory, physical_io_delta,         --reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,         --tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,         --tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,        --percent_complete, host_name, login_name, database_name, start_time, login_time        --        --Note that column names in the list must be bracket-delimited. Commas and/or white        --space are not required.     @sort_order VARCHAR(500) = ‘[start_time] ASC‘,    --Formats some of the output columns in a more "human readable" form    --0 disables outfput format    --1 formats the output for variable-width fonts    --2 formats the output for fixed-width fonts    @format_output TINYINT = 1,    --If set to a non-blank value, the script will attempt to insert into the specified     --destination table. Please note that the script will not verify that the table exists,     --or that it has the correct schema, before doing the insert.    --Table can be specified in one, two, or three-part format    @destination_table VARCHAR(4000) = ‘‘,    --If set to 1, no data collection will happen and no result set will be returned; instead,    --a CREATE TABLE statement will be returned via the @schema parameter, which will match     --the schema of the result set that would be returned by using the same collection of the    --rest of the parameters. The CREATE TABLE statement will have a placeholder token of     --<table_name> in place of an actual table name.    @return_schema BIT = 0,    @schema VARCHAR(MAX) = NULL OUTPUT,    --Help! What do I do?    @help BIT = 0--~)/*OUTPUT COLUMNS--------------Formatted/Non:    [session_id] [smallint] NOT NULL    Session ID (a.k.a. SPID)Formatted:        [dd hh:mm:ss.mss] [varchar](15) NULLNon-Formatted:    <not returned>    For an active request, time the query has been running    For a sleeping session, time since the last batch completedFormatted:        [dd hh:mm:ss.mss (avg)] [varchar](15) NULLNon-Formatted:    [avg_elapsed_time] [int] NULL    (Requires @get_avg_time option)    How much time has the active portion of the query taken in the past, on average?Formatted:        [physical_io] [varchar](30) NULLNon-Formatted:    [physical_io] [bigint] NULL    Shows the number of physical I/Os, for active requestsFormatted:        [reads] [varchar](30) NULLNon-Formatted:    [reads] [bigint] NULL    For an active request, number of reads done for the current query    For a sleeping session, total number of reads done over the lifetime of the sessionFormatted:        [physical_reads] [varchar](30) NULLNon-Formatted:    [physical_reads] [bigint] NULL    For an active request, number of physical reads done for the current query    For a sleeping session, total number of physical reads done over the lifetime of the sessionFormatted:        [writes] [varchar](30) NULLNon-Formatted:    [writes] [bigint] NULL    For an active request, number of writes done for the current query    For a sleeping session, total number of writes done over the lifetime of the sessionFormatted:        [tempdb_allocations] [varchar](30) NULLNon-Formatted:    [tempdb_allocations] [bigint] NULL    For an active request, number of TempDB writes done for the current query    For a sleeping session, total number of TempDB writes done over the lifetime of the sessionFormatted:        [tempdb_current] [varchar](30) NULLNon-Formatted:    [tempdb_current] [bigint] NULL    For an active request, number of TempDB pages currently allocated for the query    For a sleeping session, number of TempDB pages currently allocated for the sessionFormatted:        [CPU] [varchar](30) NULLNon-Formatted:    [CPU] [int] NULL    For an active request, total CPU time consumed by the current query    For a sleeping session, total CPU time consumed over the lifetime of the sessionFormatted:        [context_switches] [varchar](30) NULLNon-Formatted:    [context_switches] [bigint] NULL    Shows the number of context switches, for active requestsFormatted:        [used_memory] [varchar](30) NOT NULLNon-Formatted:    [used_memory] [bigint] NOT NULL    For an active request, total memory consumption for the current query    For a sleeping session, total current memory consumptionFormatted:        [physical_io_delta] [varchar](30) NULLNon-Formatted:    [physical_io_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of physical I/Os reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [reads_delta] [varchar](30) NULLNon-Formatted:    [reads_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of reads reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [physical_reads_delta] [varchar](30) NULLNon-Formatted:    [physical_reads_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of physical reads reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [writes_delta] [varchar](30) NULLNon-Formatted:    [writes_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of writes reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [tempdb_allocations_delta] [varchar](30) NULLNon-Formatted:    [tempdb_allocations_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of TempDB writes reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [tempdb_current_delta] [varchar](30) NULLNon-Formatted:    [tempdb_current_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the number of allocated TempDB pages reported on the first and second     collections. If the request started after the first collection, the value will be NULLFormatted:        [CPU_delta] [varchar](30) NULLNon-Formatted:    [CPU_delta] [int] NULL    (Requires @delta_interval option)    Difference between the CPU time reported on the first and second collections.     If the request started after the first collection, the value will be NULLFormatted:        [context_switches_delta] [varchar](30) NULLNon-Formatted:    [context_switches_delta] [bigint] NULL    (Requires @delta_interval option)    Difference between the context switches count reported on the first and second collections    If the request started after the first collection, the value will be NULLFormatted:        [used_memory_delta] [varchar](30) NULLNon-Formatted:    [used_memory_delta] [bigint] NULL    Difference between the memory usage reported on the first and second collections    If the request started after the first collection, the value will be NULLFormatted:        [tasks] [varchar](30) NULLNon-Formatted:    [tasks] [smallint] NULL    Number of worker tasks currently allocated, for active requestsFormatted/Non:    [status] [varchar](30) NOT NULL    Activity status for the session (running, sleeping, etc)Formatted/Non:    [wait_info] [nvarchar](4000) NULL    Aggregates wait information, in the following format:        (Ax: Bms/Cms/Dms)E    A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait    times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.    If two tasks are waiting, each of their wait times will be shown (B/C). If three or more     tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).    If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),     the page type will be identified.    If wait type E is CXPACKET, the nodeId from the query plan will be identifiedFormatted/Non:    [locks] [xml] NULL    (Requires @get_locks option)    Aggregates lock information, in XML format.    The lock XML includes the lock mode, locked object, and aggregates the number of requests.     Attempts are made to identify locked objects by nameFormatted/Non:    [tran_start_time] [datetime] NULL    (Requires @get_transaction_info option)    Date and time that the first transaction opened by a session caused a transaction log     write to occur.Formatted/Non:    [tran_log_writes] [nvarchar](4000) NULL    (Requires @get_transaction_info option)    Aggregates transaction log write information, in the following format:    A:wB (C kB)    A is a database that has been touched by an active transaction    B is the number of log writes that have been made in the database as a result of the transaction    C is the number of log kilobytes consumed by the log recordsFormatted:        [open_tran_count] [varchar](30) NULLNon-Formatted:    [open_tran_count] [smallint] NULL    Shows the number of open transactions the session has openFormatted:        [sql_command] [xml] NULLNon-Formatted:    [sql_command] [nvarchar](max) NULL    (Requires @get_outer_command option)    Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,     if availableFormatted:        [sql_text] [xml] NULLNon-Formatted:    [sql_text] [nvarchar](max) NULL    Shows the SQL text for active requests or the last statement executed    for sleeping sessions, if available in either case.    If @get_full_inner_text option is set, shows the full text of the batch.    Otherwise, shows only the active statement within the batch.    If the query text is locked, a special timeout message will be sent, in the following format:        <timeout_exceeded />    If an error occurs, an error message will be sent, in the following format:        <error message="message" />Formatted/Non:    [query_plan] [xml] NULL    (Requires @get_plans option)    Shows the query plan for the request, if available.    If the plan is locked, a special timeout message will be sent, in the following format:        <timeout_exceeded />    If an error occurs, an error message will be sent, in the following format:        <error message="message" />Formatted/Non:    [blocking_session_id] [smallint] NULL    When applicable, shows the blocking SPIDFormatted:        [blocked_session_count] [varchar](30) NULLNon-Formatted:    [blocked_session_count] [smallint] NULL    (Requires @find_block_leaders option)    The total number of SPIDs blocked by this session,    all the way down the blocking chain.Formatted:        [percent_complete] [varchar](30) NULLNon-Formatted:    [percent_complete] [real] NULL    When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)Formatted/Non:    [host_name] [sysname] NOT NULL    Shows the host name for the connectionFormatted/Non:    [login_name] [sysname] NOT NULL    Shows the login name for the connectionFormatted/Non:    [database_name] [sysname] NULL    Shows the connected databaseFormatted/Non:    [program_name] [sysname] NULL    Shows the reported program/application nameFormatted/Non:    [additional_info] [xml] NULL    (Requires @get_additional_info option)    Returns additional non-performance-related session/request information    If the script finds a SQL Agent job running, the name of the job and job step will be reported    If @get_task_info = 2 and the script finds a lock wait, the locked object will be reportedFormatted/Non:    [start_time] [datetime] NOT NULL    For active requests, shows the time the request started    For sleeping sessions, shows the time the last batch completedFormatted/Non:    [login_time] [datetime] NOT NULL    Shows the time that the session connectedFormatted/Non:    [request_id] [int] NULL    For active requests, shows the request_id    Should be 0 unless MARS is being usedFormatted/Non:    [collection_time] [datetime] NOT NULL    Time that this script‘s final SELECT ran*/ASBEGIN;    SET NOCOUNT ON;     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    SET QUOTED_IDENTIFIER ON;    SET ANSI_PADDING ON;    SET CONCAT_NULL_YIELDS_NULL ON;    SET ANSI_WARNINGS ON;    SET NUMERIC_ROUNDABORT OFF;    SET ARITHABORT ON;    IF        @filter IS NULL        OR @filter_type IS NULL        OR @not_filter IS NULL        OR @not_filter_type IS NULL        OR @show_own_spid IS NULL        OR @show_system_spids IS NULL        OR @show_sleeping_spids IS NULL        OR @get_full_inner_text IS NULL        OR @get_plans IS NULL        OR @get_outer_command IS NULL        OR @get_transaction_info IS NULL        OR @get_task_info IS NULL        OR @get_locks IS NULL        OR @get_avg_time IS NULL        OR @get_additional_info IS NULL        OR @find_block_leaders IS NULL        OR @delta_interval IS NULL        OR @format_output IS NULL        OR @output_column_list IS NULL        OR @sort_order IS NULL        OR @return_schema IS NULL        OR @destination_table IS NULL        OR @help IS NULL    BEGIN;        RAISERROR(‘Input parameters cannot be NULL‘, 16, 1);        RETURN;    END;        IF @filter_type NOT IN (‘session‘, ‘program‘, ‘database‘, ‘login‘, ‘host‘)    BEGIN;        RAISERROR(‘Valid filter types are: session, program, database, login, host‘, 16, 1);        RETURN;    END;        IF @filter_type = ‘session‘ AND @filter LIKE ‘%[^0123456789]%‘    BEGIN;        RAISERROR(‘Session filters must be valid integers‘, 16, 1);        RETURN;    END;        IF @not_filter_type NOT IN (‘session‘, ‘program‘, ‘database‘, ‘login‘, ‘host‘)    BEGIN;        RAISERROR(‘Valid filter types are: session, program, database, login, host‘, 16, 1);        RETURN;    END;        IF @not_filter_type = ‘session‘ AND @not_filter LIKE ‘%[^0123456789]%‘    BEGIN;        RAISERROR(‘Session filters must be valid integers‘, 16, 1);        RETURN;    END;        IF @show_sleeping_spids NOT IN (0, 1, 2)    BEGIN;        RAISERROR(‘Valid values for @show_sleeping_spids are: 0, 1, or 2‘, 16, 1);        RETURN;    END;        IF @get_plans NOT IN (0, 1, 2)    BEGIN;        RAISERROR(‘Valid values for @get_plans are: 0, 1, or 2‘, 16, 1);        RETURN;    END;    IF @get_task_info NOT IN (0, 1, 2)    BEGIN;        RAISERROR(‘Valid values for @get_task_info are: 0, 1, or 2‘, 16, 1);        RETURN;    END;    IF @format_output NOT IN (0, 1, 2)    BEGIN;        RAISERROR(‘Valid values for @format_output are: 0, 1, or 2‘, 16, 1);        RETURN;    END;        IF @help = 1    BEGIN;        DECLARE             @header VARCHAR(MAX),            @params VARCHAR(MAX),            @outputs VARCHAR(MAX);        SELECT             @header =                REPLACE                (                    REPLACE                    (                        CONVERT                        (                            VARCHAR(MAX),                            SUBSTRING                            (                                t.text,                                 CHARINDEX(‘/‘ + REPLICATE(‘*‘, 93), t.text) + 94,                                CHARINDEX(REPLICATE(‘*‘, 93) + ‘/‘, t.text) - (CHARINDEX(‘/‘ + REPLICATE(‘*‘, 93), t.text) + 94)                            )                        ),                        CHAR(13)+CHAR(10),                        CHAR(13)                    ),                    ‘    ‘,                    ‘‘                ),            @params =                CHAR(13) +                    REPLACE                    (                        REPLACE                        (                            CONVERT                            (                                VARCHAR(MAX),                                SUBSTRING                                (                                    t.text,                                     CHARINDEX(‘--~‘, t.text) + 5,                                     CHARINDEX(‘--~‘, t.text, CHARINDEX(‘--~‘, t.text) + 5) - (CHARINDEX(‘--~‘, t.text) + 5)                                )                            ),                            CHAR(13)+CHAR(10),                            CHAR(13)                        ),                        ‘    ‘,                        ‘‘                    ),                @outputs =                     CHAR(13) +                        REPLACE                        (                            REPLACE                            (                                REPLACE                                (                                    CONVERT                                    (                                        VARCHAR(MAX),                                        SUBSTRING                                        (                                            t.text,                                             CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32,                                            CHARINDEX(‘*/‘, t.text, CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32) - (CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32)                                        )                                    ),                                    CHAR(9),                                    CHAR(255)                                ),                                CHAR(13)+CHAR(10),                                CHAR(13)                            ),                            ‘    ‘,                            ‘‘                        ) +                        CHAR(13)        FROM sys.dm_exec_requests AS r        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t        WHERE            r.session_id = @@SPID;        WITH        a0 AS        (SELECT 1 AS n UNION ALL SELECT 1),        a1 AS        (SELECT 1 AS n FROM a0 AS a, a0 AS b),        a2 AS        (SELECT 1 AS n FROM a1 AS a, a1 AS b),        a3 AS        (SELECT 1 AS n FROM a2 AS a, a2 AS b),        a4 AS        (SELECT 1 AS n FROM a3 AS a, a3 AS b),        numbers AS        (            SELECT TOP(LEN(@header) - 1)                ROW_NUMBER() OVER                (                    ORDER BY (SELECT NULL)                ) AS number            FROM a4            ORDER BY                number        )        SELECT            RTRIM(LTRIM(                SUBSTRING                (                    @header,                    number + 1,                    CHARINDEX(CHAR(13), @header, number + 1) - number - 1                )            )) AS [------header---------------------------------------------------------------------------------------------------------------]        FROM numbers        WHERE            SUBSTRING(@header, number, 1) = CHAR(13);        WITH        a0 AS        (SELECT 1 AS n UNION ALL SELECT 1),        a1 AS        (SELECT 1 AS n FROM a0 AS a, a0 AS b),        a2 AS        (SELECT 1 AS n FROM a1 AS a, a1 AS b),        a3 AS        (SELECT 1 AS n FROM a2 AS a, a2 AS b),        a4 AS        (SELECT 1 AS n FROM a3 AS a, a3 AS b),        numbers AS        (            SELECT TOP(LEN(@params) - 1)                ROW_NUMBER() OVER                (                    ORDER BY (SELECT NULL)                ) AS number            FROM a4            ORDER BY                number        ),        tokens AS        (            SELECT                 RTRIM(LTRIM(                    SUBSTRING                    (                        @params,                        number + 1,                        CHARINDEX(CHAR(13), @params, number + 1) - number - 1                    )                )) AS token,                number,                CASE                    WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number                    ELSE COALESCE(NULLIF(CHARINDEX(‘,‘ + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params))                 END AS param_group,                ROW_NUMBER() OVER                (                    PARTITION BY                        CHARINDEX(‘,‘ + CHAR(13) + CHAR(13), @params, number),                        SUBSTRING(@params, number+1, 1)                    ORDER BY                         number                ) AS group_order            FROM numbers            WHERE                SUBSTRING(@params, number, 1) = CHAR(13)        ),        parsed_tokens AS        (            SELECT                MIN                (                    CASE                        WHEN token LIKE ‘@%‘ THEN token                        ELSE NULL                    END                ) AS parameter,                MIN                (                    CASE                        WHEN token LIKE ‘--%‘ THEN RIGHT(token, LEN(token) - 2)                        ELSE NULL                    END                ) AS description,                param_group,                group_order            FROM tokens            WHERE                NOT                 (                    token = ‘‘                     AND group_order > 1                )            GROUP BY                param_group,                group_order        )        SELECT            CASE                WHEN description IS NULL AND parameter IS NULL THEN ‘-------------------------------------------------------------------------‘                WHEN param_group = MAX(param_group) OVER() THEN parameter                ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), ‘‘)            END AS [------parameter----------------------------------------------------------],            CASE                WHEN description IS NULL AND parameter IS NULL THEN ‘----------------------------------------------------------------------------------------------------------------------‘                ELSE COALESCE(description, ‘‘)            END AS [------description-----------------------------------------------------------------------------------------------------]        FROM parsed_tokens        ORDER BY            param_group,             group_order;                WITH        a0 AS        (SELECT 1 AS n UNION ALL SELECT 1),        a1 AS        (SELECT 1 AS n FROM a0 AS a, a0 AS b),        a2 AS        (SELECT 1 AS n FROM a1 AS a, a1 AS b),        a3 AS        (SELECT 1 AS n FROM a2 AS a, a2 AS b),        a4 AS        (SELECT 1 AS n FROM a3 AS a, a3 AS b),        numbers AS        (            SELECT TOP(LEN(@outputs) - 1)                ROW_NUMBER() OVER                (                    ORDER BY (SELECT NULL)                ) AS number            FROM a4            ORDER BY                number        ),        tokens AS        (            SELECT                 RTRIM(LTRIM(                    SUBSTRING                    (                        @outputs,                        number + 1,                        CASE                            WHEN                                 COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) <                                 COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))                                THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) - number - 1                            ELSE                                COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1                        END                    )                )) AS token,                number,                COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) AS output_group,                ROW_NUMBER() OVER                (                    PARTITION BY                         COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs))                    ORDER BY                        number                ) AS output_group_order            FROM numbers            WHERE                SUBSTRING(@outputs, number, 10) = CHAR(13) + ‘Formatted‘                OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2        ),        output_tokens AS        (            SELECT                 *,                CASE output_group_order                    WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)                    ELSE ‘‘                END COLLATE Latin1_General_Bin2 AS column_info            FROM tokens        )        SELECT            CASE output_group_order                WHEN 1 THEN ‘-----------------------------------‘                WHEN 2 THEN                     CASE                        WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN                            SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info))                        ELSE                            SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1)                    END                ELSE ‘‘            END AS formatted_column_name,            CASE output_group_order                WHEN 1 THEN ‘-----------------------------------‘                WHEN 2 THEN                     CASE                        WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN                            SUBSTRING(column_info, CHARINDEX(‘]‘, column_info)+2, LEN(column_info))                        ELSE                            SUBSTRING(column_info, CHARINDEX(‘]‘, column_info)+2, CHARINDEX(‘Non-Formatted:‘, column_info, CHARINDEX(‘]‘, column_info)+2) - CHARINDEX(‘]‘, column_info)-3)                    END                ELSE ‘‘            END AS formatted_column_type,            CASE output_group_order                WHEN 1 THEN ‘---------------------------------------‘                WHEN 2 THEN                     CASE                        WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN ‘‘                        ELSE                            CASE                                WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, 1) = ‘<‘ THEN                                    SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, CHARINDEX(‘>‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info)))                                ELSE                                    SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info)))                            END                    END                ELSE ‘‘            END AS unformatted_column_name,            CASE output_group_order                WHEN 1 THEN ‘---------------------------------------‘                WHEN 2 THEN                     CASE                        WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN ‘‘                        ELSE                            CASE                                WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, 1) = ‘<‘ THEN ‘‘                                ELSE                                    SUBSTRING(column_info, CHARINDEX(‘]‘, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+2, CHARINDEX(‘Non-Formatted:‘, column_info, CHARINDEX(‘]‘, column_info)+2) - CHARINDEX(‘]‘, column_info)-3)                            END                    END                ELSE ‘‘            END AS unformatted_column_type,            CASE output_group_order                WHEN 1 THEN ‘----------------------------------------------------------------------------------------------------------------------‘                ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, ‘‘)            END AS [------description-----------------------------------------------------------------------------------------------------]        FROM output_tokens        WHERE            NOT             (                output_group_order = 1                 AND output_group = LEN(@outputs)            )        ORDER BY            output_group,            CASE output_group_order                WHEN 1 THEN 99                ELSE output_group_order            END;        RETURN;    END;    WITH    a0 AS    (SELECT 1 AS n UNION ALL SELECT 1),    a1 AS    (SELECT 1 AS n FROM a0 AS a, a0 AS b),    a2 AS    (SELECT 1 AS n FROM a1 AS a, a1 AS b),    a3 AS    (SELECT 1 AS n FROM a2 AS a, a2 AS b),    a4 AS    (SELECT 1 AS n FROM a3 AS a, a3 AS b),    numbers AS    (        SELECT TOP(LEN(@output_column_list))            ROW_NUMBER() OVER            (                ORDER BY (SELECT NULL)            ) AS number        FROM a4        ORDER BY            number    ),    tokens AS    (        SELECT             ‘|[‘ +                SUBSTRING                (                    @output_column_list,                    number + 1,                    CHARINDEX(‘]‘, @output_column_list, number) - number - 1                ) + ‘|]‘ AS token,            number        FROM numbers        WHERE            SUBSTRING(@output_column_list, number, 1) = ‘[‘    ),    ordered_columns AS    (        SELECT            x.column_name,            ROW_NUMBER() OVER            (                PARTITION BY                    x.column_name                ORDER BY                    tokens.number,                    x.default_order            ) AS r,            ROW_NUMBER() OVER            (                ORDER BY                    tokens.number,                    x.default_order            ) AS s        FROM tokens        JOIN        (            SELECT ‘[session_id]‘ AS column_name, 1 AS default_order            UNION ALL            SELECT ‘[dd hh:mm:ss.mss]‘, 2            WHERE                @format_output IN (1, 2)            UNION ALL            SELECT ‘[dd hh:mm:ss.mss (avg)]‘, 3            WHERE                @format_output IN (1, 2)                AND @get_avg_time = 1            UNION ALL            SELECT ‘[avg_elapsed_time]‘, 4            WHERE                @format_output = 0                AND @get_avg_time = 1            UNION ALL            SELECT ‘[physical_io]‘, 5            WHERE                @get_task_info = 2            UNION ALL            SELECT ‘[reads]‘, 6            UNION ALL            SELECT ‘[physical_reads]‘, 7            UNION ALL            SELECT ‘[writes]‘, 8            UNION ALL            SELECT ‘[tempdb_allocations]‘, 9            UNION ALL            SELECT ‘[tempdb_current]‘, 10            UNION ALL            SELECT ‘[CPU]‘, 11            UNION ALL            SELECT ‘[context_switches]‘, 12            WHERE                @get_task_info = 2            UNION ALL            SELECT ‘[used_memory]‘, 13            UNION ALL            SELECT ‘[physical_io_delta]‘, 14            WHERE                @delta_interval > 0                    AND @get_task_info = 2            UNION ALL            SELECT ‘[reads_delta]‘, 15            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[physical_reads_delta]‘, 16            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[writes_delta]‘, 17            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[tempdb_allocations_delta]‘, 18            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[tempdb_current_delta]‘, 19            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[CPU_delta]‘, 20            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[context_switches_delta]‘, 21            WHERE                @delta_interval > 0                AND @get_task_info = 2            UNION ALL            SELECT ‘[used_memory_delta]‘, 22            WHERE                @delta_interval > 0            UNION ALL            SELECT ‘[tasks]‘, 23            WHERE                @get_task_info = 2            UNION ALL            SELECT ‘[status]‘, 24            UNION ALL            SELECT ‘[wait_info]‘, 25            WHERE                @get_task_info > 0                OR @find_block_leaders = 1            UNION ALL            SELECT ‘[locks]‘, 26            WHERE                @get_locks = 1            UNION ALL            SELECT ‘[tran_start_time]‘, 27            WHERE                @get_transaction_info = 1            UNION ALL            SELECT ‘[tran_log_writes]‘, 28            WHERE                @get_transaction_info = 1            UNION ALL            SELECT ‘[open_tran_count]‘, 29            UNION ALL            SELECT ‘[sql_command]‘, 30            WHERE                @get_outer_command = 1            UNION ALL            SELECT ‘[sql_text]‘, 31            UNION ALL            SELECT ‘[query_plan]‘, 32            WHERE                @get_plans >= 1            UNION ALL            SELECT ‘[blocking_session_id]‘, 33            WHERE                @get_task_info > 0                OR @find_block_leaders = 1            UNION ALL            SELECT ‘[blocked_session_count]‘, 34            WHERE                @find_block_leaders = 1            UNION ALL            SELECT ‘[percent_complete]‘, 35            UNION ALL            SELECT ‘[host_name]‘, 36            UNION ALL            SELECT ‘[login_name]‘, 37            UNION ALL            SELECT ‘[database_name]‘, 38            UNION ALL            SELECT ‘[program_name]‘, 39            UNION ALL            SELECT ‘[additional_info]‘, 40            WHERE                @get_additional_info = 1            UNION ALL            SELECT ‘[start_time]‘, 41            UNION ALL            SELECT ‘[login_time]‘, 42            UNION ALL            SELECT ‘[request_id]‘, 43            UNION ALL            SELECT ‘[collection_time]‘, 44        ) AS x ON             x.column_name LIKE token ESCAPE ‘|‘    )    SELECT        @output_column_list =            STUFF            (                (                    SELECT                        ‘,‘ + column_name as [text()]                    FROM ordered_columns                    WHERE                        r = 1                    ORDER BY                        s                    FOR XML                        PATH(‘‘)                ),                1,                1,                ‘‘            );        IF COALESCE(RTRIM(@output_column_list), ‘‘) = ‘‘    BEGIN;        RAISERROR(‘No valid column matches found in @output_column_list or no columns remain due to selected options.‘, 16, 1);        RETURN;    END;        IF @destination_table <> ‘‘    BEGIN;        SET @destination_table =             --database            COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + ‘.‘, ‘‘) +            --schema            COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + ‘.‘, ‘‘) +            --table            COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), ‘‘);                    IF COALESCE(RTRIM(@destination_table), ‘‘) = ‘‘        BEGIN;            RAISERROR(‘Destination table not properly formatted.‘, 16, 1);            RETURN;        END;    END;    WITH    a0 AS    (SELECT 1 AS n UNION ALL SELECT 1),    a1 AS    (SELECT 1 AS n FROM a0 AS a, a0 AS b),    a2 AS    (SELECT 1 AS n FROM a1 AS a, a1 AS b),    a3 AS    (SELECT 1 AS n FROM a2 AS a, a2 AS b),    a4 AS    (SELECT 1 AS n FROM a3 AS a, a3 AS b),    numbers AS    (        SELECT TOP(LEN(@sort_order))            ROW_NUMBER() OVER            (                ORDER BY (SELECT NULL)            ) AS number        FROM a4        ORDER BY            number    ),    tokens AS    (        SELECT             ‘|[‘ +                SUBSTRING                (                    @sort_order,                    number + 1,                    CHARINDEX(‘]‘, @sort_order, number) - number - 1                ) + ‘|]‘ AS token,            SUBSTRING            (                @sort_order,                CHARINDEX(‘]‘, @sort_order, number) + 1,                COALESCE(NULLIF(CHARINDEX(‘[‘, @sort_order, CHARINDEX(‘]‘, @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(‘]‘, @sort_order, number)            ) AS next_chunk,            number        FROM numbers        WHERE            SUBSTRING(@sort_order, number, 1) = ‘[‘    ),    ordered_columns AS    (        SELECT            x.column_name +                CASE                    WHEN tokens.next_chunk LIKE ‘%asc%‘ THEN ‘ ASC‘                    WHEN tokens.next_chunk LIKE ‘%desc%‘ THEN ‘ DESC‘                    ELSE ‘‘                END AS column_name,            ROW_NUMBER() OVER            (                PARTITION BY                    x.column_name                ORDER BY                    tokens.number            ) AS r,            tokens.number        FROM tokens        JOIN        (            SELECT ‘[session_id]‘ AS column_name            UNION ALL            SELECT ‘[physical_io]‘            UNION ALL            SELECT ‘[reads]‘            UNION ALL            SELECT ‘[physical_reads]‘            UNION ALL            SELECT ‘[writes]‘            UNION ALL            SELECT ‘[tempdb_allocations]‘            UNION ALL            SELECT ‘[tempdb_current]‘            UNION ALL            SELECT ‘[CPU]‘            UNION ALL            SELECT ‘[context_switches]‘            UNION ALL            SELECT ‘[used_memory]‘            UNION ALL            SELECT ‘[physical_io_delta]‘            UNION ALL            SELECT ‘[reads_delta]‘            UNION ALL            SELECT ‘[physical_reads_delta]‘            UNION ALL            SELECT ‘[writes_delta]‘            UNION ALL            SELECT ‘[tempdb_allocations_delta]‘            UNION ALL            SELECT ‘[tempdb_current_delta]‘            UNION ALL            SELECT ‘[CPU_delta]‘            UNION ALL            SELECT ‘[context_switches_delta]‘            UNION ALL            SELECT ‘[used_memory_delta]‘            UNION ALL            SELECT ‘[tasks]‘            UNION ALL            SELECT ‘[tran_start_time]‘            UNION ALL            SELECT ‘[open_tran_count]‘            UNION ALL            SELECT ‘[blocking_session_id]‘            UNION ALL            SELECT ‘[blocked_session_count]‘            UNION ALL            SELECT ‘[percent_complete]‘            UNION ALL            SELECT ‘[host_name]‘            UNION ALL            SELECT ‘[login_name]‘            UNION ALL            SELECT ‘[database_name]‘            UNION ALL            SELECT ‘[start_time]‘            UNION ALL            SELECT ‘[login_time]‘        ) AS x ON             x.column_name LIKE token ESCAPE ‘|‘    )    SELECT        @sort_order = COALESCE(z.sort_order, ‘‘)    FROM    (        SELECT            STUFF            (                (                    SELECT                        ‘,‘ + column_name as [text()]                    FROM ordered_columns                    WHERE                        r = 1                    ORDER BY                        number                    FOR XML                        PATH(‘‘)                ),                1,                1,                ‘‘            ) AS sort_order    ) AS z;    CREATE TABLE #sessions    (        recursion SMALLINT NOT NULL,        session_id SMALLINT NOT NULL,        request_id INT NOT NULL,        session_number INT NOT NULL,        elapsed_time INT NOT NULL,        avg_elapsed_time INT NULL,        physical_io BIGINT NULL,        reads BIGINT NULL,        physical_reads BIGINT NULL,        writes BIGINT NULL,        tempdb_allocations BIGINT NULL,        tempdb_current BIGINT NULL,        CPU INT NULL,        thread_CPU_snapshot BIGINT NULL,        context_switches BIGINT NULL,        used_memory BIGINT NOT NULL,         tasks SMALLINT NULL,        status VARCHAR(30) NOT NULL,        wait_info NVARCHAR(4000) NULL,        locks XML NULL,        transaction_id BIGINT NULL,        tran_start_time DATETIME NULL,        tran_log_writes NVARCHAR(4000) NULL,        open_tran_count SMALLINT NULL,        sql_command XML NULL,        sql_handle VARBINARY(64) NULL,        statement_start_offset INT NULL,        statement_end_offset INT NULL,        sql_text XML NULL,        plan_handle VARBINARY(64) NULL,        query_plan XML NULL,        blocking_session_id SMALLINT NULL,        blocked_session_count SMALLINT NULL,        percent_complete REAL NULL,        host_name sysname NULL,        login_name sysname NOT NULL,        database_name sysname NULL,        program_name sysname NULL,        additional_info XML NULL,        start_time DATETIME NOT NULL,        login_time DATETIME NULL,        last_request_start_time DATETIME NULL,        PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),        UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)    );    IF @return_schema = 0    BEGIN;        --Disable unnecessary autostats on the table        CREATE STATISTICS s_session_id ON #sessions (session_id)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_request_id ON #sessions (request_id)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_session_number ON #sessions (session_number)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_status ON #sessions (status)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_start_time ON #sessions (start_time)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        CREATE STATISTICS s_recursion ON #sessions (recursion)        WITH SAMPLE 0 ROWS, NORECOMPUTE;        DECLARE @recursion SMALLINT;        SET @recursion =             CASE @delta_interval                WHEN 0 THEN 1                ELSE -1            END;        DECLARE @first_collection_ms_ticks BIGINT;        DECLARE @last_collection_start DATETIME;        --Used for the delta pull        REDO:;                IF             @get_locks = 1             AND @recursion = 1            AND @output_column_list LIKE ‘%|[locks|]%‘ ESCAPE ‘|‘        BEGIN;            SELECT                y.resource_type,                y.database_name,                y.object_id,                y.file_id,                y.page_type,                y.hobt_id,                y.allocation_unit_id,                y.index_id,                y.schema_id,                y.principal_id,                y.request_mode,                y.request_status,                y.session_id,                y.resource_description,                y.request_count,                s.request_id,                s.start_time,                CONVERT(sysname, NULL) AS object_name,                CONVERT(sysname, NULL) AS index_name,                CONVERT(sysname, NULL) AS schema_name,                CONVERT(sysname, NULL) AS principal_name,                CONVERT(NVARCHAR(2048), NULL) AS query_error            INTO #locks            FROM            (                SELECT                    sp.spid AS session_id,                    CASE sp.status                        WHEN ‘sleeping‘ THEN CONVERT(INT, 0)                        ELSE sp.request_id                    END AS request_id,                    CASE sp.status                        WHEN ‘sleeping‘ THEN sp.last_batch                        ELSE COALESCE(req.start_time, sp.last_batch)                    END AS start_time,                    sp.dbid                FROM sys.sysprocesses AS sp                OUTER APPLY                (                    SELECT TOP(1)                        CASE                            WHEN                             (                                sp.hostprocess > ‘‘                                OR r.total_elapsed_time < 0                            ) THEN                                r.start_time                            ELSE                                DATEADD                                (                                    ms,                                     1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),                                     DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())                                )                        END AS start_time                    FROM sys.dm_exec_requests AS r                    WHERE                        r.session_id = sp.spid                        AND r.request_id = sp.request_id                ) AS req                WHERE                    --Process inclusive filter                    1 =                        CASE                            WHEN @filter <> ‘‘ THEN                                CASE @filter_type                                    WHEN ‘session‘ THEN                                        CASE                                            WHEN                                                CONVERT(SMALLINT, @filter) = 0                                                OR sp.spid = CONVERT(SMALLINT, @filter)                                                    THEN 1                                            ELSE 0                                        END                                    WHEN ‘program‘ THEN                                        CASE                                            WHEN sp.program_name LIKE @filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘login‘ THEN                                        CASE                                            WHEN sp.loginame LIKE @filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘host‘ THEN                                        CASE                                            WHEN sp.hostname LIKE @filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘database‘ THEN                                        CASE                                            WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1                                            ELSE 0                                        END                                    ELSE 0                                END                            ELSE 1                        END                    --Process exclusive filter                    AND 0 =                        CASE                            WHEN @not_filter <> ‘‘ THEN                                CASE @not_filter_type                                    WHEN ‘session‘ THEN                                        CASE                                            WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1                                            ELSE 0                                        END                                    WHEN ‘program‘ THEN                                        CASE                                            WHEN sp.program_name LIKE @not_filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘login‘ THEN                                        CASE                                            WHEN sp.loginame LIKE @not_filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘host‘ THEN                                        CASE                                            WHEN sp.hostname LIKE @not_filter THEN 1                                            ELSE 0                                        END                                    WHEN ‘database‘ THEN                                        CASE                                            WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1                                            ELSE 0                                        END                                    ELSE 0                                END                            ELSE 0                        END                    AND                     (                        @show_own_spid = 1                        OR sp.spid <> @@SPID                    )                    AND                     (                        @show_system_spids = 1                        OR sp.hostprocess > ‘‘                    )                    AND sp.ecid = 0            ) AS s            INNER HASH JOIN            (                SELECT                    x.resource_type,                    x.database_name,                    x.object_id,                    x.file_id,                    CASE                        WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN ‘PFS‘                        WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN ‘GAM‘                        WHEN x.page_no = 3 OR x.page_no % 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 database_name,                        CONVERT                        (                            INT,                            CASE                                WHEN tl.resource_type = ‘OBJECT‘ THEN tl.resource_associated_entity_id                                WHEN tl.resource_description LIKE ‘%object_id = %‘ THEN                                    (                                        SUBSTRING                                        (                                            tl.resource_description,                                             (CHARINDEX(‘object_id = ‘, tl.resource_description) + 12),                                             COALESCE                                            (                                                NULLIF                                                (                                                    CHARINDEX(‘,‘, tl.resource_description, CHARINDEX(‘object_id = ‘, tl.resource_description) + 12),                                                    0                                                ),                                                 DATALENGTH(tl.resource_description)+1                                            ) - (CHARINDEX(‘object_id = ‘, tl.resource_description) + 12)                                        )                                    )                                ELSE NULL                            END                        ) AS object_id,                        CONVERT                        (                            INT,                            CASE                                 WHEN tl.resource_type = ‘FILE‘ THEN CONVERT(INT, tl.resource_description)                                WHEN tl.resource_type IN (‘PAGE‘, ‘EXTENT‘, ‘RID‘) THEN LEFT(tl.resource_description, CHARINDEX(‘:‘, tl.resource_description)-1)                                ELSE NULL                            END                        ) AS file_id,                        CONVERT                        (                            INT,                            CASE                                WHEN tl.resource_type IN (‘PAGE‘, ‘EXTENT‘, ‘RID‘) THEN                                     SUBSTRING                                    (                                        tl.resource_description,                                         CHARINDEX(‘:‘, tl.resource_description) + 1,                                         COALESCE                                        (                                            NULLIF                                            (                                                CHARINDEX(‘:‘, tl.resource_description, CHARINDEX(‘:‘, tl.resource_description) + 1),                                                 0                                            ),                                             DATALENGTH(tl.resource_description)+1                                        ) - (CHARINDEX(‘:‘, tl.resource_description) + 1)                                    )                                ELSE NULL                            END                        ) AS page_no,                        CASE                            WHEN tl.resource_type IN (‘PAGE‘, ‘KEY‘, ‘RID‘, ‘HOBT‘) THEN tl.resource_associated_entity_id                            ELSE NULL                        END AS hobt_id,                        CASE                            WHEN tl.resource_type = ‘ALLOCATION_UNIT‘ THEN tl.resource_associated_entity_id                            ELSE NULL                        END AS allocation_unit_id,                        CONVERT                        (                            INT,                            CASE                                WHEN                                    /*TODO: Deal with server principals*/                                     tl.resource_subtype <> ‘SERVER_PRINCIPAL‘                                     AND tl.resource_description LIKE ‘%index_id or stats_id = %‘ THEN                                    (                                        SUBSTRING                                        (                                            tl.resource_description,                                             (CHARINDEX(‘index_id or stats_id = ‘, tl.resource_description) + 23),                                             COALESCE                                            (                                                NULLIF                                                (                                                    CHARINDEX(‘,‘, tl.resource_description, CHARINDEX(‘index_id or stats_id = ‘, tl.resource_description) + 23),                                                     0                                                ),                                                 DATALENGTH(tl.resource_description)+1                                            ) - (CHARINDEX(‘index_id or stats_id = ‘, tl.resource_description) + 23)                                        )                                    )                                ELSE NULL                            END                         ) AS index_id,                        CONVERT                        (                            INT,                            CASE                                WHEN tl.resource_description LIKE ‘%schema_id = %‘ THEN                                    (                                        SUBSTRING                                        (                                            tl.resource_description,                                             (CHARINDEX(‘schema_id = ‘, tl.resource_description) + 12),                                             COALESCE                                            (                                                NULLIF                                                (                                                    CHARINDEX(‘,‘, tl.resource_description, CHARINDEX(‘schema_id = ‘, tl.resource_description) + 12),                                                     0                                                ),                                                 DATALENGTH(tl.resource_description)+1                                            ) - (CHARINDEX(‘schema_id = ‘, tl.resource_description) + 12)                                        )                                    )                                ELSE NULL                            END                         ) AS schema_id,                        CONVERT                        (                            INT,                            CASE                                WHEN tl.resource_description LIKE ‘%principal_id = %‘ THEN                                    (                                        SUBSTRING                                        (                                            tl.resource_description,                                             (CHARINDEX(‘principal_id = ‘, tl.resource_description) + 15),                                             COALESCE                                            (                                                NULLIF                                                (                                                    CHARINDEX(‘,‘, tl.resource_description, CHARINDEX(‘principal_id = ‘, tl.resource_description) + 15),                                                     0                                                ),                                                 DATALENGTH(tl.resource_description)+1                                            ) - (CHARINDEX(‘principal_id = ‘, tl.resource_description) + 15)                                        )                                    )                                ELSE NULL                            END                        ) AS principal_id,                        tl.request_mode,                        tl.request_status,                        tl.request_session_id AS session_id,                        tl.request_request_id AS request_id,                        /*TODO: Applocks, other resource_descriptions*/                        RTRIM(tl.resource_description) AS resource_description,                        tl.resource_associated_entity_id                        /*********************************************/                    FROM                     (                        SELECT                             request_session_id,                            CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,                            CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,                            resource_database_id,                            CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,                            resource_associated_entity_id,                            CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,                            CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,                            request_request_id                        FROM sys.dm_tran_locks                    ) AS tl                ) AS x                GROUP BY                    x.resource_type,                    x.database_name,                    x.object_id,                    x.file_id,                    CASE                        WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN ‘PFS‘                        WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN ‘GAM‘                        WHEN x.page_no = 3 OR x.page_no % 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 = s.request_id            OPTION (HASH GROUP);            --Disable unnecessary autostats on the table            CREATE STATISTICS s_database_name ON #locks (database_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_object_id ON #locks (object_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_hobt_id ON #locks (hobt_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_index_id ON #locks (index_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_schema_id ON #locks (schema_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_principal_id ON #locks (principal_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_request_id ON #locks (request_id)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_start_time ON #locks (start_time)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_resource_type ON #locks (resource_type)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_object_name ON #locks (object_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_schema_name ON #locks (schema_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_page_type ON #locks (page_type)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_request_mode ON #locks (request_mode)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_request_status ON #locks (request_status)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_resource_description ON #locks (resource_description)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_index_name ON #locks (index_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_principal_name ON #locks (principal_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;        END;                DECLARE             @sql VARCHAR(MAX),             @sql_n NVARCHAR(MAX);        SET @sql =             CONVERT(VARCHAR(MAX), ‘‘) +            ‘DECLARE @blocker BIT;            SET @blocker = 0;            DECLARE @i INT;            SET @i = 2147483647;            DECLARE @sessions TABLE            (                session_id SMALLINT NOT NULL,                request_id INT NOT NULL,                login_time DATETIME,                last_request_end_time DATETIME,                status VARCHAR(30),                statement_start_offset INT,                statement_end_offset INT,                sql_handle BINARY(20),                host_name NVARCHAR(128),                login_name NVARCHAR(128),                program_name NVARCHAR(128),                database_id SMALLINT,                memory_usage INT,                open_tran_count SMALLINT,                 ‘ +                CASE                    WHEN                     (                        @get_task_info <> 0                         OR @find_block_leaders = 1                     ) THEN                        ‘wait_type NVARCHAR(32),                        wait_resource NVARCHAR(256),                        wait_time BIGINT,                         ‘                    ELSE                         ‘‘                END +                ‘blocked SMALLINT,                is_user_process BIT,                cmd VARCHAR(32),                PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)            );            DECLARE @blockers TABLE            (                session_id INT NOT NULL PRIMARY KEY            );            BLOCKERS:;            INSERT @sessions            (                session_id,                request_id,                login_time,                last_request_end_time,                status,                statement_start_offset,                statement_end_offset,                sql_handle,                host_name,                login_name,                program_name,                database_id,                memory_usage,                open_tran_count,                 ‘ +                CASE                    WHEN                     (                        @get_task_info <> 0                        OR @find_block_leaders = 1                     ) THEN                        ‘wait_type,                        wait_resource,                        wait_time,                         ‘                    ELSE                        ‘‘                END +                ‘blocked,                is_user_process,                cmd             )            SELECT TOP(@i)                spy.session_id,                spy.request_id,                spy.login_time,                spy.last_request_end_time,                spy.status,                spy.statement_start_offset,                spy.statement_end_offset,                spy.sql_handle,                spy.host_name,                spy.login_name,                spy.program_name,                spy.database_id,                spy.memory_usage,                spy.open_tran_count,                ‘ +                CASE                    WHEN                     (                        @get_task_info <> 0                          OR @find_block_leaders = 1                     ) THEN                        ‘spy.wait_type,                        CASE                            WHEN                                spy.wait_type LIKE N‘‘PAGE%LATCH_%‘‘                                OR spy.wait_type = N‘‘CXPACKET‘‘                                OR spy.wait_type LIKE N‘‘LATCH[_]%‘‘                                OR spy.wait_type = N‘‘OLEDB‘‘ THEN                                    spy.wait_resource                            ELSE                                NULL                        END AS wait_resource,                        spy.wait_time,                         ‘                    ELSE                        ‘‘                END +                ‘spy.blocked,                spy.is_user_process,                spy.cmd            FROM            (                SELECT TOP(@i)                    spx.*,                     ‘ +                    CASE                        WHEN                         (                            @get_task_info <> 0                             OR @find_block_leaders = 1                         ) THEN                            ‘ROW_NUMBER() OVER                            (                                PARTITION BY                                    spx.session_id,                                    spx.request_id                                ORDER BY                                    CASE                                        WHEN spx.wait_type LIKE N‘‘LCK[_]%‘‘ THEN                                             1                                        ELSE                                            99                                    END,                                    spx.wait_time DESC,                                    spx.blocked DESC                            ) AS r                             ‘                        ELSE                             ‘1 AS r                             ‘                    END +                ‘FROM                (                    SELECT TOP(@i)                        sp0.session_id,                        sp0.request_id,                        sp0.login_time,                        sp0.last_request_end_time,                        LOWER(sp0.status) AS status,                        CASE                            WHEN sp0.cmd = ‘‘CREATE INDEX‘‘ THEN                                0                            ELSE                                sp0.stmt_start                        END AS statement_start_offset,                        CASE                            WHEN sp0.cmd = N‘‘CREATE INDEX‘‘ THEN                                -1                            ELSE                                COALESCE(NULLIF(sp0.stmt_end, 0), -1)                        END AS statement_end_offset,                        sp0.sql_handle,                        sp0.host_name,                        sp0.login_name,                        sp0.program_name,                        sp0.database_id,                        sp0.memory_usage,                        sp0.open_tran_count,                         ‘ +                        CASE                            WHEN                             (                                @get_task_info <> 0                                 OR @find_block_leaders = 1                             ) THEN                                ‘CASE                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N‘‘CXPACKET‘‘ THEN                                        sp0.wait_type                                    ELSE                                        NULL                                END AS wait_type,                                CASE                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N‘‘CXPACKET‘‘ THEN                                         sp0.wait_resource                                    ELSE                                        NULL                                END AS wait_resource,                                CASE                                    WHEN sp0.wait_type <> N‘‘CXPACKET‘‘ THEN                                        sp0.wait_time                                    ELSE                                        0                                END AS wait_time,                                 ‘                            ELSE                                ‘‘                        END +                        ‘sp0.blocked,                        sp0.is_user_process,                        sp0.cmd                    FROM                    (                        SELECT TOP(@i)                            sp1.session_id,                            sp1.request_id,                            sp1.login_time,                            sp1.last_request_end_time,                            sp1.status,                            sp1.cmd,                            sp1.stmt_start,                            sp1.stmt_end,                            MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,                            sp1.host_name,                            MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,                            sp1.program_name,                            sp1.database_id,                            MAX(sp1.memory_usage)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,                            MAX(sp1.open_tran_count)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,                            sp1.wait_type,                            sp1.wait_resource,                            sp1.wait_time,                            sp1.blocked,                            sp1.hostprocess,                            sp1.is_user_process                        FROM                        (                            SELECT TOP(@i)                                sp2.spid AS session_id,                                CASE sp2.status                                    WHEN ‘‘sleeping‘‘ THEN                                        CONVERT(INT, 0)                                    ELSE                                        sp2.request_id                                END AS request_id,                                MAX(sp2.login_time) AS login_time,                                MAX(sp2.last_batch) AS last_request_end_time,                                MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,                                MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,                                MAX(sp2.stmt_start) AS stmt_start,                                MAX(sp2.stmt_end) AS stmt_end,                                MAX(sp2.sql_handle) AS sql_handle,                                MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,                                MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,                                MAX                                (                                    CASE                                        WHEN blk.queue_id IS NOT NULL THEN                                            N‘‘Service Broker                                                database_id: ‘‘ + CONVERT(NVARCHAR, blk.database_id) +                                                N‘‘ queue_id: ‘‘ + CONVERT(NVARCHAR, blk.queue_id)                                        ELSE                                            CONVERT                                            (                                                sysname,                                                RTRIM(sp2.program_name)                                            )                                    END COLLATE SQL_Latin1_General_CP1_CI_AS                                ) AS program_name,                                MAX(sp2.dbid) AS database_id,                                MAX(sp2.memusage) AS memory_usage,                                MAX(sp2.open_tran) AS open_tran_count,                                RTRIM(sp2.lastwaittype) AS wait_type,                                RTRIM(sp2.waitresource) AS wait_resource,                                MAX(sp2.waittime) AS wait_time,                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,                                MAX                                (                                    CASE                                        WHEN blk.session_id = sp2.spid THEN                                            ‘‘blocker‘‘                                        ELSE                                            RTRIM(sp2.hostprocess)                                    END                                ) AS hostprocess,                                CONVERT                                (                                    BIT,                                    MAX                                    (                                        CASE                                            WHEN sp2.hostprocess > ‘‘‘‘ THEN                                                1                                            ELSE                                                0                                        END                                    )                                ) AS is_user_process                            FROM                            (                                SELECT TOP(@i)                                    session_id,                                    CONVERT(INT, NULL) AS queue_id,                                    CONVERT(INT, NULL) AS database_id                                FROM @blockers                                UNION ALL                                SELECT TOP(@i)                                    CONVERT(SMALLINT, 0),                                    CONVERT(INT, NULL) AS queue_id,                                    CONVERT(INT, NULL) AS database_id                                WHERE                                    @blocker = 0                                UNION ALL                                SELECT TOP(@i)                                    CONVERT(SMALLINT, spid),                                    queue_id,                                    database_id                                FROM sys.dm_broker_activated_tasks                                WHERE                                    @blocker = 0                            ) AS blk                            INNER JOIN sys.sysprocesses AS sp2 ON                                sp2.spid = blk.session_id                                OR                                (                                    blk.session_id = 0                                    AND @blocker = 0                                )                            ‘ +                            CASE                                 WHEN                                 (                                    @get_task_info = 0                                     AND @find_block_leaders = 0                                ) THEN                                    ‘WHERE                                        sp2.ecid = 0                                     ‘                                 ELSE                                    ‘‘                            END +                            ‘GROUP BY                                sp2.spid,                                CASE sp2.status                                    WHEN ‘‘sleeping‘‘ THEN                                        CONVERT(INT, 0)                                    ELSE                                        sp2.request_id                                END,                                RTRIM(sp2.lastwaittype),                                RTRIM(sp2.waitresource),                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)                        ) AS sp1                    ) AS sp0                    WHERE                        @blocker = 1                        OR                        (1=1                         ‘ +                            --inclusive filter                            CASE                                WHEN @filter <> ‘‘ THEN                                    CASE @filter_type                                        WHEN ‘session‘ THEN                                            CASE                                                WHEN CONVERT(SMALLINT, @filter) <> 0 THEN                                                    ‘AND sp0.session_id = CONVERT(SMALLINT, @filter)                                                     ‘                                                ELSE                                                    ‘‘                                            END                                        WHEN ‘program‘ THEN                                            ‘AND sp0.program_name LIKE @filter                                             ‘                                        WHEN ‘login‘ THEN                                            ‘AND sp0.login_name LIKE @filter                                             ‘                                        WHEN ‘host‘ THEN                                            ‘AND sp0.host_name LIKE @filter                                             ‘                                        WHEN ‘database‘ THEN                                            ‘AND DB_NAME(sp0.database_id) LIKE @filter                                             ‘                                        ELSE                                            ‘‘                                    END                                ELSE                                    ‘‘                            END +                            --exclusive filter                            CASE                                WHEN @not_filter <> ‘‘ THEN                                    CASE @not_filter_type                                        WHEN ‘session‘ THEN                                            CASE                                                WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN                                                    ‘AND sp0.session_id <> CONVERT(SMALLINT, @not_filter)                                                     ‘                                                ELSE                                                    ‘‘                                            END                                        WHEN ‘program‘ THEN                                            ‘AND sp0.program_name NOT LIKE @not_filter                                             ‘                                        WHEN ‘login‘ THEN                                            ‘AND sp0.login_name NOT LIKE @not_filter                                             ‘                                        WHEN ‘host‘ THEN                                            ‘AND sp0.host_name NOT LIKE @not_filter                                             ‘                                        WHEN ‘database‘ THEN                                            ‘AND DB_NAME(sp0.database_id) NOT LIKE @not_filter                                             ‘                                        ELSE                                            ‘‘                                    END                                ELSE                                    ‘‘                            END +                            CASE @show_own_spid                                WHEN 1 THEN                                    ‘‘                                ELSE                                    ‘AND sp0.session_id <> @@spid                                     ‘                            END +                            CASE                                 WHEN @show_system_spids = 0 THEN                                    ‘AND sp0.hostprocess > ‘‘‘‘                                     ‘                                 ELSE                                    ‘‘                            END +                            CASE @show_sleeping_spids                                WHEN 0 THEN                                    ‘AND sp0.status <> ‘‘sleeping‘‘                                     ‘                                WHEN 1 THEN                                    ‘AND                                    (                                        sp0.status <> ‘‘sleeping‘‘                                        OR sp0.open_tran_count > 0                                    )                                    ‘                                ELSE                                    ‘‘                            END +                        ‘)                ) AS spx            ) AS spy            WHERE                spy.r = 1;             ‘ +             CASE @recursion                WHEN 1 THEN                     ‘IF @@ROWCOUNT > 0                    BEGIN;                        INSERT @blockers                        (                            session_id                        )                        SELECT TOP(@i)                            blocked                        FROM @sessions                        WHERE                            NULLIF(blocked, 0) IS NOT NULL                        EXCEPT                        SELECT TOP(@i)                            session_id                        FROM @sessions;                         ‘ +                        CASE                            WHEN                            (                                @get_task_info > 0                                OR @find_block_leaders = 1                            ) THEN                                ‘IF @@ROWCOUNT > 0                                BEGIN;                                    SET @blocker = 1;                                    GOTO BLOCKERS;                                END;                                 ‘                            ELSE                                 ‘‘                        END +                    ‘END;                     ‘                ELSE                     ‘‘            END +            ‘SELECT TOP(@i)                @recursion AS recursion,                x.session_id,                x.request_id,                DENSE_RANK() OVER                (                    ORDER BY                        x.session_id                ) AS session_number,                ‘ +                CASE                    WHEN @output_column_list LIKE ‘%|[dd hh:mm:ss.mss|]%‘ ESCAPE ‘|‘ THEN                         ‘x.elapsed_time ‘                    ELSE                         ‘0 ‘                END +                     ‘AS elapsed_time,                     ‘ +                CASE                    WHEN                        (                            @output_column_list LIKE ‘%|[dd hh:mm:ss.mss (avg)|]%‘ ESCAPE ‘|‘ OR                             @output_column_list LIKE ‘%|[avg_elapsed_time|]%‘ ESCAPE ‘|‘                        )                        AND @recursion = 1                            THEN                                 ‘x.avg_elapsed_time / 1000 ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS avg_elapsed_time,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[physical_io|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[physical_io_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.physical_io ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS physical_io,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[reads|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[reads_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.reads ‘                    ELSE                         ‘0 ‘                END +                     ‘AS reads,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[physical_reads|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[physical_reads_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.physical_reads ‘                    ELSE                         ‘0 ‘                END +                     ‘AS physical_reads,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[writes|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[writes_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.writes ‘                    ELSE                         ‘0 ‘                END +                     ‘AS writes,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[tempdb_allocations|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[tempdb_allocations_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.tempdb_allocations ‘                    ELSE                         ‘0 ‘                END +                     ‘AS tempdb_allocations,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[tempdb_current|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[tempdb_current_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.tempdb_current ‘                    ELSE                         ‘0 ‘                END +                     ‘AS tempdb_current,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[CPU|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[CPU_delta|]%‘ ESCAPE ‘|‘                            THEN                                ‘x.CPU ‘                    ELSE                        ‘0 ‘                END +                     ‘AS CPU,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[CPU_delta|]%‘ ESCAPE ‘|‘                        AND @get_task_info = 2                            THEN                                 ‘x.thread_CPU_snapshot ‘                    ELSE                         ‘0 ‘                END +                     ‘AS thread_CPU_snapshot,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[context_switches|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[context_switches_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.context_switches ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS context_switches,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[used_memory|]%‘ ESCAPE ‘|‘                        OR @output_column_list LIKE ‘%|[used_memory_delta|]%‘ ESCAPE ‘|‘                            THEN                                 ‘x.used_memory ‘                    ELSE                         ‘0 ‘                END +                     ‘AS used_memory,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[tasks|]%‘ ESCAPE ‘|‘                        AND @recursion = 1                            THEN                                 ‘x.tasks ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS tasks,                     ‘ +                CASE                    WHEN                         (                            @output_column_list LIKE ‘%|[status|]%‘ ESCAPE ‘|‘                             OR @output_column_list LIKE ‘%|[sql_command|]%‘ ESCAPE ‘|‘                        )                        AND @recursion = 1                            THEN                                 ‘x.status ‘                    ELSE                         ‘‘‘‘‘ ‘                END +                     ‘AS status,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[wait_info|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 CASE @get_task_info                                    WHEN 2 THEN                                        ‘COALESCE(x.task_wait_info, x.sys_wait_info) ‘                                    ELSE                                        ‘x.sys_wait_info ‘                                END                    ELSE                         ‘NULL ‘                END +                     ‘AS wait_info,                     ‘ +                CASE                    WHEN                         (                            @output_column_list LIKE ‘%|[tran_start_time|]%‘ ESCAPE ‘|‘                             OR @output_column_list LIKE ‘%|[tran_log_writes|]%‘ ESCAPE ‘|‘                         )                        AND @recursion = 1                            THEN                                 ‘x.transaction_id ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS transaction_id,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[open_tran_count|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.open_tran_count ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS open_tran_count,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[sql_text|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.sql_handle ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS sql_handle,                     ‘ +                CASE                    WHEN                         (                            @output_column_list LIKE ‘%|[sql_text|]%‘ ESCAPE ‘|‘                             OR @output_column_list LIKE ‘%|[query_plan|]%‘ ESCAPE ‘|‘                         )                        AND @recursion = 1                            THEN                                 ‘x.statement_start_offset ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS statement_start_offset,                     ‘ +                CASE                    WHEN                         (                            @output_column_list LIKE ‘%|[sql_text|]%‘ ESCAPE ‘|‘                             OR @output_column_list LIKE ‘%|[query_plan|]%‘ ESCAPE ‘|‘                         )                        AND @recursion = 1                            THEN                                 ‘x.statement_end_offset ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS statement_end_offset,                     ‘ +                ‘NULL AS sql_text,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[query_plan|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.plan_handle ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS plan_handle,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[blocking_session_id|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘NULLIF(x.blocking_session_id, 0) ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS blocking_session_id,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[percent_complete|]%‘ ESCAPE ‘|‘                        AND @recursion = 1                            THEN                                 ‘x.percent_complete ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS percent_complete,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[host_name|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.host_name ‘                    ELSE                         ‘‘‘‘‘ ‘                END +                     ‘AS host_name,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[login_name|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.login_name ‘                    ELSE                         ‘‘‘‘‘ ‘                END +                     ‘AS login_name,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[database_name|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘DB_NAME(x.database_id) ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS database_name,                     ‘ +                CASE                    WHEN                         @output_column_list LIKE ‘%|[program_name|]%‘ ESCAPE ‘|‘                         AND @recursion = 1                            THEN                                 ‘x.program_name ‘                    ELSE                         ‘‘‘‘‘ ‘                END +                     ‘AS program_name,                     ‘ +                CASE                    WHEN                        @output_column_list LIKE ‘%|[additional_info|]%‘ ESCAPE ‘|‘                        AND @recursion = 1                            THEN                                ‘(                                    SELECT TOP(@i)                                        x.text_size,                                        x.language,                                        x.date_format,                                        x.date_first,                                        CASE x.quoted_identifier                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS quoted_identifier,                                        CASE x.arithabort                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS arithabort,                                        CASE x.ansi_null_dflt_on                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS ansi_null_dflt_on,                                        CASE x.ansi_defaults                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS ansi_defaults,                                        CASE x.ansi_warnings                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS ansi_warnings,                                        CASE x.ansi_padding                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS ansi_padding,                                        CASE ansi_nulls                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS ansi_nulls,                                        CASE x.concat_null_yields_null                                            WHEN 0 THEN ‘‘OFF‘‘                                            WHEN 1 THEN ‘‘ON‘‘                                        END AS concat_null_yields_null,                                        CASE x.transaction_isolation_level                                            WHEN 0 THEN ‘‘Unspecified‘‘                                            WHEN 1 THEN ‘‘ReadUncomitted‘‘                                            WHEN 2 THEN ‘‘ReadCommitted‘‘                                            WHEN 3 THEN ‘‘Repeatable‘‘                                            WHEN 4 THEN ‘‘Serializable‘‘                                            WHEN 5 THEN ‘‘Snapshot‘‘                                        END AS transaction_isolation_level,                                        x.lock_timeout,                                        x.deadlock_priority,                                        x.row_count,                                        x.command_type,                                         ‘ +                                        CASE                                            WHEN @output_column_list LIKE ‘%|[program_name|]%‘ ESCAPE ‘|‘ THEN                                                ‘(                                                    SELECT TOP(1)                                                        CONVERT(uniqueidentifier, CONVERT(XML, ‘‘‘‘).value(‘‘xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )‘‘, ‘‘binary(16)‘‘)) AS job_id,                                                        agent_info.step_id,                                                        (                                                            SELECT TOP(1)                                                                NULL                                                            FOR XML                                                                PATH(‘‘job_name‘‘),                                                                TYPE                                                        ),                                                        (                                                            SELECT TOP(1)                                                                NULL                                                            FOR XML                                                                PATH(‘‘step_name‘‘),                                                                TYPE                                                        )                                                    FROM                                                    (                                                        SELECT TOP(1)                                                            SUBSTRING(x.program_name, CHARINDEX(‘‘0x‘‘, x.program_name) + 2, 32) AS job_id_string,                                                            SUBSTRING(x.program_name, CHARINDEX(‘‘: Step ‘‘, x.program_name) + 7, CHARINDEX(‘‘)‘‘, x.program_name, CHARINDEX(‘‘: Step ‘‘, x.program_name)) - (CHARINDEX(‘‘: Step ‘‘, x.program_name) + 7)) AS step_id                                                        WHERE                                                            x.program_name LIKE N‘‘SQLAgent - TSQL JobStep (Job 0x%‘‘                                                    ) AS agent_info                                                    FOR XML                                                        PATH(‘‘agent_job_info‘‘),                                                        TYPE                                                ),                                                ‘                                            ELSE ‘‘                                        END +                                        CASE                                            WHEN @get_task_info = 2 THEN                                                ‘CONVERT(XML, x.block_info) AS block_info,                                                 ‘                                            ELSE                                                ‘‘                                        END +                                        ‘x.host_process_id                                     FOR XML                                        PATH(‘‘additional_info‘‘),                                        TYPE                                ) ‘                    ELSE                        ‘NULL ‘                END +                     ‘AS additional_info,                 x.start_time,                     ‘ +                CASE                    WHEN                        @output_column_list LIKE ‘%|[login_time|]%‘ ESCAPE ‘|‘                        AND @recursion = 1                            THEN                                ‘x.login_time ‘                    ELSE                         ‘NULL ‘                END +                     ‘AS login_time,                 x.last_request_start_time            FROM            (                SELECT TOP(@i)                    y.*,                    CASE                        WHEN DATEDIFF(day, y.start_time, GETDATE()) > 24 THEN                            DATEDIFF(second, GETDATE(), y.start_time)                        ELSE DATEDIFF(ms, y.start_time, GETDATE())                    END AS elapsed_time,                    COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,                    COALESCE                    (                        CASE                            WHEN tempdb_info.tempdb_current < 0 THEN 0                            ELSE tempdb_info.tempdb_current                        END,                        0                    ) AS tempdb_current,                     ‘ +                    CASE                        WHEN                             (                                @get_task_info <> 0                                OR @find_block_leaders = 1                            ) THEN                                ‘N‘‘(‘‘ + CONVERT(NVARCHAR, y.wait_duration_ms) + N‘‘ms)‘‘ +                                    y.wait_type +                                        CASE                                            WHEN y.wait_type LIKE N‘‘PAGE%LATCH_%‘‘ THEN                                                N‘‘:‘‘ +                                                COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N‘‘:‘‘, y.resource_description) - 1))), N‘‘(null)‘‘) +                                                N‘‘:‘‘ +                                                SUBSTRING(y.resource_description, CHARINDEX(N‘‘:‘‘, y.resource_description) + 1, LEN(y.resource_description) - CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - CHARINDEX(N‘‘:‘‘, y.resource_description)) +                                                N‘‘(‘‘ +                                                    CASE                                                        WHEN                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) = 1 OR                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) % 8088 = 0                                                                THEN                                                                     N‘‘PFS‘‘                                                        WHEN                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) = 2 OR                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) % 511232 = 0                                                                THEN                                                                     N‘‘GAM‘‘                                                        WHEN                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) = 3 OR                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) % 511233 = 0                                                                THEN                                                                    N‘‘SGAM‘‘                                                        WHEN                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) = 6 OR                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) % 511238 = 0                                                                 THEN                                                                     N‘‘DCM‘‘                                                        WHEN                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) = 7 OR                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(y.resource_description)) - 1)) % 511239 = 0                                                                 THEN                                                                     N‘‘BCM‘‘                                                        ELSE                                                             N‘‘*‘‘                                                    END +                                                N‘‘)‘‘                                            WHEN y.wait_type = N‘‘CXPACKET‘‘ THEN                                                N‘‘:‘‘ + SUBSTRING(y.resource_description, CHARINDEX(N‘‘nodeId‘‘, y.resource_description) + 7, 4)                                            WHEN y.wait_type LIKE N‘‘LATCH[_]%‘‘ THEN                                                N‘‘ [‘‘ + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N‘‘]‘‘                                            WHEN                                                y.wait_type = N‘‘OLEDB‘‘                                                AND y.resource_description LIKE N‘‘%(SPID=%)‘‘ THEN                                                    N‘‘[‘‘ + LEFT(y.resource_description, CHARINDEX(N‘‘(SPID=‘‘, y.resource_description) - 2) +                                                        N‘‘:‘‘ + SUBSTRING(y.resource_description, CHARINDEX(N‘‘(SPID=‘‘, y.resource_description) + 6, CHARINDEX(N‘‘)‘‘, y.resource_description, (CHARINDEX(N‘‘(SPID=‘‘, y.resource_description) + 6)) - (CHARINDEX(N‘‘(SPID=‘‘, y.resource_description) + 6)) + ‘‘]‘‘                                            ELSE                                                N‘‘‘‘                                        END COLLATE Latin1_General_Bin2 AS sys_wait_info,                                         ‘                            ELSE                                ‘‘                        END +                        CASE                            WHEN @get_task_info = 2 THEN                                ‘tasks.physical_io,                                tasks.context_switches,                                tasks.tasks,                                tasks.block_info,                                tasks.wait_info AS task_wait_info,                                tasks.thread_CPU_snapshot,                                ‘                            ELSE                                ‘‘                     END +                    CASE                         WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN                            ‘CONVERT(INT, NULL) ‘                        ELSE                             ‘qs.total_elapsed_time / qs.execution_count ‘                    END +                         ‘AS avg_elapsed_time                 FROM                (                    SELECT TOP(@i)                        sp.session_id,                        sp.request_id,                        COALESCE(r.logical_reads, s.logical_reads) AS reads,                        COALESCE(r.reads, s.reads) AS physical_reads,                        COALESCE(r.writes, s.writes) AS writes,                        COALESCE(r.CPU_time, s.CPU_time) AS CPU,                        sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,                        LOWER(sp.status) AS status,                        COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,                        COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,                        COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,                        ‘ +                        CASE                            WHEN                             (                                @get_task_info <> 0                                OR @find_block_leaders = 1                             ) THEN                                ‘sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,                                sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,                                sp.wait_time AS wait_duration_ms,                                 ‘                            ELSE                                ‘‘                        END +                        ‘NULLIF(sp.blocked, 0) AS blocking_session_id,                        r.plan_handle,                        NULLIF(r.percent_complete, 0) AS percent_complete,                        sp.host_name,                        sp.login_name,                        sp.program_name,                        s.host_process_id,                        COALESCE(r.text_size, s.text_size) AS text_size,                        COALESCE(r.language, s.language) AS language,                        COALESCE(r.date_format, s.date_format) AS date_format,                        COALESCE(r.date_first, s.date_first) AS date_first,                        COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,                        COALESCE(r.arithabort, s.arithabort) AS arithabort,                        COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,                        COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,                        COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,                        COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,                        COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,                        COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,                        COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,                        COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,                        COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,                        COALESCE(r.row_count, s.row_count) AS row_count,                        COALESCE(r.command, sp.cmd) AS command_type,                        COALESCE                        (                            CASE                                WHEN                                (                                    s.is_user_process = 0                                    AND r.total_elapsed_time >= 0                                ) THEN                                    DATEADD                                    (                                        ms,                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())                                    )                            END,                            NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ‘‘19000101‘‘, 112)),                            (                                SELECT TOP(1)                                    DATEADD(second, -(ms_ticks / 1000), GETDATE())                                FROM sys.dm_os_sys_info                            )                        ) AS start_time,                        sp.login_time,                        CASE                            WHEN s.is_user_process = 1 THEN                                s.last_request_start_time                            ELSE                                COALESCE                                (                                    DATEADD                                    (                                        ms,                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())                                    ),                                    s.last_request_start_time                                )                        END AS last_request_start_time,                        r.transaction_id,                        sp.database_id,                        sp.open_tran_count                    FROM @sessions AS sp                    LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON                        s.session_id = sp.session_id                        AND s.login_time = sp.login_time                    LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON                        sp.status <> ‘‘sleeping‘‘                        AND r.session_id = sp.session_id                        AND r.request_id = sp.request_id                        AND                        (                            (                                s.is_user_process = 0                                AND sp.is_user_process = 0                            )                            OR                            (                                r.start_time = s.last_request_start_time                                AND s.last_request_end_time = sp.last_request_end_time                            )                        )                ) AS y                ‘ +                 CASE                     WHEN @get_task_info = 2 THEN                        CONVERT(VARCHAR(MAX), ‘‘) +                        ‘LEFT OUTER HASH JOIN                        (                            SELECT TOP(@i)                                task_nodes.task_node.value(‘‘(session_id/text())[1]‘‘, ‘‘SMALLINT‘‘) AS session_id,                                task_nodes.task_node.value(‘‘(request_id/text())[1]‘‘, ‘‘INT‘‘) AS request_id,                                task_nodes.task_node.value(‘‘(physical_io/text())[1]‘‘, ‘‘BIGINT‘‘) AS physical_io,                                task_nodes.task_node.value(‘‘(context_switches/text())[1]‘‘, ‘‘BIGINT‘‘) AS context_switches,                                task_nodes.task_node.value(‘‘(tasks/text())[1]‘‘, ‘‘INT‘‘) AS tasks,                                task_nodes.task_node.value(‘‘(block_info/text())[1]‘‘, ‘‘NVARCHAR(4000)‘‘) AS block_info,                                task_nodes.task_node.value(‘‘(waits/text())[1]‘‘, ‘‘NVARCHAR(4000)‘‘) AS wait_info,                                task_nodes.task_node.value(‘‘(thread_CPU_snapshot/text())[1]‘‘, ‘‘BIGINT‘‘) AS thread_CPU_snapshot                            FROM                            (                                SELECT TOP(@i)                                    CONVERT                                    (                                        XML,                                        REPLACE                                        (                                            CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,                                            N‘‘</waits></tasks><tasks><waits>‘‘,                                            N‘‘, ‘‘                                        )                                    ) AS task_xml                                FROM                                (                                    SELECT TOP(@i)                                        CASE waits.r                                            WHEN 1 THEN                                                waits.session_id                                            ELSE                                                NULL                                        END AS [session_id],                                        CASE waits.r                                            WHEN 1 THEN                                                waits.request_id                                            ELSE                                                NULL                                        END AS [request_id],                                                                                    CASE waits.r                                            WHEN 1 THEN                                                waits.physical_io                                            ELSE                                                NULL                                        END AS [physical_io],                                        CASE waits.r                                            WHEN 1 THEN                                                waits.context_switches                                            ELSE                                                NULL                                        END AS [context_switches],                                        CASE waits.r                                            WHEN 1 THEN                                                waits.thread_CPU_snapshot                                            ELSE                                                NULL                                        END AS [thread_CPU_snapshot],                                        CASE waits.r                                            WHEN 1 THEN                                                waits.tasks                                            ELSE                                                NULL                                        END AS [tasks],                                        CASE waits.r                                            WHEN 1 THEN                                                waits.block_info                                            ELSE                                                NULL                                        END AS [block_info],                                        REPLACE                                        (                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                CONVERT                                                (                                                    NVARCHAR(MAX),                                                    N‘‘(‘‘ +                                                        CONVERT(NVARCHAR, num_waits) + N‘‘x: ‘‘ +                                                        CASE num_waits                                                            WHEN 1 THEN                                                                CONVERT(NVARCHAR, min_wait_time) + N‘‘ms‘‘                                                            WHEN 2 THEN                                                                CASE                                                                    WHEN min_wait_time <> max_wait_time THEN                                                                        CONVERT(NVARCHAR, min_wait_time) + N‘‘/‘‘ + CONVERT(NVARCHAR, max_wait_time) + N‘‘ms‘‘                                                                    ELSE                                                                        CONVERT(NVARCHAR, max_wait_time) + N‘‘ms‘‘                                                                END                                                            ELSE                                                                CASE                                                                    WHEN min_wait_time <> max_wait_time THEN                                                                        CONVERT(NVARCHAR, min_wait_time) + N‘‘/‘‘ + CONVERT(NVARCHAR, avg_wait_time) + N‘‘/‘‘ + CONVERT(NVARCHAR, max_wait_time) + N‘‘ms‘‘                                                                    ELSE                                                                         CONVERT(NVARCHAR, max_wait_time) + N‘‘ms‘‘                                                                END                                                        END +                                                    N‘‘)‘‘ + wait_type COLLATE Latin1_General_Bin2                                                ),                                                NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘),                                                NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘),                                                NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘),                                            NCHAR(0),                                            N‘‘‘‘                                        ) AS [waits]                                    FROM                                    (                                        SELECT TOP(@i)                                            w1.*,                                            ROW_NUMBER() OVER                                            (                                                PARTITION BY                                                    w1.session_id,                                                    w1.request_id                                                ORDER BY                                                    w1.block_info DESC,                                                    w1.num_waits DESC,                                                    w1.wait_type                                            ) AS r                                        FROM                                        (                                            SELECT TOP(@i)                                                task_info.session_id,                                                task_info.request_id,                                                task_info.physical_io,                                                task_info.context_switches,                                                task_info.thread_CPU_snapshot,                                                task_info.num_tasks AS tasks,                                                CASE                                                    WHEN task_info.runnable_time IS NOT NULL THEN                                                        ‘‘RUNNABLE‘‘                                                    ELSE                                                        wt2.wait_type                                                END AS wait_type,                                                NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,                                                MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,                                                AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,                                                MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,                                                MAX(wt2.block_info) AS block_info                                            FROM                                            (                                                SELECT TOP(@i)                                                    t.session_id,                                                    t.request_id,                                                    SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,                                                    SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches,                                                     ‘ +                                                    CASE                                                        WHEN @output_column_list LIKE ‘%|[CPU_delta|]%‘ ESCAPE ‘|‘                                                            THEN                                                                ‘SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) ‘                                                        ELSE                                                            ‘CONVERT(BIGINT, NULL) ‘                                                    END +                                                         ‘ AS thread_CPU_snapshot,                                                     COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,                                                    t.task_address,                                                    t.task_state,                                                    CASE                                                        WHEN                                                            t.task_state = ‘‘RUNNABLE‘‘                                                            AND w.runnable_time > 0 THEN                                                                w.runnable_time                                                        ELSE                                                            NULL                                                    END AS runnable_time                                                FROM sys.dm_os_tasks AS t                                                CROSS APPLY                                                (                                                    SELECT TOP(1)                                                        sp2.session_id                                                    FROM @sessions AS sp2                                                    WHERE                                                        sp2.session_id = t.session_id                                                        AND sp2.request_id = t.request_id                                                        AND sp2.status <> ‘‘sleeping‘‘                                                ) AS sp20                                                LEFT OUTER HASH JOIN                                                (                                                    SELECT TOP(@i)                                                        (                                                            SELECT TOP(@i)                                                                ms_ticks                                                            FROM sys.dm_os_sys_info                                                        ) -                                                            w0.wait_resumed_ms_ticks AS runnable_time,                                                        w0.worker_address,                                                        w0.thread_address,                                                        w0.task_bound_ms_ticks                                                    FROM sys.dm_os_workers AS w0                                                    WHERE                                                        w0.state = ‘‘RUNNABLE‘‘                                                        OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks                                                ) AS w ON                                                    w.worker_address = t.worker_address                                                 ‘ +                                                CASE                                                    WHEN @output_column_list LIKE ‘%|[CPU_delta|]%‘ ESCAPE ‘|‘                                                        THEN                                                            ‘LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON                                                                tr.thread_address = w.thread_address                                                                AND @first_collection_ms_ticks >= w.task_bound_ms_ticks                                                            ‘                                                    ELSE                                                        ‘‘                                                END +                                            ‘) AS task_info                                            LEFT OUTER HASH JOIN                                            (                                                SELECT TOP(@i)                                                    wt1.wait_type,                                                    wt1.waiting_task_address,                                                    MAX(wt1.wait_duration_ms) AS wait_duration_ms,                                                    MAX(wt1.block_info) AS block_info                                                FROM                                                (                                                    SELECT DISTINCT TOP(@i)                                                        wt.wait_type +                                                            CASE                                                                WHEN wt.wait_type LIKE N‘‘PAGE%LATCH_%‘‘ THEN                                                                    ‘‘:‘‘ +                                                                    COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N‘‘:‘‘, wt.resource_description) - 1))), N‘‘(null)‘‘) +                                                                    N‘‘:‘‘ +                                                                    SUBSTRING(wt.resource_description, CHARINDEX(N‘‘:‘‘, wt.resource_description) + 1, LEN(wt.resource_description) - CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - CHARINDEX(N‘‘:‘‘, wt.resource_description)) +                                                                    N‘‘(‘‘ +                                                                        CASE                                                                            WHEN                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) = 1 OR                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) % 8088 = 0                                                                                    THEN                                                                                         N‘‘PFS‘‘                                                                            WHEN                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) = 2 OR                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) % 511232 = 0                                                                                     THEN                                                                                         N‘‘GAM‘‘                                                                            WHEN                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) = 3 OR                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N‘‘:‘‘, REVERSE(wt.resource_description)) - 1)) % 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,                                                        CASE                                                            WHEN                                                            (                                                                wt.blocking_session_id IS NOT NULL                                                                AND wt.wait_type LIKE N‘‘LCK[_]%‘‘                                                            ) THEN                                                                (                                                                    SELECT TOP(@i)                                                                        x.lock_type,                                                                        REPLACE                                                                        (                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                                                DB_NAME                                                                                (                                                                                    CONVERT                                                                                    (                                                                                        INT,                                                                                        SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘dbid=‘‘, wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘dbid=‘‘, wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘dbid=‘‘, wt.resource_description) - 5)                                                                                    )                                                                                ),                                                                                NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘),                                                                                NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘),                                                                                NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘),                                                                            NCHAR(0),                                                                            N‘‘‘‘                                                                        ) AS database_name,                                                                        CASE x.lock_type                                                                            WHEN N‘‘objectlock‘‘ THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘objid=‘‘, wt.resource_description), 0) + 6, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘objid=‘‘, wt.resource_description) + 6), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘objid=‘‘, wt.resource_description) - 6)                                                                            ELSE                                                                                NULL                                                                        END AS object_id,                                                                        CASE x.lock_type                                                                            WHEN N‘‘filelock‘‘ THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘fileid=‘‘, wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘fileid=‘‘, wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘fileid=‘‘, wt.resource_description) - 7)                                                                            ELSE                                                                                NULL                                                                        END AS file_id,                                                                        CASE                                                                            WHEN x.lock_type in (N‘‘pagelock‘‘, N‘‘extentlock‘‘, N‘‘ridlock‘‘) THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘associatedObjectId=‘‘, wt.resource_description), 0) + 19, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘associatedObjectId=‘‘, wt.resource_description) + 19), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘associatedObjectId=‘‘, wt.resource_description) - 19)                                                                            WHEN x.lock_type in (N‘‘keylock‘‘, N‘‘hobtlock‘‘, N‘‘allocunitlock‘‘) THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘hobtid=‘‘, wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘hobtid=‘‘, wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘hobtid=‘‘, wt.resource_description) - 7)                                                                            ELSE                                                                                NULL                                                                        END AS hobt_id,                                                                        CASE x.lock_type                                                                            WHEN N‘‘applicationlock‘‘ THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘hash=‘‘, wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘hash=‘‘, wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘hash=‘‘, wt.resource_description) - 5)                                                                            ELSE                                                                                NULL                                                                        END AS applock_hash,                                                                        CASE x.lock_type                                                                            WHEN N‘‘metadatalock‘‘ THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘subresource=‘‘, wt.resource_description), 0) + 12, COALESCE(NULLIF(CHARINDEX(N‘‘ ‘‘, wt.resource_description, CHARINDEX(N‘‘subresource=‘‘, wt.resource_description) + 12), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N‘‘subresource=‘‘, wt.resource_description) - 12)                                                                            ELSE                                                                                NULL                                                                        END AS metadata_resource,                                                                        CASE x.lock_type                                                                            WHEN N‘‘metadatalock‘‘ THEN                                                                                SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N‘‘classid=‘‘, wt.resource_description), 0) + 8, COALESCE(NULLIF(CHARINDEX(N‘‘ dbid=‘‘, wt.resource_description) - CHARINDEX(N‘‘classid=‘‘, wt.resource_description), 0), LEN(wt.resource_description) + 1) - 8)                                                                            ELSE                                                                                NULL                                                                        END AS metadata_class_id                                                                    FROM                                                                    (                                                                        SELECT TOP(1)                                                                            LEFT(wt.resource_description, CHARINDEX(N‘‘ ‘‘, wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type                                                                    ) AS x                                                                    FOR XML                                                                        PATH(‘‘‘‘)                                                                )                                                            ELSE NULL                                                        END AS block_info,                                                        wt.wait_duration_ms,                                                        wt.waiting_task_address                                                    FROM                                                    (                                                        SELECT TOP(@i)                                                            wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,                                                            wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,                                                            wt0.wait_duration_ms,                                                            wt0.waiting_task_address,                                                            CASE                                                                WHEN wt0.blocking_session_id = p.blocked THEN                                                                    wt0.blocking_session_id                                                                ELSE                                                                    NULL                                                            END AS blocking_session_id                                                        FROM sys.dm_os_waiting_tasks AS wt0                                                        CROSS APPLY                                                        (                                                            SELECT TOP(1)                                                                s0.blocked                                                            FROM @sessions AS s0                                                            WHERE                                                                s0.session_id = wt0.session_id                                                                AND COALESCE(s0.wait_type, N‘‘‘‘) <> N‘‘OLEDB‘‘                                                                AND wt0.wait_type <> N‘‘OLEDB‘‘                                                        ) AS p                                                    ) AS wt                                                ) AS wt1                                                GROUP BY                                                    wt1.wait_type,                                                    wt1.waiting_task_address                                            ) AS wt2 ON                                                wt2.waiting_task_address = task_info.task_address                                                AND wt2.wait_duration_ms > 0                                                AND task_info.runnable_time IS NULL                                            GROUP BY                                                task_info.session_id,                                                task_info.request_id,                                                task_info.physical_io,                                                task_info.context_switches,                                                task_info.thread_CPU_snapshot,                                                task_info.num_tasks,                                                CASE                                                    WHEN task_info.runnable_time IS NOT NULL THEN                                                        ‘‘RUNNABLE‘‘                                                    ELSE                                                        wt2.wait_type                                                END                                        ) AS w1                                    ) AS waits                                    ORDER BY                                        waits.session_id,                                        waits.request_id,                                        waits.r                                    FOR XML                                        PATH(N‘‘tasks‘‘),                                        TYPE                                ) AS tasks_raw (task_xml_raw)                            ) AS tasks_final                            CROSS APPLY tasks_final.task_xml.nodes(N‘‘/tasks‘‘) AS task_nodes (task_node)                            WHERE                                task_nodes.task_node.exist(N‘‘session_id‘‘) = 1                        ) AS tasks ON                            tasks.session_id = y.session_id                            AND tasks.request_id = y.request_id                         ‘                    ELSE                        ‘‘                END +                ‘LEFT OUTER HASH JOIN                (                    SELECT TOP(@i)                        t_info.session_id,                        COALESCE(t_info.request_id, -1) AS request_id,                        SUM(t_info.tempdb_allocations) AS tempdb_allocations,                        SUM(t_info.tempdb_current) AS tempdb_current                    FROM                    (                        SELECT TOP(@i)                            tsu.session_id,                            tsu.request_id,                            tsu.user_objects_alloc_page_count +                                tsu.internal_objects_alloc_page_count AS tempdb_allocations,                            tsu.user_objects_alloc_page_count +                                tsu.internal_objects_alloc_page_count -                                tsu.user_objects_dealloc_page_count -                                tsu.internal_objects_dealloc_page_count AS tempdb_current                        FROM sys.dm_db_task_space_usage AS tsu                        CROSS APPLY                        (                            SELECT TOP(1)                                s0.session_id                            FROM @sessions AS s0                            WHERE                                s0.session_id = tsu.session_id                        ) AS p                        UNION ALL                        SELECT TOP(@i)                            ssu.session_id,                            NULL AS request_id,                            ssu.user_objects_alloc_page_count +                                ssu.internal_objects_alloc_page_count AS tempdb_allocations,                            ssu.user_objects_alloc_page_count +                                ssu.internal_objects_alloc_page_count -                                ssu.user_objects_dealloc_page_count -                                ssu.internal_objects_dealloc_page_count AS tempdb_current                        FROM sys.dm_db_session_space_usage AS ssu                        CROSS APPLY                        (                            SELECT TOP(1)                                s0.session_id                            FROM @sessions AS s0                            WHERE                                s0.session_id = ssu.session_id                        ) AS p                    ) AS t_info                    GROUP BY                        t_info.session_id,                        COALESCE(t_info.request_id, -1)                ) AS tempdb_info ON                    tempdb_info.session_id = y.session_id                    AND tempdb_info.request_id =                        CASE                            WHEN y.status = N‘‘sleeping‘‘ THEN                                -1                            ELSE                                y.request_id                        END                ‘ +                CASE                     WHEN                         NOT                         (                            @get_avg_time = 1                             AND @recursion = 1                        ) THEN                             ‘‘                    ELSE                        ‘LEFT OUTER HASH JOIN                        (                            SELECT TOP(@i)                                *                            FROM sys.dm_exec_query_stats                        ) AS qs ON                            qs.sql_handle = y.sql_handle                            AND qs.plan_handle = y.plan_handle                            AND qs.statement_start_offset = y.statement_start_offset                            AND qs.statement_end_offset = y.statement_end_offset                        ‘                END +             ‘) AS x            OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ‘;        SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);        SET @last_collection_start = GETDATE();        IF @recursion = -1        BEGIN;            SELECT                @first_collection_ms_ticks = ms_ticks            FROM sys.dm_os_sys_info;        END;        INSERT #sessions        (            recursion,            session_id,            request_id,            session_number,            elapsed_time,            avg_elapsed_time,            physical_io,            reads,            physical_reads,            writes,            tempdb_allocations,            tempdb_current,            CPU,            thread_CPU_snapshot,            context_switches,            used_memory,            tasks,            status,            wait_info,            transaction_id,            open_tran_count,            sql_handle,            statement_start_offset,            statement_end_offset,                    sql_text,            plan_handle,            blocking_session_id,            percent_complete,            host_name,            login_name,            database_name,            program_name,            additional_info,            start_time,            login_time,            last_request_start_time        )        EXEC sp_executesql             @sql_n,            N‘@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT‘,            @recursion, @filter, @not_filter, @first_collection_ms_ticks;        --Collect transaction information?        IF            @recursion = 1            AND            (                @output_column_list LIKE ‘%|[tran_start_time|]%‘ ESCAPE ‘|‘                OR @output_column_list LIKE ‘%|[tran_log_writes|]%‘ ESCAPE ‘|‘             )        BEGIN;                DECLARE @i INT;            SET @i = 2147483647;            UPDATE s            SET                tran_start_time =                    CONVERT                    (                        DATETIME,                        LEFT                        (                            x.trans_info,                            NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)                        ),                        121                    ),                tran_log_writes =                    RIGHT                    (                        x.trans_info,                        LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)                    )            FROM            (                SELECT TOP(@i)                    trans_nodes.trans_node.value(‘(session_id/text())[1]‘, ‘SMALLINT‘) AS session_id,                    COALESCE(trans_nodes.trans_node.value(‘(request_id/text())[1]‘, ‘INT‘), 0) AS request_id,                    trans_nodes.trans_node.value(‘(trans_info/text())[1]‘, ‘NVARCHAR(4000)‘) AS trans_info                                FROM                (                    SELECT TOP(@i)                        CONVERT                        (                            XML,                            REPLACE                            (                                CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2,                                 N‘</trans_info></trans><trans><trans_info>‘, N‘‘                            )                        )                    FROM                    (                        SELECT TOP(@i)                            CASE u_trans.r                                WHEN 1 THEN u_trans.session_id                                ELSE NULL                            END AS [session_id],                            CASE u_trans.r                                WHEN 1 THEN u_trans.request_id                                ELSE NULL                            END AS [request_id],                            CONVERT                            (                                NVARCHAR(MAX),                                CASE                                    WHEN u_trans.database_id IS NOT NULL THEN                                        CASE u_trans.r                                            WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N‘‘)                                            ELSE N‘‘                                        END +                                             REPLACE                                            (                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                                    CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N‘(null)‘)),                                                    NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                                    NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                                    NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                                NCHAR(0),                                                N‘?‘                                            ) +                                            N‘: ‘ +                                        CONVERT(NVARCHAR, u_trans.log_record_count) + N‘ (‘ + CONVERT(NVARCHAR, u_trans.log_kb_used) + N‘ kB)‘ +                                        N‘,‘                                    ELSE                                        N‘N/A,‘                                END COLLATE Latin1_General_Bin2                            ) AS [trans_info]                        FROM                        (                            SELECT TOP(@i)                                trans.*,                                ROW_NUMBER() OVER                                (                                    PARTITION BY                                        trans.session_id,                                        trans.request_id                                    ORDER BY                                        trans.transaction_start_time DESC                                ) AS r                            FROM                            (                                SELECT TOP(@i)                                    session_tran_map.session_id,                                    session_tran_map.request_id,                                    s_tran.database_id,                                    COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,                                    COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used,                                    MIN(s_tran.database_transaction_begin_time) AS transaction_start_time                                FROM                                (                                    SELECT TOP(@i)                                        *                                    FROM sys.dm_tran_active_transactions                                    WHERE                                        transaction_begin_time <= @last_collection_start                                ) AS a_tran                                INNER HASH JOIN                                (                                    SELECT TOP(@i)                                        *                                    FROM sys.dm_tran_database_transactions                                    WHERE                                        database_id < 32767                                ) AS s_tran ON                                    s_tran.transaction_id = a_tran.transaction_id                                LEFT OUTER HASH JOIN                                (                                    SELECT TOP(@i)                                        *                                    FROM sys.dm_tran_session_transactions                                ) AS tst ON                                    s_tran.transaction_id = tst.transaction_id                                CROSS APPLY                                (                                    SELECT TOP(1)                                        s3.session_id,                                        s3.request_id                                    FROM                                    (                                        SELECT TOP(1)                                            s1.session_id,                                            s1.request_id                                        FROM #sessions AS s1                                        WHERE                                            s1.transaction_id = s_tran.transaction_id                                            AND s1.recursion = 1                                                                                    UNION ALL                                                                            SELECT TOP(1)                                            s2.session_id,                                            s2.request_id                                        FROM #sessions AS s2                                        WHERE                                            s2.session_id = tst.session_id                                            AND s2.recursion = 1                                    ) AS s3                                    ORDER BY                                        s3.request_id                                ) AS session_tran_map                                GROUP BY                                    session_tran_map.session_id,                                    session_tran_map.request_id,                                    s_tran.database_id                            ) AS trans                        ) AS u_trans                        FOR XML                            PATH(‘trans‘),                            TYPE                    ) AS trans_raw (trans_xml_raw)                ) AS trans_final (trans_xml)                CROSS APPLY trans_final.trans_xml.nodes(‘/trans‘) AS trans_nodes (trans_node)            ) AS x            INNER HASH JOIN #sessions AS s ON                s.session_id = x.session_id                AND s.request_id = x.request_id            OPTION (OPTIMIZE FOR (@i = 1));        END;        --Variables for text and plan collection        DECLARE                @session_id SMALLINT,            @request_id INT,            @sql_handle VARBINARY(64),            @plan_handle VARBINARY(64),            @statement_start_offset INT,            @statement_end_offset INT,            @start_time DATETIME,            @database_name sysname;        IF             @recursion = 1            AND @output_column_list LIKE ‘%|[sql_text|]%‘ ESCAPE ‘|‘        BEGIN;            DECLARE sql_cursor            CURSOR LOCAL FAST_FORWARD            FOR                 SELECT                     session_id,                    request_id,                    sql_handle,                    statement_start_offset,                    statement_end_offset                FROM #sessions                WHERE                    recursion = 1                    AND sql_handle IS NOT NULL            OPTION (KEEPFIXED PLAN);            OPEN sql_cursor;            FETCH NEXT FROM sql_cursor            INTO                 @session_id,                @request_id,                @sql_handle,                @statement_start_offset,                @statement_end_offset;            --Wait up to 5 ms for the SQL text, then give up            SET LOCK_TIMEOUT 5;            WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    UPDATE s                    SET                        s.sql_text =                        (                            SELECT                                REPLACE                                (                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                        N‘--‘ + NCHAR(13) + NCHAR(10) +                                        CASE                                             WHEN @get_full_inner_text = 1 THEN est.text                                            WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text                                            WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N‘[a-zA-Z0-9][a-zA-Z0-9]‘ THEN est.text                                            ELSE                                                CASE                                                    WHEN @statement_start_offset > 0 THEN                                                        SUBSTRING                                                        (                                                            est.text,                                                            ((@statement_start_offset/2) + 1),                                                            (                                                                CASE                                                                    WHEN @statement_end_offset = -1 THEN 2147483647                                                                    ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1                                                                END                                                            )                                                        )                                                    ELSE RTRIM(LTRIM(est.text))                                                END                                        END +                                        NCHAR(13) + NCHAR(10) + N‘--‘ COLLATE Latin1_General_Bin2,                                        NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                        NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                        NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                    NCHAR(0),                                    N‘‘                                ) AS [processing-instruction(query)]                            FOR XML                                PATH(‘‘),                                TYPE                        ),                        s.statement_start_offset =                             CASE                                 WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE ‘[a-zA-Z0-9][a-zA-Z0-9]‘ THEN 0                                ELSE @statement_start_offset                            END,                        s.statement_end_offset =                             CASE                                 WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE ‘[a-zA-Z0-9][a-zA-Z0-9]‘ THEN -1                                ELSE @statement_end_offset                            END                    FROM                         #sessions AS s,                        (                            SELECT TOP(1)                                text                            FROM                            (                                SELECT                                     text,                                     0 AS row_num                                FROM sys.dm_exec_sql_text(@sql_handle)                                                                UNION ALL                                                                SELECT                                     NULL,                                    1 AS row_num                            ) AS est0                            ORDER BY                                row_num                        ) AS est                    WHERE                         s.session_id = @session_id                        AND s.request_id = @request_id                        AND s.recursion = 1                    OPTION (KEEPFIXED PLAN);                END TRY                BEGIN CATCH;                    UPDATE s                    SET                        s.sql_text =                             CASE ERROR_NUMBER()                                 WHEN 1222 THEN ‘<timeout_exceeded />‘                                ELSE ‘<error message="‘ + ERROR_MESSAGE() + ‘" />‘                            END                    FROM #sessions AS s                    WHERE                         s.session_id = @session_id                        AND s.request_id = @request_id                        AND s.recursion = 1                    OPTION (KEEPFIXED PLAN);                END CATCH;                FETCH NEXT FROM sql_cursor                INTO                    @session_id,                    @request_id,                    @sql_handle,                    @statement_start_offset,                    @statement_end_offset;            END;            --Return this to the default            SET LOCK_TIMEOUT -1;            CLOSE sql_cursor;            DEALLOCATE sql_cursor;        END;        IF             @get_outer_command = 1             AND @recursion = 1            AND @output_column_list LIKE ‘%|[sql_command|]%‘ ESCAPE ‘|‘        BEGIN;            DECLARE @buffer_results TABLE            (                EventType VARCHAR(30),                Parameters INT,                EventInfo NVARCHAR(4000),                start_time DATETIME,                session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY            );            DECLARE buffer_cursor            CURSOR LOCAL FAST_FORWARD            FOR                 SELECT                     session_id,                    MAX(start_time) AS start_time                FROM #sessions                WHERE                    recursion = 1                GROUP BY                    session_id                ORDER BY                    session_id                OPTION (KEEPFIXED PLAN);            OPEN buffer_cursor;            FETCH NEXT FROM buffer_cursor            INTO                 @session_id,                @start_time;            WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    --In SQL Server 2008, DBCC INPUTBUFFER will throw                     --an exception if the session no longer exists                    INSERT @buffer_results                    (                        EventType,                        Parameters,                        EventInfo                    )                    EXEC sp_executesql                        N‘DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;‘,                        N‘@session_id SMALLINT‘,                        @session_id;                    UPDATE br                    SET                        br.start_time = @start_time                    FROM @buffer_results AS br                    WHERE                        br.session_number =                         (                            SELECT MAX(br2.session_number)                            FROM @buffer_results br2                        );                END TRY                BEGIN CATCH                END CATCH;                FETCH NEXT FROM buffer_cursor                INTO                     @session_id,                    @start_time;            END;            UPDATE s            SET                sql_command =                 (                    SELECT                         REPLACE                        (                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                CONVERT                                (                                    NVARCHAR(MAX),                                    N‘--‘ + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N‘--‘ COLLATE Latin1_General_Bin2                                ),                                NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                            NCHAR(0),                            N‘‘                        ) AS [processing-instruction(query)]                    FROM @buffer_results AS br                    WHERE                         br.session_number = s.session_number                        AND br.start_time = s.start_time                        AND                         (                            (                                s.start_time = s.last_request_start_time                                AND EXISTS                                (                                    SELECT *                                    FROM sys.dm_exec_requests r2                                    WHERE                                        r2.session_id = s.session_id                                        AND r2.request_id = s.request_id                                        AND r2.start_time = s.start_time                                )                            )                            OR                             (                                s.request_id = 0                                AND EXISTS                                (                                    SELECT *                                    FROM sys.dm_exec_sessions s2                                    WHERE                                        s2.session_id = s.session_id                                        AND s2.last_request_start_time = s.last_request_start_time                                )                            )                        )                    FOR XML                        PATH(‘‘),                        TYPE                )            FROM #sessions AS s            WHERE                recursion = 1            OPTION (KEEPFIXED PLAN);            CLOSE buffer_cursor;            DEALLOCATE buffer_cursor;        END;        IF             @get_plans >= 1             AND @recursion = 1            AND @output_column_list LIKE ‘%|[query_plan|]%‘ ESCAPE ‘|‘        BEGIN;            DECLARE plan_cursor            CURSOR LOCAL FAST_FORWARD            FOR                 SELECT                    session_id,                    request_id,                    plan_handle,                    statement_start_offset,                    statement_end_offset                FROM #sessions                WHERE                    recursion = 1                    AND plan_handle IS NOT NULL            OPTION (KEEPFIXED PLAN);            OPEN plan_cursor;            FETCH NEXT FROM plan_cursor            INTO                 @session_id,                @request_id,                @plan_handle,                @statement_start_offset,                @statement_end_offset;            --Wait up to 5 ms for a query plan, then give up            SET LOCK_TIMEOUT 5;            WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    UPDATE s                    SET                        s.query_plan =                        (                            SELECT                                CONVERT(xml, query_plan)                            FROM sys.dm_exec_text_query_plan                            (                                @plan_handle,                                 CASE @get_plans                                    WHEN 1 THEN                                        @statement_start_offset                                    ELSE                                        0                                END,                                 CASE @get_plans                                    WHEN 1 THEN                                        @statement_end_offset                                    ELSE                                        -1                                END                            )                        )                    FROM #sessions AS s                    WHERE                         s.session_id = @session_id                        AND s.request_id = @request_id                        AND s.recursion = 1                    OPTION (KEEPFIXED PLAN);                END TRY                BEGIN CATCH;                    IF ERROR_NUMBER() = 6335                    BEGIN;                        UPDATE s                        SET                            s.query_plan =                            (                                SELECT                                    N‘--‘ + NCHAR(13) + NCHAR(10) +                                     N‘-- Could not render showplan due to XML data type limitations. ‘ + NCHAR(13) + NCHAR(10) +                                     N‘-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.‘ + NCHAR(13) + NCHAR(10) +                                    N‘--‘ + NCHAR(13) + NCHAR(10) +                                        REPLACE(qp.query_plan, N‘<RelOp‘, NCHAR(13)+NCHAR(10)+N‘<RelOp‘) +                                         NCHAR(13) + NCHAR(10) + N‘--‘ COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]                                FROM sys.dm_exec_text_query_plan                                (                                    @plan_handle,                                     CASE @get_plans                                        WHEN 1 THEN                                            @statement_start_offset                                        ELSE                                            0                                    END,                                     CASE @get_plans                                        WHEN 1 THEN                                            @statement_end_offset                                        ELSE                                            -1                                    END                                ) AS qp                                FOR XML                                    PATH(‘‘),                                    TYPE                            )                        FROM #sessions AS s                        WHERE                             s.session_id = @session_id                            AND s.request_id = @request_id                            AND s.recursion = 1                        OPTION (KEEPFIXED PLAN);                    END;                    ELSE                    BEGIN;                        UPDATE s                        SET                            s.query_plan =                                 CASE ERROR_NUMBER()                                     WHEN 1222 THEN ‘<timeout_exceeded />‘                                    ELSE ‘<error message="‘ + ERROR_MESSAGE() + ‘" />‘                                END                        FROM #sessions AS s                        WHERE                             s.session_id = @session_id                            AND s.request_id = @request_id                            AND s.recursion = 1                        OPTION (KEEPFIXED PLAN);                    END;                END CATCH;                FETCH NEXT FROM plan_cursor                INTO                    @session_id,                    @request_id,                    @plan_handle,                    @statement_start_offset,                    @statement_end_offset;            END;            --Return this to the default            SET LOCK_TIMEOUT -1;            CLOSE plan_cursor;            DEALLOCATE plan_cursor;        END;        IF             @get_locks = 1             AND @recursion = 1            AND @output_column_list LIKE ‘%|[locks|]%‘ ESCAPE ‘|‘        BEGIN;            DECLARE locks_cursor            CURSOR LOCAL FAST_FORWARD            FOR                 SELECT DISTINCT                    database_name                FROM #locks                WHERE                    EXISTS                    (                        SELECT *                        FROM #sessions AS s                        WHERE                            s.session_id = #locks.session_id                            AND recursion = 1                    )                    AND database_name <> ‘(null)‘                OPTION (KEEPFIXED PLAN);            OPEN locks_cursor;            FETCH NEXT FROM locks_cursor            INTO                 @database_name;            WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    SET @sql_n = CONVERT(NVARCHAR(MAX), ‘‘) +                        ‘UPDATE l ‘ +                        ‘SET ‘ +                            ‘object_name = ‘ +                                ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘o.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘), ‘ +                            ‘index_name = ‘ +                                ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘i.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘), ‘ +                            ‘schema_name = ‘ +                                ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘s.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘), ‘ +                            ‘principal_name = ‘ +                                 ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘dp.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘) ‘ +                        ‘FROM #locks AS l ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.allocation_units AS au ON ‘ +                            ‘au.allocation_unit_id = l.allocation_unit_id ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.partitions AS p ON ‘ +                            ‘p.hobt_id = ‘ +                                ‘COALESCE ‘ +                                ‘( ‘ +                                    ‘l.hobt_id, ‘ +                                    ‘CASE ‘ +                                        ‘WHEN au.type IN (1, 3) THEN au.container_id ‘ +                                        ‘ELSE NULL ‘ +                                    ‘END ‘ +                                ‘) ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.partitions AS p1 ON ‘ +                            ‘l.hobt_id IS NULL ‘ +                            ‘AND au.type = 2 ‘ +                            ‘AND p1.partition_id = au.container_id ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.objects AS o ON ‘ +                            ‘o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.indexes AS i ON ‘ +                            ‘i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ‘ +                            ‘AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.schemas AS s ON ‘ +                            ‘s.schema_id = COALESCE(l.schema_id, o.schema_id) ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.database_principals AS dp ON ‘ +                            ‘dp.principal_id = l.principal_id ‘ +                        ‘WHERE ‘ +                            ‘l.database_name = @database_name ‘ +                        ‘OPTION (KEEPFIXED PLAN); ‘;                                        EXEC sp_executesql                        @sql_n,                        N‘@database_name sysname‘,                        @database_name;                END TRY                BEGIN CATCH;                    UPDATE #locks                    SET                        query_error =                             REPLACE                            (                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    CONVERT                                    (                                        NVARCHAR(MAX),                                         ERROR_MESSAGE() COLLATE Latin1_General_Bin2                                    ),                                    NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                    NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                    NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                NCHAR(0),                                N‘‘                            )                    WHERE                         database_name = @database_name                    OPTION (KEEPFIXED PLAN);                END CATCH;                FETCH NEXT FROM locks_cursor                INTO                    @database_name;            END;            CLOSE locks_cursor;            DEALLOCATE locks_cursor;            CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);            UPDATE s            SET                 s.locks =                (                    SELECT                         REPLACE                        (                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                CONVERT                                (                                    NVARCHAR(MAX),                                     l1.database_name COLLATE Latin1_General_Bin2                                ),                                NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                            NCHAR(0),                            N‘‘                        ) AS [Database/@name],                        MIN(l1.query_error) AS [Database/@query_error],                        (                            SELECT                                 l2.request_mode AS [Lock/@request_mode],                                l2.request_status AS [Lock/@request_status],                                COUNT(*) AS [Lock/@request_count]                            FROM #locks AS l2                            WHERE                                 l1.session_id = l2.session_id                                AND l1.request_id = l2.request_id                                AND l2.database_name = l1.database_name                                AND l2.resource_type = ‘DATABASE‘                            GROUP BY                                l2.request_mode,                                l2.request_status                            FOR XML                                PATH(‘‘),                                TYPE                        ) AS [Database/Locks],                        (                            SELECT                                COALESCE(l3.object_name, ‘(null)‘) AS [Object/@name],                                l3.schema_name AS [Object/@schema_name],                                (                                    SELECT                                        l4.resource_type AS [Lock/@resource_type],                                        l4.page_type AS [Lock/@page_type],                                        l4.index_name AS [Lock/@index_name],                                        CASE                                             WHEN l4.object_name IS NULL THEN l4.schema_name                                            ELSE NULL                                        END AS [Lock/@schema_name],                                        l4.principal_name AS [Lock/@principal_name],                                        l4.resource_description AS [Lock/@resource_description],                                        l4.request_mode AS [Lock/@request_mode],                                        l4.request_status AS [Lock/@request_status],                                        SUM(l4.request_count) AS [Lock/@request_count]                                    FROM #locks AS l4                                    WHERE                                         l4.session_id = l3.session_id                                        AND l4.request_id = l3.request_id                                        AND l3.database_name = l4.database_name                                        AND COALESCE(l3.object_name, ‘(null)‘) = COALESCE(l4.object_name, ‘(null)‘)                                        AND COALESCE(l3.schema_name, ‘‘) = COALESCE(l4.schema_name, ‘‘)                                        AND l4.resource_type <> ‘DATABASE‘                                    GROUP BY                                        l4.resource_type,                                        l4.page_type,                                        l4.index_name,                                        CASE                                             WHEN l4.object_name IS NULL THEN l4.schema_name                                            ELSE NULL                                        END,                                        l4.principal_name,                                        l4.resource_description,                                        l4.request_mode,                                        l4.request_status                                    FOR XML                                        PATH(‘‘),                                        TYPE                                ) AS [Object/Locks]                            FROM #locks AS l3                            WHERE                                 l3.session_id = l1.session_id                                AND l3.request_id = l1.request_id                                AND l3.database_name = l1.database_name                                AND l3.resource_type <> ‘DATABASE‘                            GROUP BY                                 l3.session_id,                                l3.request_id,                                l3.database_name,                                COALESCE(l3.object_name, ‘(null)‘),                                l3.schema_name                            FOR XML                                PATH(‘‘),                                TYPE                        ) AS [Database/Objects]                    FROM #locks AS l1                    WHERE                        l1.session_id = s.session_id                        AND l1.request_id = s.request_id                        AND l1.start_time IN (s.start_time, s.last_request_start_time)                        AND s.recursion = 1                    GROUP BY                         l1.session_id,                        l1.request_id,                        l1.database_name                    FOR XML                        PATH(‘‘),                        TYPE                )            FROM #sessions s            OPTION (KEEPFIXED PLAN);        END;        IF             @find_block_leaders = 1            AND @recursion = 1            AND @output_column_list LIKE ‘%|[blocked_session_count|]%‘ ESCAPE ‘|‘        BEGIN;            WITH            blockers AS            (                SELECT                    session_id,                    session_id AS top_level_session_id                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            @get_task_info = 2            AND @output_column_list LIKE ‘%|[additional_info|]%‘ ESCAPE ‘|‘            AND @recursion = 1        BEGIN;            CREATE TABLE #blocked_requests            (                session_id SMALLINT NOT NULL,                request_id INT NOT NULL,                database_name sysname NOT NULL,                object_id INT,                hobt_id BIGINT,                schema_id INT,                schema_name sysname NULL,                object_name sysname NULL,                query_error NVARCHAR(2048),                PRIMARY KEY (database_name, session_id, request_id)            );            CREATE STATISTICS s_database_name ON #blocked_requests (database_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_object_name ON #blocked_requests (object_name)            WITH SAMPLE 0 ROWS, NORECOMPUTE;            CREATE STATISTICS s_query_error ON #blocked_requests (query_error)            WITH SAMPLE 0 ROWS, NORECOMPUTE;                    INSERT #blocked_requests            (                session_id,                request_id,                database_name,                object_id,                hobt_id,                schema_id            )            SELECT                session_id,                request_id,                database_name,                object_id,                hobt_id,                CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(‘ = ‘, schema_node) + 3, LEN(schema_node))) AS schema_id            FROM            (                SELECT                    session_id,                    request_id,                    agent_nodes.agent_node.value(‘(database_name/text())[1]‘, ‘sysname‘) AS database_name,                    agent_nodes.agent_node.value(‘(object_id/text())[1]‘, ‘int‘) AS object_id,                    agent_nodes.agent_node.value(‘(hobt_id/text())[1]‘, ‘bigint‘) AS hobt_id,                    agent_nodes.agent_node.value(‘(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]‘, ‘varchar(100)‘) AS schema_node                FROM #sessions AS s                CROSS APPLY s.additional_info.nodes(‘//block_info‘) AS agent_nodes (agent_node)                WHERE                    s.recursion = 1            ) AS t            WHERE                t.database_name IS NOT NULL                AND                (                    t.object_id IS NOT NULL                    OR t.hobt_id IS NOT NULL                    OR t.schema_node IS NOT NULL                );                        DECLARE blocks_cursor            CURSOR LOCAL FAST_FORWARD            FOR                SELECT DISTINCT                    database_name                FROM #blocked_requests;                            OPEN blocks_cursor;                        FETCH NEXT FROM blocks_cursor            INTO                 @database_name;                        WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    SET @sql_n =                         CONVERT(NVARCHAR(MAX), ‘‘) +                        ‘UPDATE b ‘ +                        ‘SET ‘ +                            ‘b.schema_name = ‘ +                                ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘s.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘), ‘ +                            ‘b.object_name = ‘ +                                ‘REPLACE ‘ +                                ‘( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                    ‘REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ‘ +                                        ‘o.name COLLATE Latin1_General_Bin2, ‘ +                                        ‘NCHAR(31),N‘‘?‘‘),NCHAR(30),N‘‘?‘‘),NCHAR(29),N‘‘?‘‘),NCHAR(28),N‘‘?‘‘),NCHAR(27),N‘‘?‘‘),NCHAR(26),N‘‘?‘‘),NCHAR(25),N‘‘?‘‘),NCHAR(24),N‘‘?‘‘),NCHAR(23),N‘‘?‘‘),NCHAR(22),N‘‘?‘‘), ‘ +                                        ‘NCHAR(21),N‘‘?‘‘),NCHAR(20),N‘‘?‘‘),NCHAR(19),N‘‘?‘‘),NCHAR(18),N‘‘?‘‘),NCHAR(17),N‘‘?‘‘),NCHAR(16),N‘‘?‘‘),NCHAR(15),N‘‘?‘‘),NCHAR(14),N‘‘?‘‘),NCHAR(12),N‘‘?‘‘), ‘ +                                        ‘NCHAR(11),N‘‘?‘‘),NCHAR(8),N‘‘?‘‘),NCHAR(7),N‘‘?‘‘),NCHAR(6),N‘‘?‘‘),NCHAR(5),N‘‘?‘‘),NCHAR(4),N‘‘?‘‘),NCHAR(3),N‘‘?‘‘),NCHAR(2),N‘‘?‘‘),NCHAR(1),N‘‘?‘‘), ‘ +                                    ‘NCHAR(0), ‘ +                                    N‘‘‘‘‘ ‘ +                                ‘) ‘ +                        ‘FROM #blocked_requests AS b ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.partitions AS p ON ‘ +                            ‘p.hobt_id = b.hobt_id ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.objects AS o ON ‘ +                            ‘o.object_id = COALESCE(p.object_id, b.object_id) ‘ +                        ‘LEFT OUTER JOIN ‘ + QUOTENAME(@database_name) + ‘.sys.schemas AS s ON ‘ +                            ‘s.schema_id = COALESCE(o.schema_id, b.schema_id) ‘ +                        ‘WHERE ‘ +                            ‘b.database_name = @database_name; ‘;                                        EXEC sp_executesql                        @sql_n,                        N‘@database_name sysname‘,                        @database_name;                END TRY                BEGIN CATCH;                    UPDATE #blocked_requests                    SET                        query_error =                             REPLACE                            (                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    CONVERT                                    (                                        NVARCHAR(MAX),                                         ERROR_MESSAGE() COLLATE Latin1_General_Bin2                                    ),                                    NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                    NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                    NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                NCHAR(0),                                N‘‘                            )                    WHERE                        database_name = @database_name;                END CATCH;                FETCH NEXT FROM blocks_cursor                INTO                    @database_name;            END;                        CLOSE blocks_cursor;            DEALLOCATE blocks_cursor;                        UPDATE s            SET                additional_info.modify                (‘                    insert <schema_name>{sql:column("b.schema_name")}</schema_name>                    as last                    into (/additional_info/block_info)[1]                ‘)            FROM #sessions AS s            INNER JOIN #blocked_requests AS b ON                b.session_id = s.session_id                AND b.request_id = s.request_id                AND s.recursion = 1            WHERE                b.schema_name IS NOT NULL;            UPDATE s            SET                additional_info.modify                (‘                    insert <object_name>{sql:column("b.object_name")}</object_name>                    as last                    into (/additional_info/block_info)[1]                ‘)            FROM #sessions AS s            INNER JOIN #blocked_requests AS b ON                b.session_id = s.session_id                AND b.request_id = s.request_id                AND s.recursion = 1            WHERE                b.object_name IS NOT NULL;            UPDATE s            SET                additional_info.modify                (‘                    insert <query_error>{sql:column("b.query_error")}</query_error>                    as last                    into (/additional_info/block_info)[1]                ‘)            FROM #sessions AS s            INNER JOIN #blocked_requests AS b ON                b.session_id = s.session_id                AND b.request_id = s.request_id                AND s.recursion = 1            WHERE                b.query_error IS NOT NULL;        END;        IF            @output_column_list LIKE ‘%|[program_name|]%‘ ESCAPE ‘|‘            AND @output_column_list LIKE ‘%|[additional_info|]%‘ ESCAPE ‘|‘            AND @recursion = 1        BEGIN;            DECLARE @job_id UNIQUEIDENTIFIER;            DECLARE @step_id INT;            DECLARE agent_cursor            CURSOR LOCAL FAST_FORWARD            FOR                 SELECT                    s.session_id,                    agent_nodes.agent_node.value(‘(job_id/text())[1]‘, ‘uniqueidentifier‘) AS job_id,                    agent_nodes.agent_node.value(‘(step_id/text())[1]‘, ‘int‘) AS step_id                FROM #sessions AS s                CROSS APPLY s.additional_info.nodes(‘//agent_job_info‘) AS agent_nodes (agent_node)                WHERE                    s.recursion = 1            OPTION (KEEPFIXED PLAN);                        OPEN agent_cursor;            FETCH NEXT FROM agent_cursor            INTO                 @session_id,                @job_id,                @step_id;            WHILE @@FETCH_STATUS = 0            BEGIN;                BEGIN TRY;                    DECLARE @job_name sysname;                    SET @job_name = NULL;                    DECLARE @step_name sysname;                    SET @step_name = NULL;                                        SELECT                        @job_name =                             REPLACE                            (                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    j.name,                                    NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                    NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                    NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                NCHAR(0),                                N‘?‘                            ),                        @step_name =                             REPLACE                            (                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(                                    s.step_name,                                    NCHAR(31),N‘?‘),NCHAR(30),N‘?‘),NCHAR(29),N‘?‘),NCHAR(28),N‘?‘),NCHAR(27),N‘?‘),NCHAR(26),N‘?‘),NCHAR(25),N‘?‘),NCHAR(24),N‘?‘),NCHAR(23),N‘?‘),NCHAR(22),N‘?‘),                                    NCHAR(21),N‘?‘),NCHAR(20),N‘?‘),NCHAR(19),N‘?‘),NCHAR(18),N‘?‘),NCHAR(17),N‘?‘),NCHAR(16),N‘?‘),NCHAR(15),N‘?‘),NCHAR(14),N‘?‘),NCHAR(12),N‘?‘),                                    NCHAR(11),N‘?‘),NCHAR(8),N‘?‘),NCHAR(7),N‘?‘),NCHAR(6),N‘?‘),NCHAR(5),N‘?‘),NCHAR(4),N‘?‘),NCHAR(3),N‘?‘),NCHAR(2),N‘?‘),NCHAR(1),N‘?‘),                                NCHAR(0),                                N‘?‘                            )                    FROM msdb.dbo.sysjobs AS j                    INNER JOIN msdb..sysjobsteps AS s ON                        j.job_id = s.job_id                    WHERE                        j.job_id = @job_id                        AND s.step_id = @step_id;                    IF @job_name IS NOT NULL                    BEGIN;                        UPDATE s                        SET                            additional_info.modify                            (‘                                insert text{sql:variable("@job_name")}                                into (/additional_info/agent_job_info/job_name)[1]                            ‘)                        FROM #sessions AS s                        WHERE                             s.session_id = @session_id                        OPTION (KEEPFIXED PLAN);                                                UPDATE s                        SET                            additional_info.modify                            (‘                                insert text{sql:variable("@step_name")}                                into (/additional_info/agent_job_info/step_name)[1]                            ‘)                        FROM #sessions AS s                        WHERE                             s.session_id = @session_id                        OPTION (KEEPFIXED PLAN);                    END;                END TRY                BEGIN CATCH;                    DECLARE @msdb_error_message NVARCHAR(256);                    SET @msdb_error_message = ERROR_MESSAGE();                                    UPDATE s                    SET                        additional_info.modify                        (‘                            insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>                            as last                            into (/additional_info/agent_job_info)[1]                        ‘)                    FROM #sessions AS s                    WHERE                         s.session_id = @session_id                        AND s.recursion = 1                    OPTION (KEEPFIXED PLAN);                END CATCH;                FETCH NEXT FROM agent_cursor                INTO                     @session_id,                    @job_id,                    @step_id;            END;            CLOSE agent_cursor;            DEALLOCATE agent_cursor;        END;                 IF             @delta_interval > 0             AND @recursion <> 1        BEGIN;            SET @recursion = 1;            DECLARE @delay_time CHAR(12);            SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);            WAITFOR DELAY @delay_time;            GOTO REDO;        END;    END;    SET @sql =         --Outer column list        CONVERT        (            VARCHAR(MAX),            CASE                WHEN                     @destination_table <> ‘‘                     AND @return_schema = 0                         THEN ‘INSERT ‘ + @destination_table + ‘ ‘                ELSE ‘‘            END +            ‘SELECT ‘ +                @output_column_list + ‘ ‘ +            CASE @return_schema                WHEN 1 THEN ‘INTO #session_schema ‘                ELSE ‘‘            END        --End outer column list        ) +         --Inner column list        CONVERT        (            VARCHAR(MAX),            ‘FROM ‘ +            ‘( ‘ +                ‘SELECT ‘ +                    ‘session_id, ‘ +                    --[dd hh:mm:ss.mss]                    CASE                        WHEN @format_output IN (1, 2) THEN                            ‘CASE ‘ +                                ‘WHEN elapsed_time < 0 THEN ‘ +                                    ‘RIGHT ‘ +                                    ‘( ‘ +                                        ‘REPLICATE(‘‘0‘‘, max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ‘ +                                        ‘max_elapsed_length ‘ +                                    ‘) + ‘ +                                        ‘RIGHT ‘ +                                        ‘( ‘ +                                            ‘CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ‘ +                                            ‘9 ‘ +                                        ‘) + ‘ +                                        ‘‘‘.000‘‘ ‘ +                                ‘ELSE ‘ +                                    ‘RIGHT ‘ +                                    ‘( ‘ +                                        ‘REPLICATE(‘‘0‘‘, max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ‘ +                                        ‘max_elapsed_length ‘ +                                    ‘) + ‘ +                                        ‘RIGHT ‘ +                                        ‘( ‘ +                                            ‘CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ‘ +                                            ‘9 ‘ +                                        ‘) + ‘ +                                        ‘‘‘.‘‘ + ‘ +                                         ‘RIGHT(‘‘000‘‘ + CONVERT(VARCHAR, elapsed_time % 1000), 3) ‘ +                            ‘END AS [dd hh:mm:ss.mss], ‘                        ELSE                            ‘‘                    END +                    --[dd hh:mm:ss.mss (avg)] / avg_elapsed_time                    CASE                         WHEN  @format_output IN (1, 2) THEN                             ‘RIGHT ‘ +                            ‘( ‘ +                                ‘‘‘00‘‘ + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ‘ +                                ‘2 ‘ +                            ‘) + ‘ +                                ‘RIGHT ‘ +                                ‘( ‘ +                                    ‘CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ‘ +                                    ‘9 ‘ +                                ‘) + ‘ +                                ‘‘‘.‘‘ + ‘ +                                ‘RIGHT(‘‘000‘‘ + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], ‘                        ELSE                            ‘avg_elapsed_time, ‘                    END +                    --physical_io                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘physical_io, ‘ +                    --reads                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘reads, ‘ +                    --physical_reads                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘physical_reads, ‘ +                    --writes                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘writes, ‘ +                    --tempdb_allocations                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘tempdb_allocations, ‘ +                    --tempdb_current                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘tempdb_current, ‘ +                    --CPU                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘CPU, ‘ +                    --context_switches                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS ‘                        WHEN 2 THEN ‘CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS ‘                        ELSE ‘‘                    END + ‘context_switches, ‘ +                    --used_memory                    CASE @format_output                        WHEN 1 THEN ‘CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS