SQL Server Monitoring - Memory

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

Purpose

To get a quick overview of the buffer pool usage and the memory clerk usage.

Report

The report has in the upper part a column chart showing the buffer pool usage pr. database over time, where the line chart in the lower part shows the memory clerk usage over time.

Sources

The feature queries the DMV’s sys.dm_os_buffer_descriptors and sys.dm_os_memory_clerks.

Model

The model for the report uses the facts [Buffer pool usage] and [Memory clerks], and the dimensions [Date], [Time], [Database] and [Memory clerk type].

Service setup

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

Parameter

There is no parameter to be configured for this service.

Schedule

The default schedule for the feature is every hour (3.600 seconds).

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPMemory',
	@Name = 'Memory',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPMemory',
	@Name = 'Memory',
	@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 tables are:

dbo.fhsmBufferDescriptions – 30 days
dbo.fhsmMemoryClerks – 30 days

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

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

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

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

SQL Server Monitoring – Memory