We have this great Website about the Stored Procedure : sp_whoisactive  -  Version 11.30 - December 10, 2017

This version does NOT create a stored procedure, it just run in an ad hoc manner.

   1
   2-- http://whoisactive.com
   3
   4SET QUOTED_IDENTIFIER ON;
   5SET ANSI_PADDING ON;
   6SET CONCAT_NULL_YIELDS_NULL ON;
   7SET ANSI_WARNINGS ON;
   8SET NUMERIC_ROUNDABORT OFF;
   9SET ARITHABORT ON;
  10GO
  11
  12DECLARE
  13    --~
  14    --Filters--Both inclusive and exclusive
  15    --Set either filter to '' to disable
  16    --Valid filter types are: session, program, database, login, and host
  17    --Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
  18    --All other filter types support % or _ as wildcards
  19      @filter               sysname       = ''
  20    , @filter_type          VARCHAR(10)   = 'session'
  21    , @not_filter           sysname       = ''
  22    , @not_filter_type      VARCHAR(10)   = 'session'
  23    ,
  24
  25    --Retrieve data about the calling session?
  26      @show_own_spid        BIT           = 0
  27    ,
  28
  29    --Retrieve data about system sessions?
  30      @show_system_spids    BIT           = 0
  31    ,
  32
  33    --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
  34    --0 does not pull any sleeping SPIDs
  35    --1 pulls only those sleeping SPIDs that also have an open transaction
  36    --2 pulls all sleeping SPIDs
  37      @show_sleeping_spids  TINYINT       = 1
  38    ,
  39
  40    --If 1, gets the full stored procedure or running batch, when available
  41    --If 0, gets only the actual statement that is currently running in the batch or procedure
  42      @get_full_inner_text  BIT           = 0
  43    ,
  44
  45    --Get associated query plans for running tasks, if available
  46    --If @get_plans = 1, gets the plan based on the request's statement offset
  47    --If @get_plans = 2, gets the entire plan based on the request's plan_handle
  48      @get_plans            TINYINT       = 0
  49    ,
  50
  51    --Get the associated outer ad hoc query or stored procedure call, if available
  52      @get_outer_command    BIT           = 0
  53    ,
  54
  55    --Enables pulling transaction log write info and transaction duration
  56      @get_transaction_info BIT           = 0
  57    ,
  58
  59    --Get information on active tasks, based on three interest levels
  60    --Level 0 does not pull any task-related information
  61    --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
  62    --Level 2 pulls all available task-based metrics, including:
  63    --number of active tasks, current wait stats, physical I/O, context switches, and blocker information
  64      @get_task_info        TINYINT       = 1
  65    ,
  66
  67    --Gets associated locks for each request, aggregated in an XML format
  68      @get_locks            BIT           = 0
  69    ,
  70
  71    --Get average time for past runs of an active query
  72    --(based on the combination of plan handle, sql handle, and offset)
  73      @get_avg_time         BIT           = 0
  74    ,
  75
  76    --Get additional non-performance-related information about the session or request
  77    --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,
  78    --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
  79    --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
  80    --
  81    --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
  82    --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
  83    --
  84    --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
  85    --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,
  86    --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
  87      @get_additional_info  BIT           = 0
  88    ,
  89
  90    --Walk the blocking chain and count the number of
  91    --total SPIDs blocked all the way down by a given session
  92    --Also enables task_info Level 1, if @get_task_info is set to 0
  93      @find_block_leaders   BIT           = 0
  94    ,
  95
  96    --Pull deltas on various metrics
  97    --Interval in seconds to wait before doing the second data pull
  98      @delta_interval       TINYINT       = 0
  99    ,
 100
 101    --List of desired output columns, in desired order
 102    --Note that the final output will be the intersection of all enabled features and all
 103    --columns in the list. Therefore, only columns associated with enabled features will
 104    --actually appear in the output. Likewise, removing columns from this list may effectively
 105    --disable features, even if they are turned on
 106    --
 107    --Each element in this list must be one of the valid output column names. Names must be
 108    --delimited by square brackets. White space, formatting, and additional characters are
 109    --allowed, as long as the list contains exact matches of delimited valid column names.
 110      @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][%]'
 111    ,
 112
 113    --Column(s) by which to sort output, optionally with sort directions.
 114    --Valid column choices:
 115    --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
 116    --tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,
 117    --physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,
 118    --CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,
 119    --open_tran_count, blocking_session_id, blocked_session_count, percent_complete,
 120    --host_name, login_name, database_name, start_time, login_time, program_name
 121    --
 122    --Note that column names in the list must be bracket-delimited. Commas and/or white
 123    --space are not required.
 124      @sort_order           VARCHAR(500)  = '[start_time] ASC'
 125    ,
 126
 127    --Formats some of the output columns in a more "human readable" form
 128    --0 disables outfput format
 129    --1 formats the output for variable-width fonts
 130    --2 formats the output for fixed-width fonts
 131      @format_output        TINYINT       = 1
 132    ,
 133
 134    --If set to a non-blank value, the script will attempt to insert into the specified
 135    --destination table. Please note that the script will not verify that the table exists,
 136    --or that it has the correct schema, before doing the insert.
 137    --Table can be specified in one, two, or three-part format
 138      @destination_table    VARCHAR(4000) = ''
 139    ,
 140
 141    --If set to 1, no data collection will happen and no result set will be returned; instead,
 142    --a CREATE TABLE statement will be returned via the @schema parameter, which will match
 143    --the schema of the result set that would be returned by using the same collection of the
 144    --rest of the parameters. The CREATE TABLE statement will have a placeholder token of
 145    --<table_name> in place of an actual table name.
 146      @return_schema        BIT           = 0
 147    , @schema               VARCHAR(MAX)  = NULL
 148    ,
 149
 150    --Help! What do I do?
 151      @help                 BIT           = 0
 152--~
 153
 154/*
 155OUTPUT COLUMNS
 156--------------
 157Formatted/Non:    [session_id] [smallint] NOT NULL
 158Session ID (a.k.a. SPID)
 159
 160Formatted:        [dd hh:mm:ss.mss] [varchar](15) NULL
 161Non-Formatted:    <not returned>
 162For an active request, time the query has been running
 163For a sleeping session, time since the last batch completed
 164
 165Formatted:        [dd hh:mm:ss.mss (avg)] [varchar](15) NULL
 166Non-Formatted:    [avg_elapsed_time] [int] NULL
 167(Requires @get_avg_time option)
 168How much time has the active portion of the query taken in the past, on average?
 169
 170Formatted:        [physical_io] [varchar](30) NULL
 171Non-Formatted:    [physical_io] [bigint] NULL
 172Shows the number of physical I/Os, for active requests
 173
 174Formatted:        [reads] [varchar](30) NULL
 175Non-Formatted:    [reads] [bigint] NULL
 176For an active request, number of reads done for the current query
 177For a sleeping session, total number of reads done over the lifetime of the session
 178
 179Formatted:        [physical_reads] [varchar](30) NULL
 180Non-Formatted:    [physical_reads] [bigint] NULL
 181For an active request, number of physical reads done for the current query
 182For a sleeping session, total number of physical reads done over the lifetime of the session
 183
 184Formatted:        [writes] [varchar](30) NULL
 185Non-Formatted:    [writes] [bigint] NULL
 186For an active request, number of writes done for the current query
 187For a sleeping session, total number of writes done over the lifetime of the session
 188
 189Formatted:        [tempdb_allocations] [varchar](30) NULL
 190Non-Formatted:    [tempdb_allocations] [bigint] NULL
 191For an active request, number of TempDB writes done for the current query
 192For a sleeping session, total number of TempDB writes done over the lifetime of the session
 193
 194Formatted:        [tempdb_current] [varchar](30) NULL
 195Non-Formatted:    [tempdb_current] [bigint] NULL
 196For an active request, number of TempDB pages currently allocated for the query
 197For a sleeping session, number of TempDB pages currently allocated for the session
 198
 199Formatted:        [CPU] [varchar](30) NULL
 200Non-Formatted:    [CPU] [int] NULL
 201For an active request, total CPU time consumed by the current query
 202For a sleeping session, total CPU time consumed over the lifetime of the session
 203
 204Formatted:        [context_switches] [varchar](30) NULL
 205Non-Formatted:    [context_switches] [bigint] NULL
 206Shows the number of context switches, for active requests
 207
 208Formatted:        [used_memory] [varchar](30) NOT NULL
 209Non-Formatted:    [used_memory] [bigint] NOT NULL
 210For an active request, total memory consumption for the current query
 211For a sleeping session, total current memory consumption
 212
 213Formatted:        [physical_io_delta] [varchar](30) NULL
 214Non-Formatted:    [physical_io_delta] [bigint] NULL
 215(Requires @delta_interval option)
 216Difference between the number of physical I/Os reported on the first and second collections.
 217If the request started after the first collection, the value will be NULL
 218
 219Formatted:        [reads_delta] [varchar](30) NULL
 220Non-Formatted:    [reads_delta] [bigint] NULL
 221(Requires @delta_interval option)
 222Difference between the number of reads reported on the first and second collections.
 223If the request started after the first collection, the value will be NULL
 224
 225Formatted:        [physical_reads_delta] [varchar](30) NULL
 226Non-Formatted:    [physical_reads_delta] [bigint] NULL
 227(Requires @delta_interval option)
 228Difference between the number of physical reads reported on the first and second collections.
 229If the request started after the first collection, the value will be NULL
 230
 231Formatted:        [writes_delta] [varchar](30) NULL
 232Non-Formatted:    [writes_delta] [bigint] NULL
 233(Requires @delta_interval option)
 234Difference between the number of writes reported on the first and second collections.
 235If the request started after the first collection, the value will be NULL
 236
 237Formatted:        [tempdb_allocations_delta] [varchar](30) NULL
 238Non-Formatted:    [tempdb_allocations_delta] [bigint] NULL
 239(Requires @delta_interval option)
 240Difference between the number of TempDB writes reported on the first and second collections.
 241If the request started after the first collection, the value will be NULL
 242
 243Formatted:        [tempdb_current_delta] [varchar](30) NULL
 244Non-Formatted:    [tempdb_current_delta] [bigint] NULL
 245(Requires @delta_interval option)
 246Difference between the number of allocated TempDB pages reported on the first and second
 247collections. If the request started after the first collection, the value will be NULL
 248
 249Formatted:        [CPU_delta] [varchar](30) NULL
 250Non-Formatted:    [CPU_delta] [int] NULL
 251(Requires @delta_interval option)
 252Difference between the CPU time reported on the first and second collections.
 253If the request started after the first collection, the value will be NULL
 254
 255Formatted:        [context_switches_delta] [varchar](30) NULL
 256Non-Formatted:    [context_switches_delta] [bigint] NULL
 257(Requires @delta_interval option)
 258Difference between the context switches count reported on the first and second collections
 259If the request started after the first collection, the value will be NULL
 260
 261Formatted:        [used_memory_delta] [varchar](30) NULL
 262Non-Formatted:    [used_memory_delta] [bigint] NULL
 263Difference between the memory usage reported on the first and second collections
 264If the request started after the first collection, the value will be NULL
 265
 266Formatted:        [tasks] [varchar](30) NULL
 267Non-Formatted:    [tasks] [smallint] NULL
 268Number of worker tasks currently allocated, for active requests
 269
 270Formatted/Non:    [status] [varchar](30) NOT NULL
 271Activity status for the session (running, sleeping, etc)
 272
 273Formatted/Non:    [wait_info] [nvarchar](4000) NULL
 274Aggregates wait information, in the following format:
 275(Ax: Bms/Cms/Dms)E
 276A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
 277times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
 278If two tasks are waiting, each of their wait times will be shown (B/C). If three or more
 279tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
 280If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),
 281the page type will be identified.
 282If wait type E is CXPACKET, the nodeId from the query plan will be identified
 283
 284Formatted/Non:    [locks] [xml] NULL
 285(Requires @get_locks option)
 286Aggregates lock information, in XML format.
 287The lock XML includes the lock mode, locked object, and aggregates the number of requests.
 288Attempts are made to identify locked objects by name
 289
 290Formatted/Non:    [tran_start_time] [datetime] NULL
 291(Requires @get_transaction_info option)
 292Date and time that the first transaction opened by a session caused a transaction log
 293write to occur.
 294
 295Formatted/Non:    [tran_log_writes] [nvarchar](4000) NULL
 296(Requires @get_transaction_info option)
 297Aggregates transaction log write information, in the following format:
 298A:wB (C kB)
 299A is a database that has been touched by an active transaction
 300B is the number of log writes that have been made in the database as a result of the transaction
 301C is the number of log kilobytes consumed by the log records
 302
 303Formatted:        [open_tran_count] [varchar](30) NULL
 304Non-Formatted:    [open_tran_count] [smallint] NULL
 305Shows the number of open transactions the session has open
 306
 307Formatted:        [sql_command] [xml] NULL
 308Non-Formatted:    [sql_command] [nvarchar](max) NULL
 309(Requires @get_outer_command option)
 310Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,
 311if available
 312
 313Formatted:        [sql_text] [xml] NULL
 314Non-Formatted:    [sql_text] [nvarchar](max) NULL
 315Shows the SQL text for active requests or the last statement executed
 316for sleeping sessions, if available in either case.
 317If @get_full_inner_text option is set, shows the full text of the batch.
 318Otherwise, shows only the active statement within the batch.
 319If the query text is locked, a special timeout message will be sent, in the following format:
 320<timeout_exceeded />
 321If an error occurs, an error message will be sent, in the following format:
 322<error message="message" />
 323
 324Formatted/Non:    [query_plan] [xml] NULL
 325(Requires @get_plans option)
 326Shows the query plan for the request, if available.
 327If the plan is locked, a special timeout message will be sent, in the following format:
 328<timeout_exceeded />
 329If an error occurs, an error message will be sent, in the following format:
 330<error message="message" />
 331
 332Formatted/Non:    [blocking_session_id] [smallint] NULL
 333When applicable, shows the blocking SPID
 334
 335Formatted:        [blocked_session_count] [varchar](30) NULL
 336Non-Formatted:    [blocked_session_count] [smallint] NULL
 337(Requires @find_block_leaders option)
 338The total number of SPIDs blocked by this session,
 339all the way down the blocking chain.
 340
 341Formatted:        [percent_complete] [varchar](30) NULL
 342Non-Formatted:    [percent_complete] [real] NULL
 343When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)
 344
 345Formatted/Non:    [host_name] [sysname] NOT NULL
 346Shows the host name for the connection
 347
 348Formatted/Non:    [login_name] [sysname] NOT NULL
 349Shows the login name for the connection
 350
 351Formatted/Non:    [database_name] [sysname] NULL
 352Shows the connected database
 353
 354Formatted/Non:    [program_name] [sysname] NULL
 355Shows the reported program/application name
 356
 357Formatted/Non:    [additional_info] [xml] NULL
 358(Requires @get_additional_info option)
 359Returns additional non-performance-related session/request information
 360If the script finds a SQL Agent job running, the name of the job and job step will be reported
 361If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported
 362
 363Formatted/Non:    [start_time] [datetime] NOT NULL
 364For active requests, shows the time the request started
 365For sleeping sessions, shows the time the last batch completed
 366
 367Formatted/Non:    [login_time] [datetime] NOT NULL
 368Shows the time that the session connected
 369
 370Formatted/Non:    [request_id] [int] NULL
 371For active requests, shows the request_id
 372Should be 0 unless MARS is being used
 373
 374Formatted/Non:    [collection_time] [datetime] NOT NULL
 375Time that this script's final SELECT ran
 376*/
 377
 378BEGIN;
 379    SET NOCOUNT ON;
 380    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 381    SET QUOTED_IDENTIFIER ON;
 382    SET ANSI_PADDING ON;
 383    SET CONCAT_NULL_YIELDS_NULL ON;
 384    SET ANSI_WARNINGS ON;
 385    SET NUMERIC_ROUNDABORT OFF;
 386    SET ARITHABORT ON;
 387
 388    IF @filter IS NULL
 389        OR @filter_type IS NULL
 390        OR @not_filter IS NULL
 391        OR @not_filter_type IS NULL
 392        OR @show_own_spid IS NULL
 393        OR @show_system_spids IS NULL
 394        OR @show_sleeping_spids IS NULL
 395        OR @get_full_inner_text IS NULL
 396        OR @get_plans IS NULL
 397        OR @get_outer_command IS NULL
 398        OR @get_transaction_info IS NULL
 399        OR @get_task_info IS NULL
 400        OR @get_locks IS NULL
 401        OR @get_avg_time IS NULL
 402        OR @get_additional_info IS NULL
 403        OR @find_block_leaders IS NULL
 404        OR @delta_interval IS NULL
 405        OR @format_output IS NULL
 406        OR @output_column_list IS NULL
 407        OR @sort_order IS NULL
 408        OR @return_schema IS NULL
 409        OR @destination_table IS NULL
 410        OR @help IS NULL
 411    BEGIN;
 412        RAISERROR('Input parameters cannot be NULL', 16, 1);
 413        RETURN;
 414    END;
 415
 416    IF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
 417    BEGIN;
 418        RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
 419        RETURN;
 420    END;
 421
 422    IF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%'
 423    BEGIN;
 424        RAISERROR('Session filters must be valid integers', 16, 1);
 425        RETURN;
 426    END;
 427
 428    IF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
 429    BEGIN;
 430        RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
 431        RETURN;
 432    END;
 433
 434    IF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%'
 435    BEGIN;
 436        RAISERROR('Session filters must be valid integers', 16, 1);
 437        RETURN;
 438    END;
 439
 440    IF @show_sleeping_spids NOT IN (0, 1, 2)
 441    BEGIN;
 442        RAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1);
 443        RETURN;
 444    END;
 445
 446    IF @get_plans NOT IN (0, 1, 2)
 447    BEGIN;
 448        RAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1);
 449        RETURN;
 450    END;
 451
 452    IF @get_task_info NOT IN (0, 1, 2)
 453    BEGIN;
 454        RAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1);
 455        RETURN;
 456    END;
 457
 458    IF @format_output NOT IN (0, 1, 2)
 459    BEGIN;
 460        RAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1);
 461        RETURN;
 462    END;
 463
 464    IF @help = 1
 465    BEGIN;
 466        DECLARE @header  VARCHAR(MAX)
 467            ,   @params  VARCHAR(MAX)
 468            ,   @outputs VARCHAR(MAX);
 469
 470        SELECT @header =
 471        REPLACE
 472        (
 473        REPLACE
 474        (
 475        CONVERT
 476        (
 477        VARCHAR(MAX),
 478        SUBSTRING
 479        (
 480        t.text,
 481        CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94,
 482        CHARINDEX(REPLICATE('*', 93) + '/', t.text) - (CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94)
 483        )
 484        ),
 485        CHAR(13)+CHAR(10),
 486        CHAR(13)
 487        ),
 488        '    ',
 489        ''
 490        )
 491        ,      @params =
 492        CHAR(13) +
 493        REPLACE
 494        (
 495        REPLACE
 496        (
 497        CONVERT
 498        (
 499        VARCHAR(MAX),
 500        SUBSTRING
 501        (
 502        t.text,
 503        CHARINDEX('--~', t.text) + 5,
 504        CHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5)
 505        )
 506        ),
 507        CHAR(13)+CHAR(10),
 508        CHAR(13)
 509        ),
 510        '    ',
 511        ''
 512        )
 513        ,      @outputs =
 514        CHAR(13) +
 515        REPLACE
 516        (
 517        REPLACE
 518        (
 519        REPLACE
 520        (
 521        CONVERT
 522        (
 523        VARCHAR(MAX),
 524        SUBSTRING
 525        (
 526        t.text,
 527        CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32,
 528        CHARINDEX('*/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32)
 529        )
 530        ),
 531        CHAR(9),
 532        CHAR(255)
 533        ),
 534        CHAR(13)+CHAR(10),
 535        CHAR(13)
 536        ),
 537        '    ',
 538        ''
 539        ) +
 540        CHAR(13)
 541        FROM        sys.dm_exec_requests               AS r
 542        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
 543        WHERE
 544            r.session_id = @@SPID;
 545
 546        WITH A0
 547        AS
 548        (
 549        SELECT 1 AS n
 550        UNION ALL
 551        SELECT 1
 552        )
 553        ,    A1
 554        AS
 555        (
 556        SELECT 1 AS n
 557        FROM a0 AS a
 558        ,    a0 AS b
 559        )
 560        ,    A2
 561        AS
 562        (
 563        SELECT 1 AS n
 564        FROM a1 AS a
 565        ,    a1 AS b
 566        )
 567        ,    A3
 568        AS
 569        (
 570        SELECT 1 AS n
 571        FROM a2 AS a
 572        ,    a2 AS b
 573        )
 574        ,    A4
 575        AS
 576        (
 577        SELECT 1 AS n
 578        FROM a3 AS a
 579        ,    a3 AS b
 580        )
 581        ,    NUMBERS
 582        AS
 583        (
 584        SELECT TOP(LEN(@header) - 1) ROW_NUMBER() OVER
 585        (
 586        ORDER BY (SELECT NULL)
 587        ) AS number
 588        FROM a4
 589        ORDER BY number
 590        )
 591        SELECT RTRIM(LTRIM(
 592        SUBSTRING
 593        (
 594        @header,
 595        number + 1,
 596        CHARINDEX(CHAR(13), @header, number + 1) - number - 1
 597        )
 598        )) AS [------header---------------------------------------------------------------------------------------------------------------]
 599        FROM numbers
 600        WHERE
 601            SUBSTRING(@header, number, 1) = CHAR(13);
 602
 603        WITH A0
 604        AS
 605        (
 606        SELECT 1 AS n
 607        UNION ALL
 608        SELECT 1
 609        )
 610        ,    A1
 611        AS
 612        (
 613        SELECT 1 AS n
 614        FROM a0 AS a
 615        ,    a0 AS b
 616        )
 617        ,    A2
 618        AS
 619        (
 620        SELECT 1 AS n
 621        FROM a1 AS a
 622        ,    a1 AS b
 623        )
 624        ,    A3
 625        AS
 626        (
 627        SELECT 1 AS n
 628        FROM a2 AS a
 629        ,    a2 AS b
 630        )
 631        ,    A4
 632        AS
 633        (
 634        SELECT 1 AS n
 635        FROM a3 AS a
 636        ,    a3 AS b
 637        )
 638        ,    NUMBERS
 639        AS
 640        (
 641        SELECT TOP(LEN(@params) - 1) ROW_NUMBER() OVER
 642        (
 643        ORDER BY (SELECT NULL)
 644        ) AS number
 645        FROM a4
 646        ORDER BY number
 647        )
 648        ,    TOKENS
 649        AS
 650        (
 651        SELECT RTRIM(LTRIM(
 652        SUBSTRING
 653        (
 654        @params,
 655        number + 1,
 656        CHARINDEX(CHAR(13), @params, number + 1) - number - 1
 657        )
 658        )) AS token
 659        ,      number
 660        ,      CASE WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
 661                                                                      ELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) END AS param_group
 662        ,      ROW_NUMBER() OVER
 663        (
 664        PARTITION BY
 665        CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number),
 666        SUBSTRING(@params, number+1, 1)
 667        ORDER BY
 668        number
 669        ) AS group_order
 670        FROM numbers
 671        WHERE
 672            SUBSTRING(@params, number, 1) = CHAR(13)
 673        )
 674        ,    PARSED_TOKENS
 675        AS
 676        (
 677        SELECT MIN
 678        (
 679        CASE    WHEN token LIKE '@%' THEN token
 680                                     ELSE NULL END
 681        ) AS parameter
 682        ,      MIN
 683        (
 684        CASE    WHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2)
 685                                      ELSE NULL END
 686        ) AS description
 687        ,      param_group
 688        ,      group_order
 689        FROM tokens
 690        WHERE
 691            NOT
 692            (
 693            token = ''
 694            AND group_order > 1
 695            )
 696        GROUP BY param_group
 697        ,        group_order
 698        )
 699        SELECT CASE WHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------'
 700                    WHEN param_group = MAX(param_group) OVER()     THEN parameter
 701                                                                   ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '') END AS [------parameter----------------------------------------------------------]
 702        ,      CASE WHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------'
 703                                                                   ELSE COALESCE(description, '') END AS [------description-----------------------------------------------------------------------------------------------------]
 704        FROM parsed_tokens
 705        ORDER BY param_group , group_order;
 706
 707        WITH A0
 708        AS
 709        (
 710        SELECT 1 AS n
 711        UNION ALL
 712        SELECT 1
 713        )
 714        ,    A1
 715        AS
 716        (
 717        SELECT 1 AS n
 718        FROM a0 AS a
 719        ,    a0 AS b
 720        )
 721        ,    A2
 722        AS
 723        (
 724        SELECT 1 AS n
 725        FROM a1 AS a
 726        ,    a1 AS b
 727        )
 728        ,    A3
 729        AS
 730        (
 731        SELECT 1 AS n
 732        FROM a2 AS a
 733        ,    a2 AS b
 734        )
 735        ,    A4
 736        AS
 737        (
 738        SELECT 1 AS n
 739        FROM a3 AS a
 740        ,    a3 AS b
 741        )
 742        ,    NUMBERS
 743        AS
 744        (
 745        SELECT TOP(LEN(@outputs) - 1) ROW_NUMBER() OVER
 746        (
 747        ORDER BY (SELECT NULL)
 748        ) AS number
 749        FROM a4
 750        ORDER BY number
 751        )
 752        ,    TOKENS
 753        AS
 754        (
 755        SELECT RTRIM(LTRIM(
 756        SUBSTRING
 757        (
 758        @outputs,
 759        number + 1,
 760        CASE    WHEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) <
 761                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
 762                                                                                                                                      ELSE COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1 END
 763        )
 764        )) AS token
 765        ,      number
 766        ,      COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group
 767        ,      ROW_NUMBER() OVER
 768        (
 769        PARTITION BY
 770        COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs))
 771        ORDER BY
 772        number
 773        ) AS output_group_order
 774        FROM numbers
 775        WHERE
 776            SUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted'
 777            OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
 778        )
 779        ,    OUTPUT_TOKENS
 780        AS
 781        (
 782        SELECT *
 783        ,      CASE output_group_order WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token
 784                                                                                      ELSE NULL END) OVER (PARTITION BY output_group)
 785                                              ELSE '' END COLLATE Latin1_General_Bin2 AS column_info
 786        FROM tokens
 787        )
 788        SELECT CASE output_group_order WHEN 1 THEN '-----------------------------------'
 789                                       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))
 790                                                                                                          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
 791                                              ELSE '' END AS formatted_column_name
 792        ,      CASE output_group_order WHEN 1 THEN '-----------------------------------'
 793                                       WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN SUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info))
 794                                                                                                          ELSE SUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3) END
 795                                              ELSE '' END AS formatted_column_type
 796        ,      CASE output_group_order WHEN 1 THEN '---------------------------------------'
 797                                       WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
 798                                                                                                          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)))
 799                                                                                                                                                                                                                                                                    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
 800                                              ELSE '' END AS unformatted_column_name
 801        ,      CASE output_group_order WHEN 1 THEN '---------------------------------------'
 802                                       WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
 803                                                                                                          ELSE CASE WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN ''
 804                                                                                                                                                                                                                                                                    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
 805                                              ELSE '' END AS unformatted_column_type
 806        ,      CASE output_group_order WHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------'
 807                                              ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '') END AS [------description-----------------------------------------------------------------------------------------------------]
 808        FROM output_tokens
 809        WHERE
 810            NOT
 811            (
 812            output_group_order = 1
 813            AND output_group = LEN(@outputs)
 814            )
 815        ORDER BY output_group , CASE output_group_order WHEN 1 THEN 99
 816                                                               ELSE output_group_order END;
 817
 818        RETURN;
 819    END;
 820
 821    WITH A0
 822    AS
 823    (
 824    SELECT 1 AS n
 825    UNION ALL
 826    SELECT 1
 827    )
 828    ,    A1
 829    AS
 830    (
 831    SELECT 1 AS n
 832    FROM a0 AS a
 833    ,    a0 AS b
 834    )
 835    ,    A2
 836    AS
 837    (
 838    SELECT 1 AS n
 839    FROM a1 AS a
 840    ,    a1 AS b
 841    )
 842    ,    A3
 843    AS
 844    (
 845    SELECT 1 AS n
 846    FROM a2 AS a
 847    ,    a2 AS b
 848    )
 849    ,    A4
 850    AS
 851    (
 852    SELECT 1 AS n
 853    FROM a3 AS a
 854    ,    a3 AS b
 855    )
 856    ,    NUMBERS
 857    AS
 858    (
 859    SELECT TOP(LEN(@output_column_list)) ROW_NUMBER() OVER
 860    (
 861    ORDER BY (SELECT NULL)
 862    ) AS number
 863    FROM a4
 864    ORDER BY number
 865    )
 866    ,    TOKENS
 867    AS
 868    (
 869    SELECT '|[' +
 870    SUBSTRING
 871    (
 872    @output_column_list,
 873    number + 1,
 874    CHARINDEX(']', @output_column_list, number) - number - 1
 875    ) + '|]' AS token
 876    ,      number
 877    FROM numbers
 878    WHERE
 879        SUBSTRING(@output_column_list, number, 1) = '['
 880    )
 881    ,    ORDERED_COLUMNS
 882    AS
 883    (
 884    SELECT x.column_name
 885    ,      ROW_NUMBER() OVER
 886    (
 887    PARTITION BY
 888    x.column_name
 889    ORDER BY
 890    tokens.number,
 891    x.default_order
 892    ) AS r
 893    ,      ROW_NUMBER() OVER
 894    (
 895    ORDER BY
 896    tokens.number,
 897    x.default_order
 898    ) AS s
 899    FROM tokens
 900    JOIN (
 901    SELECT '[session_id]' AS column_name
 902    ,      1 AS default_order
 903    UNION ALL
 904    SELECT '[dd hh:mm:ss.mss]'
 905    ,      2
 906    WHERE
 907        @format_output IN (1, 2)
 908    UNION ALL
 909    SELECT '[dd hh:mm:ss.mss (avg)]'
 910    ,      3
 911    WHERE
 912        @format_output IN (1, 2)
 913        AND @get_avg_time = 1
 914    UNION ALL
 915    SELECT '[avg_elapsed_time]'
 916    ,      4
 917    WHERE
 918        @format_output = 0
 919        AND @get_avg_time = 1
 920    UNION ALL
 921    SELECT '[physical_io]'
 922    ,      5
 923    WHERE
 924        @get_task_info = 2
 925    UNION ALL
 926    SELECT '[reads]'
 927    ,      6
 928    UNION ALL
 929    SELECT '[physical_reads]'
 930    ,      7
 931    UNION ALL
 932    SELECT '[writes]'
 933    ,      8
 934    UNION ALL
 935    SELECT '[tempdb_allocations]'
 936    ,      9
 937    UNION ALL
 938    SELECT '[tempdb_current]'
 939    ,      10
 940    UNION ALL
 941    SELECT '[CPU]'
 942    ,      11
 943    UNION ALL
 944    SELECT '[context_switches]'
 945    ,      12
 946    WHERE
 947        @get_task_info = 2
 948    UNION ALL
 949    SELECT '[used_memory]'
 950    ,      13
 951    UNION ALL
 952    SELECT '[physical_io_delta]'
 953    ,      14
 954    WHERE
 955        @delta_interval > 0
 956        AND @get_task_info = 2
 957    UNION ALL
 958    SELECT '[reads_delta]'
 959    ,      15
 960    WHERE
 961        @delta_interval > 0
 962    UNION ALL
 963    SELECT '[physical_reads_delta]'
 964    ,      16
 965    WHERE
 966        @delta_interval > 0
 967    UNION ALL
 968    SELECT '[writes_delta]'
 969    ,      17
 970    WHERE
 971        @delta_interval > 0
 972    UNION ALL
 973    SELECT '[tempdb_allocations_delta]'
 974    ,      18
 975    WHERE
 976        @delta_interval > 0
 977    UNION ALL
 978    SELECT '[tempdb_current_delta]'
 979    ,      19
 980    WHERE
 981        @delta_interval > 0
 982    UNION ALL
 983    SELECT '[CPU_delta]'
 984    ,      20
 985    WHERE
 986        @delta_interval > 0
 987    UNION ALL
 988    SELECT '[context_switches_delta]'
 989    ,      21
 990    WHERE
 991        @delta_interval > 0
 992        AND @get_task_info = 2
 993    UNION ALL
 994    SELECT '[used_memory_delta]'
 995    ,      22
 996    WHERE
 997        @delta_interval > 0
 998    UNION ALL
 999    SELECT '[tasks]'
1000    ,      23
1001    WHERE
1002        @get_task_info = 2
1003    UNION ALL
1004    SELECT '[status]'
1005    ,      24
1006    UNION ALL
1007    SELECT '[wait_info]'
1008    ,      25
1009    WHERE
1010        @get_task_info > 0
1011        OR @find_block_leaders = 1
1012    UNION ALL
1013    SELECT '[locks]'
1014    ,      26
1015    WHERE
1016        @get_locks = 1
1017    UNION ALL
1018    SELECT '[tran_start_time]'
1019    ,      27
1020    WHERE
1021        @get_transaction_info = 1
1022    UNION ALL
1023    SELECT '[tran_log_writes]'
1024    ,      28
1025    WHERE
1026        @get_transaction_info = 1
1027    UNION ALL
1028    SELECT '[open_tran_count]'
1029    ,      29
1030    UNION ALL
1031    SELECT '[sql_command]'
1032    ,      30
1033    WHERE
1034        @get_outer_command = 1
1035    UNION ALL
1036    SELECT '[sql_text]'
1037    ,      31
1038    UNION ALL
1039    SELECT '[query_plan]'
1040    ,      32
1041    WHERE
1042        @get_plans >= 1
1043    UNION ALL
1044    SELECT '[blocking_session_id]'
1045    ,      33
1046    WHERE
1047        @get_task_info > 0
1048        OR @find_block_leaders = 1
1049    UNION ALL
1050    SELECT '[blocked_session_count]'
1051    ,      34
1052    WHERE
1053        @find_block_leaders = 1
1054    UNION ALL
1055    SELECT '[percent_complete]'
1056    ,      35
1057    UNION ALL
1058    SELECT '[host_name]'
1059    ,      36
1060    UNION ALL
1061    SELECT '[login_name]'
1062    ,      37
1063    UNION ALL
1064    SELECT '[database_name]'
1065    ,      38
1066    UNION ALL
1067    SELECT '[program_name]'
1068    ,      39
1069    UNION ALL
1070    SELECT '[additional_info]'
1071    ,      40
1072    WHERE
1073        @get_additional_info = 1
1074    UNION ALL
1075    SELECT '[start_time]'
1076    ,      41
1077    UNION ALL
1078    SELECT '[login_time]'
1079    ,      42
1080    UNION ALL
1081    SELECT '[request_id]'
1082    ,      43
1083    UNION ALL
1084    SELECT '[collection_time]'
1085    ,      44
1086    )           AS x ON x.column_name LIKE token ESCAPE '|'
1087    )
1088    SELECT @output_column_list =
1089    STUFF
1090    (
1091    (
1092    SELECT ',' + column_name AS [text()]
1093    FROM ordered_columns
1094    WHERE
1095        r = 1
1096    ORDER BY s
1097    FOR XML
1098    PATH('')
1099    ),
1100    1,
1101    1,
1102    ''
1103    );
1104
1105    IF COALESCE(RTRIM(@output_column_list), '') = ''
1106    BEGIN;
1107        RAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1);
1108        RETURN;
1109    END;
1110
1111    IF @destination_table <> ''
1112    BEGIN;
1113        SET @destination_table =
1114        --database
1115        COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') +
1116        --schema
1117        COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') +
1118        --table
1119        COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), '');
1120
1121        IF COALESCE(RTRIM(@destination_table), '') = ''
1122        BEGIN;
1123            RAISERROR('Destination table not properly formatted.', 16, 1);
1124            RETURN;
1125        END;
1126    END;
1127
1128    WITH A0
1129    AS
1130    (
1131    SELECT 1 AS n
1132    UNION ALL
1133    SELECT 1
1134    )
1135    ,    A1
1136    AS
1137    (
1138    SELECT 1 AS n
1139    FROM a0 AS a
1140    ,    a0 AS b
1141    )
1142    ,    A2
1143    AS
1144    (
1145    SELECT 1 AS n
1146    FROM a1 AS a
1147    ,    a1 AS b
1148    )
1149    ,    A3
1150    AS
1151    (
1152    SELECT 1 AS n
1153    FROM a2 AS a
1154    ,    a2 AS b
1155    )
1156    ,    A4
1157    AS
1158    (
1159    SELECT 1 AS n
1160    FROM a3 AS a
1161    ,    a3 AS b
1162    )
1163    ,    NUMBERS
1164    AS
1165    (
1166    SELECT TOP(LEN(@sort_order)) ROW_NUMBER() OVER
1167    (
1168    ORDER BY (SELECT NULL)
1169    ) AS number
1170    FROM a4
1171    ORDER BY number
1172    )
1173    ,    TOKENS
1174    AS
1175    (
1176    SELECT '|[' +
1177    SUBSTRING
1178    (
1179    @sort_order,
1180    number + 1,
1181    CHARINDEX(']', @sort_order, number) - number - 1
1182    ) + '|]' AS token
1183    ,      SUBSTRING
1184    (
1185    @sort_order,
1186    CHARINDEX(']', @sort_order, number) + 1,
1187    COALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number)
1188    ) AS next_chunk
1189    ,      number
1190    FROM numbers
1191    WHERE
1192        SUBSTRING(@sort_order, number, 1) = '['
1193    )
1194    ,    ORDERED_COLUMNS
1195    AS
1196    (
1197    SELECT x.column_name +
1198    CASE    WHEN tokens.next_chunk LIKE '%asc%'  THEN ' ASC'
1199            WHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC'
1200                                                 ELSE '' END AS column_name
1201    ,      ROW_NUMBER() OVER
1202    (
1203    PARTITION BY
1204    x.column_name
1205    ORDER BY
1206    tokens.number
1207    ) AS r
1208    ,      tokens.number
1209    FROM tokens
1210    JOIN (
1211    SELECT '[session_id]' AS column_name
1212    UNION ALL
1213    SELECT '[physical_io]'
1214    UNION ALL
1215    SELECT '[reads]'
1216    UNION ALL
1217    SELECT '[physical_reads]'
1218    UNION ALL
1219    SELECT '[writes]'
1220    UNION ALL
1221    SELECT '[tempdb_allocations]'
1222    UNION ALL
1223    SELECT '[tempdb_current]'
1224    UNION ALL
1225    SELECT '[CPU]'
1226    UNION ALL
1227    SELECT '[context_switches]'
1228    UNION ALL
1229    SELECT '[used_memory]'
1230    UNION ALL
1231    SELECT '[physical_io_delta]'
1232    UNION ALL
1233    SELECT '[reads_delta]'
1234    UNION ALL
1235    SELECT '[physical_reads_delta]'
1236    UNION ALL
1237    SELECT '[writes_delta]'
1238    UNION ALL
1239    SELECT '[tempdb_allocations_delta]'
1240    UNION ALL
1241    SELECT '[tempdb_current_delta]'
1242    UNION ALL
1243    SELECT '[CPU_delta]'
1244    UNION ALL
1245    SELECT '[context_switches_delta]'
1246    UNION ALL
1247    SELECT '[used_memory_delta]'
1248    UNION ALL
1249    SELECT '[tasks]'
1250    UNION ALL
1251    SELECT '[tran_start_time]'
1252    UNION ALL
1253    SELECT '[open_tran_count]'
1254    UNION ALL
1255    SELECT '[blocking_session_id]'
1256    UNION ALL
1257    SELECT '[blocked_session_count]'
1258    UNION ALL
1259    SELECT '[percent_complete]'
1260    UNION ALL
1261    SELECT '[host_name]'
1262    UNION ALL
1263    SELECT '[login_name]'
1264    UNION ALL
1265    SELECT '[database_name]'
1266    UNION ALL
1267    SELECT '[start_time]'
1268    UNION ALL
1269    SELECT '[login_time]'
1270    UNION ALL
1271    SELECT '[program_name]'
1272    )           AS x ON x.column_name LIKE token ESCAPE '|'
1273    )
1274    SELECT @sort_order = COALESCE(z.sort_order, '')
1275    FROM (
1276    SELECT STUFF
1277    (
1278    (
1279    SELECT ',' + column_name AS [text()]
1280    FROM ordered_columns
1281    WHERE
1282        r = 1
1283    ORDER BY number
1284    FOR XML
1285    PATH('')
1286    ),
1287    1,
1288    1,
1289    ''
1290    ) AS sort_order
1291    ) AS z;
1292
1293    BEGIN TRY DROP TABLE #sessions; END TRY BEGIN CATCH END CATCH;;
1294    CREATE TABLE #sessions (
1295      recursion               SMALLINT NOT NULL
1296    , session_id              SMALLINT NOT NULL
1297    , request_id              INT NOT NULL
1298    , session_number          INT NOT NULL
1299    , elapsed_time            INT NOT NULL
1300    , avg_elapsed_time        INT NULL
1301    , physical_io             BIGINT NULL
1302    , reads                   BIGINT NULL
1303    , physical_reads          BIGINT NULL
1304    , writes                  BIGINT NULL
1305    , tempdb_allocations      BIGINT NULL
1306    , tempdb_current          BIGINT NULL
1307    , CPU                     INT NULL
1308    , thread_CPU_snapshot     BIGINT NULL
1309    , context_switches        BIGINT NULL
1310    , used_memory             BIGINT NOT NULL
1311    , tasks                   SMALLINT NULL
1312    , status                  VARCHAR(30) NOT NULL
1313    , wait_info               NVARCHAR(4000) NULL
1314    , locks                   XML NULL
1315    , transaction_id          BIGINT NULL
1316    , tran_start_time         DATETIME NULL
1317    , tran_log_writes         NVARCHAR(4000) NULL
1318    , open_tran_count         SMALLINT NULL
1319    , sql_command             XML NULL
1320    , sql_handle              VARBINARY(64) NULL
1321    , statement_start_offset  INT NULL
1322    , statement_end_offset    INT NULL
1323    , sql_text                XML NULL
1324    , plan_handle             VARBINARY(64) NULL
1325    , query_plan              XML NULL
1326    , blocking_session_id     SMALLINT NULL
1327    , blocked_session_count   SMALLINT NULL
1328    , percent_complete        REAL NULL
1329    , host_name               sysname NULL
1330    , login_name              sysname NOT NULL
1331    , database_name           sysname NULL
1332    , program_name            sysname NULL
1333    , additional_info         XML NULL
1334    , start_time              DATETIME NOT NULL
1335    , login_time              DATETIME NULL
1336    , last_request_start_time DATETIME NULL
1337    , PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
1338    , UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON) );
1339
1340    IF @return_schema = 0
1341    BEGIN;
1342        --Disable unnecessary autostats on the table
1343        CREATE STATISTICS s_session_id ON #sessions (session_id)
1344        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1345        CREATE STATISTICS s_request_id ON #sessions (request_id)
1346        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1347        CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)
1348        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1349        CREATE STATISTICS s_session_number ON #sessions (session_number)
1350        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1351        CREATE STATISTICS s_status ON #sessions (status)
1352        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1353        CREATE STATISTICS s_start_time ON #sessions (start_time)
1354        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1355        CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)
1356        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1357        CREATE STATISTICS s_recursion ON #sessions (recursion)
1358        WITH SAMPLE 0 ROWS, NORECOMPUTE;
1359
1360        DECLARE @recursion SMALLINT;
1361        SET @recursion =
1362        CASE @delta_interval WHEN 0 THEN 1
1363                                    ELSE -1 END;
1364
1365        DECLARE @first_collection_ms_ticks BIGINT;
1366        DECLARE @last_collection_start DATETIME;
1367        DECLARE @sys_info BIT;
1368        SET @sys_info = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_os_sys_info'))), 0);
1369
1370        --Used for the delta pull
1371        REDO:;
1372
1373        IF @get_locks = 1
1374            AND @recursion = 1
1375            AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
1376        BEGIN;
1377            SELECT y.resource_type
1378            ,      y.database_name
1379            ,      y.object_id
1380            ,      y.file_id
1381            ,      y.page_type
1382            ,      y.hobt_id
1383            ,      y.allocation_unit_id
1384            ,      y.index_id
1385            ,      y.schema_id
1386            ,      y.principal_id
1387            ,      y.request_mode
1388            ,      y.request_status
1389            ,      y.session_id
1390            ,      y.resource_description
1391            ,      y.request_count
1392            ,      s.request_id
1393            ,      s.start_time
1394            ,      CONVERT(sysname, NULL) AS object_name
1395            ,      CONVERT(sysname, NULL) AS index_name
1396            ,      CONVERT(sysname, NULL) AS schema_name
1397            ,      CONVERT(sysname, NULL) AS principal_name
1398            ,      CONVERT(NVARCHAR(2048), NULL) AS query_error INTO #locks
1399            FROM            (
1400            SELECT sp.spid AS session_id
1401            ,      CASE sp.status WHEN 'sleeping' THEN CONVERT(INT, 0)
1402                                                  ELSE sp.request_id END AS request_id
1403            ,      CASE sp.status WHEN 'sleeping' THEN sp.last_batch
1404                                                  ELSE COALESCE(req.start_time, sp.last_batch) END AS start_time
1405            ,      sp.dbid
1406            FROM        sys.sysprocesses AS sp
1407            OUTER APPLY (
1408            SELECT TOP(1) CASE WHEN (
1409                    sp.hostprocess > ''
1410                    OR r.total_elapsed_time < 0
1411                    )          THEN r.start_time
1412                               ELSE DATEADD
1413                        (
1414                        ms,
1415                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
1416                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
1417                        ) END AS start_time
1418            FROM sys.dm_exec_requests AS r
1419            WHERE
1420                r.session_id = sp.spid
1421                AND r.request_id = sp.request_id
1422            )                            AS req
1423            WHERE
1424                --Process inclusive filter
1425                1 =
1426                CASE    WHEN @filter <> '' THEN CASE @filter_type WHEN 'session'  THEN CASE WHEN CONVERT(SMALLINT, @filter) = 0
1427                                                OR sp.spid = CONVERT(SMALLINT, @filter)     THEN 1
1428                                                                                            ELSE 0 END
1429                                                                  WHEN 'program'  THEN CASE WHEN sp.program_name LIKE @filter THEN 1
1430                                                                                                                              ELSE 0 END
1431                                                                  WHEN 'login'    THEN CASE WHEN sp.loginame LIKE @filter THEN 1
1432                                                                                                                          ELSE 0 END
1433                                                                  WHEN 'host'     THEN CASE WHEN sp.hostname LIKE @filter THEN 1
1434                                                                                                                          ELSE 0 END
1435                                                                  WHEN 'database' THEN CASE WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
1436                                                                                                                               ELSE 0 END
1437                                                                                  ELSE 0 END
1438                                           ELSE 1 END
1439                --Process exclusive filter
1440                AND 0 =
1441                CASE    WHEN @not_filter <> '' THEN CASE @not_filter_type WHEN 'session'  THEN CASE WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
1442                                                                                                                                                  ELSE 0 END
1443                                                                          WHEN 'program'  THEN CASE WHEN sp.program_name LIKE @not_filter THEN 1
1444                                                                                                                                          ELSE 0 END
1445                                                                          WHEN 'login'    THEN CASE WHEN sp.loginame LIKE @not_filter THEN 1
1446                                                                                                                                      ELSE 0 END
1447                                                                          WHEN 'host'     THEN CASE WHEN sp.hostname LIKE @not_filter THEN 1
1448                                                                                                                                      ELSE 0 END
1449                                                                          WHEN 'database' THEN CASE WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
1450                                                                                                                                           ELSE 0 END
1451                                                                                          ELSE 0 END
1452                                               ELSE 0 END
1453                AND
1454                (
1455                @show_own_spid = 1
1456                OR sp.spid <> @@SPID
1457                )
1458                AND
1459                (
1460                @show_system_spids = 1
1461                OR sp.hostprocess > ''
1462                )
1463                AND sp.ecid = 0
1464            ) AS s
1465            INNER HASH JOIN (
1466            SELECT x.resource_type
1467            ,      x.database_name
1468            ,      x.object_id
1469            ,      x.file_id
1470            ,      CASE WHEN x.page_no = 1 OR x.page_no % 8088 = 0         THEN 'PFS'
1471                        WHEN x.page_no = 2 OR x.page_no % 511232 = 0       THEN 'GAM'
1472                        WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1473                        WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1474                        WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1475                        WHEN x.page_no IS NOT NULL                         THEN '*'
1476                                                                           ELSE NULL END AS page_type
1477            ,      x.hobt_id
1478            ,      x.allocation_unit_id
1479            ,      x.index_id
1480            ,      x.schema_id
1481            ,      x.principal_id
1482            ,      x.request_mode
1483            ,      x.request_status
1484            ,      x.session_id
1485            ,      x.request_id
1486            ,      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, '')
1487                                                                                                                                                ELSE NULL END AS resource_description
1488            ,      COUNT(*) AS request_count
1489            FROM (
1490            SELECT tl.resource_type +
1491            CASE    WHEN tl.resource_subtype = '' THEN ''
1492                                                  ELSE '.' + tl.resource_subtype END AS resource_type
1493            ,      COALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name
1494            ,      CONVERT
1495            (
1496            INT,
1497            CASE    WHEN tl.resource_type = 'OBJECT'                   THEN tl.resource_associated_entity_id
1498                    WHEN tl.resource_description LIKE '%object_id = %' THEN (
1499                        SUBSTRING
1500                        (
1501                        tl.resource_description,
1502                        (CHARINDEX('object_id = ', tl.resource_description) + 12),
1503                        COALESCE
1504                        (
1505                        NULLIF
1506                        (
1507                        CHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12),
1508                        0
1509                        ),
1510                        DATALENGTH(tl.resource_description)+1
1511                        ) - (CHARINDEX('object_id = ', tl.resource_description) + 12)
1512                        )
1513                        )
1514                                                                       ELSE NULL END
1515            ) AS object_id
1516            ,      CONVERT
1517            (
1518            INT,
1519            CASE    WHEN tl.resource_type = 'FILE'                     THEN CONVERT(INT, tl.resource_description)
1520                    WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1)
1521                                                                       ELSE NULL END
1522            ) AS file_id
1523            ,      CONVERT
1524            (
1525            INT,
1526            CASE    WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN SUBSTRING
1527                        (
1528                        tl.resource_description,
1529                        CHARINDEX(':', tl.resource_description) + 1,
1530                        COALESCE
1531                        (
1532                        NULLIF
1533                        (
1534                        CHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1),
1535                        0
1536                        ),
1537                        DATALENGTH(tl.resource_description)+1
1538                        ) - (CHARINDEX(':', tl.resource_description) + 1)
1539                        )
1540                                                                       ELSE NULL END
1541            ) AS page_no
1542            ,      CASE WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id
1543                                                                                ELSE NULL END AS hobt_id
1544            ,      CASE WHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id
1545                                                                  ELSE NULL END AS allocation_unit_id
1546            ,      CONVERT
1547            (
1548            INT,
1549            CASE    WHEN
1550                    /*TODO: Deal with server principals*/ tl.resource_subtype <> 'SERVER_PRINCIPAL'
1551                    AND tl.resource_description LIKE '%index_id or stats_id = %' THEN (
1552                        SUBSTRING
1553                        (
1554                        tl.resource_description,
1555                        (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
1556                        COALESCE
1557                        (
1558                        NULLIF
1559                        (
1560                        CHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
1561                        0
1562                        ),
1563                        DATALENGTH(tl.resource_description)+1
1564                        ) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23)
1565                        )
1566                        )
1567                                                                                 ELSE NULL END
1568            ) AS index_id
1569            ,      CONVERT
1570            (
1571            INT,
1572            CASE    WHEN tl.resource_description LIKE '%schema_id = %' THEN (
1573                        SUBSTRING
1574                        (
1575                        tl.resource_description,
1576                        (CHARINDEX('schema_id = ', tl.resource_description) + 12),
1577                        COALESCE
1578                        (
1579                        NULLIF
1580                        (
1581                        CHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12),
1582                        0
1583                        ),
1584                        DATALENGTH(tl.resource_description)+1
1585                        ) - (CHARINDEX('schema_id = ', tl.resource_description) + 12)
1586                        )
1587                        )
1588                                                                       ELSE NULL END
1589            ) AS schema_id
1590            ,      CONVERT
1591            (
1592            INT,
1593            CASE    WHEN tl.resource_description LIKE '%principal_id = %' THEN (
1594                        SUBSTRING
1595                        (
1596                        tl.resource_description,
1597                        (CHARINDEX('principal_id = ', tl.resource_description) + 15),
1598                        COALESCE
1599                        (
1600                        NULLIF
1601                        (
1602                        CHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15),
1603                        0
1604                        ),
1605                        DATALENGTH(tl.resource_description)+1
1606                        ) - (CHARINDEX('principal_id = ', tl.resource_description) + 15)
1607                        )
1608                        )
1609                                                                          ELSE NULL END
1610            ) AS principal_id
1611            ,      tl.request_mode
1612            ,      tl.request_status
1613            ,      tl.request_session_id AS session_id
1614            ,      tl.request_request_id AS request_id
1615            ,
1616
1617            /*TODO: Applocks, other resource_descriptions*/
1618                   RTRIM(tl.resource_description) AS resource_description
1619            ,      tl.resource_associated_entity_id
1620            /*********************************************/ FROM (
1621            SELECT request_session_id
1622            ,      CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type
1623            ,      CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype
1624            ,      resource_database_id
1625            ,      CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description
1626            ,      resource_associated_entity_id
1627            ,      CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode
1628            ,      CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status
1629            ,      request_request_id
1630            FROM sys.dm_tran_locks
1631            ) AS tl
1632            ) AS x
1633            GROUP BY x.resource_type
1634            ,        x.database_name
1635            ,        x.object_id
1636            ,        x.file_id
1637            ,        CASE WHEN x.page_no = 1 OR x.page_no % 8088 = 0         THEN 'PFS'
1638                          WHEN x.page_no = 2 OR x.page_no % 511232 = 0       THEN 'GAM'
1639                          WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1640                          WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1641                          WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1642                          WHEN x.page_no IS NOT NULL                         THEN '*'
1643                                                                             ELSE NULL END
1644            ,        x.hobt_id
1645            ,        x.allocation_unit_id
1646            ,        x.index_id
1647            ,        x.schema_id
1648            ,        x.principal_id
1649            ,        x.request_mode
1650            ,        x.request_status
1651            ,        x.session_id
1652            ,        x.request_id
1653            ,        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, '')
1654                                                                                                                                                  ELSE NULL END
1655            ) AS y ON y.session_id = s.session_id
1656                AND y.request_id = s.request_id
1657            OPTION (HASH GROUP);
1658
1659            --Disable unnecessary autostats on the table
1660            CREATE STATISTICS s_database_name ON #locks (database_name)
1661            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1662            CREATE STATISTICS s_object_id ON #locks (object_id)
1663            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1664            CREATE STATISTICS s_hobt_id ON #locks (hobt_id)
1665            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1666            CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)
1667            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1668            CREATE STATISTICS s_index_id ON #locks (index_id)
1669            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1670            CREATE STATISTICS s_schema_id ON #locks (schema_id)
1671            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1672            CREATE STATISTICS s_principal_id ON #locks (principal_id)
1673            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1674            CREATE STATISTICS s_request_id ON #locks (request_id)
1675            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1676            CREATE STATISTICS s_start_time ON #locks (start_time)
1677            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1678            CREATE STATISTICS s_resource_type ON #locks (resource_type)
1679            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1680            CREATE STATISTICS s_object_name ON #locks (object_name)
1681            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1682            CREATE STATISTICS s_schema_name ON #locks (schema_name)
1683            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1684            CREATE STATISTICS s_page_type ON #locks (page_type)
1685            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1686            CREATE STATISTICS s_request_mode ON #locks (request_mode)
1687            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1688            CREATE STATISTICS s_request_status ON #locks (request_status)
1689            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1690            CREATE STATISTICS s_resource_description ON #locks (resource_description)
1691            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1692            CREATE STATISTICS s_index_name ON #locks (index_name)
1693            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1694            CREATE STATISTICS s_principal_name ON #locks (principal_name)
1695            WITH SAMPLE 0 ROWS, NORECOMPUTE;
1696        END;
1697
1698        DECLARE @sql   VARCHAR(MAX)
1699            ,   @sql_n NVARCHAR(MAX);
1700
1701        SET @sql =
1702        CONVERT(VARCHAR(MAX), '') +
1703        'DECLARE @blocker BIT;
1704            SET @blocker = 0;
1705            DECLARE @i INT;
1706            SET @i = 2147483647;
1707
1708            DECLARE @sessions TABLE
1709            (
1710                session_id SMALLINT NOT NULL,
1711                request_id INT NOT NULL,
1712                login_time DATETIME,
1713                last_request_end_time DATETIME,
1714                status VARCHAR(30),
1715                statement_start_offset INT,
1716                statement_end_offset INT,
1717                sql_handle BINARY(20),
1718                host_name NVARCHAR(128),
1719                login_name NVARCHAR(128),
1720                program_name NVARCHAR(128),
1721                database_id SMALLINT,
1722                memory_usage INT,
1723                open_tran_count SMALLINT,
1724                ' +
1725        CASE    WHEN (
1726                @get_task_info <> 0
1727                OR @find_block_leaders = 1
1728                ) THEN 'wait_type NVARCHAR(32),
1729                        wait_resource NVARCHAR(256),
1730                        wait_time BIGINT,
1731                        '
1732                  ELSE '' END +
1733        'blocked SMALLINT,
1734                is_user_process BIT,
1735                cmd VARCHAR(32),
1736                PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
1737            );
1738
1739            DECLARE @blockers TABLE
1740            (
1741                session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
1742            );
1743
1744            BLOCKERS:;
1745
1746            INSERT @sessions
1747            (
1748                session_id,
1749                request_id,
1750                login_time,
1751                last_request_end_time,
1752                status,
1753                statement_start_offset,
1754                statement_end_offset,
1755                sql_handle,
1756                host_name,
1757                login_name,
1758                program_name,
1759                database_id,
1760                memory_usage,
1761                open_tran_count,
1762                ' +
1763        CASE    WHEN (
1764                @get_task_info <> 0
1765                OR @find_block_leaders = 1
1766                ) THEN 'wait_type,
1767                        wait_resource,
1768                        wait_time,
1769                        '
1770                  ELSE '' END +
1771        'blocked,
1772                is_user_process,
1773                cmd
1774            )
1775            SELECT TOP(@i)
1776                spy.session_id,
1777                spy.request_id,
1778                spy.login_time,
1779                spy.last_request_end_time,
1780                spy.status,
1781                spy.statement_start_offset,
1782                spy.statement_end_offset,
1783                spy.sql_handle,
1784                spy.host_name,
1785                spy.login_name,
1786                spy.program_name,
1787                spy.database_id,
1788                spy.memory_usage,
1789                spy.open_tran_count,
1790                ' +
1791        CASE    WHEN (
1792                @get_task_info <> 0
1793                OR @find_block_leaders = 1
1794                ) THEN 'spy.wait_type,
1795                        CASE
1796                            WHEN
1797                                spy.wait_type LIKE N''PAGE%LATCH_%''
1798                                OR spy.wait_type = N''CXPACKET''
1799                                OR spy.wait_type LIKE N''LATCH[_]%''
1800                                OR spy.wait_type = N''OLEDB'' THEN
1801                                    spy.wait_resource
1802                            ELSE
1803                                NULL
1804                        END AS wait_resource,
1805                        spy.wait_time,
1806                        '
1807                  ELSE '' END +
1808        'spy.blocked,
1809                spy.is_user_process,
1810                spy.cmd
1811            FROM
1812            (
1813                SELECT TOP(@i)
1814                    spx.*,
1815                    ' +
1816        CASE    WHEN (
1817                @get_task_info <> 0
1818                OR @find_block_leaders = 1
1819                ) THEN 'ROW_NUMBER() OVER
1820                            (
1821                                PARTITION BY
1822                                    spx.session_id,
1823                                    spx.request_id
1824                                ORDER BY
1825                                    CASE
1826                                        WHEN spx.wait_type LIKE N''LCK[_]%'' THEN
1827                                            1
1828                                        ELSE
1829                                            99
1830                                    END,
1831                                    spx.wait_time DESC,
1832                                    spx.blocked DESC
1833                            ) AS r
1834                            '
1835                  ELSE '1 AS r
1836                            ' END +
1837        'FROM
1838                (
1839                    SELECT TOP(@i)
1840                        sp0.session_id,
1841                        sp0.request_id,
1842                        sp0.login_time,
1843                        sp0.last_request_end_time,
1844                        LOWER(sp0.status) AS status,
1845                        CASE
1846                            WHEN sp0.cmd = ''CREATE INDEX'' THEN
1847                                0
1848                            ELSE
1849                                sp0.stmt_start
1850                        END AS statement_start_offset,
1851                        CASE
1852                            WHEN sp0.cmd = N''CREATE INDEX'' THEN
1853                                -1
1854                            ELSE
1855                                COALESCE(NULLIF(sp0.stmt_end, 0), -1)
1856                        END AS statement_end_offset,
1857                        sp0.sql_handle,
1858                        sp0.host_name,
1859                        sp0.login_name,
1860                        sp0.program_name,
1861                        sp0.database_id,
1862                        sp0.memory_usage,
1863                        sp0.open_tran_count,
1864                        ' +
1865        CASE    WHEN (
1866                @get_task_info <> 0
1867                OR @find_block_leaders = 1
1868                ) THEN 'CASE
1869                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
1870                                        sp0.wait_type
1871                                    ELSE
1872                                        NULL
1873                                END AS wait_type,
1874                                CASE
1875                                    WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
1876                                        sp0.wait_resource
1877                                    ELSE
1878                                        NULL
1879                                END AS wait_resource,
1880                                CASE
1881                                    WHEN sp0.wait_type <> N''CXPACKET'' THEN
1882                                        sp0.wait_time
1883                                    ELSE
1884                                        0
1885                                END AS wait_time,
1886                                '
1887                  ELSE '' END +
1888        'sp0.blocked,
1889                        sp0.is_user_process,
1890                        sp0.cmd
1891                    FROM
1892                    (
1893                        SELECT TOP(@i)
1894                            sp1.session_id,
1895                            sp1.request_id,
1896                            sp1.login_time,
1897                            sp1.last_request_end_time,
1898                            sp1.status,
1899                            sp1.cmd,
1900                            sp1.stmt_start,
1901                            sp1.stmt_end,
1902                            MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
1903                            sp1.host_name,
1904                            MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
1905                            sp1.program_name,
1906                            sp1.database_id,
1907                            MAX(sp1.memory_usage)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
1908                            MAX(sp1.open_tran_count)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
1909                            sp1.wait_type,
1910                            sp1.wait_resource,
1911                            sp1.wait_time,
1912                            sp1.blocked,
1913                            sp1.hostprocess,
1914                            sp1.is_user_process
1915                        FROM
1916                        (
1917                            SELECT TOP(@i)
1918                                sp2.spid AS session_id,
1919                                CASE sp2.status
1920                                    WHEN ''sleeping'' THEN
1921                                        CONVERT(INT, 0)
1922                                    ELSE
1923                                        sp2.request_id
1924                                END AS request_id,
1925                                MAX(sp2.login_time) AS login_time,
1926                                MAX(sp2.last_batch) AS last_request_end_time,
1927                                MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
1928                                MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
1929                                MAX(sp2.stmt_start) AS stmt_start,
1930                                MAX(sp2.stmt_end) AS stmt_end,
1931                                MAX(sp2.sql_handle) AS sql_handle,
1932                                MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
1933                                MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
1934                                MAX
1935                                (
1936                                    CASE
1937                                        WHEN blk.queue_id IS NOT NULL THEN
1938                                            N''Service Broker
1939                                                database_id: '' + CONVERT(NVARCHAR, blk.database_id) +
1940                                                N'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id)
1941                                        ELSE
1942                                            CONVERT
1943                                            (
1944                                                sysname,
1945                                                RTRIM(sp2.program_name)
1946                                            )
1947                                    END COLLATE SQL_Latin1_General_CP1_CI_AS
1948                                ) AS program_name,
1949                                MAX(sp2.dbid) AS database_id,
1950                                MAX(sp2.memusage) AS memory_usage,
1951                                MAX(sp2.open_tran) AS open_tran_count,
1952                                RTRIM(sp2.lastwaittype) AS wait_type,
1953                                RTRIM(sp2.waitresource) AS wait_resource,
1954                                MAX(sp2.waittime) AS wait_time,
1955                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
1956                                MAX
1957                                (
1958                                    CASE
1959                                        WHEN blk.session_id = sp2.spid THEN
1960                                            ''blocker''
1961                                        ELSE
1962                                            RTRIM(sp2.hostprocess)
1963                                    END
1964                                ) AS hostprocess,
1965                                CONVERT
1966                                (
1967                                    BIT,
1968                                    MAX
1969                                    (
1970                                        CASE
1971                                            WHEN sp2.hostprocess > '''' THEN
1972                                                1
1973                                            ELSE
1974                                                0
1975                                        END
1976                                    )
1977                                ) AS is_user_process
1978                            FROM
1979                            (
1980                                SELECT TOP(@i)
1981                                    session_id,
1982                                    CONVERT(INT, NULL) AS queue_id,
1983                                    CONVERT(INT, NULL) AS database_id
1984                                FROM @blockers
1985
1986                                UNION ALL
1987
1988                                SELECT TOP(@i)
1989                                    CONVERT(SMALLINT, 0),
1990                                    CONVERT(INT, NULL) AS queue_id,
1991                                    CONVERT(INT, NULL) AS database_id
1992                                WHERE
1993                                    @blocker = 0
1994
1995                                UNION ALL
1996
1997                                SELECT TOP(@i)
1998                                    CONVERT(SMALLINT, spid),
1999                                    queue_id,
2000                                    database_id
2001                                FROM sys.dm_broker_activated_tasks
2002                                WHERE
2003                                    @blocker = 0
2004                            ) AS blk
2005                            INNER JOIN sys.sysprocesses AS sp2 ON
2006                                sp2.spid = blk.session_id
2007                                OR
2008                                (
2009                                    blk.session_id = 0
2010                                    AND @blocker = 0
2011                                )
2012                            ' +
2013        CASE    WHEN (
2014                @get_task_info = 0
2015                AND @find_block_leaders = 0
2016                ) THEN 'WHERE
2017                                        sp2.ecid = 0
2018                                    '
2019                  ELSE '' END +
2020        'GROUP BY
2021                                sp2.spid,
2022                                CASE sp2.status
2023                                    WHEN ''sleeping'' THEN
2024                                        CONVERT(INT, 0)
2025                                    ELSE
2026                                        sp2.request_id
2027                                END,
2028                                RTRIM(sp2.lastwaittype),
2029                                RTRIM(sp2.waitresource),
2030                                COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
2031                        ) AS sp1
2032                    ) AS sp0
2033                    WHERE
2034                        @blocker = 1
2035                        OR
2036                        (1=1
2037                        ' +
2038        --inclusive filter
2039        CASE    WHEN @filter <> '' THEN CASE @filter_type WHEN 'session'  THEN CASE WHEN CONVERT(SMALLINT, @filter) <> 0 THEN 'AND sp0.session_id = CONVERT(SMALLINT, @filter)
2040                                                    '
2041                                                                                                                         ELSE '' END
2042                                                          WHEN 'program'  THEN 'AND sp0.program_name LIKE @filter
2043                                            '
2044                                                          WHEN 'login'    THEN 'AND sp0.login_name LIKE @filter
2045                                            '
2046                                                          WHEN 'host'     THEN 'AND sp0.host_name LIKE @filter
2047                                            '
2048                                                          WHEN 'database' THEN 'AND DB_NAME(sp0.database_id) LIKE @filter
2049                                            '
2050                                                                          ELSE '' END
2051                                   ELSE '' END +
2052        --exclusive filter
2053        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)
2054                                                    '
2055                                                                                                                                     ELSE '' END
2056                                                                  WHEN 'program'  THEN 'AND sp0.program_name NOT LIKE @not_filter
2057                                            '
2058                                                                  WHEN 'login'    THEN 'AND sp0.login_name NOT LIKE @not_filter
2059                                            '
2060                                                                  WHEN 'host'     THEN 'AND sp0.host_name NOT LIKE @not_filter
2061                                            '
2062                                                                  WHEN 'database' THEN 'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter
2063                                            '
2064                                                                                  ELSE '' END
2065                                       ELSE '' END +
2066        CASE @show_own_spid WHEN 1 THEN ''
2067                                   ELSE 'AND sp0.session_id <> @@spid
2068                                    ' END +
2069        CASE    WHEN @show_system_spids = 0 THEN 'AND sp0.hostprocess > ''''
2070                                    '
2071                                            ELSE '' END +
2072        CASE @show_sleeping_spids WHEN 0 THEN 'AND sp0.status <> ''sleeping''
2073                                    '
2074                                  WHEN 1 THEN 'AND
2075                                    (
2076                                        sp0.status <> ''sleeping''
2077                                        OR sp0.open_tran_count > 0
2078                                    )
2079                                    '
2080                                         ELSE '' END +
2081        ')
2082                ) AS spx
2083            ) AS spy
2084            WHERE
2085                spy.r = 1;
2086            ' +
2087        CASE @recursion WHEN 1 THEN 'IF @@ROWCOUNT > 0
2088                    BEGIN;
2089                        INSERT @blockers
2090                        (
2091                            session_id
2092                        )
2093                        SELECT TOP(@i)
2094                            blocked
2095                        FROM @sessions
2096                        WHERE
2097                            NULLIF(blocked, 0) IS NOT NULL
2098
2099                        EXCEPT
2100
2101                        SELECT TOP(@i)
2102                            session_id
2103                        FROM @sessions;
2104                        ' +
2105
2106                    CASE    WHEN (
2107                            @get_task_info > 0
2108                            OR @find_block_leaders = 1
2109                            ) THEN 'IF @@ROWCOUNT > 0
2110                                BEGIN;
2111                                    SET @blocker = 1;
2112                                    GOTO BLOCKERS;
2113                                END;
2114                                '
2115                              ELSE '' END +
2116                    'END;
2117                    '
2118                               ELSE '' END +
2119        'SELECT TOP(@i)
2120                @recursion AS recursion,
2121                x.session_id,
2122                x.request_id,
2123                DENSE_RANK() OVER
2124                (
2125                    ORDER BY
2126                        x.session_id
2127                ) AS session_number,
2128                ' +
2129        CASE    WHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN 'x.elapsed_time '
2130                                                                                 ELSE '0 ' END +
2131        'AS elapsed_time,
2132                    ' +
2133        CASE    WHEN (
2134                @output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR
2135                @output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|'
2136                )
2137                AND @recursion = 1 THEN 'x.avg_elapsed_time / 1000 '
2138                                   ELSE 'NULL ' END +
2139        'AS avg_elapsed_time,
2140                    ' +
2141        CASE    WHEN @output_column_list LIKE '%|[physical_io|]%' ESCAPE '|'
2142                OR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|' THEN 'x.physical_io '
2143                                                                                 ELSE 'NULL ' END +
2144        'AS physical_io,
2145                    ' +
2146        CASE    WHEN @output_column_list LIKE '%|[reads|]%' ESCAPE '|'
2147                OR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|' THEN 'x.reads '
2148                                                                           ELSE '0 ' END +
2149        'AS reads,
2150                    ' +
2151        CASE    WHEN @output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|'
2152                OR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|' THEN 'x.physical_reads '
2153                                                                                    ELSE '0 ' END +
2154        'AS physical_reads,
2155                    ' +
2156        CASE    WHEN @output_column_list LIKE '%|[writes|]%' ESCAPE '|'
2157                OR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|' THEN 'x.writes '
2158                                                                            ELSE '0 ' END +
2159        'AS writes,
2160                    ' +
2161        CASE    WHEN @output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|'
2162                OR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|' THEN 'x.tempdb_allocations '
2163                                                                                        ELSE '0 ' END +
2164        'AS tempdb_allocations,
2165                    ' +
2166        CASE    WHEN @output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|'
2167                OR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|' THEN 'x.tempdb_current '
2168                                                                                    ELSE '0 ' END +
2169        'AS tempdb_current,
2170                    ' +
2171        CASE    WHEN @output_column_list LIKE '%|[CPU|]%' ESCAPE '|'
2172                OR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' THEN 'x.CPU '
2173                                                                         ELSE '0 ' END +
2174        'AS CPU,
2175                    ' +
2176        CASE    WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2177                AND @get_task_info = 2
2178                AND @sys_info = 1 THEN 'x.thread_CPU_snapshot '
2179                                  ELSE '0 ' END +
2180        'AS thread_CPU_snapshot,
2181                    ' +
2182        CASE    WHEN @output_column_list LIKE '%|[context_switches|]%' ESCAPE '|'
2183                OR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|' THEN 'x.context_switches '
2184                                                                                      ELSE 'NULL ' END +
2185        'AS context_switches,
2186                    ' +
2187        CASE    WHEN @output_column_list LIKE '%|[used_memory|]%' ESCAPE '|'
2188                OR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|' THEN 'x.used_memory '
2189                                                                                 ELSE '0 ' END +
2190        'AS used_memory,
2191                    ' +
2192        CASE    WHEN @output_column_list LIKE '%|[tasks|]%' ESCAPE '|'
2193                AND @recursion = 1 THEN 'x.tasks '
2194                                   ELSE 'NULL ' END +
2195        'AS tasks,
2196                    ' +
2197        CASE    WHEN (
2198                @output_column_list LIKE '%|[status|]%' ESCAPE '|'
2199                OR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
2200                )
2201                AND @recursion = 1 THEN 'x.status '
2202                                   ELSE ''''' ' END +
2203        'AS status,
2204                    ' +
2205        CASE    WHEN @output_column_list LIKE '%|[wait_info|]%' ESCAPE '|'
2206                AND @recursion = 1 THEN CASE @get_task_info WHEN 2 THEN 'COALESCE(x.task_wait_info, x.sys_wait_info) '
2207                                                                   ELSE 'x.sys_wait_info ' END
2208                                   ELSE 'NULL ' END +
2209        'AS wait_info,
2210                    ' +
2211        CASE    WHEN (
2212                @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
2213                OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
2214                )
2215                AND @recursion = 1 THEN 'x.transaction_id '
2216                                   ELSE 'NULL ' END +
2217        'AS transaction_id,
2218                    ' +
2219        CASE    WHEN @output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|'
2220                AND @recursion = 1 THEN 'x.open_tran_count '
2221                                   ELSE 'NULL ' END +
2222        'AS open_tran_count,
2223                    ' +
2224        CASE    WHEN @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2225                AND @recursion = 1 THEN 'x.sql_handle '
2226                                   ELSE 'NULL ' END +
2227        'AS sql_handle,
2228                    ' +
2229        CASE    WHEN (
2230                @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2231                OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2232                )
2233                AND @recursion = 1 THEN 'x.statement_start_offset '
2234                                   ELSE 'NULL ' END +
2235        'AS statement_start_offset,
2236                    ' +
2237        CASE    WHEN (
2238                @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2239                OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2240                )
2241                AND @recursion = 1 THEN 'x.statement_end_offset '
2242                                   ELSE 'NULL ' END +
2243        'AS statement_end_offset,
2244                    ' +
2245        'NULL AS sql_text,
2246                    ' +
2247        CASE    WHEN @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2248                AND @recursion = 1 THEN 'x.plan_handle '
2249                                   ELSE 'NULL ' END +
2250        'AS plan_handle,
2251                    ' +
2252        CASE    WHEN @output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|'
2253                AND @recursion = 1 THEN 'NULLIF(x.blocking_session_id, 0) '
2254                                   ELSE 'NULL ' END +
2255        'AS blocking_session_id,
2256                    ' +
2257        CASE    WHEN @output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|'
2258                AND @recursion = 1 THEN 'x.percent_complete '
2259                                   ELSE 'NULL ' END +
2260        'AS percent_complete,
2261                    ' +
2262        CASE    WHEN @output_column_list LIKE '%|[host_name|]%' ESCAPE '|'
2263                AND @recursion = 1 THEN 'x.host_name '
2264                                   ELSE ''''' ' END +
2265        'AS host_name,
2266                    ' +
2267        CASE    WHEN @output_column_list LIKE '%|[login_name|]%' ESCAPE '|'
2268                AND @recursion = 1 THEN 'x.login_name '
2269                                   ELSE ''''' ' END +
2270        'AS login_name,
2271                    ' +
2272        CASE    WHEN @output_column_list LIKE '%|[database_name|]%' ESCAPE '|'
2273                AND @recursion = 1 THEN 'DB_NAME(x.database_id) '
2274                                   ELSE 'NULL ' END +
2275        'AS database_name,
2276                    ' +
2277        CASE    WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
2278                AND @recursion = 1 THEN 'x.program_name '
2279                                   ELSE ''''' ' END +
2280        'AS program_name,
2281                    ' +
2282        CASE    WHEN @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
2283                AND @recursion = 1 THEN '(
2284                                    SELECT TOP(@i)
2285                                        x.text_size,
2286                                        x.language,
2287                                        x.date_format,
2288                                        x.date_first,
2289                                        CASE x.quoted_identifier
2290                                            WHEN 0 THEN ''OFF''
2291                                            WHEN 1 THEN ''ON''
2292                                        END AS quoted_identifier,
2293                                        CASE x.arithabort
2294                                            WHEN 0 THEN ''OFF''
2295                                            WHEN 1 THEN ''ON''
2296                                        END AS arithabort,
2297                                        CASE x.ansi_null_dflt_on
2298                                            WHEN 0 THEN ''OFF''
2299                                            WHEN 1 THEN ''ON''
2300                                        END AS ansi_null_dflt_on,
2301                                        CASE x.ansi_defaults
2302                                            WHEN 0 THEN ''OFF''
2303                                            WHEN 1 THEN ''ON''
2304                                        END AS ansi_defaults,
2305                                        CASE x.ansi_warnings
2306                                            WHEN 0 THEN ''OFF''
2307                                            WHEN 1 THEN ''ON''
2308                                        END AS ansi_warnings,
2309                                        CASE x.ansi_padding
2310                                            WHEN 0 THEN ''OFF''
2311                                            WHEN 1 THEN ''ON''
2312                                        END AS ansi_padding,
2313                                        CASE ansi_nulls
2314                                            WHEN 0 THEN ''OFF''
2315                                            WHEN 1 THEN ''ON''
2316                                        END AS ansi_nulls,
2317                                        CASE x.concat_null_yields_null
2318                                            WHEN 0 THEN ''OFF''
2319                                            WHEN 1 THEN ''ON''
2320                                        END AS concat_null_yields_null,
2321                                        CASE x.transaction_isolation_level
2322                                            WHEN 0 THEN ''Unspecified''
2323                                            WHEN 1 THEN ''ReadUncomitted''
2324                                            WHEN 2 THEN ''ReadCommitted''
2325                                            WHEN 3 THEN ''Repeatable''
2326                                            WHEN 4 THEN ''Serializable''
2327                                            WHEN 5 THEN ''Snapshot''
2328                                        END AS transaction_isolation_level,
2329                                        x.lock_timeout,
2330                                        x.deadlock_priority,
2331                                        x.row_count,
2332                                        x.command_type,
2333                                        ' +
2334                    CASE    WHEN OBJECT_ID('master.dbo.fn_varbintohexstr') IS NOT NULL THEN 'master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
2335                                                master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,'
2336                                                                                       ELSE 'CONVERT(VARCHAR(256), x.sql_handle, 1) AS sql_handle,
2337                                                CONVERT(VARCHAR(256), x.plan_handle, 1) AS plan_handle,' END +
2338                    '
2339                                        ' +
2340                    CASE    WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN '(
2341                                                    SELECT TOP(1)
2342                                                        CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id,
2343                                                        agent_info.step_id,
2344                                                        (
2345                                                            SELECT TOP(1)
2346                                                                NULL
2347                                                            FOR XML
2348                                                                PATH(''job_name''),
2349                                                                TYPE
2350                                                        ),
2351                                                        (
2352                                                            SELECT TOP(1)
2353                                                                NULL
2354                                                            FOR XML
2355                                                                PATH(''step_name''),
2356                                                                TYPE
2357                                                        )
2358                                                    FROM
2359                                                    (
2360                                                        SELECT TOP(1)
2361                                                            SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,
2362                                                            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
2363                                                        WHERE
2364                                                            x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''
2365                                                    ) AS agent_info
2366                                                    FOR XML
2367                                                        PATH(''agent_job_info''),
2368                                                        TYPE
2369                                                ),
2370                                                '
2371                                                                                          ELSE '' END +
2372                    CASE    WHEN @get_task_info = 2 THEN 'CONVERT(XML, x.block_info) AS block_info,
2373                                                '
2374                                                    ELSE '' END + '
2375                                        x.host_process_id,
2376                                        x.group_id
2377                                    FOR XML
2378                                        PATH(''additional_info''),
2379                                        TYPE
2380                                ) '
2381                                   ELSE 'NULL ' END +
2382        'AS additional_info,
2383                x.start_time,
2384                    ' +
2385        CASE    WHEN @output_column_list LIKE '%|[login_time|]%' ESCAPE '|'
2386                AND @recursion = 1 THEN 'x.login_time '
2387                                   ELSE 'NULL ' END +
2388        'AS login_time,
2389                x.last_request_start_time
2390            FROM
2391            (
2392                SELECT TOP(@i)
2393                    y.*,
2394                    CASE
2395                        WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
2396                            DATEDIFF(second, GETDATE(), y.start_time)
2397                        ELSE DATEDIFF(ms, y.start_time, GETDATE())
2398                    END AS elapsed_time,
2399                    COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
2400                    COALESCE
2401                    (
2402                        CASE
2403                            WHEN tempdb_info.tempdb_current < 0 THEN 0
2404                            ELSE tempdb_info.tempdb_current
2405                        END,
2406                        0
2407                    ) AS tempdb_current,
2408                    ' +
2409        CASE    WHEN (
2410                @get_task_info <> 0
2411                OR @find_block_leaders = 1
2412                ) THEN 'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +
2413                                    y.wait_type +
2414                                        CASE
2415                                            WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN
2416                                                N'':'' +
2417                                                COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +
2418                                                N'':'' +
2419                                                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)) +
2420                                                N''('' +
2421                                                    CASE
2422                                                        WHEN
2423                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR
2424                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0
2425                                                                THEN
2426                                                                    N''PFS''
2427                                                        WHEN
2428                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR
2429                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0
2430                                                                THEN
2431                                                                    N''GAM''
2432                                                        WHEN
2433                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR
2434                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
2435                                                                THEN
2436                                                                    N''SGAM''
2437                                                        WHEN
2438                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR
2439                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0
2440                                                                THEN
2441                                                                    N''DCM''
2442                                                        WHEN
2443                                                            CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR
2444                                                            (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0
2445                                                                THEN
2446                                                                    N''BCM''
2447                                                        ELSE
2448                                                            N''*''
2449                                                    END +
2450                                                N'')''
2451                                            WHEN y.wait_type = N''CXPACKET'' THEN
2452                                                N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)
2453                                            WHEN y.wait_type LIKE N''LATCH[_]%'' THEN
2454                                                N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''
2455                                            WHEN
2456                                                y.wait_type = N''OLEDB''
2457                                                AND y.resource_description LIKE N''%(SPID=%)'' THEN
2458                                                    N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +
2459                                                        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)) + '']''
2460                                            ELSE
2461                                                N''''
2462                                        END COLLATE Latin1_General_Bin2 AS sys_wait_info,
2463                                        '
2464                  ELSE '' END +
2465        CASE    WHEN @get_task_info = 2 THEN 'tasks.physical_io,
2466                                tasks.context_switches,
2467                                tasks.tasks,
2468                                tasks.block_info,
2469                                tasks.wait_info AS task_wait_info,
2470                                tasks.thread_CPU_snapshot,
2471                                '
2472                                        ELSE '' END +
2473        CASE    WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN 'CONVERT(INT, NULL) '
2474                                                                ELSE 'qs.total_elapsed_time / qs.execution_count ' END +
2475        'AS avg_elapsed_time
2476                FROM
2477                (
2478                    SELECT TOP(@i)
2479                        sp.session_id,
2480                        sp.request_id,
2481                        COALESCE(r.logical_reads, s.logical_reads) AS reads,
2482                        COALESCE(r.reads, s.reads) AS physical_reads,
2483                        COALESCE(r.writes, s.writes) AS writes,
2484                        COALESCE(r.CPU_time, s.CPU_time) AS CPU,
2485                        sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
2486                        LOWER(sp.status) AS status,
2487                        COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
2488                        COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
2489                        COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
2490                        ' +
2491        CASE    WHEN (
2492                @get_task_info <> 0
2493                OR @find_block_leaders = 1
2494                ) THEN 'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2495                                sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
2496                                sp.wait_time AS wait_duration_ms,
2497                                '
2498                  ELSE '' END +
2499        'NULLIF(sp.blocked, 0) AS blocking_session_id,
2500                        r.plan_handle,
2501                        NULLIF(r.percent_complete, 0) AS percent_complete,
2502                        sp.host_name,
2503                        sp.login_name,
2504                        sp.program_name,
2505                        s.host_process_id,
2506                        COALESCE(r.text_size, s.text_size) AS text_size,
2507                        COALESCE(r.language, s.language) AS language,
2508                        COALESCE(r.date_format, s.date_format) AS date_format,
2509                        COALESCE(r.date_first, s.date_first) AS date_first,
2510                        COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
2511                        COALESCE(r.arithabort, s.arithabort) AS arithabort,
2512                        COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
2513                        COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
2514                        COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
2515                        COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
2516                        COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
2517                        COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
2518                        COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
2519                        COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
2520                        COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
2521                        COALESCE(r.row_count, s.row_count) AS row_count,
2522                        COALESCE(r.command, sp.cmd) AS command_type,
2523                        COALESCE
2524                        (
2525                            CASE
2526                                WHEN
2527                                (
2528                                    s.is_user_process = 0
2529                                    AND r.total_elapsed_time >= 0
2530                                ) THEN
2531                                    DATEADD
2532                                    (
2533                                        ms,
2534                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2535                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2536                                    )
2537                            END,
2538                            NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),
2539                            sp.login_time
2540                        ) AS start_time,
2541                        sp.login_time,
2542                        CASE
2543                            WHEN s.is_user_process = 1 THEN
2544                                s.last_request_start_time
2545                            ELSE
2546                                COALESCE
2547                                (
2548                                    DATEADD
2549                                    (
2550                                        ms,
2551                                        1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2552                                        DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2553                                    ),
2554                                    s.last_request_start_time
2555                                )
2556                        END AS last_request_start_time,
2557                        r.transaction_id,
2558                        sp.database_id,
2559                        sp.open_tran_count,
2560                        ' +
2561        CASE    WHEN EXISTS
2562                (
2563                SELECT *
2564                FROM sys.all_columns AS ac
2565                WHERE
2566                    ac.object_id = OBJECT_ID('sys.dm_exec_sessions')
2567                    AND ac.name = 'group_id'
2568                ) THEN 's.group_id'
2569                  ELSE 'CONVERT(INT, NULL) AS group_id' END + '
2570                    FROM @sessions AS sp
2571                    LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
2572                        s.session_id = sp.session_id
2573                        AND s.login_time = sp.login_time
2574                    LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
2575                        sp.status <> ''sleeping''
2576                        AND r.session_id = sp.session_id
2577                        AND r.request_id = sp.request_id
2578                        AND
2579                        (
2580                            (
2581                                s.is_user_process = 0
2582                                AND sp.is_user_process = 0
2583                            )
2584                            OR
2585                            (
2586                                r.start_time = s.last_request_start_time
2587                                AND s.last_request_end_time <= sp.last_request_end_time
2588                            )
2589                        )
2590                ) AS y
2591                ' +
2592        CASE    WHEN @get_task_info = 2 THEN CONVERT(VARCHAR(MAX), '') +
2593                    'LEFT OUTER HASH JOIN
2594                        (
2595                            SELECT TOP(@i)
2596                                task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id,
2597                                task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id,
2598                                task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io,
2599                                task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches,
2600                                task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,
2601                                task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info,
2602                                task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,
2603                                task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot
2604                            FROM
2605                            (
2606                                SELECT TOP(@i)
2607                                    CONVERT
2608                                    (
2609                                        XML,
2610                                        REPLACE
2611                                        (
2612                                            CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
2613                                            N''</waits></tasks><tasks><waits>'',
2614                                            N'', ''
2615                                        )
2616                                    ) AS task_xml
2617                                FROM
2618                                (
2619                                    SELECT TOP(@i)
2620                                        CASE waits.r
2621                                            WHEN 1 THEN
2622                                                waits.session_id
2623                                            ELSE
2624                                                NULL
2625                                        END AS [session_id],
2626                                        CASE waits.r
2627                                            WHEN 1 THEN
2628                                                waits.request_id
2629                                            ELSE
2630                                                NULL
2631                                        END AS [request_id],
2632                                        CASE waits.r
2633                                            WHEN 1 THEN
2634                                                waits.physical_io
2635                                            ELSE
2636                                                NULL
2637                                        END AS [physical_io],
2638                                        CASE waits.r
2639                                            WHEN 1 THEN
2640                                                waits.context_switches
2641                                            ELSE
2642                                                NULL
2643                                        END AS [context_switches],
2644                                        CASE waits.r
2645                                            WHEN 1 THEN
2646                                                waits.thread_CPU_snapshot
2647                                            ELSE
2648                                                NULL
2649                                        END AS [thread_CPU_snapshot],
2650                                        CASE waits.r
2651                                            WHEN 1 THEN
2652                                                waits.tasks
2653                                            ELSE
2654                                                NULL
2655                                        END AS [tasks],
2656                                        CASE waits.r
2657                                            WHEN 1 THEN
2658                                                waits.block_info
2659                                            ELSE
2660                                                NULL
2661                                        END AS [block_info],
2662                                        REPLACE
2663                                        (
2664                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2665                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2666                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2667                                                CONVERT
2668                                                (
2669                                                    NVARCHAR(MAX),
2670                                                    N''('' +
2671                                                        CONVERT(NVARCHAR, num_waits) + N''x: '' +
2672                                                        CASE num_waits
2673                                                            WHEN 1 THEN
2674                                                                CONVERT(NVARCHAR, min_wait_time) + N''ms''
2675                                                            WHEN 2 THEN
2676                                                                CASE
2677                                                                    WHEN min_wait_time <> max_wait_time THEN
2678                                                                        CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2679                                                                    ELSE
2680                                                                        CONVERT(NVARCHAR, max_wait_time) + N''ms''
2681                                                                END
2682                                                            ELSE
2683                                                                CASE
2684                                                                    WHEN min_wait_time <> max_wait_time THEN
2685                                                                        CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2686                                                                    ELSE
2687                                                                        CONVERT(NVARCHAR, max_wait_time) + N''ms''
2688                                                                END
2689                                                        END +
2690                                                    N'')'' + wait_type COLLATE Latin1_General_Bin2
2691                                                ),
2692                                                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''?''),
2693                                                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''?''),
2694                                                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''?''),
2695                                            NCHAR(0),
2696                                            N''''
2697                                        ) AS [waits]
2698                                    FROM
2699                                    (
2700                                        SELECT TOP(@i)
2701                                            w1.*,
2702                                            ROW_NUMBER() OVER
2703                                            (
2704                                                PARTITION BY
2705                                                    w1.session_id,
2706                                                    w1.request_id
2707                                                ORDER BY
2708                                                    w1.block_info DESC,
2709                                                    w1.num_waits DESC,
2710                                                    w1.wait_type
2711                                            ) AS r
2712                                        FROM
2713                                        (
2714                                            SELECT TOP(@i)
2715                                                task_info.session_id,
2716                                                task_info.request_id,
2717                                                task_info.physical_io,
2718                                                task_info.context_switches,
2719                                                task_info.thread_CPU_snapshot,
2720                                                task_info.num_tasks AS tasks,
2721                                                CASE
2722                                                    WHEN task_info.runnable_time IS NOT NULL THEN
2723                                                        ''RUNNABLE''
2724                                                    ELSE
2725                                                        wt2.wait_type
2726                                                END AS wait_type,
2727                                                NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
2728                                                MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
2729                                                AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
2730                                                MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
2731                                                MAX(wt2.block_info) AS block_info
2732                                            FROM
2733                                            (
2734                                                SELECT TOP(@i)
2735                                                    t.session_id,
2736                                                    t.request_id,
2737                                                    SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
2738                                                    SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches,
2739                                                    ' +
2740                    CASE    WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2741                            AND @sys_info = 1 THEN 'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '
2742                                              ELSE 'CONVERT(BIGINT, NULL) ' END +
2743                    ' AS thread_CPU_snapshot,
2744                                                    COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
2745                                                    t.task_address,
2746                                                    t.task_state,
2747                                                    CASE
2748                                                        WHEN
2749                                                            t.task_state = ''RUNNABLE''
2750                                                            AND w.runnable_time > 0 THEN
2751                                                                w.runnable_time
2752                                                        ELSE
2753                                                            NULL
2754                                                    END AS runnable_time
2755                                                FROM sys.dm_os_tasks AS t
2756                                                CROSS APPLY
2757                                                (
2758                                                    SELECT TOP(1)
2759                                                        sp2.session_id
2760                                                    FROM @sessions AS sp2
2761                                                    WHERE
2762                                                        sp2.session_id = t.session_id
2763                                                        AND sp2.request_id = t.request_id
2764                                                        AND sp2.status <> ''sleeping''
2765                                                ) AS sp20
2766                                                LEFT OUTER HASH JOIN
2767                                                (
2768                                                ' +
2769                    CASE    WHEN @sys_info = 1 THEN 'SELECT TOP(@i)
2770                                                                (
2771                                                                    SELECT TOP(@i)
2772                                                                        ms_ticks
2773                                                                    FROM sys.dm_os_sys_info
2774                                                                ) -
2775                                                                    w0.wait_resumed_ms_ticks AS runnable_time,
2776                                                                w0.worker_address,
2777                                                                w0.thread_address,
2778                                                                w0.task_bound_ms_ticks
2779                                                            FROM sys.dm_os_workers AS w0
2780                                                            WHERE
2781                                                                w0.state = ''RUNNABLE''
2782                                                                OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks'
2783                                               ELSE 'SELECT
2784                                                                CONVERT(BIGINT, NULL) AS runnable_time,
2785                                                                CONVERT(VARBINARY(8), NULL) AS worker_address,
2786                                                                CONVERT(VARBINARY(8), NULL) AS thread_address,
2787                                                                CONVERT(BIGINT, NULL) AS task_bound_ms_ticks
2788                                                            WHERE
2789                                                                1 = 0' END +
2790                    '
2791                                                ) AS w ON
2792                                                    w.worker_address = t.worker_address
2793                                                ' +
2794                    CASE    WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2795                            AND @sys_info = 1 THEN 'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
2796                                                                tr.thread_address = w.thread_address
2797                                                                AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
2798                                                            '
2799                                              ELSE '' END +
2800                    ') AS task_info
2801                                            LEFT OUTER HASH JOIN
2802                                            (
2803                                                SELECT TOP(@i)
2804                                                    wt1.wait_type,
2805                                                    wt1.waiting_task_address,
2806                                                    MAX(wt1.wait_duration_ms) AS wait_duration_ms,
2807                                                    MAX(wt1.block_info) AS block_info
2808                                                FROM
2809                                                (
2810                                                    SELECT DISTINCT TOP(@i)
2811                                                        wt.wait_type +
2812                                                            CASE
2813                                                                WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN
2814                                                                    '':'' +
2815                                                                    COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +
2816                                                                    N'':'' +
2817                                                                    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)) +
2818                                                                    N''('' +
2819                                                                        CASE
2820                                                                            WHEN
2821                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR
2822                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0
2823                                                                                    THEN
2824                                                                                        N''PFS''
2825                                                                            WHEN
2826                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR
2827                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0
2828                                                                                    THEN
2829                                                                                        N''GAM''
2830                                                                            WHEN
2831                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR
2832                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0
2833                                                                                    THEN
2834                                                                                        N''SGAM''
2835                                                                            WHEN
2836                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR
2837                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0
2838                                                                                    THEN
2839                                                                                        N''DCM''
2840                                                                            WHEN
2841                                                                                CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR
2842                                                                                (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
2843                                                                                    THEN
2844                                                                                        N''BCM''
2845                                                                            ELSE
2846                                                                                N''*''
2847                                                                        END +
2848                                                                    N'')''
2849                                                                WHEN wt.wait_type = N''CXPACKET'' THEN
2850                                                                    N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)
2851                                                                WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN
2852                                                                    N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''
2853                                                                ELSE
2854                                                                    N''''
2855                                                            END COLLATE Latin1_General_Bin2 AS wait_type,
2856                                                        CASE
2857                                                            WHEN
2858                                                            (
2859                                                                wt.blocking_session_id IS NOT NULL
2860                                                                AND wt.wait_type LIKE N''LCK[_]%''
2861                                                            ) THEN
2862                                                                (
2863                                                                    SELECT TOP(@i)
2864                                                                        x.lock_type,
2865                                                                        REPLACE
2866                                                                        (
2867                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2868                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2869                                                                            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2870                                                                                DB_NAME
2871                                                                                (
2872                                                                                    CONVERT
2873                                                                                    (
2874                                                                                        INT,
2875                                                                                        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)
2876                                                                                    )
2877                                                                                ),
2878                                                                                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''?''),
2879                                                                                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''?''),
2880                                                                                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''?''),
2881                                                                            NCHAR(0),
2882                                                                            N''''
2883                                                                        ) AS database_name,
2884                                                                        CASE x.lock_type
2885                                                                            WHEN N''objectlock'' THEN
2886                                                                                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)
2887                                                                            ELSE
2888                                                                                NULL
2889                                                                        END AS object_id,
2890                                                                        CASE x.lock_type
2891                                                                            WHEN N''filelock'' THEN
2892                                                                                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)
2893                                                                            ELSE
2894                                                                                NULL
2895                                                                        END AS file_id,
2896                                                                        CASE
2897                                                                            WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN
2898                                                                                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)
2899                                                                            WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN
2900                                                                                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)
2901                                                                            ELSE
2902                                                                                NULL
2903                                                                        END AS hobt_id,
2904                                                                        CASE x.lock_type
2905                                                                            WHEN N''applicationlock'' THEN
2906                                                                                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)
2907                                                                            ELSE
2908                                                                                NULL
2909                                                                        END AS applock_hash,
2910                                                                        CASE x.lock_type
2911                                                                            WHEN N''metadatalock'' THEN
2912                                                                                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)
2913                                                                            ELSE
2914                                                                                NULL
2915                                                                        END AS metadata_resource,
2916                                                                        CASE x.lock_type
2917                                                                            WHEN N''metadatalock'' THEN
2918                                                                                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)
2919                                                                            ELSE
2920                                                                                NULL
2921                                                                        END AS metadata_class_id
2922                                                                    FROM
2923                                                                    (
2924                                                                        SELECT TOP(1)
2925                                                                            LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
2926                                                                    ) AS x
2927                                                                    FOR XML
2928                                                                        PATH('''')
2929                                                                )
2930                                                            ELSE NULL
2931                                                        END AS block_info,
2932                                                        wt.wait_duration_ms,
2933                                                        wt.waiting_task_address
2934                                                    FROM
2935                                                    (
2936                                                        SELECT TOP(@i)
2937                                                            wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2938                                                            wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
2939                                                            wt0.wait_duration_ms,
2940                                                            wt0.waiting_task_address,
2941                                                            CASE
2942                                                                WHEN wt0.blocking_session_id = p.blocked THEN
2943                                                                    wt0.blocking_session_id
2944                                                                ELSE
2945                                                                    NULL
2946                                                            END AS blocking_session_id
2947                                                        FROM sys.dm_os_waiting_tasks AS wt0
2948                                                        CROSS APPLY
2949                                                        (
2950                                                            SELECT TOP(1)
2951                                                                s0.blocked
2952                                                            FROM @sessions AS s0
2953                                                            WHERE
2954                                                                s0.session_id = wt0.session_id
2955                                                                AND COALESCE(s0.wait_type, N'''') <> N''OLEDB''
2956                                                                AND wt0.wait_type <> N''OLEDB''
2957                                                        ) AS p
2958                                                    ) AS wt
2959                                                ) AS wt1
2960                                                GROUP BY
2961                                                    wt1.wait_type,
2962                                                    wt1.waiting_task_address
2963                                            ) AS wt2 ON
2964                                                wt2.waiting_task_address = task_info.task_address
2965                                                AND wt2.wait_duration_ms > 0
2966                                                AND task_info.runnable_time IS NULL
2967                                            GROUP BY
2968                                                task_info.session_id,
2969                                                task_info.request_id,
2970                                                task_info.physical_io,
2971                                                task_info.context_switches,
2972                                                task_info.thread_CPU_snapshot,
2973                                                task_info.num_tasks,
2974                                                CASE
2975                                                    WHEN task_info.runnable_time IS NOT NULL THEN
2976                                                        ''RUNNABLE''
2977                                                    ELSE
2978                                                        wt2.wait_type
2979                                                END
2980                                        ) AS w1
2981                                    ) AS waits
2982                                    ORDER BY
2983                                        waits.session_id,
2984                                        waits.request_id,
2985                                        waits.r
2986                                    FOR XML
2987                                        PATH(N''tasks''),
2988                                        TYPE
2989                                ) AS tasks_raw (task_xml_raw)
2990                            ) AS tasks_final
2991                            CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node)
2992                            WHERE
2993                                task_nodes.task_node.exist(N''session_id'') = 1
2994                        ) AS tasks ON
2995                            tasks.session_id = y.session_id
2996                            AND tasks.request_id = y.request_id
2997                        '
2998                                        ELSE '' END +
2999        'LEFT OUTER HASH JOIN
3000                (
3001                    SELECT TOP(@i)
3002                        t_info.session_id,
3003                        COALESCE(t_info.request_id, -1) AS request_id,
3004                        SUM(t_info.tempdb_allocations) AS tempdb_allocations,
3005                        SUM(t_info.tempdb_current) AS tempdb_current
3006                    FROM
3007                    (
3008                        SELECT TOP(@i)
3009                            tsu.session_id,
3010                            tsu.request_id,
3011                            tsu.user_objects_alloc_page_count +
3012                                tsu.internal_objects_alloc_page_count AS tempdb_allocations,
3013                            tsu.user_objects_alloc_page_count +
3014                                tsu.internal_objects_alloc_page_count -
3015                                tsu.user_objects_dealloc_page_count -
3016                                tsu.internal_objects_dealloc_page_count AS tempdb_current
3017                        FROM sys.dm_db_task_space_usage AS tsu
3018                        CROSS APPLY
3019                        (
3020                            SELECT TOP(1)
3021                                s0.session_id
3022                            FROM @sessions AS s0
3023                            WHERE
3024                                s0.session_id = tsu.session_id
3025                        ) AS p
3026
3027                        UNION ALL
3028
3029                        SELECT TOP(@i)
3030                            ssu.session_id,
3031                            NULL AS request_id,
3032                            ssu.user_objects_alloc_page_count +
3033                                ssu.internal_objects_alloc_page_count AS tempdb_allocations,
3034                            ssu.user_objects_alloc_page_count +
3035                                ssu.internal_objects_alloc_page_count -
3036                                ssu.user_objects_dealloc_page_count -
3037                                ssu.internal_objects_dealloc_page_count AS tempdb_current
3038                        FROM sys.dm_db_session_space_usage AS ssu
3039                        CROSS APPLY
3040                        (
3041                            SELECT TOP(1)
3042                                s0.session_id
3043                            FROM @sessions AS s0
3044                            WHERE
3045                                s0.session_id = ssu.session_id
3046                        ) AS p
3047                    ) AS t_info
3048                    GROUP BY
3049                        t_info.session_id,
3050                        COALESCE(t_info.request_id, -1)
3051                ) AS tempdb_info ON
3052                    tempdb_info.session_id = y.session_id
3053                    AND tempdb_info.request_id =
3054                        CASE
3055                            WHEN y.status = N''sleeping'' THEN
3056                                -1
3057                            ELSE
3058                                y.request_id
3059                        END
3060                ' +
3061        CASE    WHEN NOT
3062                (
3063                @get_avg_time = 1
3064                AND @recursion = 1
3065                ) THEN ''
3066                  ELSE 'LEFT OUTER HASH JOIN
3067                        (
3068                            SELECT TOP(@i)
3069                                *
3070                            FROM sys.dm_exec_query_stats
3071                        ) AS qs ON
3072                            qs.sql_handle = y.sql_handle
3073                            AND qs.plan_handle = y.plan_handle
3074                            AND qs.statement_start_offset = y.statement_start_offset
3075                            AND qs.statement_end_offset = y.statement_end_offset
3076                        ' END +
3077        ') AS x
3078            OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';
3079
3080        SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
3081
3082        SET @last_collection_start = GETDATE();
3083
3084        IF @recursion = -1
3085            AND @sys_info = 1
3086        BEGIN;
3087            SELECT @first_collection_ms_ticks = ms_ticks
3088            FROM sys.dm_os_sys_info;
3089        END;
3090
3091        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 )
3092        EXEC sp_executesql
3093        @sql_n,
3094        N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',
3095        @recursion, @filter, @not_filter, @first_collection_ms_ticks;
3096
3097        --Collect transaction information?
3098        IF @recursion = 1
3099            AND
3100            (
3101            @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
3102            OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
3103            )
3104        BEGIN;
3105            DECLARE @i INT;
3106            SET @i = 2147483647;
3107
3108            UPDATE s
3109            SET tran_start_time = CONVERT
3110            (
3111            DATETIME,
3112            LEFT
3113            (
3114            x.trans_info,
3115            NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
3116            ),
3117            121
3118            )
3119            ,   tran_log_writes = RIGHT
3120            (
3121            x.trans_info,
3122            LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
3123            )
3124            FROM            (
3125            SELECT TOP(@i) trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id
3126            ,              COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id
3127            ,              trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info
3128            FROM        (
3129            SELECT TOP(@i) CONVERT
3130            (
3131            XML,
3132            REPLACE
3133            (
3134            CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2,
3135            N'</trans_info></trans><trans><trans_info>', N''
3136            )
3137            )
3138            FROM (
3139            SELECT TOP(@i) CASE u_trans.r WHEN 1 THEN u_trans.session_id
3140                                                 ELSE NULL END AS [session_id]
3141            ,              CASE u_trans.r WHEN 1 THEN u_trans.request_id
3142                                                 ELSE NULL END AS [request_id]
3143            ,              CONVERT
3144            (
3145            NVARCHAR(MAX),
3146            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'')
3147                                                                                    ELSE N'' END +
3148                        REPLACE
3149                        (
3150                        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3151                        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3152                        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3153                        CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),
3154                        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'?'),
3155                        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'?'),
3156                        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'?'),
3157                        NCHAR(0),
3158                        N'?'
3159                        ) +
3160                        N': ' +
3161                        CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +
3162                        N','
3163                                                         ELSE N'N/A,' END COLLATE Latin1_General_Bin2
3164            ) AS [trans_info]
3165            FROM (
3166            SELECT TOP(@i) trans.*
3167            ,              ROW_NUMBER() OVER
3168            (
3169            PARTITION BY
3170            trans.session_id,
3171            trans.request_id
3172            ORDER BY
3173            trans.transaction_start_time DESC
3174            ) AS r
3175            FROM (
3176            SELECT TOP(@i) session_tran_map.session_id
3177            ,              session_tran_map.request_id
3178            ,              s_tran.database_id
3179            ,              COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count
3180            ,              COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used
3181            ,              MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
3182            FROM                 (
3183            SELECT TOP(@i) *
3184            FROM sys.dm_tran_active_transactions
3185            WHERE
3186                transaction_begin_time <= @last_collection_start
3187            ) AS a_tran
3188            INNER HASH JOIN      (
3189            SELECT TOP(@i) *
3190            FROM sys.dm_tran_database_transactions
3191            WHERE
3192                database_id < 32767
3193            ) AS s_tran           ON s_tran.transaction_id = a_tran.transaction_id
3194            LEFT OUTER HASH JOIN (
3195            SELECT TOP(@i) *
3196            FROM sys.dm_tran_session_transactions
3197            ) AS tst              ON s_tran.transaction_id = tst.transaction_id
3198            CROSS APPLY          (
3199            SELECT TOP(1) s3.session_id
3200            ,             s3.request_id
3201            FROM (
3202            SELECT TOP(1) s1.session_id
3203            ,             s1.request_id
3204            FROM #sessions AS s1
3205            WHERE
3206                s1.transaction_id = s_tran.transaction_id
3207                AND s1.recursion = 1
3208            UNION ALL
3209            SELECT TOP(1) s2.session_id
3210            ,             s2.request_id
3211            FROM #sessions AS s2
3212            WHERE
3213                s2.session_id = tst.session_id
3214                AND s2.recursion = 1
3215            ) AS s3
3216            ORDER BY s3.request_id
3217            ) AS session_tran_map
3218            GROUP BY session_tran_map.session_id
3219            ,        session_tran_map.request_id
3220            ,        s_tran.database_id
3221            ) AS trans
3222            ) AS
3223            u_trans
3224            FOR XML
3225            PATH('trans'),
3226            TYPE
3227            ) AS trans_raw (trans_xml_raw)
3228            )                                                 AS trans_final (trans_xml)
3229            CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node)
3230            )                         AS x
3231            INNER HASH JOIN #sessions AS s ON s.session_id = x.session_id
3232                AND s.request_id = x.request_id
3233            OPTION (OPTIMIZE FOR (@i = 1));
3234        END;
3235
3236        --Variables for text and plan collection
3237        DECLARE @session_id             SMALLINT
3238            ,   @request_id             INT
3239            ,   @sql_handle             VARBINARY(64)
3240            ,   @plan_handle            VARBINARY(64)
3241            ,   @statement_start_offset INT
3242            ,   @statement_end_offset   INT
3243            ,   @start_time             DATETIME
3244            ,   @database_name          sysname;
3245
3246        IF @recursion = 1
3247            AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
3248        BEGIN;
3249            DECLARE sql_cursor
3250            CURSOR LOCAL FAST_FORWARD
3251            FOR
3252            SELECT session_id
3253            ,      request_id
3254            ,      sql_handle
3255            ,      statement_start_offset
3256            ,      statement_end_offset
3257            FROM #sessions
3258            WHERE
3259                recursion = 1
3260                AND sql_handle IS NOT NULL
3261            OPTION (KEEPFIXED PLAN);
3262
3263            OPEN sql_cursor;
3264
3265            FETCH NEXT FROM sql_cursor
3266            INTO
3267            @session_id,
3268            @request_id,
3269            @sql_handle,
3270            @statement_start_offset,
3271            @statement_end_offset;
3272
3273            --Wait up to 5 ms for the SQL text, then give up
3274            SET LOCK_TIMEOUT 5;
3275
3276            WHILE @@FETCH_STATUS = 0
3277            BEGIN;
3278                BEGIN TRY;
3279                UPDATE s
3280                SET s.sql_text               = (
3281                SELECT REPLACE
3282                (
3283                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3284                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3285                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3286                N'--' + NCHAR(13) + NCHAR(10) +
3287                CASE    WHEN @get_full_inner_text = 1                                                           THEN est.text
3288                        WHEN LEN(est.text) < (@statement_end_offset / 2) + 1                                    THEN est.text
3289                        WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
3290                                                                                                                ELSE CASE WHEN @statement_start_offset > 0 THEN SUBSTRING
3291                                        (
3292                                        est.text,
3293                                        ((@statement_start_offset/2) + 1),
3294                                        (
3295                                        CASE    WHEN @statement_end_offset = -1 THEN 2147483647
3296                                                                                ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
3297                                            END )
3298                                        )
3299                                                                                                                                                           ELSE RTRIM(LTRIM(est.text)) END END +
3300                NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
3301                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'?'),
3302                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'?'),
3303                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'?'),
3304                NCHAR(0),
3305                N''
3306                ) AS
3307                [processing-instruction(query)]
3308                FOR XML
3309                PATH(''),
3310                TYPE
3311                )
3312                ,   s.statement_start_offset = CASE WHEN LEN(est.text) < (@statement_end_offset / 2) + 1                                                          THEN 0
3313                                                    WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
3314                                                                                                                                                                  ELSE @statement_start_offset END
3315                ,   s.statement_end_offset   = CASE WHEN LEN(est.text) < (@statement_end_offset / 2) + 1                                                          THEN -1
3316                                                    WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
3317                                                                                                                                                                  ELSE @statement_end_offset END
3318                FROM #sessions AS s
3319                ,    (
3320                SELECT TOP(1) text
3321                FROM (
3322                SELECT text
3323                ,      0 AS row_num
3324                FROM sys.dm_exec_sql_text(@sql_handle)
3325                UNION ALL
3326                SELECT NULL
3327                ,      1 AS row_num
3328                ) AS est0
3329                ORDER BY row_num
3330                )              AS est
3331                WHERE
3332                    s.session_id = @session_id
3333                    AND s.request_id = @request_id
3334                    AND s.recursion = 1
3335                OPTION (KEEPFIXED PLAN);
3336                END TRY
3337                BEGIN CATCH;
3338                UPDATE s
3339                SET s.sql_text = CASE ERROR_NUMBER() WHEN 1222 THEN '<timeout_exceeded />'
3340                                                               ELSE '<error message="' + ERROR_MESSAGE() + '" />' END
3341                FROM #sessions AS s
3342                WHERE
3343                    s.session_id = @session_id
3344                    AND s.request_id = @request_id
3345                    AND s.recursion = 1
3346                OPTION (KEEPFIXED PLAN);
3347                END CATCH;
3348
3349                FETCH NEXT FROM sql_cursor
3350                INTO
3351                @session_id,
3352                @request_id,
3353                @sql_handle,
3354                @statement_start_offset,
3355                @statement_end_offset;
3356            END;
3357
3358            --Return this to the default
3359            SET LOCK_TIMEOUT -1;
3360
3361            CLOSE sql_cursor;
3362            DEALLOCATE sql_cursor;
3363        END;
3364
3365        IF @get_outer_command = 1
3366            AND @recursion = 1
3367            AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
3368        BEGIN;
3369            DECLARE @buffer_results TABLE (
3370              EventType      VARCHAR(30)
3371            , Parameters     INT
3372            , EventInfo      NVARCHAR(4000)
3373            , start_time     DATETIME
3374            , session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY );
3375
3376            DECLARE buffer_cursor
3377            CURSOR LOCAL FAST_FORWARD
3378            FOR
3379            SELECT session_id
3380            ,      MAX(start_time) AS start_time
3381            FROM #sessions
3382            WHERE
3383                recursion = 1
3384            GROUP BY session_id
3385            ORDER BY session_id
3386            OPTION (KEEPFIXED PLAN);
3387
3388            OPEN buffer_cursor;
3389
3390            FETCH NEXT FROM buffer_cursor
3391            INTO
3392            @session_id,
3393            @start_time;
3394
3395            WHILE @@FETCH_STATUS = 0
3396            BEGIN;
3397                BEGIN TRY;
3398                --In SQL Server 2008, DBCC INPUTBUFFER will throw
3399                --an exception if the session no longer exists
3400                INSERT @buffer_results ( EventType, Parameters, EventInfo )
3401                EXEC sp_executesql
3402                N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
3403                N'@session_id SMALLINT',
3404                @session_id;
3405
3406                UPDATE br
3407                SET br.start_time = @start_time
3408                FROM @buffer_results AS br
3409                WHERE
3410                    br.session_number =
3411                    (
3412                    SELECT MAX(br2.session_number)
3413                    FROM @buffer_results br2
3414                    );
3415                END TRY
3416                BEGIN CATCH
3417                END CATCH;
3418
3419                FETCH NEXT FROM buffer_cursor
3420                INTO
3421                @session_id,
3422                @start_time;
3423            END;
3424
3425            UPDATE s
3426            SET sql_command = (
3427            SELECT REPLACE
3428            (
3429            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3430            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3431            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3432            CONVERT
3433            (
3434            NVARCHAR(MAX),
3435            N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
3436            ),
3437            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'?'),
3438            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'?'),
3439            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'?'),
3440            NCHAR(0),
3441            N''
3442            ) AS [processing-instruction(query)]
3443            FROM @buffer_results AS br
3444            WHERE
3445                br.session_number = s.session_number
3446                AND br.start_time = s.start_time
3447                AND
3448                (
3449                (
3450                s.start_time = s.last_request_start_time
3451                AND EXISTS
3452                (
3453                SELECT *
3454                FROM sys.dm_exec_requests r2
3455                WHERE
3456                    r2.session_id = s.session_id
3457                    AND r2.request_id = s.request_id
3458                    AND r2.start_time = s.start_time
3459                )
3460                )
3461                OR
3462                (
3463                s.request_id = 0
3464                AND EXISTS
3465                (
3466                SELECT *
3467                FROM sys.dm_exec_sessions s2
3468                WHERE
3469                    s2.session_id = s.session_id
3470                    AND s2.last_request_start_time = s.last_request_start_time
3471                )
3472                )
3473                )
3474            FOR XML
3475            PATH(''),
3476            TYPE
3477            )
3478            FROM #sessions AS s
3479            WHERE
3480                recursion = 1
3481            OPTION (KEEPFIXED PLAN);
3482
3483            CLOSE buffer_cursor;
3484            DEALLOCATE buffer_cursor;
3485        END;
3486
3487        IF @get_plans >= 1
3488            AND @recursion = 1
3489            AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
3490        BEGIN;
3491            DECLARE @live_plan BIT;
3492            SET @live_plan = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_exec_query_statistics_xml'))), 0)
3493
3494            DECLARE plan_cursor
3495            CURSOR LOCAL FAST_FORWARD
3496            FOR
3497            SELECT session_id
3498            ,      request_id
3499            ,      plan_handle
3500            ,      statement_start_offset
3501            ,      statement_end_offset
3502            FROM #sessions
3503            WHERE
3504                recursion = 1
3505                AND plan_handle IS NOT NULL
3506            OPTION (KEEPFIXED PLAN);
3507
3508            OPEN plan_cursor;
3509
3510            FETCH NEXT FROM plan_cursor
3511            INTO
3512            @session_id,
3513            @request_id,
3514            @plan_handle,
3515            @statement_start_offset,
3516            @statement_end_offset;
3517
3518            --Wait up to 5 ms for a query plan, then give up
3519            SET LOCK_TIMEOUT 5;
3520
3521            WHILE @@FETCH_STATUS = 0
3522            BEGIN;
3523                DECLARE @query_plan XML;
3524                IF @live_plan = 1
3525                BEGIN;
3526                    BEGIN TRY;
3527                    SELECT @query_plan = x.query_plan
3528                    FROM sys.dm_exec_query_statistics_xml(@session_id) AS x;
3529
3530                    IF @query_plan IS NOT NULL
3531                        AND EXISTS
3532                        (
3533                        SELECT *
3534                        FROM sys.dm_exec_requests AS r
3535                        WHERE
3536                            r.session_id = @session_id
3537                            AND r.request_id = @request_id
3538                            AND r.plan_handle = @plan_handle
3539                            AND r.statement_start_offset = @statement_start_offset
3540                            AND r.statement_end_offset = @statement_end_offset
3541                        )
3542                    BEGIN;
3543                        UPDATE s
3544                        SET s.query_plan = @query_plan
3545                        FROM #sessions AS s
3546                        WHERE
3547                            s.session_id = @session_id
3548                            AND s.request_id = @request_id
3549                            AND s.recursion = 1
3550                        OPTION (KEEPFIXED PLAN);
3551                    END;
3552                    END TRY
3553                    BEGIN CATCH;
3554                    SET @query_plan = NULL;
3555                    END CATCH;
3556                END;
3557
3558                IF @query_plan IS NULL
3559                BEGIN;
3560                    BEGIN TRY;
3561                    UPDATE s
3562                    SET s.query_plan = (
3563                    SELECT CONVERT(xml, query_plan)
3564                    FROM sys.dm_exec_text_query_plan
3565                    (
3566                    @plan_handle,
3567                    CASE @get_plans WHEN 1 THEN @statement_start_offset
3568                                           ELSE 0 END,
3569                    CASE @get_plans WHEN 1 THEN @statement_end_offset
3570                                           ELSE -1 END
3571                    )
3572                    )
3573                    FROM #sessions AS s
3574                    WHERE
3575                        s.session_id = @session_id
3576                        AND s.request_id = @request_id
3577                        AND s.recursion = 1
3578                    OPTION (KEEPFIXED PLAN);
3579                    END TRY
3580                    BEGIN CATCH;
3581                    IF ERROR_NUMBER() = 6335
3582                    BEGIN;
3583                        UPDATE s
3584                        SET s.query_plan = (
3585                        SELECT N'--' + NCHAR(13) + NCHAR(10) +
3586                        N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) +
3587                        N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +
3588                        N'--' + NCHAR(13) + NCHAR(10) +
3589                        REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') +
3590                        NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
3591                        FROM sys.dm_exec_text_query_plan
3592                        (
3593                        @plan_handle,
3594                        CASE @get_plans WHEN 1 THEN @statement_start_offset
3595                                               ELSE 0 END,
3596                        CASE @get_plans WHEN 1 THEN @statement_end_offset
3597                                               ELSE -1 END
3598                        ) AS
3599                        qp
3600                        FOR XML
3601                        PATH(''),
3602                        TYPE
3603                        )
3604                        FROM #sessions AS s
3605                        WHERE
3606                            s.session_id = @session_id
3607                            AND s.request_id = @request_id
3608                            AND s.recursion = 1
3609                        OPTION (KEEPFIXED PLAN);
3610                    END; ELSE BEGIN;
3611                        UPDATE s
3612                        SET s.query_plan = CASE ERROR_NUMBER() WHEN 1222 THEN '<timeout_exceeded />'
3613                                                                         ELSE '<error message="' + ERROR_MESSAGE() + '" />' END
3614                        FROM #sessions AS s
3615                        WHERE
3616                            s.session_id = @session_id
3617                            AND s.request_id = @request_id
3618                            AND s.recursion = 1
3619                        OPTION (KEEPFIXED PLAN);
3620                    END;
3621                    END CATCH;
3622                END;
3623
3624                FETCH NEXT FROM plan_cursor
3625                INTO
3626                @session_id,
3627                @request_id,
3628                @plan_handle,
3629                @statement_start_offset,
3630                @statement_end_offset;
3631            END;
3632
3633            --Return this to the default
3634            SET LOCK_TIMEOUT -1;
3635
3636            CLOSE plan_cursor;
3637            DEALLOCATE plan_cursor;
3638        END;
3639
3640        IF @get_locks = 1
3641            AND @recursion = 1
3642            AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
3643        BEGIN;
3644            DECLARE locks_cursor
3645            CURSOR LOCAL FAST_FORWARD
3646            FOR
3647            SELECT DISTINCT database_name
3648            FROM #locks
3649            WHERE
3650                EXISTS
3651                (
3652                SELECT *
3653                FROM #sessions AS s
3654                WHERE
3655                    s.session_id = #locks.session_id
3656                    AND recursion = 1
3657                )
3658                AND database_name <> '(null)'
3659            OPTION (KEEPFIXED PLAN);
3660
3661            OPEN locks_cursor;
3662
3663            FETCH NEXT FROM locks_cursor
3664            INTO
3665            @database_name;
3666
3667            WHILE @@FETCH_STATUS = 0
3668            BEGIN;
3669                BEGIN TRY;
3670                SET @sql_n = CONVERT(NVARCHAR(MAX), '') +
3671                'UPDATE l ' +
3672                'SET ' +
3673                'object_name = ' +
3674                'REPLACE ' +
3675                '( ' +
3676                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3677                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3678                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3679                'o.name COLLATE Latin1_General_Bin2, ' +
3680                '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''?''), ' +
3681                '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''?''), ' +
3682                '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''?''), ' +
3683                'NCHAR(0), ' +
3684                N''''' ' +
3685                '), ' +
3686                'index_name = ' +
3687                'REPLACE ' +
3688                '( ' +
3689                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3690                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3691                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3692                'i.name COLLATE Latin1_General_Bin2, ' +
3693                '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''?''), ' +
3694                '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''?''), ' +
3695                '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''?''), ' +
3696                'NCHAR(0), ' +
3697                N''''' ' +
3698                '), ' +
3699                'schema_name = ' +
3700                'REPLACE ' +
3701                '( ' +
3702                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3703                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3704                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3705                's.name COLLATE Latin1_General_Bin2, ' +
3706                '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''?''), ' +
3707                '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''?''), ' +
3708                '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''?''), ' +
3709                'NCHAR(0), ' +
3710                N''''' ' +
3711                '), ' +
3712                'principal_name = ' +
3713                'REPLACE ' +
3714                '( ' +
3715                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3716                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3717                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3718                'dp.name COLLATE Latin1_General_Bin2, ' +
3719                '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''?''), ' +
3720                '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''?''), ' +
3721                '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''?''), ' +
3722                'NCHAR(0), ' +
3723                N''''' ' +
3724                ') ' +
3725                'FROM #locks AS l ' +
3726                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +
3727                'au.allocation_unit_id = l.allocation_unit_id ' +
3728                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
3729                'p.hobt_id = ' +
3730                'COALESCE ' +
3731                '( ' +
3732                'l.hobt_id, ' +
3733                'CASE ' +
3734                'WHEN au.type IN (1, 3) THEN au.container_id ' +
3735                'ELSE NULL ' +
3736                'END ' +
3737                ') ' +
3738                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +
3739                'l.hobt_id IS NULL ' +
3740                'AND au.type = 2 ' +
3741                'AND p1.partition_id = au.container_id ' +
3742                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
3743                'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
3744                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +
3745                'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
3746                'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +
3747                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
3748                's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +
3749                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +
3750                'dp.principal_id = l.principal_id ' +
3751                'WHERE ' +
3752                'l.database_name = @database_name ' +
3753                'OPTION (KEEPFIXED PLAN); ';
3754
3755                EXEC sp_executesql @sql_n
3756                ,                  N'@database_name sysname'
3757                ,                  @database_name;
3758                END TRY
3759                BEGIN CATCH;
3760                UPDATE #locks
3761                SET query_error = REPLACE
3762                (
3763                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3764                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3765                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3766                CONVERT
3767                (
3768                NVARCHAR(MAX),
3769                ERROR_MESSAGE() COLLATE Latin1_General_Bin2
3770                ),
3771                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'?'),
3772                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'?'),
3773                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'?'),
3774                NCHAR(0),
3775                N''
3776                )
3777                WHERE
3778                    database_name = @database_name
3779                OPTION (KEEPFIXED PLAN);
3780                END CATCH;
3781
3782                FETCH NEXT FROM locks_cursor
3783                INTO
3784                @database_name;
3785            END;
3786
3787            CLOSE locks_cursor;
3788            DEALLOCATE locks_cursor;
3789
3790            CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);
3791
3792            UPDATE s
3793            SET s.locks = (
3794            SELECT REPLACE
3795            (
3796            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3797            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3798            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3799            CONVERT
3800            (
3801            NVARCHAR(MAX),
3802            l1.database_name COLLATE Latin1_General_Bin2
3803            ),
3804            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'?'),
3805            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'?'),
3806            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'?'),
3807            NCHAR(0),
3808            N''
3809            ) AS [Database/@name]
3810            ,      MIN(l1.query_error) AS [Database/@query_error]
3811            ,      (
3812            SELECT l2.request_mode AS [Lock/@request_mode]
3813            ,      l2.request_status AS [Lock/@request_status]
3814            ,      COUNT(*) AS [Lock/@request_count]
3815            FROM #locks AS l2
3816            WHERE
3817                l1.session_id = l2.session_id
3818                AND l1.request_id = l2.request_id
3819                AND l2.database_name = l1.database_name
3820                AND l2.resource_type = 'DATABASE'
3821            GROUP BY l2.request_mode
3822            ,        l2.
3823            request_status
3824            FOR XML
3825            PATH(''),
3826            TYPE
3827            ) AS [Database/Locks]
3828            ,      (
3829            SELECT COALESCE(l3.object_name, '(null)') AS [Object/@name]
3830            ,      l3.schema_name AS [Object/@schema_name]
3831            ,      (
3832            SELECT l4.resource_type AS [Lock/@resource_type]
3833            ,      l4.page_type AS [Lock/@page_type]
3834            ,      l4.index_name AS [Lock/@index_name]
3835            ,      CASE WHEN l4.object_name IS NULL THEN l4.schema_name
3836                                                    ELSE NULL END AS [Lock/@schema_name]
3837            ,      l4.principal_name AS [Lock/@principal_name]
3838            ,      l4.resource_description AS [Lock/@resource_description]
3839            ,      l4.request_mode AS [Lock/@request_mode]
3840            ,      l4.request_status AS [Lock/@request_status]
3841            ,      SUM(l4.request_count) AS [Lock/@request_count]
3842            FROM #locks AS l4
3843            WHERE
3844                l4.session_id = l3.session_id
3845                AND l4.request_id = l3.request_id
3846                AND l3.database_name = l4.database_name
3847                AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')
3848                AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')
3849                AND l4.resource_type <> 'DATABASE'
3850            GROUP BY l4.resource_type
3851            ,        l4.page_type
3852            ,        l4.index_name
3853            ,        CASE WHEN l4.object_name IS NULL THEN l4.schema_name
3854                                                      ELSE NULL END
3855            ,        l4.principal_name
3856            ,        l4.resource_description
3857            ,        l4.request_mode
3858            ,        l4.
3859            request_status
3860            FOR XML
3861            PATH(''),
3862            TYPE
3863            ) AS [Object/Locks]
3864            FROM #locks AS l3
3865            WHERE
3866                l3.session_id = l1.session_id
3867                AND l3.request_id = l1.request_id
3868                AND l3.database_name = l1.database_name
3869                AND l3.resource_type <> 'DATABASE'
3870            GROUP BY l3.session_id
3871            ,        l3.request_id
3872            ,        l3.database_name
3873            ,        COALESCE(l3.object_name, '(null)')
3874            ,        l3.
3875            schema_name
3876            FOR XML
3877            PATH(''),
3878            TYPE
3879            ) AS [Database/Objects]
3880            FROM #locks AS l1
3881            WHERE
3882                l1.session_id = s.session_id
3883                AND l1.request_id = s.request_id
3884                AND l1.start_time IN (s.start_time, s.last_request_start_time)
3885                AND s.recursion = 1
3886            GROUP BY l1.session_id
3887            ,        l1.request_id
3888            ,        l1.
3889            database_name
3890            FOR XML
3891            PATH(''),
3892            TYPE
3893            )
3894            FROM #sessions s
3895            OPTION (KEEPFIXED PLAN);
3896        END;
3897
3898        IF @find_block_leaders = 1
3899            AND @recursion = 1
3900            AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'
3901        BEGIN;
3902            WITH BLOCKERS
3903            AS
3904            (
3905            SELECT session_id
3906            ,      session_id AS top_level_session_id
3907            ,      CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path
3908            FROM #sessions
3909            WHERE
3910                recursion = 1
3911            UNION ALL
3912            SELECT s.session_id
3913            ,      b.top_level_session_id
3914            ,      CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path
3915            FROM blockers  AS b
3916            JOIN #sessions AS s ON s.blocking_session_id = b.session_id
3917                AND s.recursion = 1
3918                AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2
3919            )
3920            UPDATE s
3921            SET s.blocked_session_count = x.blocked_session_count
3922            FROM #sessions AS s
3923            JOIN (
3924            SELECT b.top_level_session_id AS session_id
3925            ,      COUNT(*) - 1 AS blocked_session_count
3926            FROM blockers AS b
3927            GROUP BY b.top_level_session_id
3928            )                 x ON s.session_id = x.session_id
3929            WHERE
3930                s.recursion = 1;
3931        END;
3932
3933        IF @get_task_info = 2
3934            AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
3935            AND @recursion = 1
3936        BEGIN;
3937            CREATE TABLE #blocked_requests (
3938              session_id    SMALLINT NOT NULL
3939            , request_id    INT NOT NULL
3940            , database_name sysname NOT NULL
3941            , object_id     INT
3942            , hobt_id       BIGINT
3943            , schema_id     INT
3944            , schema_name   sysname NULL
3945            , object_name   sysname NULL
3946            , query_error   NVARCHAR(2048)
3947            , PRIMARY KEY (database_name, session_id, request_id) );
3948
3949            CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
3950            WITH SAMPLE 0 ROWS, NORECOMPUTE;
3951            CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
3952            WITH SAMPLE 0 ROWS, NORECOMPUTE;
3953            CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
3954            WITH SAMPLE 0 ROWS, NORECOMPUTE;
3955            CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
3956            WITH SAMPLE 0 ROWS, NORECOMPUTE;
3957
3958            INSERT #blocked_requests ( session_id, request_id, database_name, object_id, hobt_id, schema_id )
3959            SELECT session_id
3960            ,      request_id
3961            ,      database_name
3962            ,      object_id
3963            ,      hobt_id
3964            ,      CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id
3965            FROM (
3966            SELECT session_id
3967            ,      request_id
3968            ,      agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name
3969            ,      agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id
3970            ,      agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id
3971            ,      agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node
3972            FROM        #sessions                               AS s
3973            CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node)
3974            WHERE
3975                s.recursion = 1
3976            ) AS t
3977            WHERE
3978                t.database_name IS NOT NULL
3979                AND
3980                (
3981                t.object_id IS NOT NULL
3982                OR t.hobt_id IS NOT NULL
3983                OR t.schema_node IS NOT NULL
3984                );
3985
3986            DECLARE blocks_cursor
3987            CURSOR LOCAL FAST_FORWARD
3988            FOR
3989            SELECT DISTINCT database_name
3990            FROM #blocked_requests;
3991
3992            OPEN blocks_cursor;
3993
3994            FETCH NEXT FROM blocks_cursor
3995            INTO
3996            @database_name;
3997
3998            WHILE @@FETCH_STATUS = 0
3999            BEGIN;
4000                BEGIN TRY;
4001                SET @sql_n =
4002                CONVERT(NVARCHAR(MAX), '') +
4003                'UPDATE b ' +
4004                'SET ' +
4005                'b.schema_name = ' +
4006                'REPLACE ' +
4007                '( ' +
4008                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4009                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4010                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4011                's.name COLLATE Latin1_General_Bin2, ' +
4012                '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''?''), ' +
4013                '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''?''), ' +
4014                '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''?''), ' +
4015                'NCHAR(0), ' +
4016                N''''' ' +
4017                '), ' +
4018                'b.object_name = ' +
4019                'REPLACE ' +
4020                '( ' +
4021                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4022                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4023                'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4024                'o.name COLLATE Latin1_General_Bin2, ' +
4025                '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''?''), ' +
4026                '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''?''), ' +
4027                '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''?''), ' +
4028                'NCHAR(0), ' +
4029                N''''' ' +
4030                ') ' +
4031                'FROM #blocked_requests AS b ' +
4032                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4033                'p.hobt_id = b.hobt_id ' +
4034                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4035                'o.object_id = COALESCE(p.object_id, b.object_id) ' +
4036                'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4037                's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +
4038                'WHERE ' +
4039                'b.database_name = @database_name; ';
4040
4041                EXEC sp_executesql @sql_n
4042                ,                  N'@database_name sysname'
4043                ,                  @database_name;
4044                END TRY
4045                BEGIN CATCH;
4046                UPDATE #blocked_requests
4047                SET query_error = REPLACE
4048                (
4049                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4050                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4051                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4052                CONVERT
4053                (
4054                NVARCHAR(MAX),
4055                ERROR_MESSAGE()