SQL Server Monitoring - Wait statistics

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

Purpose

To get a baseline of the server wait statistics.

Report

The report has in the upper part 2 line charts, the left showing the signal wait time and wait time in mS, and the right showing the number of waiting tasks, where the table in the lower part shows the wait data per wait type.

Sources

This feature queries the 2 DMV’s sys.dm_os_waiting_tasks and sys.dm_os_wait_stats which returns information about the wait queue of tasks that are waiting on some resource as well as information about all the waits encountered by threads that executed, and where the wait type is enabled in the master data table dbo.fhsmWaitCategories.

Model

The model for the report uses the fact [Wait statistics] and the dimensions [Date], [Time] and [Wait type].

Service setup

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

Parameter

There is no parameter to be configured for this feature.

Schedule

The default schedule for the feature is once every hour (3.600 sec.) throughout the day.

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPWaitStatistics',
	@Name = 'Wait statistics',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPWaitStatistics',
	@Name = 'Wait statistics',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 3600,
	@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.fhsmWaitStatistics is 30 days.

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

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

SQL Server Monitoring – Wait statistics