12,166 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 total amount of dynamic memory (in kilobytes) on the server being used for query optimization. Generally speaking, this metric should remain more or less the same over time. If you see regular changes in the baseline for this metric, this may indicate that your instance is using a lot of ad hoc queries. While the use of ad hoc queries is not necessarily bad, it is generally preferable to substitute ad hoc queries with stored procedures for better scalability. There are no alerts required for this metric, as baseline data is needed to make any evaluation of what is happening within the server. In addition, if you do see a lot of variation in this counter, there is no simple fix, as any fixes require code changes, which is generally not a simple thing to do.
Metric definition
Name
SQL Server: memory manager: optimizer memory (KB)
Description
This metric measures the total amount of dynamic memory (in kilobytes) on the server being used for query optimization. Generally speaking, this metric should remain more or less the same over time. If you see regular changes in the baseline for this metric, this may indicate that your instance is using a lot of ad hoc queries. While the use of ad hoc queries is not necessarily bad, it is generally preferable to substitute ad hoc queries with stored procedures for better scalability. There are no alerts required for this metric, as baseline data is needed to make any evaluation of what is happening within the server. In addition, if you do see a lot of variation in this counter, there is no simple fix, as any fixes require code changes, which is generally not a simple thing to do.
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
An alert is not required
