How to Log Activity Using sp_whoisactive

What is sp_whoisactive?

Sp_whoisactive is a procedure written by Adam Machanic, and it replaces tools like sp_who, sp_who2, and Activity Monitor. Download sp_whoisactive at whoisactive.com, and Adam’s got a lot of good documentation over there too.

If you haven’t used the stored procedure before, get familiar with it first. Start here!

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.

How to Schedule sp_whoisactive in an Agent Job

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.

How to Query sp_whoisactive’s Results

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.

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