Using sp_whoIsActive with DELL Spotlight on SQL Server

Script it…then walk away…

So…I missed a February post, but to be fair, I had an article published on Simple-Talk.

In an attempt to not miss March (although I’m working on part 2 of that article), I thought I’d throw a little something out there for anyone using Spotlight and sp_whoIsActive

One thing that can drive management and almost anyone insane is knowing there was a problem, but not having any information about the problem. What are you supposed to do…sit at the computer all day waiting for a problem to occur? Call your consultant every time something happens and hope that he/she can respond in time? Probably…no, and none of the above. This is part of what makes monitoring software such as Dell’s Spotlight on SQL Server so helpful. It watches the computer for you and in some cases it can gather the information that your consultant needs to help diagnose and fix problems.

If you recall back to my post , we looked at a way to raise errors and execute a script in order to gather helpful information during the time of the problem. This post is similar except it utilizes a very well known utility, Adam Mechanic’s sp_whoIsActive.Things you will need:

1. sp_whoIsActive
2. A database called DBA (for storing all your DBA stuffs)
3. Spotlight on SQL Server
4. …your mits…

This idea can also be adapted to native events rather easily.

1. First, go to Configure->Alarm Actions. Create a new alert
2. Second choose when you want sp_whoIsActive to be captured. I personally enjoy using sp_whoIsActive to identify blockers. So in this case I want to alarm to be fired when a lock happens. After you have selected your desired alarm, go ahead and select “Exectue a SQL Script” and “Send email” in the action to perform section. I like to get an email when information has been captured, but this could end up getting messy depending on when the alarm is triggered.
3. Finally, insert the following TSQL to execute.

EXEC [DBA].dbo.sp_WhoIsActive
@get_outer_command = 1,
@get_transaction_info = 1,
@get_plans = 1,
@find_block_leaders = 1,
@DESTINATION_TABLE = 'DBA.dbo.WhoIsActive';

The above code will simply execute sp_whoIsActive and save it to a table called…WhoIsActive in the DBA database. You will need to ensure this table is created and come up with your own housekeeping.

Once this is complete, wait, or make, some locks and see what happens. Spotlight will send an email letting you know information was captured and you can query your WhoIsActive table for historical information.