Stay up to date with weekly industry insights

the latest trends in web design, inbound marketing, mobile strategy and more...

Using System Stored Procedures to Monitor Your SQL Server Instance

Monitoring_SQL_Server.jpg

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.

sp_who2

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; 
GO 
EXEC sp_who2; 
GO 

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

Column Name

Description

SPID

SQL Server Session ID

Status

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

Login name associated with the particular session.

HostName

Host or computer name the login is connecting from.

BlkBy

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.

DBName

Database used by the session.

Command

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

CPUTime

CPU time used by session.

DiskIO

Disk IO used by session.

LastBatch

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

ProgramName

The application connecting to SQL Server.

SPID

SQL Server Session ID.

REQUESTID

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; 
GO 
EXEC sp_who2 'active'; 
GO

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; 
GO 
EXEC sp_who2 51;  --Where 51 specifies the session_id;
GO 

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; 
GO 
EXEC sp_who 'LOGIN NAME'; 
GO

sp_monitor

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; 
GO 
EXEC  sp_monitor; 
GO 

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

Column Name

Description

last_run

Time sp_monitor was last run.

current_run

Time sp_monitor is being run.

seconds

Number of elapsed seconds since sp_monitor was run.

cpu_busy

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

io_busy

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

idle

Number of seconds that SQL Server has been idle.

packets_received

Number of input packets read by SQL Server.

packets_sent

Number of output packets written by SQL Server.

packet_errors

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

total_read

Number of reads by SQL Server.

total_write

Number of writes by SQL Server.

total_errors

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

connections

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:

sp_monitor_output.png

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.

Output_To_File.png

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.

About the Author

Dan Sales
Dan Sales
As WSOL’s VP of IT Operations, Dan supervises all of WSOL’s internal information technology systems, as well all hosting operations and support. He enjoys working with relational database management systems such as Oracle, MySQL, and SQL Server and is also interested in big data technologies such as Hadoop and Apache Hive. Dan has completed both development and administrative training for Ektron, EPiServer, and Sitecore, and he is certified as a developer and administrator in Ektron. He has attended the Ektron Synergy and Sitecore Symposium conferences, and he received a Certificate of Completion after attending the Big Data TechCon training conference. He was awarded several other honors while attending the University of Massachusetts, including Outstanding Business Information Systems Student and Business Student of the Year. He was also awarded an Army Commendation medal and five Army Achievement medals for meritorious service. Outside of the office, Dan enjoys target shooting, skiing, hacking, and spending time with Wicker, his Labrador retriever.