Mar 16, 2023

SQL Server's SP_WHO2

SQL Server Versions That Support SP_WHO2

SP_Who2 was introduced in SQL Server version 6.5. This information is highlighted in the context provided by a source dated on today's date, March 20, 2024, not the answer indicating that SP_Who2 has a longstanding history within SQL Server environments, starting from its early versions and continuing through at least SQL Server 2008, with all these versions sharing the same foundational aspects of SP_Who2 (SQLServerCentral).

Comparing SP_WHO and SP_WHO2

SP_WHO and SP_WHO2 are system-stored procedures in Microsoft SQL Server that provide information about current users, sessions, and running processes. However, they differ in the detail and format of the information they return, making each suited to different diagnostic and monitoring needs. Here's a comparative overview:

Introduction

SP_WHO: Offers basic information on current users, sessions, and their activities within the SQL Server environment.

SP_WHO2: Provides a more detailed view compared to SP_WHO, including additional information about what each session is doing, making it a more powerful tool for diagnosing server performance and user activity.

Information Provided

SP_WHO:

Lists active user sessions and provides basic information such as the SPID (Server Process ID), status, login name, and the database they are using.

Useful for a quick overview of the user who is connected to the server and basic session information.

SP_WHO2:

Offers all the information SP_WHO does, plus additional process details like CPU time, Disk IO, Last Batch Time, and Program Name.

It is especially useful for identifying sessions that are consuming significant resources or for getting more context about what each session is doing.

Key Features & Output Of SP_WHO2

When you execute SP_WHO2 in Microsoft SQL Server, it returns a detailed dataset that helps in monitoring and troubleshooting active processes by providing insights into the current sessions and their activities. Here's a breakdown of the output columns provided by SP_WHO2 and what they signify:

SPID:

Short for Server Process ID, this column shows the unique identifier for system processes for each session or connection to the SQL Server instance. It's useful for identifying and isolating specific processes for further action, such as killing a problematic process.

Status:

Indicates the current state of the process. Common statuses include runnable, sleeping, background, suspended, etc. This gives a quick insight into what the process is currently doing.

Login:

Shows the login name that initiated the session. This is particularly useful for identifying which user's actions are affecting the server or for tracing activities back to a specific account.

HostName:

Displays the name of the client machine from which the session was initiated. This can help in identifying where connections are coming from, especially in environments with multiple clients or applications.

BlkBy:

Short for "Blocked By," this column indicates if the current session is being blocked by another session. If a session is being blocked, the SPID of the blocking session will be displayed here, allowing for easier identification of the blocking process and resolution of blocking issues.

DBName:

Shows the name of the database the session is connected to or using. This helps in understanding which databases are currently being accessed or potentially affected by the session's operations.

Command:

Provides information on the specific command or query that is currently being executed by the instance in the session. This is crucial for diagnosing performance issues or identifying long-running queries.

CPUTime: Indicates the amount of CPU time (in milliseconds) that the session has consumed. High CPU consumption by a session may point to performance issues or inefficient queries.

DiskIO:

Shows the amount of disk input/output operations generated by the session. Similar to CPU time, high DiskIO values can indicate performance bottlenecks or resource-intensive operations.

LastBatch:

Provides the date and time when the last batch from the session or particular process was executed. This helps in understanding the recency of the session or particular process's activity and can aid in diagnosing issues related to session inactivity or hang-ups.

ProgramName:

Displays the name of the client program or application that initiated the session. This can be useful for identifying the source of problematic queries or understanding the distribution of database access across different applications.

SPID (again):

Repeats the Server Process ID for the session, which can be useful for cross-referencing within the results.

RequestID:

In environments employing Multiple Active Result Sets (MARS), this column identifies the specific request within a session. This is useful for isolating activity within sessions that have multiple concurrent requests.

Understanding these columns and what they represent can significantly enhance your ability to monitor, diagnose, and optimize the SQL Server environment, making SP_WHO2 a powerful tool for database administrators and developers.

Execute SP_WHO2 In SQL Server Management Studio

In the query window, type the following command to execute SP_WHO2:

EXEC SP_WHO2;

Alternatively, you can also run it without the EXEC keyword, as SP_WHO2 is a system-stored procedure:

SP_WHO2;

How To Capture SP_WHO2 To A Table

Capturing the output of sp_who2 into a table can be useful for analyzing and monitoring SQL Server activity over time. You can achieve this by using a temporary table or a permanent table in your database. Here's how you can do it with a temporary table In SQL Server Management studio

-- Create a temporary table to hold the output of sp_who2
CREATE TABLE #sp_who2_output (
    [SPID] INT,
    [Status] VARCHAR(50),
    [Login] VARCHAR(50),
    [HostName] VARCHAR(50),
    [BlkBy] VARCHAR(50),
    [DBName] VARCHAR(50),
    [Command] VARCHAR(50),
    [CPUTime] INT,
    [DiskIO] INT,
    [LastBatch] VARCHAR(50),
    [ProgramName] VARCHAR(100),
    [SPID2] INT,
    [RequestID] INT
);

-- Insert the output of sp_who2 into the temporary table
INSERT INTO #sp_who2_output EXEC sp_who2;

-- Select data from the temporary table
SELECT * FROM #sp_who2_output;

-- Drop the temporary table when done
DROP TABLE #sp_who2_output;

Alternatively, if you want to store the data persistently, you can create a permanent table in your database and insert the data into it using similar steps:

-- Create a permanent table to hold the output of sp_who2
CREATE TABLE sp_who2_output (
    [SPID] INT,
    [Status] VARCHAR(50),
    [Login] VARCHAR(50),
    [HostName] VARCHAR(50),
    [BlkBy] VARCHAR(50),
    [DBName] VARCHAR(50),
    [Command] VARCHAR(50),
    [CPUTime] INT,
    [DiskIO] INT,
    [LastBatch] VARCHAR(50),
    [ProgramName] VARCHAR(100),
    [SPID2] INT,
    [RequestID] INT,
    [CaptureDateTime] DATETIME DEFAULT GETDATE()
);

-- Insert the output of sp_who2 into the permanent table
INSERT INTO sp_who2_output EXEC sp_who2;

-- Select data from the permanent table
SELECT * FROM sp_who2_output;

In this case, the data will be stored in the sp_who2_output table in your database, allowing you to query it later for analysis.

Additional Tips:

Check for Blocking: Look at the BlkBy column to identify if any sessions are being blocked by others. This can help resolve concurrency and performance issues.

Evaluate Resource Usage: Pay attention to the CPUTime and DiskIO columns to spot sessions that are consuming excessive resources.

Note:

SP_WHO2 does not require any special permissions beyond the ability to connect to the SQL Server instance and execute stored procedures. However, viewing certain information or performing actions based on the data (e.g., a specific session or killing a specific session call) may require additional permissions.

Executing SP_WHO2 provides valuable insights into the activity monitor the current state of the activity monitor your SQL Server instance, helping you monitor, diagnose, and optimize your database environment effectively.

Practical Applications of SP_WHO2 in Real-World Scenarios

SP_WHO2's utility is not confined to a single query context but spans various use cases, demonstrating its versatility and potency in a database administrator or developer's arsenal of resources.

In scenarios ranging from day-to-day monitoring slow queries blocking transactions to acute performance degradation, SP_WHO2 provides actionable data – for example, identifying a rogue query causing contention or spotting an unexpectedly slow queries or high number of transactions on a database.