sp_whoisactive SQL Server Monitoring

By: Alejandro Cobar | Comments | Related: > Monitoring

Problem

Throughout our career as SQL Server DBAs, we are often faced with the classic scenario "it seems there are performance issues with the database". Well, there are times when the issue isn't the database server, but when it is, wouldn't you like to have a way to capture exactly what's running at a certain time frame for post-analysis? Well, you might think about setting up a trace, extended events, or even examining the plan cache to see if you can find the culprit, but doing this for a specific set of instances at the same time can be a bit challenging to achieve. Regardless of that, a tool I like to use for quickly checking "what's going on in my instance" is the stored procedure sp_whoisactive. In this tip, I'm going to present you a custom data collection mechanism that you can very easily setup to monitor what's going on in one or a set of instances, using the power of sp_whoisactive.

Solution

The main goal of the PowerShell script in this article is to have a mechanism to automate the data collection process of queries currently being executed (those long enough to be captured by sp_whoisactive of course), in one or several instances, and store this information in a central database for post-analysis. This is basically one more monitoring module from the series of modular monitoring scripts I have been contributing to the MSSQLTips community.

sp_whoisactive Prerequisites

In order to use this module, you'll need the following:

Create the core objects found here
Have sp_whoisactive already deployed in the instances you want to target. You can do this either manually or you can use the module called MSSQL-Instance-Object-Deployer that you can find in this article.
Here's a quick link to download the stored procedure: sp_whoisactive

Database Objects

For this particular module, only one table will be created (in case it doesn't exist), and it is the table to store the information of the execution of the sp_whoisactive stored procedure, from each instance.

If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field.

Table

monitoring.WhoIsActive
- serverId: id that ties back to inventory.MasterServerList
- dd hh:mm:ss.mss: the timestamp value that indicates how much time a query has been running
- session_id: the session id from where the query is being executed
- sql_text:the actual TSQL statement being executed
- login_name: the name of the login that triggered the query
- wait_info:the information on what the query is waiting on (if it's not waiting for anything this value is NULL)
- blocking_session_id:iif there is any blocking, this field will state the session id of the query that is still running and that must finish for the actual blocked query to run
- reads: the number of logical reads (in 8KB pages) performed by the query
- writes:the number of writes performed by the query
- physical_reads:the number of physical reads (in 8KB pages) performed by the query
- CPU:the CPU time, in milliseconds, that the query has been consuming so far
- used_memory:the amount of memory the query has been using so far
- status:the current status of the query (sleeping, dormant, runnable, suspended, etc.)
- host_name:the hostname from where the query was triggered
- database_name:the target database where the query will be executed
- program_name:the name of the application that triggered the query
- start_time:the timestamp value when the query was triggered
- data_collection_timestamp:the timestamp value when the record was fetched from the instance

Important Note:

There will be times when you simply don't want to collect this information from all the registered instances you have in your Master Server List. To address this, simply modify the query text inside the variable $instanceLookupQuery within the PowerShell script and you should be good to go.

PowerShell Script for sp_whoisactive

The PowerShell script that collects the information from each instance is called:

Monitor-MSSQL-Instance-WhoIsActive.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to use sp_whoisactive in SQL Server

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Monitor-MSSQL-Instance-WhoIsActive.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Monitor-MSSQL-Instance-WhoIsActive.ps1"

Database Objects Deployment

Here are the database objects that you should see in your central database after executing the PowerShell script:

sp_whoisactive Sample Output

To show you an example of the output of the data captured by the PowerShell script, I have faked a bit of activity in one of my instances by running a simple WAITFOR DELAY '0:05' and this is what it looks like after querying the monitoring.WhoIsActive table:

I have selected just a few columns to make the image easier to read, but the real table has all the fields described above.

As you can see, I have 2 entries for the exact same query, but it is because I executed the script 2 times. Depending on your own use case, you might want to truncate the table each time you execute the script, or just keep the most recent query execution captured per session id.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Monitor-MSSQL-Instance-WhoIsActive'

If the instance(s) you are targeting doesn't have the stored procedure deployed, then you will see something like this in the monitoring.ErrorLog table:

If you'd like to know the SQL Server instance that had errors, you would have to issue the query like this:

SELECT   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
CONCAT(msl.server_name,'\',msl.instance) END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Monitor-MSSQL-Instance-WhoIsActive'

sp_whoisactive Useful Queries

Order the result set by queries that have consumed the CPU resources from longest to lowest time:

SELECT *
FROM monitoring.WhoIsActive
ORDER BY CONVERT(BIGINT,REPLACE(CPU,',','')) DESC

Order the result set by queries that have performed the most writes:

SELECT *
FROM monitoring.WhoIsActive
ORDER BY CONVERT(BIGINT, REPLACE(writes,',','')) DESC

Order the result set by queries that have a memory usage from highest to lowest:

SELECT *
FROM monitoring.WhoIsActive
ORDER BY CONVERT(BIGINT,REPLACE(used_memory,',','')) DESC

Search for queries that contain a certain string:

SELECT *
FROM monitoring.WhoIsActive
WHERE CONVERT(VARCHAR(MAX),sql_text) LIKE '%X%'

Order the result set by queries that have been executing from longest to lowest time:

SELECT *
FROM monitoring.WhoIsActive
ORDER BY [dd hh:mm:ss.mss] DESC

Download sp_whoisactive Scripts

Download the scripts for this module

Next Steps

Check out other parts of this series:

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips