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,
@RETURN_SCHEMA = 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.

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

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

DECLARE @numberOfRuns INT ;
SET @numberOfRuns = 10 ;

WHILE @numberOfRuns > 0
    BEGIN;
        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
            BEGIN
                SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' +
                 'Logged info. Waiting...'
                RAISERROR(@msg,0,0) WITH nowait ;

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

    END ;
GO

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

Tags: , , ,

About Kendra Little

Kendra specializes in high availability and performance tuning. She is a Microsoft Certified Master in SQL Server-- the highest technical SQL Server Certification available. Kendra loves databases and software development more than long walks on the beach. Those cartoons in her blog posts? She draws 'em all. You should follow Kendra on Twitter: http://twitter.com/kendra_little

22 Responses to “Be Prepared: Collecting Data from sp_WhoIsActive in a Table”

  1. Subhash Pant February 1, 2011 at 11:50 am #

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

    ….
    more..

    • Kendra Little February 1, 2011 at 2:03 pm #

      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.

  2. Shahryar G. Hashemi February 4, 2011 at 8:10 am #

    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.

  3. Bob Pusateri February 15, 2011 at 7:10 am #

    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!

  4. senthil April 12, 2011 at 10:45 am #

    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

    • Kendra Little April 12, 2011 at 11:03 am #

      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.

  5. senthil April 14, 2011 at 12:10 am #

    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.

  6. Tim Magney May 2, 2011 at 12:18 pm #

    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?

    Thanks!
    Tim

  7. Tim Magney May 2, 2011 at 12:36 pm #

    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 May 4, 2011 at 7:46 am #

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

  8. John A Davis November 16, 2011 at 5:15 pm #

    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.

  9. Dan Budiac November 24, 2011 at 5:27 am #

    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?

  10. Everett Music January 17, 2012 at 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.

    • Kendra Little February 3, 2012 at 3:19 pm #

      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.

  11. SGT_SQueequal November 20, 2012 at 4:09 am #

    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

  12. Zoid November 25, 2012 at 2:09 pm #

    Kendra rocks!!!

  13. Anup Shah November 14, 2013 at 12:46 pm #

    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?

    Thanks,

    Anup

    • Kendra Little December 10, 2013 at 7:21 am #

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

Trackbacks/Pingbacks

  1. SQL Server DBA Scripts: How to Find Slow SQL Server Queries | Brent Ozar PLF | Brent Ozar PLF - July 5, 2011

    [...] 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 [...]

  2. Something for the Weekend – SQL Server Links 04/02/11 - March 5, 2012

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

Leave a Reply