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.

Sources

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

Report

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

Model

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

Configuration

There is no configuration for this feature.

Scheduling

The default schedule for the feature is every 5 minutes (300 seconds).

Retention

The default retention for the table dbo.fhsmPlanCacheUsage is 90 days.