{"name":"SQL Server: plan cache: cache pages total (KB)","description":"<p>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 <a href=\"http:\/\/sqlmonitormetrics.red-gate.com\/sql-server-memory-manager-sql-cache-memory\/\">SQL Server: memory manager: SQL cache memory<\/a>.\r\n<\/p>\r\n\r\n<p>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.<\/p>\r\n\r\n<p>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 \u201coptimize for ad hoc workloads\u201d instance-level option to stop plan cache pollution. For more information, see <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/cc645587.aspx\">Optimize for ad hoc workloads server configuration option<\/a> (TechNet).<\/p>","tsql":"SELECT cntr_value\r\n  FROM sys.dm_os_performance_counters\r\n  WHERE counter_name = 'Cache Pages'\r\n    AND instance_name = 'SQL Plans';\r\n","instances":true,"frequency":60,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"noalertremark":"An alert is not required","alertdescription":"","aboveorbelow":"above","collections":1,"alertenabled":false,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"targetentitytype":0}