SQL Server Monitoring - Plan cache usage

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

Purpose

To show the plan cache usage over time.

Report

The report has a stacked column chart showing the size of the plan cache usage per object types over time.

Sources

This feature queries the DMV sys.dm_exec_cached_plans which returns information about the objects and their size in the plan cache.

Model

The model for the report uses the fact [Plan cache usage] and the dimensions [Date] and [Time].

Service setup

The feature uses the service ‘fhsmSPPlanCacheUsage’ 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 = 'fhsmSPPlanCacheUsage',
	@Name = 'Plan cache usage',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPPlanCacheUsage',
	@Name = 'Plan cache usage',
	@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.fhsmPlanCacheUsage is 90 days.

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

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

SQL Server Monitoring – Plan cache usage