SQL Server Monitoring – Who is active

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To provide a wrapper around the Who Is Active solution made by Adam Machanic.

Sources

When executing the stored procedure sp_WhoIsActive the result of who is active at the time of execution is stored in the table dbo.fhsmWhoIsActive.

Report

The report has a table showing who is, or more correctly, who was active the last 24 hours, helping to e.g. understand blocking situations.

Model

The model for the report uses the fact [Who is active] and the dimensions [Database].

Configuration

To enable sp_WhoIsActive to store its result into a table the following parameter string must be given and where the string <db name> is configured at the time of installation:

@format_output = 0, @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = ‘<db name>.dbo.fhsmWhoIsActive’

Scheduling

Every minute throughout the day.

It is rapid execution but sp_WhoIsActive is well known for being lightweight.

Retention

The default retention for the table dbo.fhsmWhoIsActive is 7 days.