SQL Server Monitoring - Performance statistics

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

Purpose

To get a baseline of the server performance counter metrics.

Report

The report has 2 line charts in the upper part where the left are showing batch request per second, and the right are showing compilations per second.
The lower part has a table to the left showing the latest memory grants pending, and to the right a line chart showing information about page life expectancy, average as well as per NUMA node.

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 [Memory grants pending] and [Performance statistics], and the dimensions [Date], [Time] and [Performance counter].

It is the same model that are used by the Always On traffic and Resource Governor report.

Service setup

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

Configuration

The fact [Memory grants pending] returns the latest memory grant pending rows.

The default number of rows returned by [Memory grants pending] is 25 but can be changed by using configuration.

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

Set number of rows returned to a custom value:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.MemoryGrantsPending.Rows',
	@Value = '10';


Reset number of rows returned to default value:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.MemoryGrantsPending.Rows',
	@Value = NULL;

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 – Performance statistics