18,488 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.
This metric measures the amount of memory used by the total number of ad hoc queries in the plan cache that have only run one time. This value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next.
Having too many one-time use ad hoc queries in an instance’s 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 be better used by the data cache, as generally speaking, the more RAM devoted to the data cache, the faster SQL Server can perform.
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 200 MB during one run, it is very possible that much of this same memory will later be occupied by ad hoc queries that may been 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 “typical” value for this metric for each SQL Server instance.
If the value of this metric is high, then it might be a good idea to turn on the SQL Server option “optimize for ad hoc workloads”. 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 “optimize for ad hoc workloads” will prevent plan cache bloat, helping to boost the performance of your SQL Server instance.
Generally speaking, it is not necessary to create an alert for this metric, as you need to watch this value over time. Typically, if this value exceeds 350 MB 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 “optimize for ad hoc workloads” will be beneficial is to try it, and then use the custom metric, “Number of Ad Hoc Stubs Created When ‘Optimize for Ad Hoc Workloads’ is Turned On” to determine if turning on “optimize for ad hoc workloads” is effective or not at reducing plan cache bloat.
When deciding if you should turn on “optimize for ad hoc workloads”, consider both the values of this custom metric, plus the “Number of ad hoc queries running only once”. If both of these metrics are high, then it is likely that your instance is suffering from plan cache bloat.
Metric definition
Name
Memory used by ad hoc queries running once
Description
This metric measures the amount of memory used by the total number of ad hoc queries in the plan cache that have only run one time. This value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next.
Having too many one-time use ad hoc queries in an instance’s 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 be better used by the data cache, as generally speaking, the more RAM devoted to the data cache, the faster SQL Server can perform.
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 200 MB during one run, it is very possible that much of this same memory will later be occupied by ad hoc queries that may been 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 “typical” value for this metric for each SQL Server instance.
If the value of this metric is high, then it might be a good idea to turn on the SQL Server option “optimize for ad hoc workloads”. 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 “optimize for ad hoc workloads” will prevent plan cache bloat, helping to boost the performance of your SQL Server instance.
Generally speaking, it is not necessary to create an alert for this metric, as you need to watch this value over time. Typically, if this value exceeds 350 MB 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 “optimize for ad hoc workloads” will be beneficial is to try it, and then use the custom metric, “Number of Ad Hoc Stubs Created When ‘Optimize for Ad Hoc Workloads’ is Turned On” to determine if turning on “optimize for ad hoc workloads” is effective or not at reducing plan cache bloat.
When deciding if you should turn on “optimize for ad hoc workloads”, consider both the values of this custom metric, plus the “Number of ad hoc queries running only once”. If both of these metrics are high, then it is likely that your instance is suffering from plan cache bloat.
The T-SQL query that will collect data
Instances to collect from
Select all
Databases to collect from
master
Collection frequency
60
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
Metric collection
Enabled
Alert definition
Alert name
Memory used by ad hoc queries running only once
Description
This alert is raised if the amount of memory used by the total number of ad hoc queries in the plan cache that have only run one time goes above a specified threshold. This value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next. Because of this, it is best to establish a baseline so that you can determine an average value over time. Typically, if this value exceeds 350 MB, this can indicate plan cache bloat. But this is only a rough estimate and depends on how the applications on your server have been designed. The higher the number over 350 MB, the greater the likelihood that plan cache bloat exists.
Raise an alert when the metric value goes
Above the defined threshholds
Default threshold values
| High: | 700 |
| Medium: | 500 |
| Low: | 350 |
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.
Raise an alert when the threshold is passed for
10 collections
Alert is
Disabled
