Logging Activity Using sp_WhoIsActive – Take 2

We’ve already covered how to log activity using sp_WhoIsActive, but I wanted to share how I’ve seen it done in the wild. The difference between the two methods is that this version logs everything to the same table and is for continuous activity logging (scheduled job), whereas the other version uses a new table each time it logs activity and is for when you are working on a problem.

WHAT IS IT?

If you don’t know about sp_WhoIsActive already, you’ve been living under a rock. It’s just the most awesome, free SQL Server tool there ever was. Adam Machanic, the author of sp_WhoIsActive, says it “is a comprehensive DMV-based monitoring script, designed to tell you at a glance what processes are active on your SQL Server and what they’re up to.”

I’m a fan of using free tools that can help me do my job more effectively. I’ve used sp_WhoIsActive even when I’ve also had paid tools monitoring production.

GET IT

You can download sp_WhoIsActive here.

SCHEDULE IT

Create a new job and plop the below code into the job step, modifying the first 3 variables as needed. The code will create the logging table if it doesn’t exist, the clustered index if it doesn’t exist, log current activity and purge older data based on the @retention variable.

How often should you collect activity? I think collecting sp_WhoIsActive data every 30-60 seconds is a good balance between logging enough activity to troubleshoot production problems and the storage needed to keep the data in a very busy environment.

You could also break the DELETE out into its own job so that it runs once a day. If you do this, loop through the DELETEs in batches rather than all at once.

ANALYZE IT

This is just a starter query. I generally am looking at a specific time period based on a complaint from a user that queries were running slow earlier in the day. I once used the WhoIsActive logging table to identify who was causing tempdb to grow absurdly big. By the time I got to it, tempdb was 500GB in size! I switched my query to include tempdb_allocations and tempdb_current plus ORDER BY tempdb_allocations DESC, and it was immediately clear a business user was querying ALL.THE.THINGS.

Previous Post
sp_BlitzFirst @SinceStartup = 1 Shows You Wait Stats Since, Uh, Startup
Next Post
Where Do Missing Index Requests Come From?

34 Comments. Leave new

  • I have my own version of this here

    http://thomaspullen.wordpress.com/2011/04/04/session-capture-system/

    that also includes pruning of old data, etc. Doens’t use sp_whoisactive but the same underlying DMVs

    Reply
  • Tara, I’d like a way to be able to gather a list of all usernames **and** applications that attach to a database over time. This data can be found for historic connections via something like:

    select * from
    ::fn_trace_gettable(‘D:\Apps\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_589.trc’,0) x
    INNER JOIN sys.trace_events e ON eventclass = trace_event_id

    ….however, the SPID column within this corresponds to the process id on the connecting client machine, so to find out the corresponding application, you need to reads the running application spid’s from the desktop client, which is obviously not a particularly easy thing to do.

    Are you aware of any existing implementations of this idea?

    Reply
    • The only way I’ve been able to figure this out is by requiring that all applications specify the “Application Name” parameter in the connection string. If they don’t specify it, you are going to see something generic, which is not helpful.

      The SPID column in a trace is not the process id on the client machine. It is the process id of the connection within SQL Server. But maybe I am not understanding what you mean.

      Reply
      • > The SPID column in a trace is not the process id on the client machine. It is the process id of the connection within SQL Server.

        That would certainly make more sense, but I swear when I checked to confirm what I thought it was, that it was indeed the client spid – will have to double check that I guess.

        Reply
  • Thanks for a great article, just one question
    Why is the first column a varchar(8000) and not a time?

    Reply
    • “Why is the first column a varchar(8000) and not a time?”

      Because it’s a formatted column designed for using SSMS as a GUI. (Why 8000? Probably an error on my part.)

      My recommendation for data capture is to use the @format_output = 0 switch, which outputs all of the numbers as actual numbers rather than text.

      Great job, Tara!

      Reply
    • Oh and why is it not the TIME data type? Well, two reasons: A) The proc in its current state is designed to be backward compatible to SQL Server 2005, and there was no TIME data type back then. And B) I don’t think I can fit the information into the TIME data type; what if a request has been running for over 24 hours?

      Reply
      • Do you have a copy of sp_whoisactive with all the the azure related stuff (msdb ect) commented out. The last one you posted (sp_whoisactive_v11_112.sql) still has it in there. I’m at a new job and really want to get it working in Azure. I am trying to install is on the USER database, not the master.

        Reply
  • Does anyone have a copy of sp_whoisactive with all the the azure related stuff (msdb ect) commented out. The last one Adam posted (sp_whoisactive_v11_112.sql) still has it in there. I’m at a new job and really want to get it working in Azure.

    Reply
  • From the result of sp_whoisactive, is there a way to convert the physical reads to MB?

    Reply
    • Sure, with your ol’ archenemy MATH, muhahaha. Pages are 8KB, so you can do some division to get the numbers you’re looking for.

      Reply
      • Thanks … but how many pages sql server reads in one physical read… I mean if the “sp_whoisactive” value of [physical_reads] is 12345 how many pages is that? so I will multiply it by number of reads then by 8kb … I think there is one parameter missing in this equation unless each read is equivalent to one page, and I don’t think so, ain’t?!

        Reply
  • There are a lot of &gt &lt in this, is this a browser thing? Which browser should I use, I’m using Chrome. thanks

    e.g. SET @schema = REPLACE(@schema, ‘<table_name>’, @destination_table);

    Reply
  • getting this error..
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘<'.

    Reply
  • […] approaches 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 […]

    Reply
  • while running it on SQL 2012 getting below error.

    Invalid object name ‘sys.dm_exec_query_statistics_xml’.

    Reply
  • It’s funny how many times I’ve set these captures up over the years, and how many times I have to figure it all out again. Thanks for keeping the blog and posts around Brent!

    Reply
  • Hi Tara, thanks for sharing this tool. I’m not able to get rid of the error ” Column name ‘collection_time’ does not exist in the target table or view. ” when the script run, using sp_whoisactive Version 11.32 SQL Server 2012. I was wondering if you know a fix to this issue. Thanks

    Reply
    • I have not seen that issue. I’ve got a client running version 11.32 and running the script in the blog post every minute. No issues with the job. Perhaps check that the table is getting created properly – sounds like you’re missing collection_time – though the script should work as is. Perhaps you changed the script a little? All that you need to change is the values for the variables – nothing else.

      Reply
  • Gladys Fernandes
    September 10, 2019 2:34 am

    how to track DML changes using sp_whoisactive?

    Reply
  • joris bernaerts
    October 22, 2019 6:25 am

    Hey it seems in the second query the columns in are referenced incorrectly. In the scripts start_time is incorrect since it should be starttime

    Reply
    • No, start_time is correct. I double checked it just now in the latest sp_WhoIsActive in case Adam changed the column name. It’s still start_time. Adam’s naming convention is lower case and underscores to separate words. I don’t see a single starttime in sp_WhoIsActive when I use CTRL+F.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}