{"name":"Plan cache hit ratio","description":"This metric measures how much the plan cache is being used. \r\nA high percentage here means that your SQL Server is not building a new plan for every query it is executing so is working effectively and efficiently. A low percentage here means that for some reason, the SQL Server is doing more work than it needs to. \r\nThis metric needs to be considered alongside the Plan cache reuse metric which looks at the spread of plan reuse through your cache.","tsql":"WITH    cte1\r\n          AS ( SELECT [dopc].[object_name] ,\r\n                    [dopc].[instance_name] ,\r\n                    [dopc].[counter_name] ,\r\n                    [dopc].[cntr_value] ,\r\n                    [dopc].[cntr_type] ,\r\n                    ROW_NUMBER() OVER ( PARTITION BY [dopc].[object_name], [dopc].[instance_name] ORDER BY [dopc].[counter_name] ) AS r_n\r\n                FROM [sys].[dm_os_performance_counters] AS dopc\r\n                WHERE [dopc].[counter_name] LIKE '%Cache Hit Ratio%'\r\n                    AND ( [dopc].[object_name] LIKE '%Plan Cache%'\r\n                          OR [dopc].[object_name] LIKE '%Buffer Cache%'\r\n                        )\r\n                    AND [dopc].[instance_name] LIKE '%_Total%'\r\n             )\r\n    SELECT CONVERT(DECIMAL(16, 2), ( [c].[cntr_value] * 1.0 \/ [c1].[cntr_value] ) * 100.0) AS [hit_pct]\r\n        FROM [cte1] AS c \r\n            INNER JOIN [cte1] AS c1\r\n                ON c.[object_name] = c1.[object_name]\r\n                   AND c.[instance_name] = c1.[instance_name]\r\n        WHERE [c].[r_n] = 1\r\n            AND [c1].[r_n] = 2;","instances":true,"frequency":600,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"noalertremark":"An alert is not required","alertdescription":"Alert description goes here","aboveorbelow":"above","collections":3,"alertenabled":true,"_thresholds_high":{"selected":true,"value":800},"_thresholds_medium":{"selected":true,"value":500},"_thresholds_low":{"selected":true,"value":100},"frequency_comment":"Note: The frequency for this metric should match the collection frequency of the Plan cache reuse metric.","targetentitytype":0}