SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info

Many times when I am just bored I surf Book On Line for SQL Server 2005. Almost all the time I find something new which makes me believe that I have lot to learn and there are so many things I am not aware of. Today I found system catalog view sys.dm_exec_query_optimizer_info. I just enjoyed reading about it and now I will share this with you.

If my readers have found something like this, please post it here and I will share that with everybody on this blog. I believe in community development and you can be part of it.

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

SELECT *
FROM sys.dm_exec_query_optimizer_info

Resultset will contain three columns:

counter – Name of optimizer statistics event.

occurrence – Number of occurrences of optimization event for this counter. This many times the counter specified in counter column is optimized.

value – Average property value per event occurrence.

I found reading about this very interesting. Friends, if you know something which interests you post here or point me and I will write about it.

Reference : Pinal Dave (https://blog.sqlauthority.com)