SQL Server: memory manager: optimizer memory (KB)

| 0 comments | 7,288 views
Categories:

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

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

Metric 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.
Enter the T-SQL query that will collect data:
SELECT cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Optimizer Memory (KB)';
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

An alert is not required

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)