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.
