Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query.

Type: View

Data: Accumulating reset at restart

Columns:

• counter – Name of the query
• occurrence – Number of times the counter was recorded to
• value – May or may not have some value, but is typically an average of the values that were recorded when the counter was written to (for a complete list of counters, check the books online documentation on sys.dm_exec_query_optimizer_info (1))

Example:

As an example, on a low use server, I restarted the server and executed:

select counter,occurrence, value
from sys.dm_exec_query_optimizer_info

You will get values like:

counter           occurrence    value
—————– ————- ——————–
optimizations     1             1
elapsed time      1             0.0235776097702821
final cost        1             4.1957E-05

This tells you the number of statements that have been optimized and a plan created (the same value is for all three of the displayed counters), the average elapsed time spent compiling in the value column for the elapsed time row, and finally, the average final cost in the final cost row. Using these counters and some of the others you will discover will help you to determine how often statements ae being compiled.

Execute it again, it will give you the same values (assuming you are the only user, of course.) Now, execute the same query, this time making it uppercase, forcing another complication, since plans must match in every way:

SELECT COUNTER, OCCURRENCE, VALUE
FROM SYS.DM_EXEC_QUERY_OPTIMIZER_INFO

You will see that the occurrences have increased, and there may be some difference in the average time it took:

counter           occurrence    value
—————– ————- ——————–
optimizations     2             1
elapsed time      2             0.020978588737036
final cost        1             4.1957E-05

—————————————

Note:  This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.  Also, I will begin maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx.