sys.dm_exec_query_optimizer_info

Returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements. For example, you can use the total number of optimizations, the elapsed time value, and the final cost value to compare the query optimizations of the current workload and any changes observed during the tuning process. Some counters provide data that is relevant only for SQL Server internal diagnostic use. These counters are marked as "Internal only."

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

sys.dm_exec_query_optimizer_info contains the following properties (counters). All occurrence values are cumulative and are set to 0 at system restart. All values for value fields are set to NULL at system restart. All value-column values that specify an average use the occurrence value from the same row as the denominator in the calculation of the average. All query optimizations are measured when SQL Server determines changes to dm_exec_query_optimizer_info, including both user- and system-generated queries. Execution of an already-cached plan does not change values in dm_exec_query_optimizer_info, only optimizations are significant.

Examples

A. Viewing statistics on optimizer execution

What are the current optimizer execution statistics for this instance of SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Viewing the total number of optimizations

How many optimizations are performed?

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';

C. Average elapsed time per optimization

What is the average elapsed time per optimization?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';

D. Fraction of optimizations that involve subqueries

What fraction of optimized queries contained a subquery?

SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /
       (SELECT CAST (occurrence AS float) 
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
        AS ContainsSubqueryFraction;

See Also

Reference

Dynamic Management Views and Functions
Execution Related Dynamic Management Views and Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History