SQL Server Monitoring - Always On performance

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

Purpose

To show the Always On performance over time.

Report

The report has 2 clustered column charts in the upper half and a line chart in the lower half of the report.

The left clustered column charts is showing average number of bytes sent over time, and the right is showing the average number of bytes received from replicas over time.

The line chart is showing the average synchronization lag for database transactions to be hardened on the replicas in synchronous commit mode.

The calculation is based on the article “Measuring Availability Group synchronization lag” by Derik Hammer.

Sources

This feature queries the DMV sys.dm_os_performance_counters which returns a row per performance counter that is in the master data table dbo.fhsmPerfmonCounters.

Model

The model for the report uses the facts [Always On synchronization lag] and [Performance statistics] and the dimensions [Date], [Time], [Database] and [Performance counter].

It is the same model that are used by the Performance statistics and Resource Governor report.

Service setup

The feature uses the service ‘fhsmSPPerfmonStatistics’ 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 every 5 minutes (300 seconds).

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

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPPerfmonStatistics',
	@Name = 'Performance statistics',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 300,
	@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.fhsmPerfmonStatistics is 60 days.

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

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

SQL Server Monitoring – Always On performance