Be Prepared: Collecting Data from sp_WhoIsActive in a Table

I am a fangirl of Adam Machanic‘s sp_whoisactive stored procedure.

Be prepared. When things go wrong, you want to focus on the problem itself. Make a plan for how you

One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented (see Adam’s post here), but I’ve chatted with several people who either didn’t know you could do this, or didn’t have a script saved.

What’s sp_whoisactive?

Sp_whoisactive is a procedure written by Adam Machanic. It can be found here: sp_whoisactive downloads

What to Do Today

Do your future self a favor: Today, set yourself up the following:

  • Create the sp_whoisactive stored procedure in a user database you use only for administration
  • Play with the script I provide below and look at the ¬†parameters of the stored procedure
  • Save a script which creates a table for your preferred version of sp_whoisactive, then runs in a loop to store the data.

Why Do This?

Even if you’re already running monitoring tools, using sp_whoisactive to sample data from the DMVs (including query plans) can be invaluable.

When things go wrong, terribly wrong, you want to have this script in your back pocket to collect data about activity on your SQL instance in the background while you’re focusing on the problem. This will help you resolve the issue as quickly as possible, by referring periodically to the data it’s collecting. Since the data is stored, you can also use it afterward to explain the situation and prevent it from happening again.

Step 1: Create Your @destination_table

Let me just note again: this is not my original work. I don’t want to take credit for it, but I do want to advocate for it!

First, create your destination table. Sp_whoIsActive lets you generate a destination table easily. Here is a basic version which includes the current date in the table name, as well as three flags to get transaction info (tlog write info and duration), query plans, and block leaders:

DECLARE @destination_table VARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;

DECLARE @schema VARCHAR(4000) ;
EXEC sp_WhoIsActive
@get_transaction_info = 1,
@get_plans = 1,
@find_block_leaders = 1,
@SCHEMA = @schema OUTPUT ;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table) ;

PRINT @schema
EXEC(@schema) ;

Step 2: Create Your Loop to Periodically Log Data

You need to need to make sure to specify the same parameters for sp_whoisactive in this loop, so the output matches the schema for the table you’ve created.

This does 10 runs with a 5 second wait between runs. Depending on what’s going on, I change those numbers accordingly.

    @destination_table VARCHAR(4000) ,
    @msg NVARCHAR(1000) ;

SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;

DECLARE @numberOfRuns INT ;
SET @numberOfRuns = 10 ;

WHILE @numberOfRuns > 0
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
            @find_block_leaders = 1, @DESTINATION_TABLE = @destination_table ;

        SET @numberOfRuns = @numberOfRuns - 1 ;

        IF @numberOfRuns > 0
                SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' +
                 'Logged info. Waiting...'
                RAISERROR(@msg,0,0) WITH nowait ;

                WAITFOR DELAY '00:00:05'
                SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Done.'
                RAISERROR(@msg,0,0) WITH nowait ;

    END ;

Step 3: Set Up Your Query to Look at the Results

This is optional, but since the table is named by datestamp, I like to keep a query on hand to look at the results without having to copy and paste the table name. I also like to put the collection_time column first and sort by it.

DECLARE @destination_table NVARCHAR(2000), @dSQL NVARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;
SET @dSQL = N'SELECT collection_time, * FROM dbo.' +
 QUOTENAME(@destination_table) + N' order by 1 desc' ;
print @dSQL
EXEC sp_executesql @dSQL

