17,479 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.
This metric collects the number of 8-kilobyte pages that make up the plan cache to help identify memory pressure or plan cache pollution. If you want to see the total amount of memory used by the plan cache of an instance, see SQL Server: memory manager: SQL cache memory.
A sudden drop in values for this metric may indicate that the instance is under memory pressure and SQL Server had to reclaim part of the plan cache for other use by SQL Server, which causes this metric to decrease.
Sudden increases may indicate that a large number of one-time use ad hoc queries may have been executed, causing plan cache pollution. This is most often seen shortly after SQL Server has been restarted, and as one-time use ad hoc queries begin to occur, this metric will increase over time. If this is the case, consider turning on the “optimize for ad hoc workloads” instance-level option to stop plan cache pollution. For more information, see Optimize for ad hoc workloads server configuration option (TechNet).
Metric definition
Name
SQL Server: plan cache: cache pages total (KB)
Description
This metric collects the number of 8-kilobyte pages that make up the plan cache to help identify memory pressure or plan cache pollution. If you want to see the total amount of memory in kilobytes used by the plan cache of an instance, see SQL Server: memory manager: SQL cache memory.
A sudden drop in values for this metric may indicate that the instance is under memory pressure and SQL Server had to reclaim part of the plan cache for other use by SQL Server, which causes this metric to decrease.
Sudden increases may indicate that a large number of one-time use ad hoc queries may have been executed, causing plan cache pollution. This is most often seen shortly after SQL Server has been restarted, and as one-time use ad hoc queries begin to occur, this metric will increase over time. If this is the case, consider turning on the “optimize for ad hoc workloads” instance-level option to stop plan cache pollution. For more information, see Optimize for ad hoc workloads server configuration option (TechNet).
The T-SQL query that will collect data
Instances to collect from
Select all
Databases to collect from
master
Collection frequency
60
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
Metric collection
Enabled
Alert definition
An alert is not required
