SQL Server: memory manager: SQL cache memory

| 1 Comment | 15,044 views
Categories:

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

This metric specifies the total amount of dynamic memory, in kilobytes (KB) the server is using for the dynamic SQL plan cache. It is very similar to the SQL Server: plan cache: cache pages total metric, but instead of providing the size of the dynamic SQL plan cache in kilobytes of memory, it provides very similar data in the form of the number of 8-kilobyte (KB) pages that make up the size of the plan cache.

There is no right or wrong number for this counter. In many instances, the value for this counter won’t change much over time, which is what you would expect on a server with no memory pressure. On the other hand, if you see sudden drops over time for this counter, it might be an indication that the instance is under memory pressure and SQL Server had to reclaim part of the plan cache for other use. Alternatively, rapid increases in this counter 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 this counter 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.

SQL Server: memory manager: SQL cache memory, 5.0 out of 5 based on 1 rating

Metric definition

Metric Name:
SQL Server: memory manager: SQL cache memory (KB)
Description:

This metric specifies the total amount of dynamic memory, in kilobytes (KB) the server is using for the dynamic SQL plan cache. It is very similar to the SQL Server: plan cache: cache pages total metric but instead of providing the size of the dynamic SQL plan cache in kilobytes of memory, it gives similar data in the form of the number of 8-kilobyte (KB) pages that make up the size of the plan cache.

There is no right or wrong number for this counter. In many instances, the value won’t change much over time, which is what you would expect on a server with no memory pressure. On the other hand, if you see sudden drops over time for this counter, it might be an indication that the instance is under memory pressure and SQL Server had to reclaim part of the plan cache for other use by SQL Server. The latter would cause this counter to suddenly decrease. Increases in this counter, however, 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 counter 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.

Enter the T-SQL query that will collect data:
--SQL Server: Memory Manager: SQL Cache Memory(KB) (Custom Metric)
SELECT cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'SQL Cache Memory (KB)';
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: 5.0/5 (1 vote cast)
  • Lenz

    The description above says: “It is very similar to the SQL Server: plan cache: cache pages total metric, but instead of providing the size of the dynamic SQL plan cache in kilobytes of memory, it provides very similar data in the form of the number of 8-kilobyte (KB) pages that make up the size of the plan cache.”

    On the contrary, the above metric provides precisely ” the size of the dynamic SQL plan cache in kilobytes of memory…”

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