This metric measures the total number of ad hoc queries in the plan cache that have only run one time. The value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next.<\/p>\r\n\r\n
Having too many one-time use ad hoc queries in an instance\u2019s plan cache may indicate plan cache bloat, which is a condition where memory in the plan cache is wasted by storing the execution plans of queries that will never be run more than one time. This wasted space can often better be used by the data cache, as generally speaking, the more RAM devoted to the data cache, the faster SQL Server can perform. This particular metric measures the number of one-time use ad hoc queries that are currently in the plan cache.<\/p>\r\n\r\n
Keep in mind that this value changes often, and that one-time use ad hoc queries can become multi-use ad hoc queries. For example, if this metric returns 3,500 during one run, it is very possible that 3,450 of these one-time use ad hoc queries may be used a second time before the next time the same metric runs again. Because this value changes often, it is important to maintain a baseline of data in order to establish a \u201ctypical\u201d value for this metric for each SQL Server instance.<\/p>\r\n\r\n
If the value of this metric is high, then it might be a good idea to turn on the SQL Server option \u201coptimize for ad hoc workloads\u201d. When this option is turned on, then the first time an ad hoc query is executed, the entire execution plan is not stored in the plan cache. Instead, a small stub, taking very little memory is stored. The next time the same ad hoc query runs again, SQL Server will notice this, and this time around, will store the entire execution plan in the plan cache. If your instance has a lot of one-time use ad hoc queries, then turning on \u201coptimize for ad hoc workloads\u201d will prevent plan cache bloat, helping to boost the performance of your SQL Server instance.<\/p>\r\n\r\n
Generally speaking, it is not necessary to create an alert for this metric, as you need to watch this value over time. Typically speaking, if this value exceeds 10,000 over time, then there is a good chance your SQL Server may be suffering from plan cache bloat. But this is only an estimated number. The only way to really know if turning on \u201coptimize for ad hoc workloads\u201d will be beneficial is to try it, and then use the custom metric, \u201cNumber of Ad Hoc Stubs Created When \u2018Optimize for Ad Hoc Workloads\u2019 is Turned On\u201d to determine if turning on \u201coptimize for ad hoc workloads\u201d is effective or not at reducing plan cache bloat.<\/p>\r\n\r\n
When deciding if you should turn on \u201coptimize for ad hoc workloads\u201d, consider both the values of this custom metric, plus the Memory used by ad hoc queries running only once<\/a> custom metric. If both of these metrics are high, then it is likely that your instance is suffering from plan cache bloat.<\/p>\r\n","tsql":"SELECT SUM(CASE WHEN usecounts = 1 THEN 1\r\n ELSE 0\r\n END) AS [Adhoc Plans Use Count of 1]\r\nFROM sys.dm_exec_cached_plans\r\nWHERE objtype = 'Adhoc'\r\nGROUP BY objtype;","instances":true,"frequency":60,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Ad hoc queries running once","alertdescription":" This alert is raised if the total number of ad hoc queries in the plan cache that have only run one time goes above the specified thresholds. The value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next.<\/p>\r\n\r\n Having too many one-time use ad hoc queries in an instance\u2019s plan cache may indicate plan cache bloat, which is a condition where memory in the plan cache is wasted by storing the execution plans of queries that will never be run more than one time. This wasted space can often better be used by the data cache, as generally speaking, the more RAM devoted to the data cache, the faster SQL Server can perform. The metric that this particular alert is based on measures the number of one-time use ad hoc queries that are currently in the plan cache.<\/p>\r\n\r\n Keep in mind that this value changes often, and that one-time use ad hoc queries can become multi-use ad hoc queries. For example, if this metric returns 3,500 during one run, it is very possible that 3,450 of these one-time use ad hoc queries may be used a second time before the next time the same metric runs again. Because this value changes often, it is important to maintain a baseline of data in order to establish a \u201ctypical\u201d value for this metric for each SQL Server instance.<\/p>\r\n\r\n If the value of this metric is high, then it might be a good idea to turn on the SQL Server option \u201coptimize for ad hoc workloads\u201d. When this option is turned on, then the first time an ad hoc query is executed, the entire execution plan is not stored in the plan cache. Instead, a small stub, taking very little memory is stored. The next time the same ad hoc query runs again, SQL Server will notice this, and this time around, will store the entire execution plan in the plan cache. If your instance has a lot of one-time use ad hoc queries, then turning on \u201coptimize for ad hoc workloads\u201d will prevent plan cache bloat, helping to boost the performance of your SQL Server instance.<\/p>\r\n\r\n Generally speaking, it is not necessary to create an alert for this metric, as you need to watch this value over time. Typically speaking, if this value exceeds 10,000 over time, then there is a good chance your SQL Server may be suffering from plan cache bloat. But this is only an estimated number. The only way to really know if turning on \u201coptimize for ad hoc workloads\u201d will be beneficial is to try it, and then use the custom metric, \u201cNumber of Ad Hoc Stubs Created When \u2018Optimize for Ad Hoc Workloads\u2019 is Turned On\u201d to determine if turning on \u201coptimize for ad hoc workloads\u201d is effective or not at reducing plan cache bloat.<\/p>\r\n\r\n When deciding if you should turn on \u201coptimize for ad hoc workloads\u201d, consider both the values of this custom metric, plus the Memory used by ad hoc queries running only once<\/a> custom metric. If both of these metrics are high, then it is likely that your instance is suffering from plan cache bloat.<\/p>\r\n Supported SQL Server versions:<\/strong> SQL Server 2005 or later<\/p>","aboveorbelow":"above","collections":10,"alertenabled":false,"_thresholds_high":{"selected":true,"value":20000},"_thresholds_medium":{"selected":true,"value":15000},"_thresholds_low":{"selected":true,"value":10000},"thresholds_comment":"Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.","targetentitytype":0}