Updated January 26, 2022.
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.
Reports
The report page Who Is Active has a table showing who is, or maybe 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].
The fact view [Who is active] in the database contains all values from the table dbo.fhsmWhoIsActive, but as good practice dictates it, it is only the values used that are loaded into Power BI report.
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.
