THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

sys.dm_exec_query_optimizer_info

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. (reference: http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx )

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.

Published Monday, August 06, 2007 8:40 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Davide Mauri said:

Hi Louis

with this query

with opt as

(

select

server_start_time = (select login_time from sys.dm_exec_sessions where session_id = 1),

total_optimizations = [occurrence],

avg_optimization_time_s = [value]

from

sys.dm_exec_query_optimizer_info

where

counter = 'elapsed time'

),

opt_perc as

(

select

opt.*,

available_time_s = datediff(ss, server_start_time, getdate()),

total_optimization_time_s = avg_optimization_time_s * total_optimizations

from

opt

)

select

opt_perc.*,

percent_time = total_optimization_time_s / available_time_s * 100.

from

opt_perc

you can also see how much time has been spent from server startup to optmize queryies.

August 8, 2007 3:19 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement