sys.dm_exec_query_optimizer_info (Transact-SQL)


THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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."

System_CAPS_ICON_note.jpg Note

To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_query_optimizer_info.

NameData typeDescription
counternvarchar(4000)Name of optimizer statistics event.
occurrencebigintNumber of occurrences of optimization event for this counter.
valuefloatAverage property value per event occurrence.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

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.

optimizationsTotal number of optimizations.Not applicable
elapsed timeTotal number of optimizations.Average elapsed time per optimization of an individual statement (query), in seconds.
final costTotal number of optimizations.Average estimated cost for an optimized plan in internal cost units.
trivial planInternal onlyInternal only
tasksInternal onlyInternal only
no planInternal onlyInternal only
search 0Internal onlyInternal only
search 0 timeInternal onlyInternal only
search 0 tasksInternal onlyInternal only
search 1Internal onlyInternal only
search 1 timeInternal onlyInternal only
search 1 tasksInternal onlyInternal only
search 2Internal onlyInternal only
search 2 timeInternal onlyInternal only
search 2 tasksInternal onlyInternal only
gain stage 0 to stage 1Internal onlyInternal only
gain stage 1 to stage 2Internal onlyInternal only
timeoutInternal onlyInternal only
memory limit exceededInternal onlyInternal only
insert stmtNumber of optimizations that are for INSERT statements.Not applicable
delete stmtNumber of optimizations that are for DELETE statements.Not applicable
update stmtNumber of optimizations that are for UPDATE statements.Not applicable
contains subqueryNumber of optimizations for a query that contains at least one subquery.Not applicable
unnest failedInternal onlyInternal only
tablesTotal number of optimizations.Average number of tables referenced per query optimized.
hintsNumber of times some hint was specified. Hints counted include: JOIN, GROUP, UNION and FORCE ORDER query hints, FORCE PLAN set option, and join hints.Not applicable
order hintNumber of times a force order hint was specified.Not applicable
join hintNumber of times the join algorithm was forced by a join hint.Not applicable
view referenceNumber of times a view has been referenced in a query.Not applicable
remote queryNumber of optimizations where the query referenced at least one remote data source, such as a table with a four-part name or an OPENROWSET result.Not applicable
maximum DOPTotal number of optimizations.Average effective MAXDOP value for an optimized plan. By default, effective MAXDOP is determined by the max degree of parallelism server configuration option, and may be overridden for a specific query by the value of the MAXDOP query hint.
maximum recursion levelNumber of optimizations in which a MAXRECURSION level greater than 0 has been specified with the query hint.Average MAXRECURSION level in optimizations where a maximum recursion level is specified with the query hint.
indexed views loadedInternal onlyInternal only
indexed views matchedNumber of optimizations where one or more indexed views have been matched.Average number of views matched.
indexed views usedNumber of optimizations where one or more indexed views are used in the output plan after being matched.Average number of views used.
indexed views updatedNumber of optimizations of a DML statement that produce a plan that maintains one or more indexed views.Average number of views maintained.
dynamic cursor requestNumber of optimizations in which a dynamic cursor request has been specified.Not applicable
fast forward cursor requestNumber of optimizations in which a fast-forward cursor request has been specified.Not applicable
merge stmtNumber of optimizations that are for MERGE statements.Not applicable

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;  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions