Collecting sp_whoisactive data from multiple servers into a single server

An accurate depiction of sp_whoisactive awesomeness

Needless to say that I’m a huge fan of Adam Machanic’s procedure sp_whoisactive. It saved my ass a few times and still is a relevant tool for any SQL DBA. In this short article, I’ll fiddle around with the procedure to add a column, create a small repository database and setup a Powershell script to collect data from multiple servers. Let’s do this.

Create a repository database

CREATE DATABASE SQLWIA;

GO

USE SQLWIA;

GO

CREATE TABLE SQLWIA_Servers

([servername] VARCHAR(256) NOT NULL,

[isactive] BIT NOT NULL

);

GO

INSERT INTO SQLWIA_Servers

SELECT ‘SERVSQL1’,

1;

GO

INSERT INTO SQLWIA_Servers

SELECT ‘SERVSQL2’,

0;

GO

INSERT INTO SQLWIA_Servers

SELECT ‘DEVSQL3’,

1;

GO

2. Add @@SERVERNAME as a column to sp_whoisactive

I couldn’t find an easy way to add @@SERVERNAME as a column to sp_whoisactive so I’ll go commando and change a few lines. Please note that I’ve used v11.17 for this example. And yeah, it totally screws the maintainability of the code because you would have to modify the procedure every time a new version comes out. Wish this could be added to the official procedure ;)

3. Create/replace the modified sp_whoisactive procedure on all the SQL instances (duh)

4. IT’S POWERSHELL TIME BOIS

Now the fun begins. This simple script uses sqlserver PS module and Powershell 5.0 to loop through the SQL instances in our SQLWIA_Servers table, run sp_whoisactive and BULK insert everything in a table called SQLWIA_Activity. Maybe I should add a TRY/CATCH and some logging ( ͡° ͜ʖ ͡°)

$SQLRepo = "localhost"

$SQLUser = "monitoringusr"

$SQLPwd = "neatpassword"

$tablename = "SQLWIA_Activity"

$instances = Invoke-SqlCmd -Database SQLWIA -Server $SQLRepo -User $SQLUser -Password $SQLPwd -Query "SELECT DISTINCT servername FROM SQLWIA_Servers WHERE isactive = 1" | SELECT -expand servername

$queryRetrieve = "EXEC master.dbo.sp_WhoIsActive @find_block_leaders = 1, @get_transaction_info = 1, @get_plans = 1, @show_sleeping_spids = 1" foreach($row in ($instances))

{

Invoke-Sqlcmd -ServerInstance $row -User $SQLUser -Password $SQLPwd -Query $queryRetrieve -OutputAs DataTables |

Write-SqlTableData -ServerInstance $SQLRepo -DatabaseName SQLWIA -TableName $tablename -SchemaName dbo -Force

}

6. Set up a SQL job/scheduled task

Set up a recurring job with two steps. The first step will execute the Powershell script that we’ve just created and the second step should be a cleanup task. Something like this:

USE SQLWIA;

DECLARE @RetentionDays INT;

SELECT @RetentionDays = 5;

DELETE a

FROM SQLWIA_Activity a

WHERE DATEDIFF(day, collection_time, GETDATE()) > @RetentionDays;

Don’t forget to create a non-clustered index to help your poor SQL repository.

CREATE NONCLUSTERED INDEX IX_SQLWIA_Activity_1 ON SQLWIA_Activity (collection_time)

Voilà!

There are many possible improvements. PAGE Compress the table if it’s getting too big (specially if you’re storing query plans), use parallel ForEach loops in the Powershell script to increase performance.

In the next article, we’ll create neat SSRS reports to visualize all the gathered data and impress your boss. Feel free to comment and improve these scripts.

Stay tuned!