<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1639164799743833&amp;ev=PageView&amp;noscript=1">
Diagram Views

Using System Stored Procedures to Monitor SQL Server

Dan Sales
#Hosting, #Hosting Insights, #Tutorials
Published on September 8, 2016

We look at how to use the sp_who2 and sp_monitor system stored processes to find valuable data about your SQL Server instance.

SQL databases are one of the key parts of enterprise websites, but hosting and managing a SQL Server instance can be a complex task. Luckily systems administrators have a variety of tools available to help them do so. In my last few blogs, I have covered using SQL Server Management Studio Activity Monitor and other GUI (graphical user interface) based tools. Today, I will go over two System Stored Procedures that can be used when GUI based tools are not available.

Activity Monitor is a great tool, but it can use a sizable amount of system resources when run on the same server as the SQL Server database engine. This is because Activity Monitor is gathering a large amount of data about what’s going on in the SQL Server instance and displaying it to the user in a meaningful graphical manner. Most of the time, this is not an issue, but when the database engine that is being monitored is using all the system resources allotted or all the resources available on the server, Activity Monitor can time out and stop displaying real time data.

When I run into this kind of issue and cannot use Activity Monitor, I fall back on SQL Server’s System Stored Procedures. There are a large number of system stored procedures in 28 different categories that can provide a DBA or designer a great deal of data about what’s happening within SQL Server. The two system stored procedures I will review here are part of the Database Engine Stored Procedures, which are used for general maintenance of the SQL Server database engine.


The first system stored procedure I go to when Activity Monitor is not working for me is sp_who2. This system stored procedure is a variation on the sp_who system stored procedure, and it provides a little more data related to the resources being used in the SQL Server instance. To run the sp_who2 system stored procedure, use the following TSQL statement:

USE master; 
EXEC sp_who2; 

The sp_who2 procedure returns a result set with the following information:

Column Name



SQL Server Session ID


Process status. The possible values are:

  • dormant - SQL Server is resetting the session.
  • running - The session is running one or more batches.
  • background - The session is running a background task, such as deadlock detection.
  • rollback - The session has a transaction rollback in process.
  • pending - The session is waiting for a worker thread to become available.
  • runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.
  • spinloop - The session's task is waiting for a spinlock to become free.
  • suspended - The session is waiting for an event, such as I/O, to complete.


Login name associated with the particular session.


Host or computer name the login is connecting from.


If a process is being blocked by another process, the session ID for the blocking process will be returned here.
When a transaction associated with a specified session ID is blocked by an orphaned distributed transaction, this column will return a '-2' for the blocking orphaned transaction.


Database used by the session.


Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process.


CPU time used by session.


Disk IO used by session.


The last time a batch request was run by the session.


The application connecting to SQL Server.


SQL Server Session ID.


ID for requests running in a specific session.

From this list of output columns, we can see that the data provided by this system stored procedures is similar to the Processes pane in Activity Monitor and can be used in the same way. The difference is that the results from sp_who2 are only updated when you execute the procedure.

The sp_who2 system stored procedures also allow for parameters to be passed to it. If I wanted sp_who2 to only return results for active SQL Server Session IDs, I can use the ‘active’ parameter. The TSQL statement for this is:

USE master; 
EXEC sp_who2 'active'; 

If I wanted to only display results for a single SQL Server Session ID, then I could use the SPID parameter. The TSQL statement for this is:

USE master; 
EXEC sp_who2 51;  --Where 51 specifies the session_id;

Sadly, there is no sp_who2 parameter for returning results for a given user login. To do this, we need to use the sp_who system stored procedure. To get results for a single user login with sp_who, the TSQL statement is:

USE master; 
EXEC sp_who 'LOGIN NAME'; 


The second system stored procedure I find helpful when I’m not able to use Activity Monitor is sp_monitor. To understand why sp_monitor can be helpful, we must first understand that SQL Server tracks, through a series of functions, the work it has done. Executing the sp_monitor system stored procedure displays the current values returned by these functions and shows how much they have changed since the last time the procedure was run. Knowing how much work the SQL Server instance is doing can give a better understanding of the load being placed on the instance over a set period of time.

To run the sp_monitor system stored procedure, use the following TSQL statement:

USE master; 
EXEC  sp_monitor; 

The sp_monitor procedure returns a result set with the following information:

Column Name



Time sp_monitor was last run.


Time sp_monitor is being run.


Number of elapsed seconds since sp_monitor was run.


Number of seconds that the server computer's CPU has been doing SQL Server work.


Number of seconds that SQL Server has spent doing input and output operations.


Number of seconds that SQL Server has been idle.


Number of input packets read by SQL Server.


Number of output packets written by SQL Server.


Number of errors encountered by SQL Server while reading and writing packets.


Number of reads by SQL Server.


Number of writes by SQL Server.


Number of errors encountered by SQL Server while reading and writing.


Number of logins or attempted logins to SQL Server.

The output of sp_monitor is not returned is a standard single results set, but in 4 results sets. Normally they look like this:


As you can see from the output of sp_monitor, the information it provides is similar in nature to the information provided in the Overview pane of Activity Monitor, and it can be used in the same ways.

The output of sp_who2 or sp_monitor system stored procedures can be saved to a file for review at a later time. This can be helpful when working with a team or working on a performance issues over time. To save the output of any query in SQL Server Management Studio, simply right click in the query window then select Results To/Results to File.


Once that is done, each time the statement in the query window is run, Management Studio will ask for a location and file name to store the results in.

With Activity Monitor and the other tools provided by Management Studio, SQL Server provides a wealth of information and raw data related to what’s going on in any SQL Server instance. When you are not able to use Activity Monitor or want to keep a record of what’s happening in a SQL Server instance, the system stored procedures sp_who2 and sp_monitor can also provide a great deal of information.

I hope this blog series on Activity Monitor and related tools has been helpful. Please stay tuned for more SQL Server blogs in the future. If you have any questions or want to share your own SQL Server tips, please feel free to leave a comment below.