SQL Server: Plan cache: cache pages total

| 2 Comments | 6,051 views
Categories:

VN:F [1.9.17_1161]
Rating: 4.0/5 (1 vote cast)

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).

SQL Server: Plan cache: cache pages total, 4.0 out of 5 based on 1 rating

Metric definition

Metric 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).

Enter the T-SQL query that will collect data:
SELECT cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Cache Pages'
    AND instance_name = 'SQL Plans';
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

An alert is not required

VN:F [1.9.17_1161]
Rating: 4.0/5 (1 vote cast)
  • B.D.

    “This metric collects the total amount of memory, in kilobytes” – is this really the case? Perfmon description of the counter is “Number of 8k pages used by cache objects.”

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • SqlMonitorMetrics

    Hi B.D. You’re right – thanks for bringing it to our attention. This metric collects the number of 8 KB pages. We’ve updated this page, and added a link to the SQL Server: memory manager: SQL cache memory that does collect total memory.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)