How to Log Activity Using sp_whoisactive in a Loop
What is sp_whoisactive?
Sp_whoisactive is a procedure written by Adam Machanic. Download sp_whoisactive at whoisactive.com.
Getting Started with sp_whoisactive
If you haven’t used the stored procedure before, get familiar with it first. Start here!
Logging Results from sp_whoisactive
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 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 we 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. Integrate this into your First Responder Kit.
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
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 flags to get transaction info (tlog write info and duration) and query plans:
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, @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, @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