Be Prepared: Collecting Data from sp_WhoIsActive in a Table

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

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.

What’s sp_WhoIsActive?

Sp_whoisactive is a procedure written by Adam Machanic. It can be found at

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

33 Comments. Leave new

  • 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.


    • 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.

  • 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.

  • 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!

  • Hi
    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

    • 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.

      • I washed ashore here on this older blog post after I ran into this same issue, but good news is, Adam M.’s new version (11.17) claims to have fixed the infinite recursion bug, in case anyone else runs into it with find_block_leaders.

  • 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.

  • 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?


  • 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. 🙂

    • 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.)

  • […] How to ollect sp_WhoIsActive results into a table over time for troubleshooting history Brent OzarBrent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.Website – Twitter – Facebook – More Posts 39 Comments – Leave a comment! « Previous PostNext Post » 39 Responses to SQL Server DBA Scripts: How to Find Slow SQL Server Queries […]

  • 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.

  • 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
    January 17, 2012 9:34 am

    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.

  • […] Be Prepared: Collecting Data from sp_WhoIsActive in a Table […]

  • 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

  • Kendra rocks!!!

  • 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?



  • 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

  • […] Collecting Data from sp_WhoIsActive in a Table is a good technique for tracking down blocking and locking issues. […]

  • […] If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little. […]

  • […] LCK_M_U indicates that someone somewhere is waiting for an update lock. This particular system has a very high Read ratio versus writes, and most writes are done during nightly update tasks. As such, this wait type was not expected and further analysis was needed. I found an old article by Kendra Little describing how to collect data using  sp_WhoIsactive: […]

  • […] You can check current activity in the database with Adam Machanic’s sp_WhoIsActive. […]

  • […] This can be used to log information to a table – […]

  • Hi Kendra, how can I export the xml column (sql_text) of the table to Excel? I want show to the users your bad queries!. but when I do copy+paste for several lines, the format is lost.

    Thanks for your help.

  • […] have their merits, and neither is that uncommon.  However, the latter is much more regularly blogged about, so I’m going to present the former, because it kinda helped remind me of a few things […]

  • Still a valid attempt today.
    The script snippets shown need the &[email protected]@amp …. things replaced before executing them.
    I am currently working on implementig this via SQLAgent to track down an over-intense tempdb usage ….


Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: