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.

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.

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.

Model

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

Service setup

The feature uses the service ‘fhsmSPWhoIsActive’ which can be configured using the stored procedure fhsmSPControl.

Configuration

The default range for rows returned by fact [Who is active] is 1.000 rows and rows up to 24 hours old.

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'list',
	@Key = 'View.WhoIsActive';

Set range of rows returned to custom values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.WhoIsActive.Hours',
	@Value = '12';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.WhoIsActive.Rows',
	@Value = '500';


Reset range of rows returned to default values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.WhoIsActive.Hours',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.WhoIsActive.Rows',
	@Value = NULL;

Parameter

The default parameter to configure sp_WhoIsActive to store its result into a table is:

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

When the service executes it replaces the placeholder string <FHSQLMonitorDatabase> with the database name used by the installation.

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'list',
	@Task = 'fhsmSPWhoIsActive',
	@Name = 'Who is active',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPWhoIsActive',
	@Name = 'Who is active',
	@Parameter = '@format_output = 0, @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = ''<FHSQLMonitorDatabase>.dbo.fhsmWhoIsActive''';

Schedule

The default schedule for the feature is every minute (60 sec.) throughout the day and configured as schedule type 1.

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPWhoIsActive',
	@Name = 'Who is active',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPWhoIsActive',
	@Name = 'Who is active',
	@ScheduleType = 1,
	@Enabled = 1,
	@ExecutionDelaySec = 60,
	@FromTime = '00:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

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

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmWhoIsActive',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmWhoIsActive',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 7;

SQL Server Monitoring – Who is active