, , ,

  • Subhash Pant

    Got some error messages, although it did the logging. This is in SQL Server 2008 R2 with the latest WhoIsActive available from Adam Machanic:

    Warning: The join order has been enforced because a local join hint is used.
    Warning: The join order has been enforced because a local join hint is used.
    Warning: Null value is eliminated by an aggregate or other SET operation.
    2011-02-01 13:46:11: Logged info. Waiting…
    Warning: Null value is eliminated by an aggregate or other SET operation.
    2011-02-01 13:46:17: Logged info. Waiting…
    Warning: Null value is eliminated by an aggregate or other SET operation.


    • Kendra Little

      Hi Subhash,

      Those warnings are OK. The join order warning just means that a join hint was used in sp_whoisactive. If you look at the source, you’ll see things like ‘LEFT OUTER HASH JOIN’ is used– the engine is just alerting you of that.
      The null value warning occurs if you have null values in a column and are using an aggregate/group by.
      Both of them can be ignored safely.

      The logging messages with the timestamp I just added so if you’re running a long loop, you can see things printing on the message tab. It’s just a visual indicator that the loop is not hung, in case that’s ever helpful.

  • Shahryar G. Hashemi

    Great stuff Kendra. I do have a suggestion. Why not make it simpler by creating 1 destination table [as long as the input to the SP never changes] and then partition the table. I would suggest a DAYOFYEAR column added at the end and create a partition function/scheme based on 366 days [including LEAP].

    You can also go further and compress the destination table [PAGE] if you are on SQL 2008 + since it is an APPEND only table.

    Just some ideas.

  • Bob Pusateri

    Thanks so much – this was a huge help!

    I got a request yesterday and figured it would be a good excuse to try this out and it definitely did the trick!

    • Kendra Little

      Awesome! I’m glad it was helpful.

  • senthil

    I am investigating blocking issues on the server. This works fine when there is no blocking on the server. But when there is blocking I get an error stating

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    when I execute the scripts

    – Senthil

    • Kendra Little

      I feel your pain– when you have a lot of blocking, that can happen. It’s related to the way that whoisactive finds block leaders– recursion has its limits.

      If you work on a system where the blocking can overload this value, you can set up your table and run your loop with @find_block_leaders set to 0.

      The good news: You’ll still get the blocking_session_id column and it’s data. You just won’t get the blocked_session_count column.

  • senthil

    Thanks Kendra. I am investigating to find out the issue of blocking and it is pretty bad that I could not take a dump from the system which will really assist in debugging. But I guess your suggestion should help me out. Thanks a lot.

  • Tim Magney

    Hi Kendra,

    We’ve been running WhoIsActive in a loop via a sql job on about a half dozen of our production servers. I have it set up to run at one minute intervals until a specified endtime is reached. On most servers this runs from 6:00 AM to 2:00 PM. It’s been running seamlessly for almost two months now, but this past weekend we hit up on the issue that Senthil mentioned. The job has failed multiple times on one of our servers due to the “maximum recursion” error mentioned above.

    I’ve taken a look at the results that were logged, and there appears to be very little blocking happening on the server based on what I’m seeing in the [blocking_session_id] and [blocked_session_count] columns. Any ideas for where else I should be looking?


  • Tim Magney

    p.s. after I posted my comment/question, it occurred to me that maybe I should be directing this question to Adam. But any thoughts you have are appreciated. :)

    • Kendra Little

      Hey Tim! What’s the full set of options you’re using with sp_whoisactive? (Not sure if you’re using the exact set from the post or if it might be slightly different.)

  • Pingback: SQL Server DBA Scripts: How to Find Slow SQL Server Queries | Brent Ozar PLF | Brent Ozar PLF()

  • John A Davis

    Dang! I wish I had a database that wasn’t working well. Can’t seem to find much trouble. sigh
    Got this saved as a script ready to go.

  • Dan Budiac

    How resource-intensive is sp_whoisactive to run in this fashion? Is there any reason why I couldn’t let it collect data for several hours, or even a day?

  • Everett Music

    First: Thanks for taking the time to post this.

    My only request is that when posting code, it would be useful for those of us in a case sensitive environment if the code being posted followed the case of the sp.

    Again, thanks.

    • Kendra Little

      Thanks for your comment! That’s a good point– I’m a little too used to the case insensitive world. I’ll work on that.

  • Pingback: Something for the Weekend – SQL Server Links 04/02/11()

  • SGT_SQueequal

    HI, great post here, WHOISACTIVE working fine for me however, i have set some setting somehow that means when i click the execution plan it opens up the full XML plan instead of the graphical plan anyone know what i have done? or help change it back cheers

  • Zoid

    Kendra rocks!!!

  • Anup Shah

    Hi Kendra,

    One of the user wants to execute ” sp_whoisactive @find_block_leaders =1, @get_locks =1” on Dev and User servers but he is getting error message:

    Warning: The join order has been enforced because a local join hint is used.
    Msg 297, Level 16, State 1, Procedure sp_WhoIsActive, Line 1284
    The user does not have permission to perform this action.
    Msg 1088, Level 16, State 12, Procedure sp_WhoIsActive, Line 1664
    Cannot find the object “#locks” because it does not exist or you do not have permissions.

    However, he can run successfully on Production server. I tried to search online but didn’t find anything as I am not Developer.

    Can you please provide solution?



    • Kendra Little

      Sorry I missed this. For this permissions issue, compare the user’s permissions between the two environments.

  • pokiri

    I have captured sp_whoisactive for few days and I was trying to gather the blocking information out of it, where I wanted details for session which is blocker and which got blocked. After some research and time spent I was able to get this query which seem to work well.

    Please check and let me know if this query returns the results as expected or any issues you see in this query.

    select collection_time,blocking_session_id, * from [dbo].[WhoIsActive_Output] A
    where blocking_session_id is not null
    session_id in
    (select blocking_session_id from [dbo].[WhoIsActive_Output] B where blocking_session_id is

    not NULL
    and SUBSTRING(CONVERT(varchar, A.collection_time, 121),1,19)=SUBSTRING(CONVERT(varchar,

    B.collection_time, 121),1,19))
    –and collection_time > ‘2014-04-27 23:35:00’
    order by 1 desc

  • Pingback: Investigating high SQL waits, especically LCK_M_U | XL-UAT()

  • Pingback: Usage of temporary tables in my tempdb database on sql server 2005 [duplicate] | XL-UAT()

  • Pingback: BlackCat Research Facility · LCK_M_U Wait troubleshooting()

Powered by WordPress. Designed by WooThemes