sp_CheckActivity

This is a free SQL Server tool from Straight Path Solutions for SQL Server Database Administrators (or people who play DBA at their organization) that shows the current activity of any sessions that have a currently executing request or an open transaction. It is a quick, low-resource way to see the active state of a SQL Server instance. You can download the most recent version of sp_CheckActivity from the Straight Path Solutions GitHub repository.

Check the query activity on your SQL Server instances!

Here at Straight Path Solutions, we’re big fans of community tools like sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database consultants who are always looking at SQL Server instances, we wanted a tool that could quickly and easily show us query activity, backup/restore progress, blocking chains, and more. Like many of you, we’ve been using sp_WhoIsActive for years, but we wanted a tool that was faster, easier, and less resource intensive. So, we wrote one.

Note: sp_CheckActivity is not meant to be a full replacement for sp_WhoIsActive, which has many more features. But it is handy for a fast snapshot of different query activity. 

What does sp_CheckActivity do?

This tool will give you a fast, comprehensive picture of query activity on your SQL Server instance. It uses minimal resources but is still feature rich, including:

• Shows a snapshot of active sessions and requests: elapsed time, session id, the currently executing statement and its parent batch or procedure, status, wait type and time, blocking session, CPU, reads, writes, tempdb usage, row count, transaction count, memory grant, isolation level, login, host, program, and session timing.
• Optionally includes the query plan for the currently running statement (@Plans).
• Reports tempdb consumption(GB) per session, both for the currently running statement and the historical allocation since the session started.
• Has a dedicated blocking mode (@ShowBlocking) that returns lead blockers and the sessions blocked by them, including blocking chains.
• Has a dedicated progress mode (@ShowProgress) that estimates completion time for any operation that reports percent complete (Backup, Restore, DBCC, Recovery, RollBack, TDE Encryption, Reorganize), filtering out everything else.
• Can filter by login account, one or more session ids, and sleeping-session behavior, and can show the top-most procedure for nested calls (@ShowTopProc).
• Can persist results to a table for later analysis (@TableName), and offers a raw (unformatted) output option for programmatic use (@Raw).
• Includes basic SQL injection guards on the @Account and @TableName parameters.Optionally includes the query plan for the currently running statement (@Plans).

How do I use it?

Execute the script to create sp_CheckActivity in the database of your choice.

Executing it without using parameters returns a single result set that includes information about current query activity. Although you can simply execute it as is, there are several optional parameters.

@Account – The account used to log in to SQL Server (Domain\User). Returns all requests for this account. Rejected if it contains common injection characters.

@SessionID – A specific SPID, or a comma-delimited list of SPIDs, to report on. More than one SPID must be passed as a string.

@Plans – 1 = include the query plan for the currently running statement.

@FilterSleeping – 0 = do not show any sleeping sessions; 1 = show sleeping sessions with open transactions; 2 = show all non-system sessions.

@ShowProgress – 1 = show the estimated completion time for any operation that supports percent complete (Backup, Restore, DBCC, Recovery, and similar). Filters everything else out.

@ShowBlocking – 1 = show any sessions that are blocking or being blocked, including the lead blocker.

@ShowTopProc – 1 = show the top-most procedure when procedure calls are nested.

@TableName – Fully qualified table ([DB Name].[schema].[Table Name]) to insert the results into.

@Raw – 1 = perform no formatting on the output; otherwise some numeric columns are converted to character and formatted for readability and alignment.

@Help – 1 = print the help for this procedure and exit.

@VersionCheck – the default is 0, but setting this to 1 will return the version number and version date of the copy of sp_CheckHealth you are running, and nothing else.

Here are some examples:

-- Snapshot of current activity: active requests plus sleeping sessions with open transactions
EXEC master.dbo.sp_CheckActivity;

-- Show only blocking: lead blockers and the sessions they block
EXEC master.dbo.sp_CheckActivity
      @ShowBlocking = 1;

-- Estimate completion time for long-running backup/restore/DBCC/recovery commands
EXEC master.dbo.sp_CheckActivity
      @ShowProgress = 1;

-- Output the results in a Raw (Unformatted) output and show the Query Plans
EXEC master.dbo.sp_CheckActivity
      @Raw = 1
    , @Plans = 1;

What are the columns returned?

The default result set includes one row per qualifying session. The @FilterSleeping, @Account, and @SessionID parameters narrow which rows are returned; system sessions and the calling session are excluded. Note that some metrics such as CPU, Reads, Writes and Elapsed Time are at the Request (Batch / Procedure) level and are cumulative, whereas others such as Wait time, Estimated Completion, % Complete etc. are at the Statement level and are relative to the snapshot at that specific time due to how the metrics are reported by the DMVs. The descriptions below outline which level is used for each column.

Column	Description
[hhh:mm:ss]	Elapsed time as Hours:Minutes:Seconds of the request.
[SessionID]	The session id (SPID).
[Executed Statement]	The individual statement in the batch, function, or procedure that is currently running.
[Batch or Procedure]	The direct parent batch, function, or procedure (if not a standalone statement).
[Query Plan]	Query plan for the statement (NULL when @Plans = 0).
[Blocking ID]	NULL or the session id that is blocking the currently running request.
[CPU ms]	CPU time in ms of the currently running request, or the entire session if there is no active request.
[Logical Reads]	Pages read by the currently running request, or the entire session if there is no active request.
[Writes]	Pages written by the currently running request, or the entire session if there is no active request.
[Status]	Status of the currently running request, or the entire session if there is no active request.
[Wait Type]	Wait type of the currently running request, or the entire session if there is no active request.
[Wait ms]	Milliseconds the currently running request has been waiting on the most recent wait type.
[C Tempdb GB]	tempdb space being used by the currently running statement.
[H Tempdb GB]	tempdb allocations minus any deallocations used by the session since it started.
[% Complete]	Percent complete as reported by SQL Server.
[DB Name]	Database name.
[Program Name]	Program name.
[Login Name]	Login name.
[Host Name]	Host name.
[Row Count]	Number of rows returned to the client by this request.
[Tran Count]	Transaction count.
[DOP]	Degree of parallelism for the currently running statement (NULL before SQL Server 2016).
[Workers]	Reserved parallel workers if this is a parallel query (NULL before SQL Server 2016).
[Memory Grant MB]	Query memory allocated to the executing statement.
[ISO Level]	Transaction isolation level.
[CaptureDT]	Date and time the snapshot was taken.
[Login Mins]	Minutes since the session started.
[Login DT]	Date and time the session started.
[Top Procedure]	The top-most procedure when calls are nested (only shown when @ShowTopProc = 1).

What are the requirements to use sp_CheckHealth?

These are the requirements.

1. SQL Server version: Designed for SQL Server 2016 (13.x) or higher. It also runs on SQL Server 2012 (11.x) and 2014 (12.x), but some data is not available before 2016 and is returned as NULL on those versions (specifically the [DOP] and [Workers] columns); the procedure also switches between DATEDIFF and DATEDIFF_BIG based on version. Execution is aborted on versions older than 2012 (@SQLVersionMajor < 11).
2. You need to have VIEW SERVER STATE to see other sessions. The procedure reads execution DMVs (sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_exec_sql_text, sys.dm_exec_input_buffer, sys.dm_exec_text_query_plan) and tempdb space-usage DMVs.
3. @TableName target: The table will be created if it does not already exist, must be fully qualified ([DB].[Schema].[Table]), and is validated and rejected if malformed or containing injection characters. The blocking (@ShowBlocking) and progress (@ShowProgress) modes do not support output to a table. When @TableName is supplied, the caller also needs INSERT permission on the target table.