5 common Problems to Troubleshoot with sp_WhoIsActive in SQL Server

If there were a Mount Rushmore of SQL Server community tools, Adam Machanic would have to be one of the four faces. You may not know his name, but you more than likely know the free troubleshooting tool he created over a decade ago: sp_WhoIsActive. Fix your performance problems, fix your blocking, fix a bunch.

The beauty of WhoIsActive is in its simplicity. Install this one stored procedure in your master database, and any time someone tells you “the database is slow” you can execute it and get a plethora of information from system dynamic management views about who is running what, what resources are being consumed, how long they’ve been running, and more.

Unfortunately, the simplicity may also be a bit of a curse, as many data professionals don’t execute sp_WhoIsActive with any of the many helpful parameters that are built in. Which is unfortunate, because typing a few extra characters can save a lot of time scrolling through results, especially when there are hundreds of active queries on your instance.

Let me save you some time by showing you how much easier it is to examine specific issues by incorporating just a handful of these helpful parameters. Here are five situations I encounter all the time, and how I use WhoIsActive to identify the root cause.

What’s really happening right now?

EXEC sp_WhoIsActive
 @output_column_list = '[dd%][session_id][%delta][login_name][sql_text][%]'
 , @delta_Interval = 5;

By default, the WhoIsActive procedure will give you a lot of information about reads, writes, CPU usage, tempdb usage, and memory usage. The thing many folks don’t consider when examining the output is that these are all cumulative values, meaning they reflect the total amount of resources used since the session started. Without factoring in how long the session has been using resources, you can end up looking at the wrong sessions as the cause of whatever problem you are trying to troubleshoot.

SP_WhoIsActive has an @delta_interval parameter to help with this. It represents a sample rate in seconds, and returns extra columns with a “_delta” suffix to show the amount of resource usage during the sample. The variable above represents a 5-second sample, so you can more accurately understand what is causing the pain.

As you may notice, we’re also using the @output_column_list parameter. This allows us to rearrange the order of the output columns so we can see the data we’re most interested in without having to scroll around. Note three important things about this:

1. Column names are enclosed in brackets, and they are NOT comma separated.
2. A “%” wildcard is used before or after column names to include more results or reduce typing.
3. The final “[%]” wildcard all by itself indicates we want to return all remaining columns in the default order.

We will use the @output_column_list throughout this post. It’s a great alternative to scrolling left and right to find the information you want in hurry.

What filled up the transaction log?

EXEC sp_WhoIsActive
 @output_column_list = '[dd%][session_id][tran%][login_name][sql_text][%]'
 , @get_transaction_info = 1;

Ever had a transaction log fill up and wanted to know what the culprit was? Here’s a quick way to find the answer. By using @get_transaction_info = 1 we will get two additional columns returned: tran_start_time and tran_log_writes. The latter is the main one here, as it will contain a database name, the number of log records written, and the size in KB of those log records.

One additional thing to note is that if the session is using log files in more than one database, they will all be represented in the output.

What’s using the most memory?

EXEC sp_WhoIsActive
 @output_column_list = '[dd%][session_id][%memory%][login_name][sql_text][%]'
 , @get_memory_info = 1;

Last year there were updates to WhoIsActive, so you need to make sure you are running version 12.00 or higher to enjoy this feature. Using @get_memory_info = 1 will return five additional columns.

• Used_memory
• Max_used_memory
• Requested_memory
• Granted_memory
• Memory_info

That last column is a clickable XML column, which will have additional information. This is helpful if you are experiencing RESOURCE_SEMAPHORE waits, as it will show wait_time_ms, queue_id, wait_order, and is_next_candidate. You can see which queries are waiting for memory.

Even without RESOURCE_SEMAPHORE waits, the other columns can still be helpful, as you can see if one or two queries are requesting or granted a significant portion of memory, and if there is any disparity between the amount requested and the amount used.

What filled up tempdb?

EXEC sp_WhoIsActive
 @output_column_list = '[start_time][session_id][temp%][sql_text][query_plan][wait_info][%]'
 , @get_plans = 1
 , @sort_order = '[tempdb_current] DESC';

I’ve seen times where one poorly written query, usually selecting too much data and trying to sort it all, ends up consuming all the space in your tempdb. This can cause the data files to grow and use all available space on their assigned drives, which is usually not a good situation. If this happens to you, you can run sp_WhoIsActive with @sort_order as noted above to make the largest consumer of tempdb appear in the first row. Where @output_column_list can reduce your scrolling left and right, @sort_order can keep you from having to scroll up and down if you have dozens or even hundreds of active queries.

This execution is also using @get_plans = 1, which will include a “query_plan” XML column in the results. You can click on that to review the query plan for any problematic query and see about pinpointing the cause of the tempdb usage. Note that query plans are not included in the default results of WhoIsActive, as they can be quite large and may delay the results.

Is there blocking?

EXEC sp_WhoIsActive
 @output_column_list = '[start_time][session_id][block%][login%][locks][sql_text][%]'
 , @find_block_leaders = 1
 , @get_locks = 1
 , @get_additional_info = 1
 , @sort_order = '[blocked_session_count] DESC';

If you are looking into slow response times for queries and you see anything populated in the “blocking_session_id” column, then you likely have delays caused by blocking. If you have more than a handful of queries, it may be difficult to navigate the default results to find the lead blocker.

Fortunately, there’s a parameter for that as well. With @find_block_leaders = 1, sp_WhoIsActive will return a “blocked_session_count” column that will show the number of other sessions being blocked by each session. We can then use @sort_order to sort the results from most to least, so that lead blockers appear at the top.

We can also use @get_locks = 1 to return an XML column named “locks” that shows the locks taken by each session. This is helpful for telling us why blocking is occurring, as we can click on the output in “locks” to see which objects are locked and related to the blocking.

When troubleshooting blocking, it’s also useful to use @get_additional_info=1, as this includes other columns related to locks, such transaction_isolation_level and deadlock_priority. The more information you have, the better you can understand why the blocking is occurring, and hopefully how to resolve it.

Summary

This is just scratching the surface, as there are many other helpful parameters to use with sp_WhoIsActive. If you want to learn more, you can review the official WhoIsActive documentation. If you don’t like reading, I have a course on Pluralsight showing more ways to use WhoIsActive. Regardless of your learning preference, I highly encourage you to learn more about how you can use this wonderful tool to help make your life with SQL Server much easier.