Skip to content

The sp_who3 stored procedure is a custom and open source alternative to the sp_who system stored procedures available in SQL Server.

License

Notifications You must be signed in to change notification settings

ronascentes/sp_who3

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 

Repository files navigation

SP_WHO3

Community version of sp_who which provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

  • Current active sessions/ requests
  • Current idle sessions that have open transactions
  • Connected users and how many sessions they have
  • Connected sessions that are running no requests (sleeping)

Why yet another stored procedure similiar of sp_who?

There are other great tools with same purpose such as sp_blitz and sp_whoisactive. Those tools might be more compreensive and complete than sp_who3 for activity monitoring. For example, sp_whoisactive has twenty-four parameters which you choose from a variety of options but sp_who3 has only three.

The motivation behind the sp_who3 development is to have a simple, small and straightforward user experience tool to run faster during a critical war room where every second wasted to restore a service counts. The sp_who3 code can be easily maintained and modified as per user needs. To sum up, sp_who3 follow the KISS Principle of "Keep it Simple, Stupid".

Sintax

sp_who3 [ [ @filter = ] 'login_name' | SPID ]
[, [ @info = ] 'IDLE' | 'COUNT' | 'SLEEPING' ]
[, [ @orderby = ] 'CPU' | 'DURATION' ]

Parameters

[ @filter = ] 'login_name' | SPID

Is used to filter the result set. Default value is null.

login_name is sysname that identifies processes belonging to a particular login. It has no effect for @info = 'IDLE'

SPID is a session identification number belonging to the SQL Server instance. SPID is smallint. It has no effect for @info = 'IDLE' | 'COUNT'.

[ @info = ] 'IDLE' | 'COUNT' | 'SLEEPING'

Is used to select the type of information. Default value is null which show information about current users, sessions and requests in an SQL Server instance.

IDLE provides information about current idle sessions that have open transactions

COUNT provides information about connected users and how many sessions they have

SLEEPING provides information about connected sessions that are not running requests

[ @orderby = ] 'CPU' | 'DURATION'

Is used to order the result set by the selected option. Default value is null.

CPU provides information from highest to lowest cpu_time value. It has no effect for @info = 'IDLE' | 'COUNT' | 'SLEEPING'.

DURATION provides information from highest to lowest running_time value. It has no effect for @info = 'IDLE' | 'COUNT'.

Result set

Column Data Type Description
session_id smallint ID of the session to which this request is related. Is not nullable.
host_name nvarchar(128) Name of the client workstation that is specific to a session. The value is NULL for internal sessions. Is nullable.
login_name nvarchar(128) SQL Server login name under which the session is currently executing.
db_name nvarchar(128) Name of the database the request is executing against. Is not nullable.
status nvarchar(30) Status of the request.
command nvarchar(32) Identifies the current type of command that is being processed.
running_time varchar Period of time that request is running. Is not nullable.
blk_by smallint ID of the session that is blocking the request.
open_tran_count int Number of transactions that are open for this request. Is not nullable.
wait_type nvarchar(60) If the request is currently blocked, this column returns the type of wait. Is nullable.
wait_resource nvarchar(256) If the request is currently blocked, this column returns the resource for which the request is currently waiting. Isn't nullable.
page_type_desc nvarchar(64) Description of the page type (only available for SQL Server 2019 or later).
object_name sysname Name of object.
program_name nvarchar(128) Name of client program that initiated the session. The value is NULL for internal sessions. Is nullable.
query_plan xml Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle.
sql_text varchar(max) Retrieve the currently executing statement for the request. Is nullable.
sql_handle varbinary(64) Hash map of the SQL text of the request. Is nullable.
requested_memory_kb bigint Total requested amount of memory in kilobytes.
granted_memory_kb bigint Total amount of memory actually granted in kilobytes. Can be NULL if the memory is not granted yet. For a typical situation, this value should be the same as requested_memory_kb.
ideal_memory_kb bigint Size, in kilobytes (KB), of the memory grant to fit everything into physical memory. This is based on the cardinality estimate.
query_cost float Estimated query cost.
user_obj_in_tempdb_MB bigint Space usage for user objects in tempdb by the session.
internal_obj_in_tempdb_MB bigint Space usage for internal objects in tempdb by the session.
cpu_time int CPU time in milliseconds that is used by the request. Is not nullable.
start_time datetime Timestamp when the request arrived. Is not nullable.
percent_complete real Percentage of work completed for some commands.
est_time_to_go datetime Estimate complete time of the request.
est_completion_time datetime Estimate complete datetime of the request.

Limitation

Tested and validated for SQL Server 2012 or above.

License

sp_who3 (and its repository) is licensed under the MIT License

Maintainer

Rodrigo Nascentes - @ronascentes

Reference

System Dynamic Management Views - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views

Microsoft Tiger Team - https://github.com/Microsoft/tigertoolbox

DynamicsPerf - https://blogs.msdn.microsoft.com/axinthefield and https://blogs.msdn.microsoft.com/axperf/