{"name":"Plan cache reuse","description":"Reuse of plans that are stored in the plan cache is important to the efficiency of SQL Server. Having the cache full of plans that are not reused is wasteful, and it means that there may be lots of queries not using the cache efficiently. \r\nIf this metric is low then investigate the number of plans in your cache that have a usecount of 1 and see if any can be parameterized.","tsql":"DECLARE @single DECIMAL(18, 2)\r\nDECLARE @reused DECIMAL(18, 2)\r\nDECLARE @total DECIMAL(18, 2)\r\n-- the above variables may need a precision greater than 18 on VLDB instances. This will incur a storage penalty in the RedgateMonitor database however.\r\nSELECT @single = SUM(CASE ( usecounts )\r\n                       WHEN 1 THEN 1\r\n                       ELSE 0\r\n                     END) * 1.0 ,\r\n        @reused = SUM(CASE ( usecounts )\r\n                        WHEN 1 THEN 0\r\n                        ELSE 1\r\n                      END) * 1.0 ,\r\n        @total = COUNT(usecounts) * 1.0\r\n    FROM sys.dm_exec_cached_plans;\r\n\r\n\r\nSELECT ( @single \/ @total ) * 100.0;","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: If you suspect that there may be an issue with plan cache reuse, increase the collection frequency to every minute so you can analyze more data.","targetentitytype":0}