Sp_whois_active ile veritaban\u0131n\u0131 izleme<\/strong><\/p>\nAdam mechanic taraf\u0131ndan yaz\u0131lan ve \u00fccretsiz olarak kullan\u0131labilen sp_whois_active stored procedure \u00fc veritaban\u0131nda anl\u0131k \u00e7al\u0131\u015fan sessionlar\u0131 ve \u00e7al\u0131\u015ft\u0131rd\u0131klar\u0131 sql ler hakk\u0131nda bilgileri verir. Sp_whois_active sp sini veritaban\u0131nda manuel olu\u015fturduktan sonra a\u015fa\u011f\u0131daki gibi \u00e7al\u0131\u015ft\u0131rabilirsiniz.<\/p>\n
<\/p>\n
USE master\r\nGO\r\n\r\nIF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')\r\nEXEC ('CREATE PROC dbo.sp_WhoIsActive AS SELECT ''stub version, to be replaced''')\r\nGO\r\n\r\n\/*********************************************************************************************\r\nWho Is Active? v11.11 (2012-03-22)\r\n(C) 2007-2012, Adam Machanic\r\n\r\nFeedback: mailto:amachanic@gmail.com\r\nUpdates: http:\/\/sqlblog.com\/blogs\/adam_machanic\/archive\/tags\/who+is+active\/default.aspx\r\n\"Beta\" Builds: http:\/\/sqlblog.com\/files\/folders\/beta\/tags\/who+is+active\/default.aspx\r\n\r\nDonate! Support this project: http:\/\/tinyurl.com\/WhoIsActiveDonate\r\n\r\nLicense: \r\nWho is Active? is free to download and use for personal, educational, and internal \r\ncorporate purposes, provided that this header is preserved. Redistribution or sale \r\nof Who is Active?, in whole or in part, is prohibited without the author's express \r\nwritten consent.\r\n*********************************************************************************************\/\r\nALTER PROC dbo.sp_WhoIsActive\r\n(\r\n--~\r\n--Filters--Both inclusive and exclusive\r\n--Set either filter to '' to disable\r\n--Valid filter types are: session, program, database, login, and host\r\n--Session is a session ID, and either 0 or '' can be used to indicate \"all\" sessions\r\n--All other filter types support % or _ as wildcards\r\n@filter sysname = '',\r\n@filter_type VARCHAR(10) = 'session',\r\n@not_filter sysname = '',\r\n@not_filter_type VARCHAR(10) = 'session',\r\n\r\n--Retrieve data about the calling session?\r\n@show_own_spid BIT = 0,\r\n\r\n--Retrieve data about system sessions?\r\n@show_system_spids BIT = 0,\r\n\r\n--Controls how sleeping SPIDs are handled, based on the idea of levels of interest\r\n--0 does not pull any sleeping SPIDs\r\n--1 pulls only those sleeping SPIDs that also have an open transaction\r\n--2 pulls all sleeping SPIDs\r\n@show_sleeping_spids TINYINT = 1,\r\n\r\n--If 1, gets the full stored procedure or running batch, when available\r\n--If 0, gets only the actual statement that is currently running in the batch or procedure\r\n@get_full_inner_text BIT = 0,\r\n\r\n--Get associated query plans for running tasks, if available\r\n--If @get_plans = 1, gets the plan based on the request's statement offset\r\n--If @get_plans = 2, gets the entire plan based on the request's plan_handle\r\n@get_plans TINYINT = 0,\r\n\r\n--Get the associated outer ad hoc query or stored procedure call, if available\r\n@get_outer_command BIT = 0,\r\n\r\n--Enables pulling transaction log write info and transaction duration\r\n@get_transaction_info BIT = 0,\r\n\r\n--Get information on active tasks, based on three interest levels\r\n--Level 0 does not pull any task-related information\r\n--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers\r\n--Level 2 pulls all available task-based metrics, including: \r\n--number of active tasks, current wait stats, physical I\/O, context switches, and blocker information\r\n@get_task_info TINYINT = 1,\r\n\r\n--Gets associated locks for each request, aggregated in an XML format\r\n@get_locks BIT = 0,\r\n\r\n--Get average time for past runs of an active query\r\n--(based on the combination of plan handle, sql handle, and offset)\r\n@get_avg_time BIT = 0,\r\n\r\n--Get additional non-performance-related information about the session or request\r\n--text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, \r\n--ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, \r\n--transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type\r\n--\r\n--If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of\r\n--the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)\r\n--\r\n--If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be\r\n--populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id, \r\n--applock_hash, metadata_resource, metadata_class_id, object_name, schema_name\r\n@get_additional_info BIT = 0,\r\n\r\n--Walk the blocking chain and count the number of \r\n--total SPIDs blocked all the way down by a given session\r\n--Also enables task_info Level 1, if @get_task_info is set to 0\r\n@find_block_leaders BIT = 0,\r\n\r\n--Pull deltas on various metrics\r\n--Interval in seconds to wait before doing the second data pull\r\n@delta_interval TINYINT = 0,\r\n\r\n--List of desired output columns, in desired order\r\n--Note that the final output will be the intersection of all enabled features and all \r\n--columns in the list. Therefore, only columns associated with enabled features will \r\n--actually appear in the output. Likewise, removing columns from this list may effectively\r\n--disable features, even if they are turned on\r\n--\r\n--Each element in this list must be one of the valid output column names. Names must be\r\n--delimited by square brackets. White space, formatting, and additional characters are\r\n--allowed, as long as the list contains exact matches of delimited valid column names.\r\n@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][%]',\r\n\r\n--Column(s) by which to sort output, optionally with sort directions. \r\n--Valid column choices:\r\n--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,\r\n--tempdb_current, CPU, context_switches, used_memory, physical_io_delta, \r\n--reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta, \r\n--tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta, \r\n--tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,\r\n--percent_complete, host_name, login_name, database_name, start_time, login_time\r\n--\r\n--Note that column names in the list must be bracket-delimited. Commas and\/or white\r\n--space are not required. \r\n@sort_order VARCHAR(500) = '[start_time] ASC',\r\n\r\n--Formats some of the output columns in a more \"human readable\" form\r\n--0 disables outfput format\r\n--1 formats the output for variable-width fonts\r\n--2 formats the output for fixed-width fonts\r\n@format_output TINYINT = 1,\r\n\r\n--If set to a non-blank value, the script will attempt to insert into the specified \r\n--destination table. Please note that the script will not verify that the table exists, \r\n--or that it has the correct schema, before doing the insert.\r\n--Table can be specified in one, two, or three-part format\r\n@destination_table VARCHAR(4000) = '',\r\n\r\n--If set to 1, no data collection will happen and no result set will be returned; instead,\r\n--a CREATE TABLE statement will be returned via the @schema parameter, which will match \r\n--the schema of the result set that would be returned by using the same collection of the\r\n--rest of the parameters. The CREATE TABLE statement will have a placeholder token of \r\n--<table_name> in place of an actual table name.\r\n@return_schema BIT = 0,\r\n@schema VARCHAR(MAX) = NULL OUTPUT,\r\n\r\n--Help! What do I do?\r\n@help BIT = 0\r\n--~\r\n)\r\n\/*\r\nOUTPUT COLUMNS\r\n--------------\r\nFormatted\/Non: [session_id] [smallint] NOT NULL\r\nSession ID (a.k.a. SPID)\r\n\r\nFormatted: [dd hh:mm:ss.mss] [varchar](15) NULL\r\nNon-Formatted: <not returned>\r\nFor an active request, time the query has been running\r\nFor a sleeping session, time since the last batch completed\r\n\r\nFormatted: [dd hh:mm:ss.mss (avg)] [varchar](15) NULL\r\nNon-Formatted: [avg_elapsed_time] [int] NULL\r\n(Requires @get_avg_time option)\r\nHow much time has the active portion of the query taken in the past, on average?\r\n\r\nFormatted: [physical_io] [varchar](30) NULL\r\nNon-Formatted: [physical_io] [bigint] NULL\r\nShows the number of physical I\/Os, for active requests\r\n\r\nFormatted: [reads] [varchar](30) NULL\r\nNon-Formatted: [reads] [bigint] NULL\r\nFor an active request, number of reads done for the current query\r\nFor a sleeping session, total number of reads done over the lifetime of the session\r\n\r\nFormatted: [physical_reads] [varchar](30) NULL\r\nNon-Formatted: [physical_reads] [bigint] NULL\r\nFor an active request, number of physical reads done for the current query\r\nFor a sleeping session, total number of physical reads done over the lifetime of the session\r\n\r\nFormatted: [writes] [varchar](30) NULL\r\nNon-Formatted: [writes] [bigint] NULL\r\nFor an active request, number of writes done for the current query\r\nFor a sleeping session, total number of writes done over the lifetime of the session\r\n\r\nFormatted: [tempdb_allocations] [varchar](30) NULL\r\nNon-Formatted: [tempdb_allocations] [bigint] NULL\r\nFor an active request, number of TempDB writes done for the current query\r\nFor a sleeping session, total number of TempDB writes done over the lifetime of the session\r\n\r\nFormatted: [tempdb_current] [varchar](30) NULL\r\nNon-Formatted: [tempdb_current] [bigint] NULL\r\nFor an active request, number of TempDB pages currently allocated for the query\r\nFor a sleeping session, number of TempDB pages currently allocated for the session\r\n\r\nFormatted: [CPU] [varchar](30) NULL\r\nNon-Formatted: [CPU] [int] NULL\r\nFor an active request, total CPU time consumed by the current query\r\nFor a sleeping session, total CPU time consumed over the lifetime of the session\r\n\r\nFormatted: [context_switches] [varchar](30) NULL\r\nNon-Formatted: [context_switches] [bigint] NULL\r\nShows the number of context switches, for active requests\r\n\r\nFormatted: [used_memory] [varchar](30) NOT NULL\r\nNon-Formatted: [used_memory] [bigint] NOT NULL\r\nFor an active request, total memory consumption for the current query\r\nFor a sleeping session, total current memory consumption\r\n\r\nFormatted: [physical_io_delta] [varchar](30) NULL\r\nNon-Formatted: [physical_io_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of physical I\/Os reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [reads_delta] [varchar](30) NULL\r\nNon-Formatted: [reads_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of reads reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [physical_reads_delta] [varchar](30) NULL\r\nNon-Formatted: [physical_reads_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of physical reads reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [writes_delta] [varchar](30) NULL\r\nNon-Formatted: [writes_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of writes reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [tempdb_allocations_delta] [varchar](30) NULL\r\nNon-Formatted: [tempdb_allocations_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of TempDB writes reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [tempdb_current_delta] [varchar](30) NULL\r\nNon-Formatted: [tempdb_current_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the number of allocated TempDB pages reported on the first and second \r\ncollections. If the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [CPU_delta] [varchar](30) NULL\r\nNon-Formatted: [CPU_delta] [int] NULL\r\n(Requires @delta_interval option)\r\nDifference between the CPU time reported on the first and second collections. \r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [context_switches_delta] [varchar](30) NULL\r\nNon-Formatted: [context_switches_delta] [bigint] NULL\r\n(Requires @delta_interval option)\r\nDifference between the context switches count reported on the first and second collections\r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [used_memory_delta] [varchar](30) NULL\r\nNon-Formatted: [used_memory_delta] [bigint] NULL\r\nDifference between the memory usage reported on the first and second collections\r\nIf the request started after the first collection, the value will be NULL\r\n\r\nFormatted: [tasks] [varchar](30) NULL\r\nNon-Formatted: [tasks] [smallint] NULL\r\nNumber of worker tasks currently allocated, for active requests\r\n\r\nFormatted\/Non: [status] [varchar](30) NOT NULL\r\nActivity status for the session (running, sleeping, etc)\r\n\r\nFormatted\/Non: [wait_info] [nvarchar](4000) NULL\r\nAggregates wait information, in the following format:\r\n(Ax: Bms\/Cms\/Dms)E\r\nA is the number of waiting tasks currently waiting on resource type E. B\/C\/D are wait\r\ntimes, in milliseconds. If only one thread is waiting, its wait time will be shown as B.\r\nIf two tasks are waiting, each of their wait times will be shown (B\/C). If three or more \r\ntasks are waiting, the minimum, average, and maximum wait times will be shown (B\/C\/D).\r\nIf wait type E is a page latch wait and the page is of a \"special\" type (e.g. PFS, GAM, SGAM), \r\nthe page type will be identified.\r\nIf wait type E is CXPACKET, the nodeId from the query plan will be identified\r\n\r\nFormatted\/Non: [locks] [xml] NULL\r\n(Requires @get_locks option)\r\nAggregates lock information, in XML format.\r\nThe lock XML includes the lock mode, locked object, and aggregates the number of requests. \r\nAttempts are made to identify locked objects by name\r\n\r\nFormatted\/Non: [tran_start_time] [datetime] NULL\r\n(Requires @get_transaction_info option)\r\nDate and time that the first transaction opened by a session caused a transaction log \r\nwrite to occur.\r\n\r\nFormatted\/Non: [tran_log_writes] [nvarchar](4000) NULL\r\n(Requires @get_transaction_info option)\r\nAggregates transaction log write information, in the following format:\r\nA:wB (C kB)\r\nA is a database that has been touched by an active transaction\r\nB is the number of log writes that have been made in the database as a result of the transaction\r\nC is the number of log kilobytes consumed by the log records\r\n\r\nFormatted: [open_tran_count] [varchar](30) NULL\r\nNon-Formatted: [open_tran_count] [smallint] NULL\r\nShows the number of open transactions the session has open\r\n\r\nFormatted: [sql_command] [xml] NULL\r\nNon-Formatted: [sql_command] [nvarchar](max) NULL\r\n(Requires @get_outer_command option)\r\nShows the \"outer\" SQL command, i.e. the text of the batch or RPC sent to the server, \r\nif available\r\n\r\nFormatted: [sql_text] [xml] NULL\r\nNon-Formatted: [sql_text] [nvarchar](max) NULL\r\nShows the SQL text for active requests or the last statement executed\r\nfor sleeping sessions, if available in either case.\r\nIf @get_full_inner_text option is set, shows the full text of the batch.\r\nOtherwise, shows only the active statement within the batch.\r\nIf the query text is locked, a special timeout message will be sent, in the following format:\r\n<timeout_exceeded \/>\r\nIf an error occurs, an error message will be sent, in the following format:\r\n<error message=\"message\" \/>\r\n\r\nFormatted\/Non: [query_plan] [xml] NULL\r\n(Requires @get_plans option)\r\nShows the query plan for the request, if available.\r\nIf the plan is locked, a special timeout message will be sent, in the following format:\r\n<timeout_exceeded \/>\r\nIf an error occurs, an error message will be sent, in the following format:\r\n<error message=\"message\" \/>\r\n\r\nFormatted\/Non: [blocking_session_id] [smallint] NULL\r\nWhen applicable, shows the blocking SPID\r\n\r\nFormatted: [blocked_session_count] [varchar](30) NULL\r\nNon-Formatted: [blocked_session_count] [smallint] NULL\r\n(Requires @find_block_leaders option)\r\nThe total number of SPIDs blocked by this session,\r\nall the way down the blocking chain.\r\n\r\nFormatted: [percent_complete] [varchar](30) NULL\r\nNon-Formatted: [percent_complete] [real] NULL\r\nWhen applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)\r\n\r\nFormatted\/Non: [host_name] [sysname] NOT NULL\r\nShows the host name for the connection\r\n\r\nFormatted\/Non: [login_name] [sysname] NOT NULL\r\nShows the login name for the connection\r\n\r\nFormatted\/Non: [database_name] [sysname] NULL\r\nShows the connected database\r\n\r\nFormatted\/Non: [program_name] [sysname] NULL\r\nShows the reported program\/application name\r\n\r\nFormatted\/Non: [additional_info] [xml] NULL\r\n(Requires @get_additional_info option)\r\nReturns additional non-performance-related session\/request information\r\nIf the script finds a SQL Agent job running, the name of the job and job step will be reported\r\nIf @get_task_info = 2 and the script finds a lock wait, the locked object will be reported\r\n\r\nFormatted\/Non: [start_time] [datetime] NOT NULL\r\nFor active requests, shows the time the request started\r\nFor sleeping sessions, shows the time the last batch completed\r\n\r\nFormatted\/Non: [login_time] [datetime] NOT NULL\r\nShows the time that the session connected\r\n\r\nFormatted\/Non: [request_id] [int] NULL\r\nFor active requests, shows the request_id\r\nShould be 0 unless MARS is being used\r\n\r\nFormatted\/Non: [collection_time] [datetime] NOT NULL\r\nTime that this script's final SELECT ran\r\n*\/\r\nAS\r\nBEGIN;\r\nSET NOCOUNT ON; \r\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nSET QUOTED_IDENTIFIER ON;\r\nSET ANSI_PADDING ON;\r\nSET CONCAT_NULL_YIELDS_NULL ON;\r\nSET ANSI_WARNINGS ON;\r\nSET NUMERIC_ROUNDABORT OFF;\r\nSET ARITHABORT ON;\r\n\r\nIF\r\n@filter IS NULL\r\nOR @filter_type IS NULL\r\nOR @not_filter IS NULL\r\nOR @not_filter_type IS NULL\r\nOR @show_own_spid IS NULL\r\nOR @show_system_spids IS NULL\r\nOR @show_sleeping_spids IS NULL\r\nOR @get_full_inner_text IS NULL\r\nOR @get_plans IS NULL\r\nOR @get_outer_command IS NULL\r\nOR @get_transaction_info IS NULL\r\nOR @get_task_info IS NULL\r\nOR @get_locks IS NULL\r\nOR @get_avg_time IS NULL\r\nOR @get_additional_info IS NULL\r\nOR @find_block_leaders IS NULL\r\nOR @delta_interval IS NULL\r\nOR @format_output IS NULL\r\nOR @output_column_list IS NULL\r\nOR @sort_order IS NULL\r\nOR @return_schema IS NULL\r\nOR @destination_table IS NULL\r\nOR @help IS NULL\r\nBEGIN;\r\nRAISERROR('Input parameters cannot be NULL', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host')\r\nBEGIN;\r\nRAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%'\r\nBEGIN;\r\nRAISERROR('Session filters must be valid integers', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host')\r\nBEGIN;\r\nRAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%'\r\nBEGIN;\r\nRAISERROR('Session filters must be valid integers', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @show_sleeping_spids NOT IN (0, 1, 2)\r\nBEGIN;\r\nRAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @get_plans NOT IN (0, 1, 2)\r\nBEGIN;\r\nRAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @get_task_info NOT IN (0, 1, 2)\r\nBEGIN;\r\nRAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @format_output NOT IN (0, 1, 2)\r\nBEGIN;\r\nRAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @help = 1\r\nBEGIN;\r\nDECLARE \r\n@header VARCHAR(MAX),\r\n@params VARCHAR(MAX),\r\n@outputs VARCHAR(MAX);\r\n\r\nSELECT \r\n@header =\r\nREPLACE\r\n(\r\nREPLACE\r\n(\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\nSUBSTRING\r\n(\r\nt.text, \r\nCHARINDEX('\/' + REPLICATE('*', 93), t.text) + 94,\r\nCHARINDEX(REPLICATE('*', 93) + '\/', t.text) - (CHARINDEX('\/' + REPLICATE('*', 93), t.text) + 94)\r\n)\r\n),\r\nCHAR(13)+CHAR(10),\r\nCHAR(13)\r\n),\r\n' ',\r\n''\r\n),\r\n@params =\r\nCHAR(13) +\r\nREPLACE\r\n(\r\nREPLACE\r\n(\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\nSUBSTRING\r\n(\r\nt.text, \r\nCHARINDEX('--~', t.text) + 5, \r\nCHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5)\r\n)\r\n),\r\nCHAR(13)+CHAR(10),\r\nCHAR(13)\r\n),\r\n' ',\r\n''\r\n),\r\n@outputs = \r\nCHAR(13) +\r\nREPLACE\r\n(\r\nREPLACE\r\n(\r\nREPLACE\r\n(\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\nSUBSTRING\r\n(\r\nt.text, \r\nCHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32,\r\nCHARINDEX('*\/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32)\r\n)\r\n),\r\nCHAR(9),\r\nCHAR(255)\r\n),\r\nCHAR(13)+CHAR(10),\r\nCHAR(13)\r\n),\r\n' ',\r\n''\r\n) +\r\nCHAR(13)\r\nFROM sys.dm_exec_requests AS r\r\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t\r\nWHERE\r\nr.session_id = @@SPID;\r\n\r\nWITH\r\na0 AS\r\n(SELECT 1 AS n UNION ALL SELECT 1),\r\na1 AS\r\n(SELECT 1 AS n FROM a0 AS a, a0 AS b),\r\na2 AS\r\n(SELECT 1 AS n FROM a1 AS a, a1 AS b),\r\na3 AS\r\n(SELECT 1 AS n FROM a2 AS a, a2 AS b),\r\na4 AS\r\n(SELECT 1 AS n FROM a3 AS a, a3 AS b),\r\nnumbers AS\r\n(\r\nSELECT TOP(LEN(@header) - 1)\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY (SELECT NULL)\r\n) AS number\r\nFROM a4\r\nORDER BY\r\nnumber\r\n)\r\nSELECT\r\nRTRIM(LTRIM(\r\nSUBSTRING\r\n(\r\n@header,\r\nnumber + 1,\r\nCHARINDEX(CHAR(13), @header, number + 1) - number - 1\r\n)\r\n)) AS [------header---------------------------------------------------------------------------------------------------------------]\r\nFROM numbers\r\nWHERE\r\nSUBSTRING(@header, number, 1) = CHAR(13);\r\n\r\nWITH\r\na0 AS\r\n(SELECT 1 AS n UNION ALL SELECT 1),\r\na1 AS\r\n(SELECT 1 AS n FROM a0 AS a, a0 AS b),\r\na2 AS\r\n(SELECT 1 AS n FROM a1 AS a, a1 AS b),\r\na3 AS\r\n(SELECT 1 AS n FROM a2 AS a, a2 AS b),\r\na4 AS\r\n(SELECT 1 AS n FROM a3 AS a, a3 AS b),\r\nnumbers AS\r\n(\r\nSELECT TOP(LEN(@params) - 1)\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY (SELECT NULL)\r\n) AS number\r\nFROM a4\r\nORDER BY\r\nnumber\r\n),\r\ntokens AS\r\n(\r\nSELECT \r\nRTRIM(LTRIM(\r\nSUBSTRING\r\n(\r\n@params,\r\nnumber + 1,\r\nCHARINDEX(CHAR(13), @params, number + 1) - number - 1\r\n)\r\n)) AS token,\r\nnumber,\r\nCASE\r\nWHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number\r\nELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) \r\nEND AS param_group,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\nCHARINDEX(',' + CHAR(13) + CHAR(13), @params, number),\r\nSUBSTRING(@params, number+1, 1)\r\nORDER BY \r\nnumber\r\n) AS group_order\r\nFROM numbers\r\nWHERE\r\nSUBSTRING(@params, number, 1) = CHAR(13)\r\n),\r\nparsed_tokens AS\r\n(\r\nSELECT\r\nMIN\r\n(\r\nCASE\r\nWHEN token LIKE '@%' THEN token\r\nELSE NULL\r\nEND\r\n) AS parameter,\r\nMIN\r\n(\r\nCASE\r\nWHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2)\r\nELSE NULL\r\nEND\r\n) AS description,\r\nparam_group,\r\ngroup_order\r\nFROM tokens\r\nWHERE\r\nNOT \r\n(\r\ntoken = '' \r\nAND group_order > 1\r\n)\r\nGROUP BY\r\nparam_group,\r\ngroup_order\r\n)\r\nSELECT\r\nCASE\r\nWHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------'\r\nWHEN param_group = MAX(param_group) OVER() THEN parameter\r\nELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '')\r\nEND AS [------parameter----------------------------------------------------------],\r\nCASE\r\nWHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------'\r\nELSE COALESCE(description, '')\r\nEND AS [------description-----------------------------------------------------------------------------------------------------]\r\nFROM parsed_tokens\r\nORDER BY\r\nparam_group, \r\ngroup_order;\r\n\r\nWITH\r\na0 AS\r\n(SELECT 1 AS n UNION ALL SELECT 1),\r\na1 AS\r\n(SELECT 1 AS n FROM a0 AS a, a0 AS b),\r\na2 AS\r\n(SELECT 1 AS n FROM a1 AS a, a1 AS b),\r\na3 AS\r\n(SELECT 1 AS n FROM a2 AS a, a2 AS b),\r\na4 AS\r\n(SELECT 1 AS n FROM a3 AS a, a3 AS b),\r\nnumbers AS\r\n(\r\nSELECT TOP(LEN(@outputs) - 1)\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY (SELECT NULL)\r\n) AS number\r\nFROM a4\r\nORDER BY\r\nnumber\r\n),\r\ntokens AS\r\n(\r\nSELECT \r\nRTRIM(LTRIM(\r\nSUBSTRING\r\n(\r\n@outputs,\r\nnumber + 1,\r\nCASE\r\nWHEN \r\nCOALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) < \r\nCOALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))\r\nTHEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) - number - 1\r\nELSE\r\nCOALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1\r\nEND\r\n)\r\n)) AS token,\r\nnumber,\r\nCOALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY \r\nCOALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs))\r\nORDER BY\r\nnumber\r\n) AS output_group_order\r\nFROM numbers\r\nWHERE\r\nSUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted'\r\nOR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2\r\n),\r\noutput_tokens AS\r\n(\r\nSELECT \r\n*,\r\nCASE output_group_order\r\nWHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)\r\nELSE ''\r\nEND COLLATE Latin1_General_Bin2 AS column_info\r\nFROM tokens\r\n)\r\nSELECT\r\nCASE output_group_order\r\nWHEN 1 THEN '-----------------------------------'\r\nWHEN 2 THEN \r\nCASE\r\nWHEN CHARINDEX('Formatted\/Non:', column_info) = 1 THEN\r\nSUBSTRING(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))\r\nELSE\r\nSUBSTRING(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)\r\nEND\r\nELSE ''\r\nEND AS formatted_column_name,\r\nCASE output_group_order\r\nWHEN 1 THEN '-----------------------------------'\r\nWHEN 2 THEN \r\nCASE\r\nWHEN CHARINDEX('Formatted\/Non:', column_info) = 1 THEN\r\nSUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info))\r\nELSE\r\nSUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)\r\nEND\r\nELSE ''\r\nEND AS formatted_column_type,\r\nCASE output_group_order\r\nWHEN 1 THEN '---------------------------------------'\r\nWHEN 2 THEN \r\nCASE\r\nWHEN CHARINDEX('Formatted\/Non:', column_info) = 1 THEN ''\r\nELSE\r\nCASE\r\nWHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN\r\nSUBSTRING(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)))\r\nELSE\r\nSUBSTRING(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)))\r\nEND\r\nEND\r\nELSE ''\r\nEND AS unformatted_column_name,\r\nCASE output_group_order\r\nWHEN 1 THEN '---------------------------------------'\r\nWHEN 2 THEN \r\nCASE\r\nWHEN CHARINDEX('Formatted\/Non:', column_info) = 1 THEN ''\r\nELSE\r\nCASE\r\nWHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN ''\r\nELSE\r\nSUBSTRING(column_info, CHARINDEX(']', column_info, CHARINDEX('Non-Formatted:', column_info))+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)\r\nEND\r\nEND\r\nELSE ''\r\nEND AS unformatted_column_type,\r\nCASE output_group_order\r\nWHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------'\r\nELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '')\r\nEND AS [------description-----------------------------------------------------------------------------------------------------]\r\nFROM output_tokens\r\nWHERE\r\nNOT \r\n(\r\noutput_group_order = 1 \r\nAND output_group = LEN(@outputs)\r\n)\r\nORDER BY\r\noutput_group,\r\nCASE output_group_order\r\nWHEN 1 THEN 99\r\nELSE output_group_order\r\nEND;\r\n\r\nRETURN;\r\nEND;\r\n\r\nWITH\r\na0 AS\r\n(SELECT 1 AS n UNION ALL SELECT 1),\r\na1 AS\r\n(SELECT 1 AS n FROM a0 AS a, a0 AS b),\r\na2 AS\r\n(SELECT 1 AS n FROM a1 AS a, a1 AS b),\r\na3 AS\r\n(SELECT 1 AS n FROM a2 AS a, a2 AS b),\r\na4 AS\r\n(SELECT 1 AS n FROM a3 AS a, a3 AS b),\r\nnumbers AS\r\n(\r\nSELECT TOP(LEN(@output_column_list))\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY (SELECT NULL)\r\n) AS number\r\nFROM a4\r\nORDER BY\r\nnumber\r\n),\r\ntokens AS\r\n(\r\nSELECT \r\n'|[' +\r\nSUBSTRING\r\n(\r\n@output_column_list,\r\nnumber + 1,\r\nCHARINDEX(']', @output_column_list, number) - number - 1\r\n) + '|]' AS token,\r\nnumber\r\nFROM numbers\r\nWHERE\r\nSUBSTRING(@output_column_list, number, 1) = '['\r\n),\r\nordered_columns AS\r\n(\r\nSELECT\r\nx.column_name,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\nx.column_name\r\nORDER BY\r\ntokens.number,\r\nx.default_order\r\n) AS r,\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY\r\ntokens.number,\r\nx.default_order\r\n) AS s\r\nFROM tokens\r\nJOIN\r\n(\r\nSELECT '[session_id]' AS column_name, 1 AS default_order\r\nUNION ALL\r\nSELECT '[dd hh:mm:ss.mss]', 2\r\nWHERE\r\n@format_output IN (1, 2)\r\nUNION ALL\r\nSELECT '[dd hh:mm:ss.mss (avg)]', 3\r\nWHERE\r\n@format_output IN (1, 2)\r\nAND @get_avg_time = 1\r\nUNION ALL\r\nSELECT '[avg_elapsed_time]', 4\r\nWHERE\r\n@format_output = 0\r\nAND @get_avg_time = 1\r\nUNION ALL\r\nSELECT '[physical_io]', 5\r\nWHERE\r\n@get_task_info = 2\r\nUNION ALL\r\nSELECT '[reads]', 6\r\nUNION ALL\r\nSELECT '[physical_reads]', 7\r\nUNION ALL\r\nSELECT '[writes]', 8\r\nUNION ALL\r\nSELECT '[tempdb_allocations]', 9\r\nUNION ALL\r\nSELECT '[tempdb_current]', 10\r\nUNION ALL\r\nSELECT '[CPU]', 11\r\nUNION ALL\r\nSELECT '[context_switches]', 12\r\nWHERE\r\n@get_task_info = 2\r\nUNION ALL\r\nSELECT '[used_memory]', 13\r\nUNION ALL\r\nSELECT '[physical_io_delta]', 14\r\nWHERE\r\n@delta_interval > 0 \r\nAND @get_task_info = 2\r\nUNION ALL\r\nSELECT '[reads_delta]', 15\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[physical_reads_delta]', 16\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[writes_delta]', 17\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[tempdb_allocations_delta]', 18\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[tempdb_current_delta]', 19\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[CPU_delta]', 20\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[context_switches_delta]', 21\r\nWHERE\r\n@delta_interval > 0\r\nAND @get_task_info = 2\r\nUNION ALL\r\nSELECT '[used_memory_delta]', 22\r\nWHERE\r\n@delta_interval > 0\r\nUNION ALL\r\nSELECT '[tasks]', 23\r\nWHERE\r\n@get_task_info = 2\r\nUNION ALL\r\nSELECT '[status]', 24\r\nUNION ALL\r\nSELECT '[wait_info]', 25\r\nWHERE\r\n@get_task_info > 0\r\nOR @find_block_leaders = 1\r\nUNION ALL\r\nSELECT '[locks]', 26\r\nWHERE\r\n@get_locks = 1\r\nUNION ALL\r\nSELECT '[tran_start_time]', 27\r\nWHERE\r\n@get_transaction_info = 1\r\nUNION ALL\r\nSELECT '[tran_log_writes]', 28\r\nWHERE\r\n@get_transaction_info = 1\r\nUNION ALL\r\nSELECT '[open_tran_count]', 29\r\nUNION ALL\r\nSELECT '[sql_command]', 30\r\nWHERE\r\n@get_outer_command = 1\r\nUNION ALL\r\nSELECT '[sql_text]', 31\r\nUNION ALL\r\nSELECT '[query_plan]', 32\r\nWHERE\r\n@get_plans >= 1\r\nUNION ALL\r\nSELECT '[blocking_session_id]', 33\r\nWHERE\r\n@get_task_info > 0\r\nOR @find_block_leaders = 1\r\nUNION ALL\r\nSELECT '[blocked_session_count]', 34\r\nWHERE\r\n@find_block_leaders = 1\r\nUNION ALL\r\nSELECT '[percent_complete]', 35\r\nUNION ALL\r\nSELECT '[host_name]', 36\r\nUNION ALL\r\nSELECT '[login_name]', 37\r\nUNION ALL\r\nSELECT '[database_name]', 38\r\nUNION ALL\r\nSELECT '[program_name]', 39\r\nUNION ALL\r\nSELECT '[additional_info]', 40\r\nWHERE\r\n@get_additional_info = 1\r\nUNION ALL\r\nSELECT '[start_time]', 41\r\nUNION ALL\r\nSELECT '[login_time]', 42\r\nUNION ALL\r\nSELECT '[request_id]', 43\r\nUNION ALL\r\nSELECT '[collection_time]', 44\r\n) AS x ON \r\nx.column_name LIKE token ESCAPE '|'\r\n)\r\nSELECT\r\n@output_column_list =\r\nSTUFF\r\n(\r\n(\r\nSELECT\r\n',' + column_name as [text()]\r\nFROM ordered_columns\r\nWHERE\r\nr = 1\r\nORDER BY\r\ns\r\nFOR XML\r\nPATH('')\r\n),\r\n1,\r\n1,\r\n''\r\n);\r\n\r\nIF COALESCE(RTRIM(@output_column_list), '') = ''\r\nBEGIN;\r\nRAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1);\r\nRETURN;\r\nEND;\r\n\r\nIF @destination_table <> ''\r\nBEGIN;\r\nSET @destination_table = \r\n--database\r\nCOALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') +\r\n--schema\r\nCOALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') +\r\n--table\r\nCOALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), '');\r\n\r\nIF COALESCE(RTRIM(@destination_table), '') = ''\r\nBEGIN;\r\nRAISERROR('Destination table not properly formatted.', 16, 1);\r\nRETURN;\r\nEND;\r\nEND;\r\n\r\nWITH\r\na0 AS\r\n(SELECT 1 AS n UNION ALL SELECT 1),\r\na1 AS\r\n(SELECT 1 AS n FROM a0 AS a, a0 AS b),\r\na2 AS\r\n(SELECT 1 AS n FROM a1 AS a, a1 AS b),\r\na3 AS\r\n(SELECT 1 AS n FROM a2 AS a, a2 AS b),\r\na4 AS\r\n(SELECT 1 AS n FROM a3 AS a, a3 AS b),\r\nnumbers AS\r\n(\r\nSELECT TOP(LEN(@sort_order))\r\nROW_NUMBER() OVER\r\n(\r\nORDER BY (SELECT NULL)\r\n) AS number\r\nFROM a4\r\nORDER BY\r\nnumber\r\n),\r\ntokens AS\r\n(\r\nSELECT \r\n'|[' +\r\nSUBSTRING\r\n(\r\n@sort_order,\r\nnumber + 1,\r\nCHARINDEX(']', @sort_order, number) - number - 1\r\n) + '|]' AS token,\r\nSUBSTRING\r\n(\r\n@sort_order,\r\nCHARINDEX(']', @sort_order, number) + 1,\r\nCOALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number)\r\n) AS next_chunk,\r\nnumber\r\nFROM numbers\r\nWHERE\r\nSUBSTRING(@sort_order, number, 1) = '['\r\n),\r\nordered_columns AS\r\n(\r\nSELECT\r\nx.column_name +\r\nCASE\r\nWHEN tokens.next_chunk LIKE '%asc%' THEN ' ASC'\r\nWHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC'\r\nELSE ''\r\nEND AS column_name,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\nx.column_name\r\nORDER BY\r\ntokens.number\r\n) AS r,\r\ntokens.number\r\nFROM tokens\r\nJOIN\r\n(\r\nSELECT '[session_id]' AS column_name\r\nUNION ALL\r\nSELECT '[physical_io]'\r\nUNION ALL\r\nSELECT '[reads]'\r\nUNION ALL\r\nSELECT '[physical_reads]'\r\nUNION ALL\r\nSELECT '[writes]'\r\nUNION ALL\r\nSELECT '[tempdb_allocations]'\r\nUNION ALL\r\nSELECT '[tempdb_current]'\r\nUNION ALL\r\nSELECT '[CPU]'\r\nUNION ALL\r\nSELECT '[context_switches]'\r\nUNION ALL\r\nSELECT '[used_memory]'\r\nUNION ALL\r\nSELECT '[physical_io_delta]'\r\nUNION ALL\r\nSELECT '[reads_delta]'\r\nUNION ALL\r\nSELECT '[physical_reads_delta]'\r\nUNION ALL\r\nSELECT '[writes_delta]'\r\nUNION ALL\r\nSELECT '[tempdb_allocations_delta]'\r\nUNION ALL\r\nSELECT '[tempdb_current_delta]'\r\nUNION ALL\r\nSELECT '[CPU_delta]'\r\nUNION ALL\r\nSELECT '[context_switches_delta]'\r\nUNION ALL\r\nSELECT '[used_memory_delta]'\r\nUNION ALL\r\nSELECT '[tasks]'\r\nUNION ALL\r\nSELECT '[tran_start_time]'\r\nUNION ALL\r\nSELECT '[open_tran_count]'\r\nUNION ALL\r\nSELECT '[blocking_session_id]'\r\nUNION ALL\r\nSELECT '[blocked_session_count]'\r\nUNION ALL\r\nSELECT '[percent_complete]'\r\nUNION ALL\r\nSELECT '[host_name]'\r\nUNION ALL\r\nSELECT '[login_name]'\r\nUNION ALL\r\nSELECT '[database_name]'\r\nUNION ALL\r\nSELECT '[start_time]'\r\nUNION ALL\r\nSELECT '[login_time]'\r\n) AS x ON \r\nx.column_name LIKE token ESCAPE '|'\r\n)\r\nSELECT\r\n@sort_order = COALESCE(z.sort_order, '')\r\nFROM\r\n(\r\nSELECT\r\nSTUFF\r\n(\r\n(\r\nSELECT\r\n',' + column_name as [text()]\r\nFROM ordered_columns\r\nWHERE\r\nr = 1\r\nORDER BY\r\nnumber\r\nFOR XML\r\nPATH('')\r\n),\r\n1,\r\n1,\r\n''\r\n) AS sort_order\r\n) AS z;\r\n\r\nCREATE TABLE #sessions\r\n(\r\nrecursion SMALLINT NOT NULL,\r\nsession_id SMALLINT NOT NULL,\r\nrequest_id INT NOT NULL,\r\nsession_number INT NOT NULL,\r\nelapsed_time INT NOT NULL,\r\navg_elapsed_time INT NULL,\r\nphysical_io BIGINT NULL,\r\nreads BIGINT NULL,\r\nphysical_reads BIGINT NULL,\r\nwrites BIGINT NULL,\r\ntempdb_allocations BIGINT NULL,\r\ntempdb_current BIGINT NULL,\r\nCPU INT NULL,\r\nthread_CPU_snapshot BIGINT NULL,\r\ncontext_switches BIGINT NULL,\r\nused_memory BIGINT NOT NULL, \r\ntasks SMALLINT NULL,\r\nstatus VARCHAR(30) NOT NULL,\r\nwait_info NVARCHAR(4000) NULL,\r\nlocks XML NULL,\r\ntransaction_id BIGINT NULL,\r\ntran_start_time DATETIME NULL,\r\ntran_log_writes NVARCHAR(4000) NULL,\r\nopen_tran_count SMALLINT NULL,\r\nsql_command XML NULL,\r\nsql_handle VARBINARY(64) NULL,\r\nstatement_start_offset INT NULL,\r\nstatement_end_offset INT NULL,\r\nsql_text XML NULL,\r\nplan_handle VARBINARY(64) NULL,\r\nquery_plan XML NULL,\r\nblocking_session_id SMALLINT NULL,\r\nblocked_session_count SMALLINT NULL,\r\npercent_complete REAL NULL,\r\nhost_name sysname NULL,\r\nlogin_name sysname NOT NULL,\r\ndatabase_name sysname NULL,\r\nprogram_name sysname NULL,\r\nadditional_info XML NULL,\r\nstart_time DATETIME NOT NULL,\r\nlogin_time DATETIME NULL,\r\nlast_request_start_time DATETIME NULL,\r\nPRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),\r\nUNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)\r\n);\r\n\r\nIF @return_schema = 0\r\nBEGIN;\r\n--Disable unnecessary autostats on the table\r\nCREATE STATISTICS s_session_id ON #sessions (session_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_request_id ON #sessions (request_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_transaction_id ON #sessions (transaction_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_session_number ON #sessions (session_number)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_status ON #sessions (status)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_start_time ON #sessions (start_time)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_recursion ON #sessions (recursion)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\n\r\nDECLARE @recursion SMALLINT;\r\nSET @recursion = \r\nCASE @delta_interval\r\nWHEN 0 THEN 1\r\nELSE -1\r\nEND;\r\n\r\nDECLARE @first_collection_ms_ticks BIGINT;\r\nDECLARE @last_collection_start DATETIME;\r\n\r\n--Used for the delta pull\r\nREDO:;\r\n\r\nIF \r\n@get_locks = 1 \r\nAND @recursion = 1\r\nAND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'\r\nBEGIN;\r\nSELECT\r\ny.resource_type,\r\ny.database_name,\r\ny.object_id,\r\ny.file_id,\r\ny.page_type,\r\ny.hobt_id,\r\ny.allocation_unit_id,\r\ny.index_id,\r\ny.schema_id,\r\ny.principal_id,\r\ny.request_mode,\r\ny.request_status,\r\ny.session_id,\r\ny.resource_description,\r\ny.request_count,\r\ns.request_id,\r\ns.start_time,\r\nCONVERT(sysname, NULL) AS object_name,\r\nCONVERT(sysname, NULL) AS index_name,\r\nCONVERT(sysname, NULL) AS schema_name,\r\nCONVERT(sysname, NULL) AS principal_name,\r\nCONVERT(NVARCHAR(2048), NULL) AS query_error\r\nINTO #locks\r\nFROM\r\n(\r\nSELECT\r\nsp.spid AS session_id,\r\nCASE sp.status\r\nWHEN 'sleeping' THEN CONVERT(INT, 0)\r\nELSE sp.request_id\r\nEND AS request_id,\r\nCASE sp.status\r\nWHEN 'sleeping' THEN sp.last_batch\r\nELSE COALESCE(req.start_time, sp.last_batch)\r\nEND AS start_time,\r\nsp.dbid\r\nFROM sys.sysprocesses AS sp\r\nOUTER APPLY\r\n(\r\nSELECT TOP(1)\r\nCASE\r\nWHEN \r\n(\r\nsp.hostprocess > ''\r\nOR r.total_elapsed_time < 0\r\n) THEN\r\nr.start_time\r\nELSE\r\nDATEADD\r\n(\r\nms, \r\n1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())) \/ 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())), \r\nDATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())\r\n)\r\nEND AS start_time\r\nFROM sys.dm_exec_requests AS r\r\nWHERE\r\nr.session_id = sp.spid\r\nAND r.request_id = sp.request_id\r\n) AS req\r\nWHERE\r\n--Process inclusive filter\r\n1 =\r\nCASE\r\nWHEN @filter <> '' THEN\r\nCASE @filter_type\r\nWHEN 'session' THEN\r\nCASE\r\nWHEN\r\nCONVERT(SMALLINT, @filter) = 0\r\nOR sp.spid = CONVERT(SMALLINT, @filter)\r\nTHEN 1\r\nELSE 0\r\nEND\r\nWHEN 'program' THEN\r\nCASE\r\nWHEN sp.program_name LIKE @filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'login' THEN\r\nCASE\r\nWHEN sp.loginame LIKE @filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'host' THEN\r\nCASE\r\nWHEN sp.hostname LIKE @filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'database' THEN\r\nCASE\r\nWHEN DB_NAME(sp.dbid) LIKE @filter THEN 1\r\nELSE 0\r\nEND\r\nELSE 0\r\nEND\r\nELSE 1\r\nEND\r\n--Process exclusive filter\r\nAND 0 =\r\nCASE\r\nWHEN @not_filter <> '' THEN\r\nCASE @not_filter_type\r\nWHEN 'session' THEN\r\nCASE\r\nWHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'program' THEN\r\nCASE\r\nWHEN sp.program_name LIKE @not_filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'login' THEN\r\nCASE\r\nWHEN sp.loginame LIKE @not_filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'host' THEN\r\nCASE\r\nWHEN sp.hostname LIKE @not_filter THEN 1\r\nELSE 0\r\nEND\r\nWHEN 'database' THEN\r\nCASE\r\nWHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1\r\nELSE 0\r\nEND\r\nELSE 0\r\nEND\r\nELSE 0\r\nEND\r\nAND \r\n(\r\n@show_own_spid = 1\r\nOR sp.spid <> @@SPID\r\n)\r\nAND \r\n(\r\n@show_system_spids = 1\r\nOR sp.hostprocess > ''\r\n)\r\nAND sp.ecid = 0\r\n) AS s\r\nINNER HASH JOIN\r\n(\r\nSELECT\r\nx.resource_type,\r\nx.database_name,\r\nx.object_id,\r\nx.file_id,\r\nCASE\r\nWHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'\r\nWHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'\r\nWHEN x.page_no = 3 OR x.page_no % 511233 = 0 THEN 'SGAM'\r\nWHEN x.page_no = 6 OR x.page_no % 511238 = 0 THEN 'DCM'\r\nWHEN x.page_no = 7 OR x.page_no % 511239 = 0 THEN 'BCM'\r\nWHEN x.page_no IS NOT NULL THEN '*'\r\nELSE NULL\r\nEND AS page_type,\r\nx.hobt_id,\r\nx.allocation_unit_id,\r\nx.index_id,\r\nx.schema_id,\r\nx.principal_id,\r\nx.request_mode,\r\nx.request_status,\r\nx.session_id,\r\nx.request_id,\r\nCASE\r\nWHEN 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, '')\r\nELSE NULL\r\nEND AS resource_description,\r\nCOUNT(*) AS request_count\r\nFROM\r\n(\r\nSELECT\r\ntl.resource_type +\r\nCASE\r\nWHEN tl.resource_subtype = '' THEN ''\r\nELSE '.' + tl.resource_subtype\r\nEND AS resource_type,\r\nCOALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name,\r\nCONVERT\r\n(\r\nINT,\r\nCASE\r\nWHEN tl.resource_type = 'OBJECT' THEN tl.resource_associated_entity_id\r\nWHEN tl.resource_description LIKE '%object_id = %' THEN\r\n(\r\nSUBSTRING\r\n(\r\ntl.resource_description, \r\n(CHARINDEX('object_id = ', tl.resource_description) + 12), \r\nCOALESCE\r\n(\r\nNULLIF\r\n(\r\nCHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12),\r\n0\r\n), \r\nDATALENGTH(tl.resource_description)+1\r\n) - (CHARINDEX('object_id = ', tl.resource_description) + 12)\r\n)\r\n)\r\nELSE NULL\r\nEND\r\n) AS object_id,\r\nCONVERT\r\n(\r\nINT,\r\nCASE \r\nWHEN tl.resource_type = 'FILE' THEN CONVERT(INT, tl.resource_description)\r\nWHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1)\r\nELSE NULL\r\nEND\r\n) AS file_id,\r\nCONVERT\r\n(\r\nINT,\r\nCASE\r\nWHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN \r\nSUBSTRING\r\n(\r\ntl.resource_description, \r\nCHARINDEX(':', tl.resource_description) + 1, \r\nCOALESCE\r\n(\r\nNULLIF\r\n(\r\nCHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1), \r\n0\r\n), \r\nDATALENGTH(tl.resource_description)+1\r\n) - (CHARINDEX(':', tl.resource_description) + 1)\r\n)\r\nELSE NULL\r\nEND\r\n) AS page_no,\r\nCASE\r\nWHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id\r\nELSE NULL\r\nEND AS hobt_id,\r\nCASE\r\nWHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id\r\nELSE NULL\r\nEND AS allocation_unit_id,\r\nCONVERT\r\n(\r\nINT,\r\nCASE\r\nWHEN\r\n\/*TODO: Deal with server principals*\/ \r\ntl.resource_subtype <> 'SERVER_PRINCIPAL' \r\nAND tl.resource_description LIKE '%index_id or stats_id = %' THEN\r\n(\r\nSUBSTRING\r\n(\r\ntl.resource_description, \r\n(CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23), \r\nCOALESCE\r\n(\r\nNULLIF\r\n(\r\nCHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23), \r\n0\r\n), \r\nDATALENGTH(tl.resource_description)+1\r\n) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23)\r\n)\r\n)\r\nELSE NULL\r\nEND \r\n) AS index_id,\r\nCONVERT\r\n(\r\nINT,\r\nCASE\r\nWHEN tl.resource_description LIKE '%schema_id = %' THEN\r\n(\r\nSUBSTRING\r\n(\r\ntl.resource_description, \r\n(CHARINDEX('schema_id = ', tl.resource_description) + 12), \r\nCOALESCE\r\n(\r\nNULLIF\r\n(\r\nCHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12), \r\n0\r\n), \r\nDATALENGTH(tl.resource_description)+1\r\n) - (CHARINDEX('schema_id = ', tl.resource_description) + 12)\r\n)\r\n)\r\nELSE NULL\r\nEND \r\n) AS schema_id,\r\nCONVERT\r\n(\r\nINT,\r\nCASE\r\nWHEN tl.resource_description LIKE '%principal_id = %' THEN\r\n(\r\nSUBSTRING\r\n(\r\ntl.resource_description, \r\n(CHARINDEX('principal_id = ', tl.resource_description) + 15), \r\nCOALESCE\r\n(\r\nNULLIF\r\n(\r\nCHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15), \r\n0\r\n), \r\nDATALENGTH(tl.resource_description)+1\r\n) - (CHARINDEX('principal_id = ', tl.resource_description) + 15)\r\n)\r\n)\r\nELSE NULL\r\nEND\r\n) AS principal_id,\r\ntl.request_mode,\r\ntl.request_status,\r\ntl.request_session_id AS session_id,\r\ntl.request_request_id AS request_id,\r\n\r\n\/*TODO: Applocks, other resource_descriptions*\/\r\nRTRIM(tl.resource_description) AS resource_description,\r\ntl.resource_associated_entity_id\r\n\/*********************************************\/\r\nFROM \r\n(\r\nSELECT \r\nrequest_session_id,\r\nCONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,\r\nCONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,\r\nresource_database_id,\r\nCONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,\r\nresource_associated_entity_id,\r\nCONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,\r\nCONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,\r\nrequest_request_id\r\nFROM sys.dm_tran_locks\r\n) AS tl\r\n) AS x\r\nGROUP BY\r\nx.resource_type,\r\nx.database_name,\r\nx.object_id,\r\nx.file_id,\r\nCASE\r\nWHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'\r\nWHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'\r\nWHEN x.page_no = 3 OR x.page_no % 511233 = 0 THEN 'SGAM'\r\nWHEN x.page_no = 6 OR x.page_no % 511238 = 0 THEN 'DCM'\r\nWHEN x.page_no = 7 OR x.page_no % 511239 = 0 THEN 'BCM'\r\nWHEN x.page_no IS NOT NULL THEN '*'\r\nELSE NULL\r\nEND,\r\nx.hobt_id,\r\nx.allocation_unit_id,\r\nx.index_id,\r\nx.schema_id,\r\nx.principal_id,\r\nx.request_mode,\r\nx.request_status,\r\nx.session_id,\r\nx.request_id,\r\nCASE\r\nWHEN 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, '')\r\nELSE NULL\r\nEND\r\n) AS y ON\r\ny.session_id = s.session_id\r\nAND y.request_id = s.request_id\r\nOPTION (HASH GROUP);\r\n\r\n--Disable unnecessary autostats on the table\r\nCREATE STATISTICS s_database_name ON #locks (database_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_object_id ON #locks (object_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_hobt_id ON #locks (hobt_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_index_id ON #locks (index_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_schema_id ON #locks (schema_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_principal_id ON #locks (principal_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_request_id ON #locks (request_id)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_start_time ON #locks (start_time)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_resource_type ON #locks (resource_type)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_object_name ON #locks (object_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_schema_name ON #locks (schema_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_page_type ON #locks (page_type)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_request_mode ON #locks (request_mode)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_request_status ON #locks (request_status)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_resource_description ON #locks (resource_description)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_index_name ON #locks (index_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_principal_name ON #locks (principal_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nEND;\r\n\r\nDECLARE \r\n@sql VARCHAR(MAX), \r\n@sql_n NVARCHAR(MAX);\r\n\r\nSET @sql = \r\nCONVERT(VARCHAR(MAX), '') +\r\n'DECLARE @blocker BIT;\r\nSET @blocker = 0;\r\nDECLARE @i INT;\r\nSET @i = 2147483647;\r\n\r\nDECLARE @sessions TABLE\r\n(\r\nsession_id SMALLINT NOT NULL,\r\nrequest_id INT NOT NULL,\r\nlogin_time DATETIME,\r\nlast_request_end_time DATETIME,\r\nstatus VARCHAR(30),\r\nstatement_start_offset INT,\r\nstatement_end_offset INT,\r\nsql_handle BINARY(20),\r\nhost_name NVARCHAR(128),\r\nlogin_name NVARCHAR(128),\r\nprogram_name NVARCHAR(128),\r\ndatabase_id SMALLINT,\r\nmemory_usage INT,\r\nopen_tran_count SMALLINT, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0 \r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'wait_type NVARCHAR(32),\r\nwait_resource NVARCHAR(256),\r\nwait_time BIGINT, \r\n'\r\nELSE \r\n''\r\nEND +\r\n'blocked SMALLINT,\r\nis_user_process BIT,\r\ncmd VARCHAR(32),\r\nPRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)\r\n);\r\n\r\nDECLARE @blockers TABLE\r\n(\r\nsession_id INT NOT NULL PRIMARY KEY\r\n);\r\n\r\nBLOCKERS:;\r\n\r\nINSERT @sessions\r\n(\r\nsession_id,\r\nrequest_id,\r\nlogin_time,\r\nlast_request_end_time,\r\nstatus,\r\nstatement_start_offset,\r\nstatement_end_offset,\r\nsql_handle,\r\nhost_name,\r\nlogin_name,\r\nprogram_name,\r\ndatabase_id,\r\nmemory_usage,\r\nopen_tran_count, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0\r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'wait_type,\r\nwait_resource,\r\nwait_time, \r\n'\r\nELSE\r\n''\r\nEND +\r\n'blocked,\r\nis_user_process,\r\ncmd \r\n)\r\nSELECT TOP(@i)\r\nspy.session_id,\r\nspy.request_id,\r\nspy.login_time,\r\nspy.last_request_end_time,\r\nspy.status,\r\nspy.statement_start_offset,\r\nspy.statement_end_offset,\r\nspy.sql_handle,\r\nspy.host_name,\r\nspy.login_name,\r\nspy.program_name,\r\nspy.database_id,\r\nspy.memory_usage,\r\nspy.open_tran_count,\r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0 \r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'spy.wait_type,\r\nCASE\r\nWHEN\r\nspy.wait_type LIKE N''PAGE%LATCH_%''\r\nOR spy.wait_type = N''CXPACKET''\r\nOR spy.wait_type LIKE N''LATCH[_]%''\r\nOR spy.wait_type = N''OLEDB'' THEN\r\nspy.wait_resource\r\nELSE\r\nNULL\r\nEND AS wait_resource,\r\nspy.wait_time, \r\n'\r\nELSE\r\n''\r\nEND +\r\n'spy.blocked,\r\nspy.is_user_process,\r\nspy.cmd\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nspx.*, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0 \r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'ROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\nspx.session_id,\r\nspx.request_id\r\nORDER BY\r\nCASE\r\nWHEN spx.wait_type LIKE N''LCK[_]%'' THEN \r\n1\r\nELSE\r\n99\r\nEND,\r\nspx.wait_time DESC,\r\nspx.blocked DESC\r\n) AS r \r\n'\r\nELSE \r\n'1 AS r \r\n'\r\nEND +\r\n'FROM\r\n(\r\nSELECT TOP(@i)\r\nsp0.session_id,\r\nsp0.request_id,\r\nsp0.login_time,\r\nsp0.last_request_end_time,\r\nLOWER(sp0.status) AS status,\r\nCASE\r\nWHEN sp0.cmd = ''CREATE INDEX'' THEN\r\n0\r\nELSE\r\nsp0.stmt_start\r\nEND AS statement_start_offset,\r\nCASE\r\nWHEN sp0.cmd = N''CREATE INDEX'' THEN\r\n-1\r\nELSE\r\nCOALESCE(NULLIF(sp0.stmt_end, 0), -1)\r\nEND AS statement_end_offset,\r\nsp0.sql_handle,\r\nsp0.host_name,\r\nsp0.login_name,\r\nsp0.program_name,\r\nsp0.database_id,\r\nsp0.memory_usage,\r\nsp0.open_tran_count, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0 \r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'CASE\r\nWHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN\r\nsp0.wait_type\r\nELSE\r\nNULL\r\nEND AS wait_type,\r\nCASE\r\nWHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN \r\nsp0.wait_resource\r\nELSE\r\nNULL\r\nEND AS wait_resource,\r\nCASE\r\nWHEN sp0.wait_type <> N''CXPACKET'' THEN\r\nsp0.wait_time\r\nELSE\r\n0\r\nEND AS wait_time, \r\n'\r\nELSE\r\n''\r\nEND +\r\n'sp0.blocked,\r\nsp0.is_user_process,\r\nsp0.cmd\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nsp1.session_id,\r\nsp1.request_id,\r\nsp1.login_time,\r\nsp1.last_request_end_time,\r\nsp1.status,\r\nsp1.cmd,\r\nsp1.stmt_start,\r\nsp1.stmt_end,\r\nMAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,\r\nsp1.host_name,\r\nMAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,\r\nsp1.program_name,\r\nsp1.database_id,\r\nMAX(sp1.memory_usage) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,\r\nMAX(sp1.open_tran_count) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,\r\nsp1.wait_type,\r\nsp1.wait_resource,\r\nsp1.wait_time,\r\nsp1.blocked,\r\nsp1.hostprocess,\r\nsp1.is_user_process\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nsp2.spid AS session_id,\r\nCASE sp2.status\r\nWHEN ''sleeping'' THEN\r\nCONVERT(INT, 0)\r\nELSE\r\nsp2.request_id\r\nEND AS request_id,\r\nMAX(sp2.login_time) AS login_time,\r\nMAX(sp2.last_batch) AS last_request_end_time,\r\nMAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,\r\nMAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,\r\nMAX(sp2.stmt_start) AS stmt_start,\r\nMAX(sp2.stmt_end) AS stmt_end,\r\nMAX(sp2.sql_handle) AS sql_handle,\r\nMAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,\r\nMAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,\r\nMAX\r\n(\r\nCASE\r\nWHEN blk.queue_id IS NOT NULL THEN\r\nN''Service Broker\r\ndatabase_id: '' + CONVERT(NVARCHAR, blk.database_id) +\r\nN'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id)\r\nELSE\r\nCONVERT\r\n(\r\nsysname,\r\nRTRIM(sp2.program_name)\r\n)\r\nEND COLLATE SQL_Latin1_General_CP1_CI_AS\r\n) AS program_name,\r\nMAX(sp2.dbid) AS database_id,\r\nMAX(sp2.memusage) AS memory_usage,\r\nMAX(sp2.open_tran) AS open_tran_count,\r\nRTRIM(sp2.lastwaittype) AS wait_type,\r\nRTRIM(sp2.waitresource) AS wait_resource,\r\nMAX(sp2.waittime) AS wait_time,\r\nCOALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,\r\nMAX\r\n(\r\nCASE\r\nWHEN blk.session_id = sp2.spid THEN\r\n''blocker''\r\nELSE\r\nRTRIM(sp2.hostprocess)\r\nEND\r\n) AS hostprocess,\r\nCONVERT\r\n(\r\nBIT,\r\nMAX\r\n(\r\nCASE\r\nWHEN sp2.hostprocess > '''' THEN\r\n1\r\nELSE\r\n0\r\nEND\r\n)\r\n) AS is_user_process\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nsession_id,\r\nCONVERT(INT, NULL) AS queue_id,\r\nCONVERT(INT, NULL) AS database_id\r\nFROM @blockers\r\n\r\nUNION ALL\r\n\r\nSELECT TOP(@i)\r\nCONVERT(SMALLINT, 0),\r\nCONVERT(INT, NULL) AS queue_id,\r\nCONVERT(INT, NULL) AS database_id\r\nWHERE\r\n@blocker = 0\r\n\r\nUNION ALL\r\n\r\nSELECT TOP(@i)\r\nCONVERT(SMALLINT, spid),\r\nqueue_id,\r\ndatabase_id\r\nFROM sys.dm_broker_activated_tasks\r\nWHERE\r\n@blocker = 0\r\n) AS blk\r\nINNER JOIN sys.sysprocesses AS sp2 ON\r\nsp2.spid = blk.session_id\r\nOR\r\n(\r\nblk.session_id = 0\r\nAND @blocker = 0\r\n)\r\n' +\r\nCASE \r\nWHEN \r\n(\r\n@get_task_info = 0 \r\nAND @find_block_leaders = 0\r\n) THEN\r\n'WHERE\r\nsp2.ecid = 0 \r\n' \r\nELSE\r\n''\r\nEND +\r\n'GROUP BY\r\nsp2.spid,\r\nCASE sp2.status\r\nWHEN ''sleeping'' THEN\r\nCONVERT(INT, 0)\r\nELSE\r\nsp2.request_id\r\nEND,\r\nRTRIM(sp2.lastwaittype),\r\nRTRIM(sp2.waitresource),\r\nCOALESCE(NULLIF(sp2.blocked, sp2.spid), 0)\r\n) AS sp1\r\n) AS sp0\r\nWHERE\r\n@blocker = 1\r\nOR\r\n(1=1 \r\n' +\r\n--inclusive filter\r\nCASE\r\nWHEN @filter <> '' THEN\r\nCASE @filter_type\r\nWHEN 'session' THEN\r\nCASE\r\nWHEN CONVERT(SMALLINT, @filter) <> 0 THEN\r\n'AND sp0.session_id = CONVERT(SMALLINT, @filter) \r\n'\r\nELSE\r\n''\r\nEND\r\nWHEN 'program' THEN\r\n'AND sp0.program_name LIKE @filter \r\n'\r\nWHEN 'login' THEN\r\n'AND sp0.login_name LIKE @filter \r\n'\r\nWHEN 'host' THEN\r\n'AND sp0.host_name LIKE @filter \r\n'\r\nWHEN 'database' THEN\r\n'AND DB_NAME(sp0.database_id) LIKE @filter \r\n'\r\nELSE\r\n''\r\nEND\r\nELSE\r\n''\r\nEND +\r\n--exclusive filter\r\nCASE\r\nWHEN @not_filter <> '' THEN\r\nCASE @not_filter_type\r\nWHEN 'session' THEN\r\nCASE\r\nWHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN\r\n'AND sp0.session_id <> CONVERT(SMALLINT, @not_filter) \r\n'\r\nELSE\r\n''\r\nEND\r\nWHEN 'program' THEN\r\n'AND sp0.program_name NOT LIKE @not_filter \r\n'\r\nWHEN 'login' THEN\r\n'AND sp0.login_name NOT LIKE @not_filter \r\n'\r\nWHEN 'host' THEN\r\n'AND sp0.host_name NOT LIKE @not_filter \r\n'\r\nWHEN 'database' THEN\r\n'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter \r\n'\r\nELSE\r\n''\r\nEND\r\nELSE\r\n''\r\nEND +\r\nCASE @show_own_spid\r\nWHEN 1 THEN\r\n''\r\nELSE\r\n'AND sp0.session_id <> @@spid \r\n'\r\nEND +\r\nCASE \r\nWHEN @show_system_spids = 0 THEN\r\n'AND sp0.hostprocess > '''' \r\n' \r\nELSE\r\n''\r\nEND +\r\nCASE @show_sleeping_spids\r\nWHEN 0 THEN\r\n'AND sp0.status <> ''sleeping'' \r\n'\r\nWHEN 1 THEN\r\n'AND\r\n(\r\nsp0.status <> ''sleeping''\r\nOR sp0.open_tran_count > 0\r\n)\r\n'\r\nELSE\r\n''\r\nEND +\r\n')\r\n) AS spx\r\n) AS spy\r\nWHERE\r\nspy.r = 1; \r\n' + \r\nCASE @recursion\r\nWHEN 1 THEN \r\n'IF @@ROWCOUNT > 0\r\nBEGIN;\r\nINSERT @blockers\r\n(\r\nsession_id\r\n)\r\nSELECT TOP(@i)\r\nblocked\r\nFROM @sessions\r\nWHERE\r\nNULLIF(blocked, 0) IS NOT NULL\r\n\r\nEXCEPT\r\n\r\nSELECT TOP(@i)\r\nsession_id\r\nFROM @sessions; \r\n' +\r\n\r\nCASE\r\nWHEN\r\n(\r\n@get_task_info > 0\r\nOR @find_block_leaders = 1\r\n) THEN\r\n'IF @@ROWCOUNT > 0\r\nBEGIN;\r\nSET @blocker = 1;\r\nGOTO BLOCKERS;\r\nEND; \r\n'\r\nELSE \r\n''\r\nEND +\r\n'END; \r\n'\r\nELSE \r\n''\r\nEND +\r\n'SELECT TOP(@i)\r\n@recursion AS recursion,\r\nx.session_id,\r\nx.request_id,\r\nDENSE_RANK() OVER\r\n(\r\nORDER BY\r\nx.session_id\r\n) AS session_number,\r\n' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN \r\n'x.elapsed_time '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS elapsed_time, \r\n' +\r\nCASE\r\nWHEN\r\n(\r\n@output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR \r\n@output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|'\r\n)\r\nAND @recursion = 1\r\nTHEN \r\n'x.avg_elapsed_time \/ 1000 '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS avg_elapsed_time, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[physical_io|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.physical_io '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS physical_io, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[reads|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.reads '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS reads, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.physical_reads '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS physical_reads, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[writes|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.writes '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS writes, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.tempdb_allocations '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS tempdb_allocations, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.tempdb_current '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS tempdb_current, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[CPU|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'\r\nTHEN\r\n'x.CPU '\r\nELSE\r\n'0 '\r\nEND + \r\n'AS CPU, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'\r\nAND @get_task_info = 2\r\nTHEN \r\n'x.thread_CPU_snapshot '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS thread_CPU_snapshot, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[context_switches|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.context_switches '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS context_switches, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[used_memory|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|'\r\nTHEN \r\n'x.used_memory '\r\nELSE \r\n'0 '\r\nEND + \r\n'AS used_memory, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[tasks|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nTHEN \r\n'x.tasks '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS tasks, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@output_column_list LIKE '%|[status|]%' ESCAPE '|' \r\nOR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'\r\n)\r\nAND @recursion = 1\r\nTHEN \r\n'x.status '\r\nELSE \r\n''''' '\r\nEND + \r\n'AS status, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[wait_info|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\nCASE @get_task_info\r\nWHEN 2 THEN\r\n'COALESCE(x.task_wait_info, x.sys_wait_info) '\r\nELSE\r\n'x.sys_wait_info '\r\nEND\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS wait_info, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|' \r\nOR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|' \r\n)\r\nAND @recursion = 1\r\nTHEN \r\n'x.transaction_id '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS transaction_id, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.open_tran_count '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS open_tran_count, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.sql_handle '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS sql_handle, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' \r\nOR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' \r\n)\r\nAND @recursion = 1\r\nTHEN \r\n'x.statement_start_offset '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS statement_start_offset, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' \r\nOR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' \r\n)\r\nAND @recursion = 1\r\nTHEN \r\n'x.statement_end_offset '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS statement_end_offset, \r\n' +\r\n'NULL AS sql_text, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.plan_handle '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS plan_handle, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'NULLIF(x.blocking_session_id, 0) '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS blocking_session_id, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nTHEN \r\n'x.percent_complete '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS percent_complete, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[host_name|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.host_name '\r\nELSE \r\n''''' '\r\nEND + \r\n'AS host_name, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[login_name|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.login_name '\r\nELSE \r\n''''' '\r\nEND + \r\n'AS login_name, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[database_name|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'DB_NAME(x.database_id) '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS database_name, \r\n' +\r\nCASE\r\nWHEN \r\n@output_column_list LIKE '%|[program_name|]%' ESCAPE '|' \r\nAND @recursion = 1\r\nTHEN \r\n'x.program_name '\r\nELSE \r\n''''' '\r\nEND + \r\n'AS program_name, \r\n' +\r\nCASE\r\nWHEN\r\n@output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nTHEN\r\n'(\r\nSELECT TOP(@i)\r\nx.text_size,\r\nx.language,\r\nx.date_format,\r\nx.date_first,\r\nCASE x.quoted_identifier\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS quoted_identifier,\r\nCASE x.arithabort\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS arithabort,\r\nCASE x.ansi_null_dflt_on\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS ansi_null_dflt_on,\r\nCASE x.ansi_defaults\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS ansi_defaults,\r\nCASE x.ansi_warnings\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS ansi_warnings,\r\nCASE x.ansi_padding\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS ansi_padding,\r\nCASE ansi_nulls\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS ansi_nulls,\r\nCASE x.concat_null_yields_null\r\nWHEN 0 THEN ''OFF''\r\nWHEN 1 THEN ''ON''\r\nEND AS concat_null_yields_null,\r\nCASE x.transaction_isolation_level\r\nWHEN 0 THEN ''Unspecified''\r\nWHEN 1 THEN ''ReadUncomitted''\r\nWHEN 2 THEN ''ReadCommitted''\r\nWHEN 3 THEN ''Repeatable''\r\nWHEN 4 THEN ''Serializable''\r\nWHEN 5 THEN ''Snapshot''\r\nEND AS transaction_isolation_level,\r\nx.lock_timeout,\r\nx.deadlock_priority,\r\nx.row_count,\r\nx.command_type, \r\n' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN\r\n'(\r\nSELECT TOP(1)\r\nCONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column(\"agent_info.job_id_string\"), 0) )'', ''binary(16)'')) AS job_id,\r\nagent_info.step_id,\r\n(\r\nSELECT TOP(1)\r\nNULL\r\nFOR XML\r\nPATH(''job_name''),\r\nTYPE\r\n),\r\n(\r\nSELECT TOP(1)\r\nNULL\r\nFOR XML\r\nPATH(''step_name''),\r\nTYPE\r\n)\r\nFROM\r\n(\r\nSELECT TOP(1)\r\nSUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,\r\nSUBSTRING(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\r\nWHERE\r\nx.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''\r\n) AS agent_info\r\nFOR XML\r\nPATH(''agent_job_info''),\r\nTYPE\r\n),\r\n'\r\nELSE ''\r\nEND +\r\nCASE\r\nWHEN @get_task_info = 2 THEN\r\n'CONVERT(XML, x.block_info) AS block_info, \r\n'\r\nELSE\r\n''\r\nEND +\r\n'x.host_process_id \r\nFOR XML\r\nPATH(''additional_info''),\r\nTYPE\r\n) '\r\nELSE\r\n'NULL '\r\nEND + \r\n'AS additional_info, \r\nx.start_time, \r\n' +\r\nCASE\r\nWHEN\r\n@output_column_list LIKE '%|[login_time|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nTHEN\r\n'x.login_time '\r\nELSE \r\n'NULL '\r\nEND + \r\n'AS login_time, \r\nx.last_request_start_time\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\ny.*,\r\nCASE\r\nWHEN DATEDIFF(day, y.start_time, GETDATE()) > 24 THEN\r\nDATEDIFF(second, GETDATE(), y.start_time)\r\nELSE DATEDIFF(ms, y.start_time, GETDATE())\r\nEND AS elapsed_time,\r\nCOALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,\r\nCOALESCE\r\n(\r\nCASE\r\nWHEN tempdb_info.tempdb_current < 0 THEN 0\r\nELSE tempdb_info.tempdb_current\r\nEND,\r\n0\r\n) AS tempdb_current, \r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0\r\nOR @find_block_leaders = 1\r\n) THEN\r\n'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +\r\ny.wait_type +\r\nCASE\r\nWHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN\r\nN'':'' +\r\nCOALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +\r\nN'':'' +\r\nSUBSTRING(y.resource_description, CHARINDEX(N'':'', y.resource_description) + 1, LEN(y.resource_description) - CHARINDEX(N'':'', REVERSE(y.resource_description)) - CHARINDEX(N'':'', y.resource_description)) +\r\nN''('' +\r\nCASE\r\nWHEN\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0\r\nTHEN \r\nN''PFS''\r\nWHEN\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0\r\nTHEN \r\nN''GAM''\r\nWHEN\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511233 = 0\r\nTHEN\r\nN''SGAM''\r\nWHEN\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511238 = 0 \r\nTHEN \r\nN''DCM''\r\nWHEN\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR\r\nCONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511239 = 0 \r\nTHEN \r\nN''BCM''\r\nELSE \r\nN''*''\r\nEND +\r\nN'')''\r\nWHEN y.wait_type = N''CXPACKET'' THEN\r\nN'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)\r\nWHEN y.wait_type LIKE N''LATCH[_]%'' THEN\r\nN'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''\r\nWHEN\r\ny.wait_type = N''OLEDB''\r\nAND y.resource_description LIKE N''%(SPID=%)'' THEN\r\nN''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +\r\nN'':'' + 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)) + '']''\r\nELSE\r\nN''''\r\nEND COLLATE Latin1_General_Bin2 AS sys_wait_info, \r\n'\r\nELSE\r\n''\r\nEND +\r\nCASE\r\nWHEN @get_task_info = 2 THEN\r\n'tasks.physical_io,\r\ntasks.context_switches,\r\ntasks.tasks,\r\ntasks.block_info,\r\ntasks.wait_info AS task_wait_info,\r\ntasks.thread_CPU_snapshot,\r\n'\r\nELSE\r\n'' \r\nEND +\r\nCASE \r\nWHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN\r\n'CONVERT(INT, NULL) '\r\nELSE \r\n'qs.total_elapsed_time \/ qs.execution_count '\r\nEND + \r\n'AS avg_elapsed_time \r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nsp.session_id,\r\nsp.request_id,\r\nCOALESCE(r.logical_reads, s.logical_reads) AS reads,\r\nCOALESCE(r.reads, s.reads) AS physical_reads,\r\nCOALESCE(r.writes, s.writes) AS writes,\r\nCOALESCE(r.CPU_time, s.CPU_time) AS CPU,\r\nsp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,\r\nLOWER(sp.status) AS status,\r\nCOALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,\r\nCOALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,\r\nCOALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,\r\n' +\r\nCASE\r\nWHEN \r\n(\r\n@get_task_info <> 0\r\nOR @find_block_leaders = 1 \r\n) THEN\r\n'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,\r\nsp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,\r\nsp.wait_time AS wait_duration_ms, \r\n'\r\nELSE\r\n''\r\nEND +\r\n'NULLIF(sp.blocked, 0) AS blocking_session_id,\r\nr.plan_handle,\r\nNULLIF(r.percent_complete, 0) AS percent_complete,\r\nsp.host_name,\r\nsp.login_name,\r\nsp.program_name,\r\ns.host_process_id,\r\nCOALESCE(r.text_size, s.text_size) AS text_size,\r\nCOALESCE(r.language, s.language) AS language,\r\nCOALESCE(r.date_format, s.date_format) AS date_format,\r\nCOALESCE(r.date_first, s.date_first) AS date_first,\r\nCOALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,\r\nCOALESCE(r.arithabort, s.arithabort) AS arithabort,\r\nCOALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,\r\nCOALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,\r\nCOALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,\r\nCOALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,\r\nCOALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,\r\nCOALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,\r\nCOALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,\r\nCOALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,\r\nCOALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,\r\nCOALESCE(r.row_count, s.row_count) AS row_count,\r\nCOALESCE(r.command, sp.cmd) AS command_type,\r\nCOALESCE\r\n(\r\nCASE\r\nWHEN\r\n(\r\ns.is_user_process = 0\r\nAND r.total_elapsed_time >= 0\r\n) THEN\r\nDATEADD\r\n(\r\nms,\r\n1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())) \/ 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())),\r\nDATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())\r\n)\r\nEND,\r\nNULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),\r\n(\r\nSELECT TOP(1)\r\nDATEADD(second, -(ms_ticks \/ 1000), GETDATE())\r\nFROM sys.dm_os_sys_info\r\n)\r\n) AS start_time,\r\nsp.login_time,\r\nCASE\r\nWHEN s.is_user_process = 1 THEN\r\ns.last_request_start_time\r\nELSE\r\nCOALESCE\r\n(\r\nDATEADD\r\n(\r\nms,\r\n1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())) \/ 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())),\r\nDATEADD(second, -(r.total_elapsed_time \/ 1000), GETDATE())\r\n),\r\ns.last_request_start_time\r\n)\r\nEND AS last_request_start_time,\r\nr.transaction_id,\r\nsp.database_id,\r\nsp.open_tran_count\r\nFROM @sessions AS sp\r\nLEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON\r\ns.session_id = sp.session_id\r\nAND s.login_time = sp.login_time\r\nLEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON\r\nsp.status <> ''sleeping''\r\nAND r.session_id = sp.session_id\r\nAND r.request_id = sp.request_id\r\nAND\r\n(\r\n(\r\ns.is_user_process = 0\r\nAND sp.is_user_process = 0\r\n)\r\nOR\r\n(\r\nr.start_time = s.last_request_start_time\r\nAND s.last_request_end_time = sp.last_request_end_time\r\n)\r\n)\r\n) AS y\r\n' + \r\nCASE \r\nWHEN @get_task_info = 2 THEN\r\nCONVERT(VARCHAR(MAX), '') +\r\n'LEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\ntask_nodes.task_node.value(''(session_id\/text())[1]'', ''SMALLINT'') AS session_id,\r\ntask_nodes.task_node.value(''(request_id\/text())[1]'', ''INT'') AS request_id,\r\ntask_nodes.task_node.value(''(physical_io\/text())[1]'', ''BIGINT'') AS physical_io,\r\ntask_nodes.task_node.value(''(context_switches\/text())[1]'', ''BIGINT'') AS context_switches,\r\ntask_nodes.task_node.value(''(tasks\/text())[1]'', ''INT'') AS tasks,\r\ntask_nodes.task_node.value(''(block_info\/text())[1]'', ''NVARCHAR(4000)'') AS block_info,\r\ntask_nodes.task_node.value(''(waits\/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,\r\ntask_nodes.task_node.value(''(thread_CPU_snapshot\/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nCONVERT\r\n(\r\nXML,\r\nREPLACE\r\n(\r\nCONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,\r\nN''<\/waits><\/tasks><tasks><waits>'',\r\nN'', ''\r\n)\r\n) AS task_xml\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.session_id\r\nELSE\r\nNULL\r\nEND AS [session_id],\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.request_id\r\nELSE\r\nNULL\r\nEND AS [request_id], \r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.physical_io\r\nELSE\r\nNULL\r\nEND AS [physical_io],\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.context_switches\r\nELSE\r\nNULL\r\nEND AS [context_switches],\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.thread_CPU_snapshot\r\nELSE\r\nNULL\r\nEND AS [thread_CPU_snapshot],\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.tasks\r\nELSE\r\nNULL\r\nEND AS [tasks],\r\nCASE waits.r\r\nWHEN 1 THEN\r\nwaits.block_info\r\nELSE\r\nNULL\r\nEND AS [block_info],\r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT\r\n(\r\nNVARCHAR(MAX),\r\nN''('' +\r\nCONVERT(NVARCHAR, num_waits) + N''x: '' +\r\nCASE num_waits\r\nWHEN 1 THEN\r\nCONVERT(NVARCHAR, min_wait_time) + N''ms''\r\nWHEN 2 THEN\r\nCASE\r\nWHEN min_wait_time <> max_wait_time THEN\r\nCONVERT(NVARCHAR, min_wait_time) + N''\/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''\r\nELSE\r\nCONVERT(NVARCHAR, max_wait_time) + N''ms''\r\nEND\r\nELSE\r\nCASE\r\nWHEN min_wait_time <> max_wait_time THEN\r\nCONVERT(NVARCHAR, min_wait_time) + N''\/'' + CONVERT(NVARCHAR, avg_wait_time) + N''\/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''\r\nELSE \r\nCONVERT(NVARCHAR, max_wait_time) + N''ms''\r\nEND\r\nEND +\r\nN'')'' + wait_type COLLATE Latin1_General_Bin2\r\n),\r\nNCHAR(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''?''),\r\nNCHAR(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''?''),\r\nNCHAR(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''?''),\r\nNCHAR(0),\r\nN''''\r\n) AS [waits]\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nw1.*,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\nw1.session_id,\r\nw1.request_id\r\nORDER BY\r\nw1.block_info DESC,\r\nw1.num_waits DESC,\r\nw1.wait_type\r\n) AS r\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\ntask_info.session_id,\r\ntask_info.request_id,\r\ntask_info.physical_io,\r\ntask_info.context_switches,\r\ntask_info.thread_CPU_snapshot,\r\ntask_info.num_tasks AS tasks,\r\nCASE\r\nWHEN task_info.runnable_time IS NOT NULL THEN\r\n''RUNNABLE''\r\nELSE\r\nwt2.wait_type\r\nEND AS wait_type,\r\nNULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,\r\nMIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,\r\nAVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,\r\nMAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,\r\nMAX(wt2.block_info) AS block_info\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nt.session_id,\r\nt.request_id,\r\nSUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,\r\nSUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches, \r\n' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'\r\nTHEN\r\n'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '\r\nELSE\r\n'CONVERT(BIGINT, NULL) '\r\nEND + \r\n' AS thread_CPU_snapshot, \r\nCOUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,\r\nt.task_address,\r\nt.task_state,\r\nCASE\r\nWHEN\r\nt.task_state = ''RUNNABLE''\r\nAND w.runnable_time > 0 THEN\r\nw.runnable_time\r\nELSE\r\nNULL\r\nEND AS runnable_time\r\nFROM sys.dm_os_tasks AS t\r\nCROSS APPLY\r\n(\r\nSELECT TOP(1)\r\nsp2.session_id\r\nFROM @sessions AS sp2\r\nWHERE\r\nsp2.session_id = t.session_id\r\nAND sp2.request_id = t.request_id\r\nAND sp2.status <> ''sleeping''\r\n) AS sp20\r\nLEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\n(\r\nSELECT TOP(@i)\r\nms_ticks\r\nFROM sys.dm_os_sys_info\r\n) -\r\nw0.wait_resumed_ms_ticks AS runnable_time,\r\nw0.worker_address,\r\nw0.thread_address,\r\nw0.task_bound_ms_ticks\r\nFROM sys.dm_os_workers AS w0\r\nWHERE\r\nw0.state = ''RUNNABLE''\r\nOR @first_collection_ms_ticks >= w0.task_bound_ms_ticks\r\n) AS w ON\r\nw.worker_address = t.worker_address \r\n' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'\r\nTHEN\r\n'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON\r\ntr.thread_address = w.thread_address\r\nAND @first_collection_ms_ticks >= w.task_bound_ms_ticks\r\n'\r\nELSE\r\n''\r\nEND +\r\n') AS task_info\r\nLEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\nwt1.wait_type,\r\nwt1.waiting_task_address,\r\nMAX(wt1.wait_duration_ms) AS wait_duration_ms,\r\nMAX(wt1.block_info) AS block_info\r\nFROM\r\n(\r\nSELECT DISTINCT TOP(@i)\r\nwt.wait_type +\r\nCASE\r\nWHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN\r\n'':'' +\r\nCOALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +\r\nN'':'' +\r\nSUBSTRING(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) + 1, LEN(wt.resource_description) - CHARINDEX(N'':'', REVERSE(wt.resource_description)) - CHARINDEX(N'':'', wt.resource_description)) +\r\nN''('' +\r\nCASE\r\nWHEN\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0\r\nTHEN \r\nN''PFS''\r\nWHEN\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0 \r\nTHEN \r\nN''GAM''\r\nWHEN\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511233 = 0 \r\nTHEN \r\nN''SGAM''\r\nWHEN\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511238 = 0 \r\nTHEN \r\nN''DCM''\r\nWHEN\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR\r\nCONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511239 = 0\r\nTHEN \r\nN''BCM''\r\nELSE\r\nN''*''\r\nEND +\r\nN'')''\r\nWHEN wt.wait_type = N''CXPACKET'' THEN\r\nN'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)\r\nWHEN wt.wait_type LIKE N''LATCH[_]%'' THEN\r\nN'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''\r\nELSE \r\nN''''\r\nEND COLLATE Latin1_General_Bin2 AS wait_type,\r\nCASE\r\nWHEN\r\n(\r\nwt.blocking_session_id IS NOT NULL\r\nAND wt.wait_type LIKE N''LCK[_]%''\r\n) THEN\r\n(\r\nSELECT TOP(@i)\r\nx.lock_type,\r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nDB_NAME\r\n(\r\nCONVERT\r\n(\r\nINT,\r\nSUBSTRING(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)\r\n)\r\n),\r\nNCHAR(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''?''),\r\nNCHAR(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''?''),\r\nNCHAR(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''?''),\r\nNCHAR(0),\r\nN''''\r\n) AS database_name,\r\nCASE x.lock_type\r\nWHEN N''objectlock'' THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS object_id,\r\nCASE x.lock_type\r\nWHEN N''filelock'' THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS file_id,\r\nCASE\r\nWHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN\r\nSUBSTRING(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)\r\nWHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS hobt_id,\r\nCASE x.lock_type\r\nWHEN N''applicationlock'' THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS applock_hash,\r\nCASE x.lock_type\r\nWHEN N''metadatalock'' THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS metadata_resource,\r\nCASE x.lock_type\r\nWHEN N''metadatalock'' THEN\r\nSUBSTRING(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)\r\nELSE\r\nNULL\r\nEND AS metadata_class_id\r\nFROM\r\n(\r\nSELECT TOP(1)\r\nLEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type\r\n) AS x\r\nFOR XML\r\nPATH('''')\r\n)\r\nELSE NULL\r\nEND AS block_info,\r\nwt.wait_duration_ms,\r\nwt.waiting_task_address\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nwt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,\r\nwt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,\r\nwt0.wait_duration_ms,\r\nwt0.waiting_task_address,\r\nCASE\r\nWHEN wt0.blocking_session_id = p.blocked THEN\r\nwt0.blocking_session_id\r\nELSE\r\nNULL\r\nEND AS blocking_session_id\r\nFROM sys.dm_os_waiting_tasks AS wt0\r\nCROSS APPLY\r\n(\r\nSELECT TOP(1)\r\ns0.blocked\r\nFROM @sessions AS s0\r\nWHERE\r\ns0.session_id = wt0.session_id\r\nAND COALESCE(s0.wait_type, N'''') <> N''OLEDB''\r\nAND wt0.wait_type <> N''OLEDB''\r\n) AS p\r\n) AS wt\r\n) AS wt1\r\nGROUP BY\r\nwt1.wait_type,\r\nwt1.waiting_task_address\r\n) AS wt2 ON\r\nwt2.waiting_task_address = task_info.task_address\r\nAND wt2.wait_duration_ms > 0\r\nAND task_info.runnable_time IS NULL\r\nGROUP BY\r\ntask_info.session_id,\r\ntask_info.request_id,\r\ntask_info.physical_io,\r\ntask_info.context_switches,\r\ntask_info.thread_CPU_snapshot,\r\ntask_info.num_tasks,\r\nCASE\r\nWHEN task_info.runnable_time IS NOT NULL THEN\r\n''RUNNABLE''\r\nELSE\r\nwt2.wait_type\r\nEND\r\n) AS w1\r\n) AS waits\r\nORDER BY\r\nwaits.session_id,\r\nwaits.request_id,\r\nwaits.r\r\nFOR XML\r\nPATH(N''tasks''),\r\nTYPE\r\n) AS tasks_raw (task_xml_raw)\r\n) AS tasks_final\r\nCROSS APPLY tasks_final.task_xml.nodes(N''\/tasks'') AS task_nodes (task_node)\r\nWHERE\r\ntask_nodes.task_node.exist(N''session_id'') = 1\r\n) AS tasks ON\r\ntasks.session_id = y.session_id\r\nAND tasks.request_id = y.request_id \r\n'\r\nELSE\r\n''\r\nEND +\r\n'LEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\nt_info.session_id,\r\nCOALESCE(t_info.request_id, -1) AS request_id,\r\nSUM(t_info.tempdb_allocations) AS tempdb_allocations,\r\nSUM(t_info.tempdb_current) AS tempdb_current\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\ntsu.session_id,\r\ntsu.request_id,\r\ntsu.user_objects_alloc_page_count +\r\ntsu.internal_objects_alloc_page_count AS tempdb_allocations,\r\ntsu.user_objects_alloc_page_count +\r\ntsu.internal_objects_alloc_page_count -\r\ntsu.user_objects_dealloc_page_count -\r\ntsu.internal_objects_dealloc_page_count AS tempdb_current\r\nFROM sys.dm_db_task_space_usage AS tsu\r\nCROSS APPLY\r\n(\r\nSELECT TOP(1)\r\ns0.session_id\r\nFROM @sessions AS s0\r\nWHERE\r\ns0.session_id = tsu.session_id\r\n) AS p\r\n\r\nUNION ALL\r\n\r\nSELECT TOP(@i)\r\nssu.session_id,\r\nNULL AS request_id,\r\nssu.user_objects_alloc_page_count +\r\nssu.internal_objects_alloc_page_count AS tempdb_allocations,\r\nssu.user_objects_alloc_page_count +\r\nssu.internal_objects_alloc_page_count -\r\nssu.user_objects_dealloc_page_count -\r\nssu.internal_objects_dealloc_page_count AS tempdb_current\r\nFROM sys.dm_db_session_space_usage AS ssu\r\nCROSS APPLY\r\n(\r\nSELECT TOP(1)\r\ns0.session_id\r\nFROM @sessions AS s0\r\nWHERE\r\ns0.session_id = ssu.session_id\r\n) AS p\r\n) AS t_info\r\nGROUP BY\r\nt_info.session_id,\r\nCOALESCE(t_info.request_id, -1)\r\n) AS tempdb_info ON\r\ntempdb_info.session_id = y.session_id\r\nAND tempdb_info.request_id =\r\nCASE\r\nWHEN y.status = N''sleeping'' THEN\r\n-1\r\nELSE\r\ny.request_id\r\nEND\r\n' +\r\nCASE \r\nWHEN \r\nNOT \r\n(\r\n@get_avg_time = 1 \r\nAND @recursion = 1\r\n) THEN \r\n''\r\nELSE\r\n'LEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\n*\r\nFROM sys.dm_exec_query_stats\r\n) AS qs ON\r\nqs.sql_handle = y.sql_handle\r\nAND qs.plan_handle = y.plan_handle\r\nAND qs.statement_start_offset = y.statement_start_offset\r\nAND qs.statement_end_offset = y.statement_end_offset\r\n'\r\nEND + \r\n') AS x\r\nOPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';\r\n\r\nSET @sql_n = CONVERT(NVARCHAR(MAX), @sql);\r\n\r\nSET @last_collection_start = GETDATE();\r\n\r\nIF @recursion = -1\r\nBEGIN;\r\nSELECT\r\n@first_collection_ms_ticks = ms_ticks\r\nFROM sys.dm_os_sys_info;\r\nEND;\r\n\r\nINSERT #sessions\r\n(\r\nrecursion,\r\nsession_id,\r\nrequest_id,\r\nsession_number,\r\nelapsed_time,\r\navg_elapsed_time,\r\nphysical_io,\r\nreads,\r\nphysical_reads,\r\nwrites,\r\ntempdb_allocations,\r\ntempdb_current,\r\nCPU,\r\nthread_CPU_snapshot,\r\ncontext_switches,\r\nused_memory,\r\ntasks,\r\nstatus,\r\nwait_info,\r\ntransaction_id,\r\nopen_tran_count,\r\nsql_handle,\r\nstatement_start_offset,\r\nstatement_end_offset, \r\nsql_text,\r\nplan_handle,\r\nblocking_session_id,\r\npercent_complete,\r\nhost_name,\r\nlogin_name,\r\ndatabase_name,\r\nprogram_name,\r\nadditional_info,\r\nstart_time,\r\nlogin_time,\r\nlast_request_start_time\r\n)\r\nEXEC sp_executesql \r\n@sql_n,\r\nN'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',\r\n@recursion, @filter, @not_filter, @first_collection_ms_ticks;\r\n\r\n--Collect transaction information?\r\nIF\r\n@recursion = 1\r\nAND\r\n(\r\n@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'\r\nOR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|' \r\n)\r\nBEGIN; \r\nDECLARE @i INT;\r\nSET @i = 2147483647;\r\n\r\nUPDATE s\r\nSET\r\ntran_start_time =\r\nCONVERT\r\n(\r\nDATETIME,\r\nLEFT\r\n(\r\nx.trans_info,\r\nNULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)\r\n),\r\n121\r\n),\r\ntran_log_writes =\r\nRIGHT\r\n(\r\nx.trans_info,\r\nLEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)\r\n)\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\ntrans_nodes.trans_node.value('(session_id\/text())[1]', 'SMALLINT') AS session_id,\r\nCOALESCE(trans_nodes.trans_node.value('(request_id\/text())[1]', 'INT'), 0) AS request_id,\r\ntrans_nodes.trans_node.value('(trans_info\/text())[1]', 'NVARCHAR(4000)') AS trans_info \r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nCONVERT\r\n(\r\nXML,\r\nREPLACE\r\n(\r\nCONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2, \r\nN'<\/trans_info><\/trans><trans><trans_info>', N''\r\n)\r\n)\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nCASE u_trans.r\r\nWHEN 1 THEN u_trans.session_id\r\nELSE NULL\r\nEND AS [session_id],\r\nCASE u_trans.r\r\nWHEN 1 THEN u_trans.request_id\r\nELSE NULL\r\nEND AS [request_id],\r\nCONVERT\r\n(\r\nNVARCHAR(MAX),\r\nCASE\r\nWHEN u_trans.database_id IS NOT NULL THEN\r\nCASE u_trans.r\r\nWHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'')\r\nELSE N''\r\nEND + \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN'?'\r\n) +\r\nN': ' +\r\nCONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +\r\nN','\r\nELSE\r\nN'N\/A,'\r\nEND COLLATE Latin1_General_Bin2\r\n) AS [trans_info]\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\ntrans.*,\r\nROW_NUMBER() OVER\r\n(\r\nPARTITION BY\r\ntrans.session_id,\r\ntrans.request_id\r\nORDER BY\r\ntrans.transaction_start_time DESC\r\n) AS r\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\nsession_tran_map.session_id,\r\nsession_tran_map.request_id,\r\ns_tran.database_id,\r\nCOALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,\r\nCOALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) \/ 1024 AS log_kb_used,\r\nMIN(s_tran.database_transaction_begin_time) AS transaction_start_time\r\nFROM\r\n(\r\nSELECT TOP(@i)\r\n*\r\nFROM sys.dm_tran_active_transactions\r\nWHERE\r\ntransaction_begin_time <= @last_collection_start\r\n) AS a_tran\r\nINNER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\n*\r\nFROM sys.dm_tran_database_transactions\r\nWHERE\r\ndatabase_id < 32767\r\n) AS s_tran ON\r\ns_tran.transaction_id = a_tran.transaction_id\r\nLEFT OUTER HASH JOIN\r\n(\r\nSELECT TOP(@i)\r\n*\r\nFROM sys.dm_tran_session_transactions\r\n) AS tst ON\r\ns_tran.transaction_id = tst.transaction_id\r\nCROSS APPLY\r\n(\r\nSELECT TOP(1)\r\ns3.session_id,\r\ns3.request_id\r\nFROM\r\n(\r\nSELECT TOP(1)\r\ns1.session_id,\r\ns1.request_id\r\nFROM #sessions AS s1\r\nWHERE\r\ns1.transaction_id = s_tran.transaction_id\r\nAND s1.recursion = 1\r\n\r\nUNION ALL\r\n\r\nSELECT TOP(1)\r\ns2.session_id,\r\ns2.request_id\r\nFROM #sessions AS s2\r\nWHERE\r\ns2.session_id = tst.session_id\r\nAND s2.recursion = 1\r\n) AS s3\r\nORDER BY\r\ns3.request_id\r\n) AS session_tran_map\r\nGROUP BY\r\nsession_tran_map.session_id,\r\nsession_tran_map.request_id,\r\ns_tran.database_id\r\n) AS trans\r\n) AS u_trans\r\nFOR XML\r\nPATH('trans'),\r\nTYPE\r\n) AS trans_raw (trans_xml_raw)\r\n) AS trans_final (trans_xml)\r\nCROSS APPLY trans_final.trans_xml.nodes('\/trans') AS trans_nodes (trans_node)\r\n) AS x\r\nINNER HASH JOIN #sessions AS s ON\r\ns.session_id = x.session_id\r\nAND s.request_id = x.request_id\r\nOPTION (OPTIMIZE FOR (@i = 1));\r\nEND;\r\n\r\n--Variables for text and plan collection\r\nDECLARE \r\n@session_id SMALLINT,\r\n@request_id INT,\r\n@sql_handle VARBINARY(64),\r\n@plan_handle VARBINARY(64),\r\n@statement_start_offset INT,\r\n@statement_end_offset INT,\r\n@start_time DATETIME,\r\n@database_name sysname;\r\n\r\nIF \r\n@recursion = 1\r\nAND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'\r\nBEGIN;\r\nDECLARE sql_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR \r\nSELECT \r\nsession_id,\r\nrequest_id,\r\nsql_handle,\r\nstatement_start_offset,\r\nstatement_end_offset\r\nFROM #sessions\r\nWHERE\r\nrecursion = 1\r\nAND sql_handle IS NOT NULL\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nOPEN sql_cursor;\r\n\r\nFETCH NEXT FROM sql_cursor\r\nINTO \r\n@session_id,\r\n@request_id,\r\n@sql_handle,\r\n@statement_start_offset,\r\n@statement_end_offset;\r\n\r\n--Wait up to 5 ms for the SQL text, then give up\r\nSET LOCK_TIMEOUT 5;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\nUPDATE s\r\nSET\r\ns.sql_text =\r\n(\r\nSELECT\r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nN'--' + NCHAR(13) + NCHAR(10) +\r\nCASE \r\nWHEN @get_full_inner_text = 1 THEN est.text\r\nWHEN LEN(est.text) < (@statement_end_offset \/ 2) + 1 THEN est.text\r\nWHEN SUBSTRING(est.text, (@statement_start_offset\/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text\r\nELSE\r\nCASE\r\nWHEN @statement_start_offset > 0 THEN\r\nSUBSTRING\r\n(\r\nest.text,\r\n((@statement_start_offset\/2) + 1),\r\n(\r\nCASE\r\nWHEN @statement_end_offset = -1 THEN 2147483647\r\nELSE ((@statement_end_offset - @statement_start_offset)\/2) + 1\r\nEND\r\n)\r\n)\r\nELSE RTRIM(LTRIM(est.text))\r\nEND\r\nEND +\r\nNCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN''\r\n) AS [processing-instruction(query)]\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n),\r\ns.statement_start_offset = \r\nCASE \r\nWHEN LEN(est.text) < (@statement_end_offset \/ 2) + 1 THEN 0\r\nWHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset\/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0\r\nELSE @statement_start_offset\r\nEND,\r\ns.statement_end_offset = \r\nCASE \r\nWHEN LEN(est.text) < (@statement_end_offset \/ 2) + 1 THEN -1\r\nWHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset\/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1\r\nELSE @statement_end_offset\r\nEND\r\nFROM \r\n#sessions AS s,\r\n(\r\nSELECT TOP(1)\r\ntext\r\nFROM\r\n(\r\nSELECT \r\ntext, \r\n0 AS row_num\r\nFROM sys.dm_exec_sql_text(@sql_handle)\r\n\r\nUNION ALL\r\n\r\nSELECT \r\nNULL,\r\n1 AS row_num\r\n) AS est0\r\nORDER BY\r\nrow_num\r\n) AS est\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.request_id = @request_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND TRY\r\nBEGIN CATCH;\r\nUPDATE s\r\nSET\r\ns.sql_text = \r\nCASE ERROR_NUMBER() \r\nWHEN 1222 THEN '<timeout_exceeded \/>'\r\nELSE '<error message=\"' + ERROR_MESSAGE() + '\" \/>'\r\nEND\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.request_id = @request_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM sql_cursor\r\nINTO\r\n@session_id,\r\n@request_id,\r\n@sql_handle,\r\n@statement_start_offset,\r\n@statement_end_offset;\r\nEND;\r\n\r\n--Return this to the default\r\nSET LOCK_TIMEOUT -1;\r\n\r\nCLOSE sql_cursor;\r\nDEALLOCATE sql_cursor;\r\nEND;\r\n\r\nIF \r\n@get_outer_command = 1 \r\nAND @recursion = 1\r\nAND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'\r\nBEGIN;\r\nDECLARE @buffer_results TABLE\r\n(\r\nEventType VARCHAR(30),\r\nParameters INT,\r\nEventInfo NVARCHAR(4000),\r\nstart_time DATETIME,\r\nsession_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY\r\n);\r\n\r\nDECLARE buffer_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR \r\nSELECT \r\nsession_id,\r\nMAX(start_time) AS start_time\r\nFROM #sessions\r\nWHERE\r\nrecursion = 1\r\nGROUP BY\r\nsession_id\r\nORDER BY\r\nsession_id\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nOPEN buffer_cursor;\r\n\r\nFETCH NEXT FROM buffer_cursor\r\nINTO \r\n@session_id,\r\n@start_time;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\n--In SQL Server 2008, DBCC INPUTBUFFER will throw \r\n--an exception if the session no longer exists\r\nINSERT @buffer_results\r\n(\r\nEventType,\r\nParameters,\r\nEventInfo\r\n)\r\nEXEC sp_executesql\r\nN'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',\r\nN'@session_id SMALLINT',\r\n@session_id;\r\n\r\nUPDATE br\r\nSET\r\nbr.start_time = @start_time\r\nFROM @buffer_results AS br\r\nWHERE\r\nbr.session_number = \r\n(\r\nSELECT MAX(br2.session_number)\r\nFROM @buffer_results br2\r\n);\r\nEND TRY\r\nBEGIN CATCH\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM buffer_cursor\r\nINTO \r\n@session_id,\r\n@start_time;\r\nEND;\r\n\r\nUPDATE s\r\nSET\r\nsql_command = \r\n(\r\nSELECT \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT\r\n(\r\nNVARCHAR(MAX),\r\nN'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2\r\n),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN''\r\n) AS [processing-instruction(query)]\r\nFROM @buffer_results AS br\r\nWHERE \r\nbr.session_number = s.session_number\r\nAND br.start_time = s.start_time\r\nAND \r\n(\r\n(\r\ns.start_time = s.last_request_start_time\r\nAND EXISTS\r\n(\r\nSELECT *\r\nFROM sys.dm_exec_requests r2\r\nWHERE\r\nr2.session_id = s.session_id\r\nAND r2.request_id = s.request_id\r\nAND r2.start_time = s.start_time\r\n)\r\n)\r\nOR \r\n(\r\ns.request_id = 0\r\nAND EXISTS\r\n(\r\nSELECT *\r\nFROM sys.dm_exec_sessions s2\r\nWHERE\r\ns2.session_id = s.session_id\r\nAND s2.last_request_start_time = s.last_request_start_time\r\n)\r\n)\r\n)\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n)\r\nFROM #sessions AS s\r\nWHERE\r\nrecursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nCLOSE buffer_cursor;\r\nDEALLOCATE buffer_cursor;\r\nEND;\r\n\r\nIF \r\n@get_plans >= 1 \r\nAND @recursion = 1\r\nAND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'\r\nBEGIN;\r\nDECLARE plan_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR \r\nSELECT\r\nsession_id,\r\nrequest_id,\r\nplan_handle,\r\nstatement_start_offset,\r\nstatement_end_offset\r\nFROM #sessions\r\nWHERE\r\nrecursion = 1\r\nAND plan_handle IS NOT NULL\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nOPEN plan_cursor;\r\n\r\nFETCH NEXT FROM plan_cursor\r\nINTO \r\n@session_id,\r\n@request_id,\r\n@plan_handle,\r\n@statement_start_offset,\r\n@statement_end_offset;\r\n\r\n--Wait up to 5 ms for a query plan, then give up\r\nSET LOCK_TIMEOUT 5;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\nUPDATE s\r\nSET\r\ns.query_plan =\r\n(\r\nSELECT\r\nCONVERT(xml, query_plan)\r\nFROM sys.dm_exec_text_query_plan\r\n(\r\n@plan_handle, \r\nCASE @get_plans\r\nWHEN 1 THEN\r\n@statement_start_offset\r\nELSE\r\n0\r\nEND, \r\nCASE @get_plans\r\nWHEN 1 THEN\r\n@statement_end_offset\r\nELSE\r\n-1\r\nEND\r\n)\r\n)\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.request_id = @request_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND TRY\r\nBEGIN CATCH;\r\nIF ERROR_NUMBER() = 6335\r\nBEGIN;\r\nUPDATE s\r\nSET\r\ns.query_plan =\r\n(\r\nSELECT\r\nN'--' + NCHAR(13) + NCHAR(10) + \r\nN'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) + \r\nN'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +\r\nN'--' + NCHAR(13) + NCHAR(10) +\r\nREPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') + \r\nNCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]\r\nFROM sys.dm_exec_text_query_plan\r\n(\r\n@plan_handle, \r\nCASE @get_plans\r\nWHEN 1 THEN\r\n@statement_start_offset\r\nELSE\r\n0\r\nEND, \r\nCASE @get_plans\r\nWHEN 1 THEN\r\n@statement_end_offset\r\nELSE\r\n-1\r\nEND\r\n) AS qp\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n)\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.request_id = @request_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND;\r\nELSE\r\nBEGIN;\r\nUPDATE s\r\nSET\r\ns.query_plan = \r\nCASE ERROR_NUMBER() \r\nWHEN 1222 THEN '<timeout_exceeded \/>'\r\nELSE '<error message=\"' + ERROR_MESSAGE() + '\" \/>'\r\nEND\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.request_id = @request_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND;\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM plan_cursor\r\nINTO\r\n@session_id,\r\n@request_id,\r\n@plan_handle,\r\n@statement_start_offset,\r\n@statement_end_offset;\r\nEND;\r\n\r\n--Return this to the default\r\nSET LOCK_TIMEOUT -1;\r\n\r\nCLOSE plan_cursor;\r\nDEALLOCATE plan_cursor;\r\nEND;\r\n\r\nIF \r\n@get_locks = 1 \r\nAND @recursion = 1\r\nAND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'\r\nBEGIN;\r\nDECLARE locks_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR \r\nSELECT DISTINCT\r\ndatabase_name\r\nFROM #locks\r\nWHERE\r\nEXISTS\r\n(\r\nSELECT *\r\nFROM #sessions AS s\r\nWHERE\r\ns.session_id = #locks.session_id\r\nAND recursion = 1\r\n)\r\nAND database_name <> '(null)'\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nOPEN locks_cursor;\r\n\r\nFETCH NEXT FROM locks_cursor\r\nINTO \r\n@database_name;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\nSET @sql_n = CONVERT(NVARCHAR(MAX), '') +\r\n'UPDATE l ' +\r\n'SET ' +\r\n'object_name = ' +\r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'o.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n'), ' +\r\n'index_name = ' +\r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'i.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n'), ' +\r\n'schema_name = ' +\r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n's.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n'), ' +\r\n'principal_name = ' + \r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'dp.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n') ' +\r\n'FROM #locks AS l ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +\r\n'au.allocation_unit_id = l.allocation_unit_id ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +\r\n'p.hobt_id = ' +\r\n'COALESCE ' +\r\n'( ' +\r\n'l.hobt_id, ' +\r\n'CASE ' +\r\n'WHEN au.type IN (1, 3) THEN au.container_id ' +\r\n'ELSE NULL ' +\r\n'END ' +\r\n') ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +\r\n'l.hobt_id IS NULL ' +\r\n'AND au.type = 2 ' +\r\n'AND p1.partition_id = au.container_id ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +\r\n'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +\r\n'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +\r\n'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +\r\n's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +\r\n'dp.principal_id = l.principal_id ' +\r\n'WHERE ' +\r\n'l.database_name = @database_name ' +\r\n'OPTION (KEEPFIXED PLAN); ';\r\n\r\nEXEC sp_executesql\r\n@sql_n,\r\nN'@database_name sysname',\r\n@database_name;\r\nEND TRY\r\nBEGIN CATCH;\r\nUPDATE #locks\r\nSET\r\nquery_error = \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT\r\n(\r\nNVARCHAR(MAX), \r\nERROR_MESSAGE() COLLATE Latin1_General_Bin2\r\n),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN''\r\n)\r\nWHERE \r\ndatabase_name = @database_name\r\nOPTION (KEEPFIXED PLAN);\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM locks_cursor\r\nINTO\r\n@database_name;\r\nEND;\r\n\r\nCLOSE locks_cursor;\r\nDEALLOCATE locks_cursor;\r\n\r\nCREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);\r\n\r\nUPDATE s\r\nSET \r\ns.locks =\r\n(\r\nSELECT \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT\r\n(\r\nNVARCHAR(MAX), \r\nl1.database_name COLLATE Latin1_General_Bin2\r\n),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN''\r\n) AS [Database\/@name],\r\nMIN(l1.query_error) AS [Database\/@query_error],\r\n(\r\nSELECT \r\nl2.request_mode AS [Lock\/@request_mode],\r\nl2.request_status AS [Lock\/@request_status],\r\nCOUNT(*) AS [Lock\/@request_count]\r\nFROM #locks AS l2\r\nWHERE \r\nl1.session_id = l2.session_id\r\nAND l1.request_id = l2.request_id\r\nAND l2.database_name = l1.database_name\r\nAND l2.resource_type = 'DATABASE'\r\nGROUP BY\r\nl2.request_mode,\r\nl2.request_status\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n) AS [Database\/Locks],\r\n(\r\nSELECT\r\nCOALESCE(l3.object_name, '(null)') AS [Object\/@name],\r\nl3.schema_name AS [Object\/@schema_name],\r\n(\r\nSELECT\r\nl4.resource_type AS [Lock\/@resource_type],\r\nl4.page_type AS [Lock\/@page_type],\r\nl4.index_name AS [Lock\/@index_name],\r\nCASE \r\nWHEN l4.object_name IS NULL THEN l4.schema_name\r\nELSE NULL\r\nEND AS [Lock\/@schema_name],\r\nl4.principal_name AS [Lock\/@principal_name],\r\nl4.resource_description AS [Lock\/@resource_description],\r\nl4.request_mode AS [Lock\/@request_mode],\r\nl4.request_status AS [Lock\/@request_status],\r\nSUM(l4.request_count) AS [Lock\/@request_count]\r\nFROM #locks AS l4\r\nWHERE \r\nl4.session_id = l3.session_id\r\nAND l4.request_id = l3.request_id\r\nAND l3.database_name = l4.database_name\r\nAND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')\r\nAND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')\r\nAND l4.resource_type <> 'DATABASE'\r\nGROUP BY\r\nl4.resource_type,\r\nl4.page_type,\r\nl4.index_name,\r\nCASE \r\nWHEN l4.object_name IS NULL THEN l4.schema_name\r\nELSE NULL\r\nEND,\r\nl4.principal_name,\r\nl4.resource_description,\r\nl4.request_mode,\r\nl4.request_status\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n) AS [Object\/Locks]\r\nFROM #locks AS l3\r\nWHERE \r\nl3.session_id = l1.session_id\r\nAND l3.request_id = l1.request_id\r\nAND l3.database_name = l1.database_name\r\nAND l3.resource_type <> 'DATABASE'\r\nGROUP BY \r\nl3.session_id,\r\nl3.request_id,\r\nl3.database_name,\r\nCOALESCE(l3.object_name, '(null)'),\r\nl3.schema_name\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n) AS [Database\/Objects]\r\nFROM #locks AS l1\r\nWHERE\r\nl1.session_id = s.session_id\r\nAND l1.request_id = s.request_id\r\nAND l1.start_time IN (s.start_time, s.last_request_start_time)\r\nAND s.recursion = 1\r\nGROUP BY \r\nl1.session_id,\r\nl1.request_id,\r\nl1.database_name\r\nFOR XML\r\nPATH(''),\r\nTYPE\r\n)\r\nFROM #sessions s\r\nOPTION (KEEPFIXED PLAN);\r\nEND;\r\n\r\nIF \r\n@find_block_leaders = 1\r\nAND @recursion = 1\r\nAND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'\r\nBEGIN;\r\nWITH\r\nblockers AS\r\n(\r\nSELECT\r\nsession_id,\r\nsession_id AS top_level_session_id\r\nFROM #sessions\r\nWHERE\r\nrecursion = 1\r\n\r\nUNION ALL\r\n\r\nSELECT\r\ns.session_id,\r\nb.top_level_session_id\r\nFROM blockers AS b\r\nJOIN #sessions AS s ON\r\ns.blocking_session_id = b.session_id\r\nAND s.recursion = 1\r\n)\r\nUPDATE s\r\nSET\r\ns.blocked_session_count = x.blocked_session_count\r\nFROM #sessions AS s\r\nJOIN\r\n(\r\nSELECT\r\nb.top_level_session_id AS session_id,\r\nCOUNT(*) - 1 AS blocked_session_count\r\nFROM blockers AS b\r\nGROUP BY\r\nb.top_level_session_id\r\n) x ON\r\ns.session_id = x.session_id\r\nWHERE\r\ns.recursion = 1;\r\nEND;\r\n\r\nIF\r\n@get_task_info = 2\r\nAND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nBEGIN;\r\nCREATE TABLE #blocked_requests\r\n(\r\nsession_id SMALLINT NOT NULL,\r\nrequest_id INT NOT NULL,\r\ndatabase_name sysname NOT NULL,\r\nobject_id INT,\r\nhobt_id BIGINT,\r\nschema_id INT,\r\nschema_name sysname NULL,\r\nobject_name sysname NULL,\r\nquery_error NVARCHAR(2048),\r\nPRIMARY KEY (database_name, session_id, request_id)\r\n);\r\n\r\nCREATE STATISTICS s_database_name ON #blocked_requests (database_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_object_name ON #blocked_requests (object_name)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\nCREATE STATISTICS s_query_error ON #blocked_requests (query_error)\r\nWITH SAMPLE 0 ROWS, NORECOMPUTE;\r\n\r\nINSERT #blocked_requests\r\n(\r\nsession_id,\r\nrequest_id,\r\ndatabase_name,\r\nobject_id,\r\nhobt_id,\r\nschema_id\r\n)\r\nSELECT\r\nsession_id,\r\nrequest_id,\r\ndatabase_name,\r\nobject_id,\r\nhobt_id,\r\nCONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id\r\nFROM\r\n(\r\nSELECT\r\nsession_id,\r\nrequest_id,\r\nagent_nodes.agent_node.value('(database_name\/text())[1]', 'sysname') AS database_name,\r\nagent_nodes.agent_node.value('(object_id\/text())[1]', 'int') AS object_id,\r\nagent_nodes.agent_node.value('(hobt_id\/text())[1]', 'bigint') AS hobt_id,\r\nagent_nodes.agent_node.value('(metadata_resource\/text()[.=\"SCHEMA\"]\/..\/..\/metadata_class_id\/text())[1]', 'varchar(100)') AS schema_node\r\nFROM #sessions AS s\r\nCROSS APPLY s.additional_info.nodes('\/\/block_info') AS agent_nodes (agent_node)\r\nWHERE\r\ns.recursion = 1\r\n) AS t\r\nWHERE\r\nt.database_name IS NOT NULL\r\nAND\r\n(\r\nt.object_id IS NOT NULL\r\nOR t.hobt_id IS NOT NULL\r\nOR t.schema_node IS NOT NULL\r\n);\r\n\r\nDECLARE blocks_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR\r\nSELECT DISTINCT\r\ndatabase_name\r\nFROM #blocked_requests;\r\n\r\nOPEN blocks_cursor;\r\n\r\nFETCH NEXT FROM blocks_cursor\r\nINTO \r\n@database_name;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\nSET @sql_n = \r\nCONVERT(NVARCHAR(MAX), '') +\r\n'UPDATE b ' +\r\n'SET ' +\r\n'b.schema_name = ' +\r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n's.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n'), ' +\r\n'b.object_name = ' +\r\n'REPLACE ' +\r\n'( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +\r\n'o.name COLLATE Latin1_General_Bin2, ' +\r\n'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''?''), ' +\r\n'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +\r\n'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +\r\n'NCHAR(0), ' +\r\nN''''' ' +\r\n') ' +\r\n'FROM #blocked_requests AS b ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +\r\n'p.hobt_id = b.hobt_id ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +\r\n'o.object_id = COALESCE(p.object_id, b.object_id) ' +\r\n'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +\r\n's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +\r\n'WHERE ' +\r\n'b.database_name = @database_name; ';\r\n\r\nEXEC sp_executesql\r\n@sql_n,\r\nN'@database_name sysname',\r\n@database_name;\r\nEND TRY\r\nBEGIN CATCH;\r\nUPDATE #blocked_requests\r\nSET\r\nquery_error = \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nCONVERT\r\n(\r\nNVARCHAR(MAX), \r\nERROR_MESSAGE() COLLATE Latin1_General_Bin2\r\n),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN''\r\n)\r\nWHERE\r\ndatabase_name = @database_name;\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM blocks_cursor\r\nINTO\r\n@database_name;\r\nEND;\r\n\r\nCLOSE blocks_cursor;\r\nDEALLOCATE blocks_cursor;\r\n\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert <schema_name>{sql:column(\"b.schema_name\")}<\/schema_name>\r\nas last\r\ninto (\/additional_info\/block_info)[1]\r\n')\r\nFROM #sessions AS s\r\nINNER JOIN #blocked_requests AS b ON\r\nb.session_id = s.session_id\r\nAND b.request_id = s.request_id\r\nAND s.recursion = 1\r\nWHERE\r\nb.schema_name IS NOT NULL;\r\n\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert <object_name>{sql:column(\"b.object_name\")}<\/object_name>\r\nas last\r\ninto (\/additional_info\/block_info)[1]\r\n')\r\nFROM #sessions AS s\r\nINNER JOIN #blocked_requests AS b ON\r\nb.session_id = s.session_id\r\nAND b.request_id = s.request_id\r\nAND s.recursion = 1\r\nWHERE\r\nb.object_name IS NOT NULL;\r\n\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert <query_error>{sql:column(\"b.query_error\")}<\/query_error>\r\nas last\r\ninto (\/additional_info\/block_info)[1]\r\n')\r\nFROM #sessions AS s\r\nINNER JOIN #blocked_requests AS b ON\r\nb.session_id = s.session_id\r\nAND b.request_id = s.request_id\r\nAND s.recursion = 1\r\nWHERE\r\nb.query_error IS NOT NULL;\r\nEND;\r\n\r\nIF\r\n@output_column_list LIKE '%|[program_name|]%' ESCAPE '|'\r\nAND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'\r\nAND @recursion = 1\r\nBEGIN;\r\nDECLARE @job_id UNIQUEIDENTIFIER;\r\nDECLARE @step_id INT;\r\n\r\nDECLARE agent_cursor\r\nCURSOR LOCAL FAST_FORWARD\r\nFOR \r\nSELECT\r\ns.session_id,\r\nagent_nodes.agent_node.value('(job_id\/text())[1]', 'uniqueidentifier') AS job_id,\r\nagent_nodes.agent_node.value('(step_id\/text())[1]', 'int') AS step_id\r\nFROM #sessions AS s\r\nCROSS APPLY s.additional_info.nodes('\/\/agent_job_info') AS agent_nodes (agent_node)\r\nWHERE\r\ns.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nOPEN agent_cursor;\r\n\r\nFETCH NEXT FROM agent_cursor\r\nINTO \r\n@session_id,\r\n@job_id,\r\n@step_id;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN;\r\nBEGIN TRY;\r\nDECLARE @job_name sysname;\r\nSET @job_name = NULL;\r\nDECLARE @step_name sysname;\r\nSET @step_name = NULL;\r\n\r\nSELECT\r\n@job_name = \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nj.name,\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN'?'\r\n),\r\n@step_name = \r\nREPLACE\r\n(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\nREPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(\r\ns.step_name,\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(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'?'),\r\nNCHAR(0),\r\nN'?'\r\n)\r\nFROM msdb.dbo.sysjobs AS j\r\nINNER JOIN msdb..sysjobsteps AS s ON\r\nj.job_id = s.job_id\r\nWHERE\r\nj.job_id = @job_id\r\nAND s.step_id = @step_id;\r\n\r\nIF @job_name IS NOT NULL\r\nBEGIN;\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert text{sql:variable(\"@job_name\")}\r\ninto (\/additional_info\/agent_job_info\/job_name)[1]\r\n')\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nOPTION (KEEPFIXED PLAN);\r\n\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert text{sql:variable(\"@step_name\")}\r\ninto (\/additional_info\/agent_job_info\/step_name)[1]\r\n')\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nOPTION (KEEPFIXED PLAN);\r\nEND;\r\nEND TRY\r\nBEGIN CATCH;\r\nDECLARE @msdb_error_message NVARCHAR(256);\r\nSET @msdb_error_message = ERROR_MESSAGE();\r\n\r\nUPDATE s\r\nSET\r\nadditional_info.modify\r\n('\r\ninsert <msdb_query_error>{sql:variable(\"@msdb_error_message\")}<\/msdb_query_error>\r\nas last\r\ninto (\/additional_info\/agent_job_info)[1]\r\n')\r\nFROM #sessions AS s\r\nWHERE \r\ns.session_id = @session_id\r\nAND s.recursion = 1\r\nOPTION (KEEPFIXED PLAN);\r\nEND CATCH;\r\n\r\nFETCH NEXT FROM agent_cursor\r\nINTO \r\n@session_id,\r\n@job_id,\r\n@step_id;\r\nEND;\r\n\r\nCLOSE agent_cursor;\r\nDEALLOCATE agent_cursor;\r\nEND; \r\n\r\nIF \r\n@delta_interval > 0 \r\nAND @recursion <> 1\r\nBEGIN;\r\nSET @recursion = 1;\r\n\r\nDECLARE @delay_time CHAR(12);\r\nSET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);\r\nWAITFOR DELAY @delay_time;\r\n\r\nGOTO REDO;\r\nEND;\r\nEND;\r\n\r\nSET @sql = \r\n--Outer column list\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\nCASE\r\nWHEN \r\n@destination_table <> '' \r\nAND @return_schema = 0 \r\nTHEN 'INSERT ' + @destination_table + ' '\r\nELSE ''\r\nEND +\r\n'SELECT ' +\r\n@output_column_list + ' ' +\r\nCASE @return_schema\r\nWHEN 1 THEN 'INTO #session_schema '\r\nELSE ''\r\nEND\r\n--End outer column list\r\n) + \r\n--Inner column list\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\n'FROM ' +\r\n'( ' +\r\n'SELECT ' +\r\n'session_id, ' +\r\n--[dd hh:mm:ss.mss]\r\nCASE\r\nWHEN @format_output IN (1, 2) THEN\r\n'CASE ' +\r\n'WHEN elapsed_time < 0 THEN ' +\r\n'RIGHT ' +\r\n'( ' +\r\n'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) \/ 86400), ' +\r\n'max_elapsed_length ' +\r\n') + ' +\r\n'RIGHT ' +\r\n'( ' +\r\n'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' +\r\n'9 ' +\r\n') + ' +\r\n'''.000'' ' +\r\n'ELSE ' +\r\n'RIGHT ' +\r\n'( ' +\r\n'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time \/ 86400000), ' +\r\n'max_elapsed_length ' +\r\n') + ' +\r\n'RIGHT ' +\r\n'( ' +\r\n'CONVERT(VARCHAR, DATEADD(second, elapsed_time \/ 1000, 0), 120), ' +\r\n'9 ' +\r\n') + ' +\r\n'''.'' + ' + \r\n'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' +\r\n'END AS [dd hh:mm:ss.mss], '\r\nELSE\r\n''\r\nEND +\r\n--[dd hh:mm:ss.mss (avg)] \/ avg_elapsed_time\r\nCASE \r\nWHEN @format_output IN (1, 2) THEN \r\n'RIGHT ' +\r\n'( ' +\r\n'''00'' + CONVERT(VARCHAR, avg_elapsed_time \/ 86400000), ' +\r\n'2 ' +\r\n') + ' +\r\n'RIGHT ' +\r\n'( ' +\r\n'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time \/ 1000, 0), 120), ' +\r\n'9 ' +\r\n') + ' +\r\n'''.'' + ' +\r\n'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], '\r\nELSE\r\n'avg_elapsed_time, '\r\nEND +\r\n--physical_io\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '\r\nELSE ''\r\nEND + 'physical_io, ' +\r\n--reads\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '\r\nELSE ''\r\nEND + 'reads, ' +\r\n--physical_reads\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '\r\nELSE ''\r\nEND + 'physical_reads, ' +\r\n--writes\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '\r\nELSE ''\r\nEND + 'writes, ' +\r\n--tempdb_allocations\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '\r\nELSE ''\r\nEND + 'tempdb_allocations, ' +\r\n--tempdb_current\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '\r\nELSE ''\r\nEND + 'tempdb_current, ' +\r\n--CPU\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '\r\nELSE ''\r\nEND + 'CPU, ' +\r\n--context_switches\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '\r\nELSE ''\r\nEND + 'context_switches, ' +\r\n--used_memory\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '\r\nELSE ''\r\nEND + 'used_memory, ' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN\r\n--physical_io_delta \r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND physical_io_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_io_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) ' \r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '\r\nELSE 'physical_io_delta '\r\nEND +\r\n'ELSE NULL ' +\r\n'END AS physical_io_delta, ' +\r\n--reads_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND reads_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads_delta))) OVER() - LEN(CONVERT(VARCHAR, reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '\r\nELSE 'reads_delta '\r\nEND +\r\n'ELSE NULL ' +\r\n'END AS reads_delta, ' +\r\n--physical_reads_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND physical_reads_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '\r\nELSE 'physical_reads_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS physical_reads_delta, ' +\r\n--writes_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND writes_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes_delta))) OVER() - LEN(CONVERT(VARCHAR, writes_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '\r\nELSE 'writes_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS writes_delta, ' +\r\n--tempdb_allocations_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND tempdb_allocations_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '\r\nELSE 'tempdb_allocations_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS tempdb_allocations_delta, ' +\r\n--tempdb_current_delta\r\n--this is the only one that can (legitimately) go negative \r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '\r\nELSE 'tempdb_current_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS tempdb_current_delta, ' +\r\n--CPU_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'THEN ' +\r\n'CASE ' +\r\n'WHEN ' +\r\n'thread_CPU_delta > CPU_delta ' +\r\n'AND thread_CPU_delta > 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, thread_CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '\r\nELSE 'thread_CPU_delta '\r\nEND + \r\n'WHEN CPU_delta >= 0 THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '\r\nELSE 'CPU_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END ' +\r\n'ELSE ' +\r\n'NULL ' +\r\n'END AS CPU_delta, ' +\r\n--context_switches_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND context_switches_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches_delta))) OVER() - LEN(CONVERT(VARCHAR, context_switches_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '\r\nELSE 'context_switches_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS context_switches_delta, ' +\r\n--used_memory_delta\r\n'CASE ' +\r\n'WHEN ' +\r\n'first_request_start_time = last_request_start_time ' + \r\n'AND num_events = 2 ' +\r\n'AND used_memory_delta >= 0 ' +\r\n'THEN ' +\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory_delta))) OVER() - LEN(CONVERT(VARCHAR, used_memory_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '\r\nELSE 'used_memory_delta '\r\nEND + \r\n'ELSE NULL ' +\r\n'END AS used_memory_delta, '\r\nELSE ''\r\nEND +\r\n--tasks\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tasks))) OVER() - LEN(CONVERT(VARCHAR, tasks))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) '\r\nELSE ''\r\nEND + 'tasks, ' +\r\n'status, ' +\r\n'wait_info, ' +\r\n'locks, ' +\r\n'tran_start_time, ' +\r\n'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' +\r\n--open_tran_count\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, open_tran_count))) OVER() - LEN(CONVERT(VARCHAR, open_tran_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '\r\nELSE ''\r\nEND + 'open_tran_count, ' +\r\n--sql_command\r\nCASE @format_output \r\nWHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '\r\nELSE ''\r\nEND + 'sql_command, ' +\r\n--sql_text\r\nCASE @format_output \r\nWHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '\r\nELSE ''\r\nEND + 'sql_text, ' +\r\n'query_plan, ' +\r\n'blocking_session_id, ' +\r\n--blocked_session_count\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, blocked_session_count))) OVER() - LEN(CONVERT(VARCHAR, blocked_session_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '\r\nELSE ''\r\nEND + 'blocked_session_count, ' +\r\n--percent_complete\r\nCASE @format_output\r\nWHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) OVER() - LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) + CONVERT(CHAR(22), CONVERT(MONEY, percent_complete), 2)) AS '\r\nWHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS '\r\nELSE ''\r\nEND + 'percent_complete, ' +\r\n'host_name, ' +\r\n'login_name, ' +\r\n'database_name, ' +\r\n'program_name, ' +\r\n'additional_info, ' +\r\n'start_time, ' +\r\n'login_time, ' +\r\n'CASE ' +\r\n'WHEN status = N''sleeping'' THEN NULL ' +\r\n'ELSE request_id ' +\r\n'END AS request_id, ' +\r\n'GETDATE() AS collection_time '\r\n--End inner column list\r\n) +\r\n--Derived table and INSERT specification\r\nCONVERT\r\n(\r\nVARCHAR(MAX),\r\n'FROM ' +\r\n'( ' +\r\n'SELECT TOP(2147483647) ' +\r\n'*, ' +\r\n'CASE ' +\r\n'MAX ' +\r\n'( ' +\r\n'LEN ' +\r\n'( ' +\r\n'CONVERT ' +\r\n'( ' +\r\n'VARCHAR, ' +\r\n'CASE ' +\r\n'WHEN elapsed_time < 0 THEN ' +\r\n'(-1 * elapsed_time) \/ 86400 ' +\r\n'ELSE ' +\r\n'elapsed_time \/ 86400000 ' +\r\n'END ' +\r\n') ' +\r\n') ' +\r\n') OVER () ' +\r\n'WHEN 1 THEN 2 ' +\r\n'ELSE ' +\r\n'MAX ' +\r\n'( ' +\r\n'LEN ' +\r\n'( ' +\r\n'CONVERT ' +\r\n'( ' +\r\n'VARCHAR, ' +\r\n'CASE ' +\r\n'WHEN elapsed_time < 0 THEN ' +\r\n'(-1 * elapsed_time) \/ 86400 ' +\r\n'ELSE ' +\r\n'elapsed_time \/ 86400000 ' +\r\n'END ' +\r\n') ' +\r\n') ' +\r\n') OVER () ' +\r\n'END AS max_elapsed_length, ' +\r\nCASE\r\nWHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN\r\n'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' +\r\n'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' +\r\n'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' +\r\n'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' +\r\n'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' +\r\n'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' +\r\n'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' +\r\n'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' +\r\n'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' +\r\n'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' +\r\n'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' +\r\n'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, '\r\nELSE ''\r\nEND +\r\n'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' +\r\n'FROM #sessions AS s1 ' +\r\nCASE \r\nWHEN @sort_order = '' THEN ''\r\nELSE\r\n'ORDER BY ' +\r\n@sort_order\r\nEND +\r\n') AS s ' +\r\n'WHERE ' +\r\n's.recursion = 1 ' +\r\n') x ' +\r\n'OPTION (KEEPFIXED PLAN); ' +\r\n'' +\r\nCASE @return_schema\r\nWHEN 1 THEN\r\n'SET @schema = ' +\r\n'''CREATE TABLE <table_name> ( '' + ' +\r\n'STUFF ' +\r\n'( ' +\r\n'( ' +\r\n'SELECT ' +\r\n''','' + ' +\r\n'QUOTENAME(COLUMN_NAME) + '' '' + ' +\r\n'DATA_TYPE + ' + \r\n'CASE ' +\r\n'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' +\r\n'ELSE '' '' ' +\r\n'END + ' +\r\n'CASE IS_NULLABLE ' +\r\n'WHEN ''NO'' THEN ''NOT '' ' +\r\n'ELSE '''' ' +\r\n'END + ''NULL'' AS [text()] ' +\r\n'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' +\r\n'WHERE ' +\r\n'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' +\r\n'ORDER BY ' +\r\n'ORDINAL_POSITION ' +\r\n'FOR XML ' +\r\n'PATH('''') ' +\r\n'), + ' +\r\n'1, ' +\r\n'1, ' +\r\n''''' ' +\r\n') + ' +\r\n''')''; ' \r\nELSE ''\r\nEND\r\n--End derived table and INSERT specification\r\n);\r\n\r\nSET @sql_n = CONVERT(NVARCHAR(MAX), @sql);\r\n\r\nEXEC sp_executesql\r\n@sql_n,\r\nN'@schema VARCHAR(MAX) OUTPUT',\r\n@schema OUTPUT;\r\nEND;\r\nGO<\/pre>\n <\/p>\n
sp_WhoIsActive \u00e7\u0131kt\u0131s\u0131 a\u015fa\u011f\u0131daki gibidir.<\/p>\n
![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/21.png\")
<\/p>\n
<\/p>\n
B\u00f6ylece bu yaz\u0131n\u0131n sonuna gelmi\u015f bulunmaktay\u0131m bir sonraki yaz\u0131da SQL Server Performance Troubleshooting e devam ediyor olaca\u011f\u0131m.<\/p>\n\n
<\/p>\n
Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda a\u015fa\u011f\u0131daki konularda 7×24 Uzman Dan\u0131\u015fmanlara yada E\u011fitimlere mi\u00a0<\/span>\u0130htiyac\u0131n\u0131z var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle ileti\u015fime ge\u00e7ebilirsiniz.<\/span><\/p>\n– Oracle Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle Veritaban\u0131 Bak\u0131m ve Destek<\/span>
\n– Exadata Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Exadata Bak\u0131m ve Destek<\/span>
\n– SQL Server Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– SQL Server Veritaban\u0131 Bak\u0131m ve Destek<\/span>
\n– Goldengate Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Goldengate Bak\u0131m ve Destek<\/span>
\n– Linux Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Linux Bak\u0131m ve Destek<\/span>
\n– Oracle EBS Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle EBS Bak\u0131m ve Destek<\/span>
\n– Weblogic Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Weblogic Bak\u0131m ve Destek<\/span>
\n– Oracle Veritaban\u0131 E\u011fitimleri<\/span>
\n– Oracle VM Server Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle VM Server Bak\u0131m ve Destek<\/span>
\n– Oracle EPPM Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle EPPM Bak\u0131m ve Destek<\/span>
\n– Oracle Primavera Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle Primavera Bak\u0131m ve Destek<\/span>
\n– Oracle E\u011fitimleri<\/span>
\n– SQL Server E\u011fitimleri<\/span>
\n– Goldengate E\u011fitimleri<\/span>
\n– Exadata E\u011fitimleri<\/span>
\n– Linux E\u011fitimleri<\/span>
\n– Oracle EBS E\u011fitimleri<\/span>
\n– Oracle VM Server E\u011fitimleri<\/span>
\n– Weblogic E\u011fitimleri<\/span>
\n– Oracle EPPM E\u011fitimleri<\/span>
\n– Oracle Primavera E\u011fitimleri<\/span><\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Merhaba Arkada\u015flar, Bu yaz\u0131mda sizlere \u00f6nceki yaz\u0131mda ba\u015flad\u0131\u011f\u0131m SQL Server da Performance Troubleshooting kavram\u0131n\u0131 ve nas\u0131l yap\u0131ld\u0131\u011f\u0131n\u0131n devam\u0131n\u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[19,23],"tags":[457,480,586,1028,1031,1120,1199,1266,1267,1623,1629,1644,1659,1661,1667,1859,1860,1861],"class_list":["post-5152","post","type-post","status-publish","format-standard","","category-sql-server","category-veritabani","tag-database-bakim-ve-destek","tag-dba-danismanlik","tag-exadata-egitim","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-oracle","tag-oracle-danismanlik","tag-oracle-egitim","tag-oracle-egitimi","tag-sql-server-bakim-destek","tag-sql-server-danismanlik","tag-sql-server-egitim","tag-sql-server-performance-troubleshooting","tag-sql-server-performans-tuning","tag-sql-server-sorun-cozme","tag-veritabani-bakim-ve-destek","tag-veritabani-danismanlik","tag-veritabani-egitim"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=5152"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5152\/revisions"}],"predecessor-version":[{"id":10533,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5152\/revisions\/10533"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=5152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=5152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=5152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/interrelated_systems.gif\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/18-1.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/19.png\")
<\/p>\n![\"\"](\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/20.png\")
<\/p>\n