sp_whoisactive SQL Server Monitoring
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.
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.
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
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.
- 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
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:
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:
- Right click on Monitor-MSSQL-Instance-WhoIsActive.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
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
Check out other parts of this series:
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
Last Updated: 2020-11-30
About the author
View all my tips