在线情况统计sp_WhoIsActive



/*********************************************************************************************

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.

*********************************************************************************************/

CREATE 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)+