Useful Tools: sp_whoisactive

One incredibly useful SQL Server tool that I often forget about is Adam Machanic’s sp_whoisactive procedure.  The reason I say I forget about it is that it is not something that I use every day, it’s one of those tools that sits quietly in the background waiting until you need it.  But believe me, when the time comes that you need it, YOU NEED IT!  It gives you a way to see what queries and stored procedures are currently running, and helps identify slow running queries and blocking (and more).

To quote one of the posts about sp_whoisactive on Adam Machanic’s blog:

here are some quick facts on Who is Active:

• Who is Active is a DMV-based monitoring stored procedure that uses 15 different views to show a large amount of data about what’s running on your server
• Who is Active was designed to be extremely flexible, and includes options to not only get different types of data, but also to change the output column list and sort order
• Who is Active was designed with performance in mind at every step; users report that under normal conditions response times are generally subsecond, with slightly longer response times on servers that are extremely taxed
• Who is Active is compatible with all versions of SQL Server after SQL Server 2005 RTM. It does require that the host database (generally master) is not set for SQL Server 2000 compatibility mode

Adam mentions in his release of version 11.11 that it will be the last release that is compatible with SQL Server 2005 and 2008.  Future versions will begin taking advantage of several SQL Server 2012 features.  It also appears to work on Azure SQL Database.

If you’re not already using sp_whoisactive, download it and get it on your servers. It is a great troubleshooting tool that you’ll be glad you have!

07.27.2017 – Edited to link to Adam’s new sp_whoisactive website.

This is part of a series of Getting Started posts that I’m doing in which I spotlight the tools I use in my day to day duties as a DBA.  I asked myself “What tools would I immediately install if I started a new job as a company’s first DBA?”  This series of posts are the answer to that question.