人人都是 DBA(XII)查询信息收集脚本汇编
时间:2022-03-14 03:41
什么?有个 SQL 执行了 8 秒!
哪里出了问题?臣妾不知道啊,得找 DBA 啊。
DBA 人呢?离职了!!擦!!!
程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA"。
索引
按页编号查看数据表信息
SELECT sc.[name] AS [schema] ,o.[name] AS [table_name] ,o.type_desc ,obd.[file_id] ,obd.page_id ,obd.page_level ,obd.row_count ,obd.free_space_in_bytes ,obd.is_modified ,obd.numa_node FROM sys.dm_os_buffer_descriptors AS obd JOIN sys.allocation_units AS au ON obd.allocation_unit_id = au.allocation_unit_id JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.objects AS o ON p.[object_id] = o.[object_id] JOIN sys.schemas AS sc ON o.[schema_id] = sc.[schema_id] WHERE database_id = DB_ID() AND o.is_ms_shipped = 0 ORDER BY obd.page_id ,o.[name]
获取查询 SELECT 语句的执行次数排名
SQL Server 2012 版本
SELECT TOP (100) qs.execution_count ,qs.total_rows ,qs.last_rows ,qs.min_rows ,qs.max_rows ,qs.last_elapsed_time ,qs.min_elapsed_time ,qs.max_elapsed_time ,total_worker_time ,total_logical_reads ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, ( CASE WHEN qs.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2) AS query_text FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
SQL Server 2008 R2 版本
SELECT TOP (100) qs.execution_count ,qs.last_elapsed_time ,qs.min_elapsed_time ,qs.max_elapsed_time ,total_worker_time ,total_logical_reads ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, ( CASE WHEN qs.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2) AS query_text FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
看看哪些 Ad-hoc Query 在浪费资源
SELECT TOP (50) [text] AS [QueryText] ,cp.cacheobjtype ,cp.objtype ,cp.size_in_bytes / 1024 AS [Plan Size in KB] FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = N‘Compiled Plan‘ AND cp.objtype IN ( N‘Adhoc‘ ,N‘Prepared‘ ) AND cp.usecounts = 1 ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
查看当前处于等待状态的 Task 在等什么
SELECT dm_ws.wait_duration_ms ,dm_ws.wait_type ,dm_es.STATUS ,dm_t.TEXT ,dm_qp.query_plan ,dm_ws.session_ID ,dm_es.cpu_time ,dm_es.memory_usage ,dm_es.logical_reads ,dm_es.total_elapsed_time ,dm_es.program_name ,DB_NAME(dm_r.database_id) DatabaseName ,dm_ws.blocking_session_id ,dm_r.wait_resource ,dm_es.login_name ,dm_r.command ,dm_r.last_wait_type FROM sys.dm_os_waiting_tasks dm_ws INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id CROSS APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_t CROSS APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qp WHERE dm_es.is_user_process = 1;
查询谁在占着 Session 连接
CREATE TABLE #sp_who2 ( SPID INT ,STATUS VARCHAR(255) ,LOGIN VARCHAR(255) ,HostName VARCHAR(255) ,BlkBy VARCHAR(255) ,DBName VARCHAR(255) ,Command VARCHAR(255) ,CPUTime INT ,DiskIO INT ,LastBatch VARCHAR(255) ,ProgramName VARCHAR(255) ,SPID2 INT ,REQUESTID INT ) INSERT INTO #sp_who2 EXEC sp_who2 SELECT * FROM #sp_who2 w --WHERE w.ProgramName = ‘xxx‘ DROP TABLE #sp_who2
查询程序占用的 SPID 信息
SELECT spid ,a.[status] ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,b.[name] ,loginame FROM master.dbo.sysprocesses a INNER JOIN master.dbo.sysdatabases b ON a.dbid = b.dbid where hostname != ‘‘ ORDER BY program_name
查询所有执行 SQL 对应的 sql_handle
DECLARE @current_sql_handle BINARY (20); DECLARE @sql_text_list TABLE ( sql_handle BINARY (20) ,TEXT NVARCHAR(max) ); DECLARE sql_handle_cursor CURSOR FOR SELECT sp.sql_handle FROM sys.sysprocesses sp WHERE sp.sql_handle != 0x0000000000000000000000000000000000000000 --AND sp.program_name = ‘xxxx‘ ; OPEN sql_handle_cursor FETCH NEXT FROM sql_handle_cursor INTO @current_sql_handle WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @sql_text_list ( sql_handle ,TEXT ) SELECT @current_sql_handle ,est.TEXT FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est; FETCH NEXT FROM sql_handle_cursor INTO @current_sql_handle END SELECT DISTINCT * FROM @sql_text_list tl WHERE tl.TEXT NOT LIKE ‘%statement_start_offset%‘; CLOSE sql_handle_cursor DEALLOCATE sql_handle_cursor
查询最近 60 秒平均执行时间超过 300 毫秒的 SQL 语句
SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ( ( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END ) - qs.statement_start_offset ) / 2 ) + 1) AS statement_text ,last_execution_time ,total_elapsed_time / execution_count avg_elapsed_time ,total_physical_reads ,total_logical_reads ,total_logical_writes ,execution_count ,total_worker_time ,total_elapsed_time ,creation_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE()) AND st.TEXT NOT LIKE ‘%statement_start_offset%‘ AND total_elapsed_time / execution_count >= 300 ORDER BY last_execution_time DESC;
查询最近 60 秒平均执行时间超过 100 毫秒的非 SELECT 语句
SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ( ( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END ) - qs.statement_start_offset ) / 2 ) + 1) AS statement_text ,last_execution_time ,total_elapsed_time / execution_count avg_elapsed_time ,total_physical_reads ,total_logical_reads ,total_logical_writes ,execution_count ,total_worker_time ,total_elapsed_time ,creation_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE()) AND st.TEXT NOT LIKE ‘%statement_start_offset%‘ AND execution_count < 100 AND total_elapsed_time / execution_count > 100 AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ( ( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END ) - qs.statement_start_offset ) / 2 ) + 1) NOT LIKE ‘SELECT%‘ ORDER BY last_execution_time DESC;
查询最近 60 秒累计总执行次数大于 1000 次的 SQL 语句
SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ( ( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END ) - qs.statement_start_offset ) / 2 ) + 1) AS statement_text ,total_elapsed_time / execution_count / 1000 AS avg_elapsed_time_by_ms ,last_execution_time ,total_elapsed_time ,execution_count ,total_worker_time ,total_physical_reads ,total_logical_reads ,total_logical_writes ,creation_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE qs.execution_count > 1000 AND last_execution_time > DATEADD(SECOND, - 60, GETDATE()) --AND ( -- st.TEXT LIKE ‘%[[]AAA]%‘ -- OR st.TEXT LIKE ‘%[[]BBB]%‘ -- OR st.TEXT LIKE ‘%[[]CCC]%‘ -- ) ORDER BY total_elapsed_time / execution_count DESC;
查询前 10 个可能是性能最差的 SQL 语句
SELECT TOP 10 TEXT AS ‘SQL Statement‘ ,last_execution_time AS ‘Last Execution Time‘ ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO] ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)] ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)] ,execution_count AS "Execution Count" ,qp.query_plan AS "Query Plan" FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_elapsed_time / execution_count DESC
看看当前哪些查询正在活跃着
USE master GO IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ‘sp_WhoIsActive‘) EXEC (‘CREATE PROC dbo.sp_WhoIsActive AS SELECT ‘‘stub version, to be replaced‘‘‘) GO /********************************************************************************************* Who Is Active? v11.11 (2012-03-22) (C) 2007-2012, Adam Machanic Feedback: mailto:amachanic@gmail.com Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx "Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx Donate! Support this project: http://tinyurl.com/WhoIsActiveDonate License: Who is Active? is free to download and use for personal, educational, and internal corporate purposes, provided that this header is preserved. Redistribution or sale of Who is Active?, in whole or in part, is prohibited without the author‘s express written consent. *********************************************************************************************/ ALTER PROC dbo.sp_WhoIsActive ( --~ --Filters--Both inclusive and exclusive --Set either filter to ‘‘ to disable --Valid filter types are: session, program, database, login, and host --Session is a session ID, and either 0 or ‘‘ can be used to indicate "all" sessions --All other filter types support % or _ as wildcards @filter sysname = ‘‘, @filter_type VARCHAR(10) = ‘session‘, @not_filter sysname = ‘‘, @not_filter_type VARCHAR(10) = ‘session‘, --Retrieve data about the calling session? @show_own_spid BIT = 0, --Retrieve data about system sessions? @show_system_spids BIT = 0, --Controls how sleeping SPIDs are handled, based on the idea of levels of interest --0 does not pull any sleeping SPIDs --1 pulls only those sleeping SPIDs that also have an open transaction --2 pulls all sleeping SPIDs @show_sleeping_spids TINYINT = 1, --If 1, gets the full stored procedure or running batch, when available --If 0, gets only the actual statement that is currently running in the batch or procedure @get_full_inner_text BIT = 0, --Get associated query plans for running tasks, if available --If @get_plans = 1, gets the plan based on the request‘s statement offset --If @get_plans = 2, gets the entire plan based on the request‘s plan_handle @get_plans TINYINT = 0, --Get the associated outer ad hoc query or stored procedure call, if available @get_outer_command BIT = 0, --Enables pulling transaction log write info and transaction duration @get_transaction_info BIT = 0, --Get information on active tasks, based on three interest levels --Level 0 does not pull any task-related information --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers --Level 2 pulls all available task-based metrics, including: --number of active tasks, current wait stats, physical I/O, context switches, and blocker information @get_task_info TINYINT = 1, --Gets associated locks for each request, aggregated in an XML format @get_locks BIT = 0, --Get average time for past runs of an active query --(based on the combination of plan handle, sql handle, and offset) @get_avg_time BIT = 0, --Get additional non-performance-related information about the session or request --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type -- --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error) -- --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id, --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name @get_additional_info BIT = 0, --Walk the blocking chain and count the number of --total SPIDs blocked all the way down by a given session --Also enables task_info Level 1, if @get_task_info is set to 0 @find_block_leaders BIT = 0, --Pull deltas on various metrics --Interval in seconds to wait before doing the second data pull @delta_interval TINYINT = 0, --List of desired output columns, in desired order --Note that the final output will be the intersection of all enabled features and all --columns in the list. Therefore, only columns associated with enabled features will --actually appear in the output. Likewise, removing columns from this list may effectively --disable features, even if they are turned on -- --Each element in this list must be one of the valid output column names. Names must be --delimited by square brackets. White space, formatting, and additional characters are --allowed, as long as the list contains exact matches of delimited valid column names. @output_column_list VARCHAR(8000) = ‘[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]‘, --Column(s) by which to sort output, optionally with sort directions. --Valid column choices: --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations, --tempdb_current, CPU, context_switches, used_memory, physical_io_delta, --reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta, --tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta, --tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count, --percent_complete, host_name, login_name, database_name, start_time, login_time -- --Note that column names in the list must be bracket-delimited. Commas and/or white --space are not required. @sort_order VARCHAR(500) = ‘[start_time] ASC‘, --Formats some of the output columns in a more "human readable" form --0 disables outfput format --1 formats the output for variable-width fonts --2 formats the output for fixed-width fonts @format_output TINYINT = 1, --If set to a non-blank value, the script will attempt to insert into the specified --destination table. Please note that the script will not verify that the table exists, --or that it has the correct schema, before doing the insert. --Table can be specified in one, two, or three-part format @destination_table VARCHAR(4000) = ‘‘, --If set to 1, no data collection will happen and no result set will be returned; instead, --a CREATE TABLE statement will be returned via the @schema parameter, which will match --the schema of the result set that would be returned by using the same collection of the --rest of the parameters. The CREATE TABLE statement will have a placeholder token of --<table_name> in place of an actual table name. @return_schema BIT = 0, @schema VARCHAR(MAX) = NULL OUTPUT, --Help! What do I do? @help BIT = 0 --~ ) /* OUTPUT COLUMNS -------------- Formatted/Non: [session_id] [smallint] NOT NULL Session ID (a.k.a. SPID) Formatted: [dd hh:mm:ss.mss] [varchar](15) NULL Non-Formatted: <not returned> For an active request, time the query has been running For a sleeping session, time since the last batch completed Formatted: [dd hh:mm:ss.mss (avg)] [varchar](15) NULL Non-Formatted: [avg_elapsed_time] [int] NULL (Requires @get_avg_time option) How much time has the active portion of the query taken in the past, on average? Formatted: [physical_io] [varchar](30) NULL Non-Formatted: [physical_io] [bigint] NULL Shows the number of physical I/Os, for active requests Formatted: [reads] [varchar](30) NULL Non-Formatted: [reads] [bigint] NULL For an active request, number of reads done for the current query For a sleeping session, total number of reads done over the lifetime of the session Formatted: [physical_reads] [varchar](30) NULL Non-Formatted: [physical_reads] [bigint] NULL For an active request, number of physical reads done for the current query For a sleeping session, total number of physical reads done over the lifetime of the session Formatted: [writes] [varchar](30) NULL Non-Formatted: [writes] [bigint] NULL For an active request, number of writes done for the current query For a sleeping session, total number of writes done over the lifetime of the session Formatted: [tempdb_allocations] [varchar](30) NULL Non-Formatted: [tempdb_allocations] [bigint] NULL For an active request, number of TempDB writes done for the current query For a sleeping session, total number of TempDB writes done over the lifetime of the session Formatted: [tempdb_current] [varchar](30) NULL Non-Formatted: [tempdb_current] [bigint] NULL For an active request, number of TempDB pages currently allocated for the query For a sleeping session, number of TempDB pages currently allocated for the session Formatted: [CPU] [varchar](30) NULL Non-Formatted: [CPU] [int] NULL For an active request, total CPU time consumed by the current query For a sleeping session, total CPU time consumed over the lifetime of the session Formatted: [context_switches] [varchar](30) NULL Non-Formatted: [context_switches] [bigint] NULL Shows the number of context switches, for active requests Formatted: [used_memory] [varchar](30) NOT NULL Non-Formatted: [used_memory] [bigint] NOT NULL For an active request, total memory consumption for the current query For a sleeping session, total current memory consumption Formatted: [physical_io_delta] [varchar](30) NULL Non-Formatted: [physical_io_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of physical I/Os reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [reads_delta] [varchar](30) NULL Non-Formatted: [reads_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of reads reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [physical_reads_delta] [varchar](30) NULL Non-Formatted: [physical_reads_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of physical reads reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [writes_delta] [varchar](30) NULL Non-Formatted: [writes_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of writes reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [tempdb_allocations_delta] [varchar](30) NULL Non-Formatted: [tempdb_allocations_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of TempDB writes reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [tempdb_current_delta] [varchar](30) NULL Non-Formatted: [tempdb_current_delta] [bigint] NULL (Requires @delta_interval option) Difference between the number of allocated TempDB pages reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [CPU_delta] [varchar](30) NULL Non-Formatted: [CPU_delta] [int] NULL (Requires @delta_interval option) Difference between the CPU time reported on the first and second collections. If the request started after the first collection, the value will be NULL Formatted: [context_switches_delta] [varchar](30) NULL Non-Formatted: [context_switches_delta] [bigint] NULL (Requires @delta_interval option) Difference between the context switches count reported on the first and second collections If the request started after the first collection, the value will be NULL Formatted: [used_memory_delta] [varchar](30) NULL Non-Formatted: [used_memory_delta] [bigint] NULL Difference between the memory usage reported on the first and second collections If the request started after the first collection, the value will be NULL Formatted: [tasks] [varchar](30) NULL Non-Formatted: [tasks] [smallint] NULL Number of worker tasks currently allocated, for active requests Formatted/Non: [status] [varchar](30) NOT NULL Activity status for the session (running, sleeping, etc) Formatted/Non: [wait_info] [nvarchar](4000) NULL Aggregates wait information, in the following format: (Ax: Bms/Cms/Dms)E A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait times, in milliseconds. If only one thread is waiting, its wait time will be shown as B. If two tasks are waiting, each of their wait times will be shown (B/C). If three or more tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D). If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM), the page type will be identified. If wait type E is CXPACKET, the nodeId from the query plan will be identified Formatted/Non: [locks] [xml] NULL (Requires @get_locks option) Aggregates lock information, in XML format. The lock XML includes the lock mode, locked object, and aggregates the number of requests. Attempts are made to identify locked objects by name Formatted/Non: [tran_start_time] [datetime] NULL (Requires @get_transaction_info option) Date and time that the first transaction opened by a session caused a transaction log write to occur. Formatted/Non: [tran_log_writes] [nvarchar](4000) NULL (Requires @get_transaction_info option) Aggregates transaction log write information, in the following format: A:wB (C kB) A is a database that has been touched by an active transaction B is the number of log writes that have been made in the database as a result of the transaction C is the number of log kilobytes consumed by the log records Formatted: [open_tran_count] [varchar](30) NULL Non-Formatted: [open_tran_count] [smallint] NULL Shows the number of open transactions the session has open Formatted: [sql_command] [xml] NULL Non-Formatted: [sql_command] [nvarchar](max) NULL (Requires @get_outer_command option) Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server, if available Formatted: [sql_text] [xml] NULL Non-Formatted: [sql_text] [nvarchar](max) NULL Shows the SQL text for active requests or the last statement executed for sleeping sessions, if available in either case. If @get_full_inner_text option is set, shows the full text of the batch. Otherwise, shows only the active statement within the batch. If the query text is locked, a special timeout message will be sent, in the following format: <timeout_exceeded /> If an error occurs, an error message will be sent, in the following format: <error message="message" /> Formatted/Non: [query_plan] [xml] NULL (Requires @get_plans option) Shows the query plan for the request, if available. If the plan is locked, a special timeout message will be sent, in the following format: <timeout_exceeded /> If an error occurs, an error message will be sent, in the following format: <error message="message" /> Formatted/Non: [blocking_session_id] [smallint] NULL When applicable, shows the blocking SPID Formatted: [blocked_session_count] [varchar](30) NULL Non-Formatted: [blocked_session_count] [smallint] NULL (Requires @find_block_leaders option) The total number of SPIDs blocked by this session, all the way down the blocking chain. Formatted: [percent_complete] [varchar](30) NULL Non-Formatted: [percent_complete] [real] NULL When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks) Formatted/Non: [host_name] [sysname] NOT NULL Shows the host name for the connection Formatted/Non: [login_name] [sysname] NOT NULL Shows the login name for the connection Formatted/Non: [database_name] [sysname] NULL Shows the connected database Formatted/Non: [program_name] [sysname] NULL Shows the reported program/application name Formatted/Non: [additional_info] [xml] NULL (Requires @get_additional_info option) Returns additional non-performance-related session/request information If the script finds a SQL Agent job running, the name of the job and job step will be reported If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported Formatted/Non: [start_time] [datetime] NOT NULL For active requests, shows the time the request started For sleeping sessions, shows the time the last batch completed Formatted/Non: [login_time] [datetime] NOT NULL Shows the time that the session connected Formatted/Non: [request_id] [int] NULL For active requests, shows the request_id Should be 0 unless MARS is being used Formatted/Non: [collection_time] [datetime] NOT NULL Time that this script‘s final SELECT ran */ AS BEGIN; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_WARNINGS ON; SET NUMERIC_ROUNDABORT OFF; SET ARITHABORT ON; IF @filter IS NULL OR @filter_type IS NULL OR @not_filter IS NULL OR @not_filter_type IS NULL OR @show_own_spid IS NULL OR @show_system_spids IS NULL OR @show_sleeping_spids IS NULL OR @get_full_inner_text IS NULL OR @get_plans IS NULL OR @get_outer_command IS NULL OR @get_transaction_info IS NULL OR @get_task_info IS NULL OR @get_locks IS NULL OR @get_avg_time IS NULL OR @get_additional_info IS NULL OR @find_block_leaders IS NULL OR @delta_interval IS NULL OR @format_output IS NULL OR @output_column_list IS NULL OR @sort_order IS NULL OR @return_schema IS NULL OR @destination_table IS NULL OR @help IS NULL BEGIN; RAISERROR(‘Input parameters cannot be NULL‘, 16, 1); RETURN; END; IF @filter_type NOT IN (‘session‘, ‘program‘, ‘database‘, ‘login‘, ‘host‘) BEGIN; RAISERROR(‘Valid filter types are: session, program, database, login, host‘, 16, 1); RETURN; END; IF @filter_type = ‘session‘ AND @filter LIKE ‘%[^0123456789]%‘ BEGIN; RAISERROR(‘Session filters must be valid integers‘, 16, 1); RETURN; END; IF @not_filter_type NOT IN (‘session‘, ‘program‘, ‘database‘, ‘login‘, ‘host‘) BEGIN; RAISERROR(‘Valid filter types are: session, program, database, login, host‘, 16, 1); RETURN; END; IF @not_filter_type = ‘session‘ AND @not_filter LIKE ‘%[^0123456789]%‘ BEGIN; RAISERROR(‘Session filters must be valid integers‘, 16, 1); RETURN; END; IF @show_sleeping_spids NOT IN (0, 1, 2) BEGIN; RAISERROR(‘Valid values for @show_sleeping_spids are: 0, 1, or 2‘, 16, 1); RETURN; END; IF @get_plans NOT IN (0, 1, 2) BEGIN; RAISERROR(‘Valid values for @get_plans are: 0, 1, or 2‘, 16, 1); RETURN; END; IF @get_task_info NOT IN (0, 1, 2) BEGIN; RAISERROR(‘Valid values for @get_task_info are: 0, 1, or 2‘, 16, 1); RETURN; END; IF @format_output NOT IN (0, 1, 2) BEGIN; RAISERROR(‘Valid values for @format_output are: 0, 1, or 2‘, 16, 1); RETURN; END; IF @help = 1 BEGIN; DECLARE @header VARCHAR(MAX), @params VARCHAR(MAX), @outputs VARCHAR(MAX); SELECT @header = REPLACE ( REPLACE ( CONVERT ( VARCHAR(MAX), SUBSTRING ( t.text, CHARINDEX(‘/‘ + REPLICATE(‘*‘, 93), t.text) + 94, CHARINDEX(REPLICATE(‘*‘, 93) + ‘/‘, t.text) - (CHARINDEX(‘/‘ + REPLICATE(‘*‘, 93), t.text) + 94) ) ), CHAR(13)+CHAR(10), CHAR(13) ), ‘ ‘, ‘‘ ), @params = CHAR(13) + REPLACE ( REPLACE ( CONVERT ( VARCHAR(MAX), SUBSTRING ( t.text, CHARINDEX(‘--~‘, t.text) + 5, CHARINDEX(‘--~‘, t.text, CHARINDEX(‘--~‘, t.text) + 5) - (CHARINDEX(‘--~‘, t.text) + 5) ) ), CHAR(13)+CHAR(10), CHAR(13) ), ‘ ‘, ‘‘ ), @outputs = CHAR(13) + REPLACE ( REPLACE ( REPLACE ( CONVERT ( VARCHAR(MAX), SUBSTRING ( t.text, CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32, CHARINDEX(‘*/‘, t.text, CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32) - (CHARINDEX(‘OUTPUT COLUMNS‘+CHAR(13)+CHAR(10)+‘--------------‘, t.text) + 32) ) ), CHAR(9), CHAR(255) ), CHAR(13)+CHAR(10), CHAR(13) ), ‘ ‘, ‘‘ ) + CHAR(13) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.session_id = @@SPID; WITH a0 AS (SELECT 1 AS n UNION ALL SELECT 1), a1 AS (SELECT 1 AS n FROM a0 AS a, a0 AS b), a2 AS (SELECT 1 AS n FROM a1 AS a, a1 AS b), a3 AS (SELECT 1 AS n FROM a2 AS a, a2 AS b), a4 AS (SELECT 1 AS n FROM a3 AS a, a3 AS b), numbers AS ( SELECT TOP(LEN(@header) - 1) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS number FROM a4 ORDER BY number ) SELECT RTRIM(LTRIM( SUBSTRING ( @header, number + 1, CHARINDEX(CHAR(13), @header, number + 1) - number - 1 ) )) AS [------header---------------------------------------------------------------------------------------------------------------] FROM numbers WHERE SUBSTRING(@header, number, 1) = CHAR(13); WITH a0 AS (SELECT 1 AS n UNION ALL SELECT 1), a1 AS (SELECT 1 AS n FROM a0 AS a, a0 AS b), a2 AS (SELECT 1 AS n FROM a1 AS a, a1 AS b), a3 AS (SELECT 1 AS n FROM a2 AS a, a2 AS b), a4 AS (SELECT 1 AS n FROM a3 AS a, a3 AS b), numbers AS ( SELECT TOP(LEN(@params) - 1) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS number FROM a4 ORDER BY number ), tokens AS ( SELECT RTRIM(LTRIM( SUBSTRING ( @params, number + 1, CHARINDEX(CHAR(13), @params, number + 1) - number - 1 ) )) AS token, number, CASE WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number ELSE COALESCE(NULLIF(CHARINDEX(‘,‘ + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) END AS param_group, ROW_NUMBER() OVER ( PARTITION BY CHARINDEX(‘,‘ + CHAR(13) + CHAR(13), @params, number), SUBSTRING(@params, number+1, 1) ORDER BY number ) AS group_order FROM numbers WHERE SUBSTRING(@params, number, 1) = CHAR(13) ), parsed_tokens AS ( SELECT MIN ( CASE WHEN token LIKE ‘@%‘ THEN token ELSE NULL END ) AS parameter, MIN ( CASE WHEN token LIKE ‘--%‘ THEN RIGHT(token, LEN(token) - 2) ELSE NULL END ) AS description, param_group, group_order FROM tokens WHERE NOT ( token = ‘‘ AND group_order > 1 ) GROUP BY param_group, group_order ) SELECT CASE WHEN description IS NULL AND parameter IS NULL THEN ‘-------------------------------------------------------------------------‘ WHEN param_group = MAX(param_group) OVER() THEN parameter ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), ‘‘) END AS [------parameter----------------------------------------------------------], CASE WHEN description IS NULL AND parameter IS NULL THEN ‘----------------------------------------------------------------------------------------------------------------------‘ ELSE COALESCE(description, ‘‘) END AS [------description-----------------------------------------------------------------------------------------------------] FROM parsed_tokens ORDER BY param_group, group_order; WITH a0 AS (SELECT 1 AS n UNION ALL SELECT 1), a1 AS (SELECT 1 AS n FROM a0 AS a, a0 AS b), a2 AS (SELECT 1 AS n FROM a1 AS a, a1 AS b), a3 AS (SELECT 1 AS n FROM a2 AS a, a2 AS b), a4 AS (SELECT 1 AS n FROM a3 AS a, a3 AS b), numbers AS ( SELECT TOP(LEN(@outputs) - 1) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS number FROM a4 ORDER BY number ), tokens AS ( SELECT RTRIM(LTRIM( SUBSTRING ( @outputs, number + 1, CASE WHEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) < COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) - number - 1 ELSE COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1 END ) )) AS token, number, COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) AS output_group, ROW_NUMBER() OVER ( PARTITION BY COALESCE(NULLIF(CHARINDEX(CHAR(13) + ‘Formatted‘, @outputs, number + 1), 0), LEN(@outputs)) ORDER BY number ) AS output_group_order FROM numbers WHERE SUBSTRING(@outputs, number, 10) = CHAR(13) + ‘Formatted‘ OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2 ), output_tokens AS ( SELECT *, CASE output_group_order WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group) ELSE ‘‘ END COLLATE Latin1_General_Bin2 AS column_info FROM tokens ) SELECT CASE output_group_order WHEN 1 THEN ‘-----------------------------------‘ WHEN 2 THEN CASE WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)) ELSE SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1) END ELSE ‘‘ END AS formatted_column_name, CASE output_group_order WHEN 1 THEN ‘-----------------------------------‘ WHEN 2 THEN CASE WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN SUBSTRING(column_info, CHARINDEX(‘]‘, column_info)+2, LEN(column_info)) ELSE SUBSTRING(column_info, CHARINDEX(‘]‘, column_info)+2, CHARINDEX(‘Non-Formatted:‘, column_info, CHARINDEX(‘]‘, column_info)+2) - CHARINDEX(‘]‘, column_info)-3) END ELSE ‘‘ END AS formatted_column_type, CASE output_group_order WHEN 1 THEN ‘---------------------------------------‘ WHEN 2 THEN CASE WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN ‘‘ ELSE CASE WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, 1) = ‘<‘ THEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, CHARINDEX(‘>‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))) ELSE SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, CHARINDEX(‘]‘, column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))) END END ELSE ‘‘ END AS unformatted_column_name, CASE output_group_order WHEN 1 THEN ‘---------------------------------------‘ WHEN 2 THEN CASE WHEN CHARINDEX(‘Formatted/Non:‘, column_info) = 1 THEN ‘‘ ELSE CASE WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+1, 1) = ‘<‘ THEN ‘‘ ELSE SUBSTRING(column_info, CHARINDEX(‘]‘, column_info, CHARINDEX(‘Non-Formatted:‘, column_info))+2, CHARINDEX(‘Non-Formatted:‘, column_info, CHARINDEX(‘]‘, column_info)+2) - CHARINDEX(‘]‘, column_info)-3) END END ELSE ‘‘ END AS unformatted_column_type, CASE output_group_order WHEN 1 THEN ‘----------------------------------------------------------------------------------------------------------------------‘ ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, ‘‘) END AS [------description-----------------------------------------------------------------------------------------------------] FROM output_tokens WHERE NOT ( output_group_order = 1 AND output_group = LEN(@outputs) ) ORDER BY output_group, CASE output_group_order WHEN 1 THEN 99 ELSE output_group_order END; RETURN; END; WITH a0 AS (SELECT 1 AS n UNION ALL SELECT 1), a1 AS (SELECT 1 AS n FROM a0 AS a, a0 AS b), a2 AS (SELECT 1 AS n FROM a1 AS a, a1 AS b), a3 AS (SELECT 1 AS n FROM a2 AS a, a2 AS b), a4 AS (SELECT 1 AS n FROM a3 AS a, a3 AS b), numbers AS ( SELECT TOP(LEN(@output_column_list)) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS number FROM a4 ORDER BY number ), tokens AS ( SELECT ‘|[‘ + SUBSTRING ( @output_column_list, number + 1, CHARINDEX(‘]‘, @output_column_list, number) - number - 1 ) + ‘|]‘ AS token, number FROM numbers WHERE SUBSTRING(@output_column_list, number, 1) = ‘[‘ ), ordered_columns AS ( SELECT x.column_name, ROW_NUMBER() OVER ( PARTITION BY x.column_name ORDER BY tokens.number, x.default_order ) AS r, ROW_NUMBER() OVER ( ORDER BY tokens.number, x.default_order ) AS s FROM tokens JOIN ( SELECT ‘[session_id]‘ AS column_name, 1 AS default_order UNION ALL SELECT ‘[dd hh:mm:ss.mss]‘, 2 WHERE @format_output IN (1, 2) UNION ALL SELECT ‘[dd hh:mm:ss.mss (avg)]‘, 3 WHERE @format_output IN (1, 2) AND @get_avg_time = 1 UNION ALL SELECT ‘[avg_elapsed_time]‘, 4 WHERE @format_output = 0 AND @get_avg_time = 1 UNION ALL SELECT ‘[physical_io]‘, 5 WHERE @get_task_info = 2 UNION ALL SELECT ‘[reads]‘, 6 UNION ALL SELECT ‘[physical_reads]‘, 7 UNION ALL SELECT ‘[writes]‘, 8 UNION ALL SELECT ‘[tempdb_allocations]‘, 9 UNION ALL SELECT ‘[tempdb_current]‘, 10 UNION ALL SELECT ‘[CPU]‘, 11 UNION ALL SELECT ‘[context_switches]‘, 12 WHERE @get_task_info = 2 UNION ALL SELECT ‘[used_memory]‘, 13 UNION ALL SELECT ‘[physical_io_delta]‘, 14 WHERE @delta_interval > 0 AND @get_task_info = 2 UNION ALL SELECT ‘[reads_delta]‘, 15 WHERE @delta_interval > 0 UNION ALL SELECT ‘[physical_reads_delta]‘, 16 WHERE @delta_interval > 0 UNION ALL SELECT ‘[writes_delta]‘, 17 WHERE @delta_interval > 0 UNION ALL SELECT ‘[tempdb_allocations_delta]‘, 18 WHERE @delta_interval > 0 UNION ALL SELECT ‘[tempdb_current_delta]‘, 19 WHERE @delta_interval > 0 UNION ALL SELECT ‘[CPU_delta]‘, 20 WHERE @delta_interval > 0 UNION ALL SELECT ‘[context_switches_delta]‘, 21 WHERE @delta_interval > 0 AND @get_task_info = 2 UNION ALL SELECT ‘[used_memory_delta]‘, 22 WHERE @delta_interval > 0 UNION ALL SELECT ‘[tasks]‘, 23 WHERE @get_task_info = 2 UNION ALL SELECT ‘[status]‘, 24 UNION ALL SELECT ‘[wait_info]‘, 25 WHERE @get_task_info > 0 OR @find_block_leaders = 1 UNION ALL SELECT ‘[locks]‘, 26 WHERE @get_locks = 1 UNION ALL SELECT ‘[tran_start_time]‘, 27 WHERE @get_transaction_info = 1 UNION ALL SELECT ‘[tran_log_writes]‘, 28 WHERE @get_transaction_info = 1 UNION ALL SELECT ‘[open_tran_count]‘, 29 UNION ALL SELECT ‘[sql_command]‘, 30 WHERE @get_outer_command = 1 UNION ALL SELECT ‘[sql_text]‘, 31 UNION ALL SELECT ‘[query_plan]‘, 32 WHERE @get_plans >= 1 UNION ALL SELECT ‘[blocking_session_id]‘, 33 WHERE @get_task_info > 0 OR @find_block_leaders = 1 UNION ALL SELECT ‘[blocked_session_count]‘, 34 WHERE @find_block_leaders = 1 UNION ALL SELECT ‘[percent_complete]‘, 35 UNION ALL SELECT ‘[host_name]‘, 36 UNION ALL SELECT ‘[login_name]‘, 37 UNION ALL SELECT ‘[database_name]‘, 38 UNION ALL SELECT ‘[program_name]‘, 39 UNION ALL SELECT ‘[additional_info]‘, 40 WHERE @get_additional_info = 1 UNION ALL SELECT ‘[start_time]‘, 41 UNION ALL SELECT ‘[login_time]‘, 42 UNION ALL SELECT ‘[request_id]‘, 43 UNION ALL SELECT ‘[collection_time]‘, 44 ) AS x ON x.column_name LIKE token ESCAPE ‘|‘ ) SELECT @output_column_list = STUFF ( ( SELECT ‘,‘ + column_name as [text()] FROM ordered_columns WHERE r = 1 ORDER BY s FOR XML PATH(‘‘) ), 1, 1, ‘‘ ); IF COALESCE(RTRIM(@output_column_list), ‘‘) = ‘‘ BEGIN; RAISERROR(‘No valid column matches found in @output_column_list or no columns remain due to selected options.‘, 16, 1); RETURN; END; IF @destination_table <> ‘‘ BEGIN; SET @destination_table = --database COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + ‘.‘, ‘‘) + --schema COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + ‘.‘, ‘‘) + --table COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), ‘‘); IF COALESCE(RTRIM(@destination_table), ‘‘) = ‘‘ BEGIN; RAISERROR(‘Destination table not properly formatted.‘, 16, 1); RETURN; END; END; WITH a0 AS (SELECT 1 AS n UNION ALL SELECT 1), a1 AS (SELECT 1 AS n FROM a0 AS a, a0 AS b), a2 AS (SELECT 1 AS n FROM a1 AS a, a1 AS b), a3 AS (SELECT 1 AS n FROM a2 AS a, a2 AS b), a4 AS (SELECT 1 AS n FROM a3 AS a, a3 AS b), numbers AS ( SELECT TOP(LEN(@sort_order)) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS number FROM a4 ORDER BY number ), tokens AS ( SELECT ‘|[‘ + SUBSTRING ( @sort_order, number + 1, CHARINDEX(‘]‘, @sort_order, number) - number - 1 ) + ‘|]‘ AS token, SUBSTRING ( @sort_order, CHARINDEX(‘]‘, @sort_order, number) + 1, COALESCE(NULLIF(CHARINDEX(‘[‘, @sort_order, CHARINDEX(‘]‘, @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(‘]‘, @sort_order, number) ) AS next_chunk, number FROM numbers WHERE SUBSTRING(@sort_order, number, 1) = ‘[‘ ), ordered_columns AS ( SELECT x.column_name + CASE WHEN tokens.next_chunk LIKE ‘%asc%‘ THEN ‘ ASC‘ WHEN tokens.next_chunk LIKE ‘%desc%‘ THEN ‘ DESC‘ ELSE ‘‘ END AS column_name, ROW_NUMBER() OVER ( PARTITION BY x.column_name ORDER BY tokens.number ) AS r, tokens.number FROM tokens JOIN ( SELECT ‘[session_id]‘ AS column_name UNION ALL SELECT ‘[physical_io]‘ UNION ALL SELECT ‘[reads]‘ UNION ALL SELECT ‘[physical_reads]‘ UNION ALL SELECT ‘[writes]‘ UNION ALL SELECT ‘[tempdb_allocations]‘ UNION ALL SELECT ‘[tempdb_current]‘ UNION ALL SELECT ‘[CPU]‘ UNION ALL SELECT ‘[context_switches]‘ UNION ALL SELECT ‘[used_memory]‘ UNION ALL SELECT ‘[physical_io_delta]‘ UNION ALL SELECT ‘[reads_delta]‘ UNION ALL SELECT ‘[physical_reads_delta]‘ UNION ALL SELECT ‘[writes_delta]‘ UNION ALL SELECT ‘[tempdb_allocations_delta]‘ UNION ALL SELECT ‘[tempdb_current_delta]‘ UNION ALL SELECT ‘[CPU_delta]‘ UNION ALL SELECT ‘[context_switches_delta]‘ UNION ALL SELECT ‘[used_memory_delta]‘ UNION ALL SELECT ‘[tasks]‘ UNION ALL SELECT ‘[tran_start_time]‘ UNION ALL SELECT ‘[open_tran_count]‘ UNION ALL SELECT ‘[blocking_session_id]‘ UNION ALL SELECT ‘[blocked_session_count]‘ UNION ALL SELECT ‘[percent_complete]‘ UNION ALL SELECT ‘[host_name]‘ UNION ALL SELECT ‘[login_name]‘ UNION ALL SELECT ‘[database_name]‘ UNION ALL SELECT ‘[start_time]‘ UNION ALL SELECT ‘[login_time]‘ ) AS x ON x.column_name LIKE token ESCAPE ‘|‘ ) SELECT @sort_order = COALESCE(z.sort_order, ‘‘) FROM ( SELECT STUFF ( ( SELECT ‘,‘ + column_name as [text()] FROM ordered_columns WHERE r = 1 ORDER BY number FOR XML PATH(‘‘) ), 1, 1, ‘‘ ) AS sort_order ) AS z; CREATE TABLE #sessions ( recursion SMALLINT NOT NULL, session_id SMALLINT NOT NULL, request_id INT NOT NULL, session_number INT NOT NULL, elapsed_time INT NOT NULL, avg_elapsed_time INT NULL, physical_io BIGINT NULL, reads BIGINT NULL, physical_reads BIGINT NULL, writes BIGINT NULL, tempdb_allocations BIGINT NULL, tempdb_current BIGINT NULL, CPU INT NULL, thread_CPU_snapshot BIGINT NULL, context_switches BIGINT NULL, used_memory BIGINT NOT NULL, tasks SMALLINT NULL, status VARCHAR(30) NOT NULL, wait_info NVARCHAR(4000) NULL, locks XML NULL, transaction_id BIGINT NULL, tran_start_time DATETIME NULL, tran_log_writes NVARCHAR(4000) NULL, open_tran_count SMALLINT NULL, sql_command XML NULL, sql_handle VARBINARY(64) NULL, statement_start_offset INT NULL, statement_end_offset INT NULL, sql_text XML NULL, plan_handle VARBINARY(64) NULL, query_plan XML NULL, blocking_session_id SMALLINT NULL, blocked_session_count SMALLINT NULL, percent_complete REAL NULL, host_name sysname NULL, login_name sysname NOT NULL, database_name sysname NULL, program_name sysname NULL, additional_info XML NULL, start_time DATETIME NOT NULL, login_time DATETIME NULL, last_request_start_time DATETIME NULL, PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON), UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON) ); IF @return_schema = 0 BEGIN; --Disable unnecessary autostats on the table CREATE STATISTICS s_session_id ON #sessions (session_id) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_request_id ON #sessions (request_id) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_transaction_id ON #sessions (transaction_id) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_session_number ON #sessions (session_number) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_status ON #sessions (status) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_start_time ON #sessions (start_time) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time) WITH SAMPLE 0 ROWS, NORECOMPUTE; CREATE STATISTICS s_recursion ON #sessions (recursion) WITH SAMPLE 0 ROWS, NORECOMPUTE; DECLARE @recursion SMALLINT; SET @recursion = CASE @delta_interval WHEN 0 THEN 1 ELSE -1 END; DECLARE @first_collection_ms_ticks BIGINT; DECLARE @last_collection_start DATETIME; --Used for the delta pull REDO:; IF @get_locks = 1 AND @recursion = 1 AND @output_column_list LIKE ‘%|[locks|]%‘ ESCAPE ‘|‘ BEGIN; SELECT y.resource_type, y.database_name, y.object_id, y.file_id, y.page_type, y.hobt_id, y.allocation_unit_id, y.index_id, y.schema_id, y.principal_id, y.request_mode, y.request_status, y.session_id, y.resource_description, y.request_count, s.request_id, s.start_time, CONVERT(sysname, NULL) AS object_name, CONVERT(sysname, NULL) AS index_name, CONVERT(sysname, NULL) AS schema_name, CONVERT(sysname, NULL) AS principal_name, CONVERT(NVARCHAR(2048), NULL) AS query_error INTO #locks FROM ( SELECT sp.spid AS session_id, CASE sp.status WHEN ‘sleeping‘ THEN CONVERT(INT, 0) ELSE sp.request_id END AS request_id, CASE sp.status WHEN ‘sleeping‘ THEN sp.last_batch ELSE COALESCE(req.start_time, sp.last_batch) END AS start_time, sp.dbid FROM sys.sysprocesses AS sp OUTER APPLY ( SELECT TOP(1) CASE WHEN ( sp.hostprocess > ‘‘ OR r.total_elapsed_time < 0 ) THEN r.start_time ELSE DATEADD ( ms, 1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())), DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE()) ) END AS start_time FROM sys.dm_exec_requests AS r WHERE r.session_id = sp.spid AND r.request_id = sp.request_id ) AS req WHERE --Process inclusive filter 1 = CASE WHEN @filter <> ‘‘ THEN CASE @filter_type WHEN ‘session‘ THEN CASE WHEN CONVERT(SMALLINT, @filter) = 0 OR sp.spid = CONVERT(SMALLINT, @filter) THEN 1 ELSE 0 END