SQL SERVER – Optimization Using DMV Object sys.dm_exec_query_optimizer_info in 2005/2008

This is the view sys.dm_exec_query_optimizer_info that is returns detail statistics about the operation of the SQL Server query optimizer. Using this you can Tune workload to identify query optimization problems or improvements. For example, you can use the Following things-
1. Total number of optimizations.
2. The elapsed time value.
3. The final cost value and you can use this value to compare Query.

-- Select Database name

Use AdventureWorks

GO

-- Run Query optimization info view

SELECT *

FROM sys.dm_exec_query_optimizer_info

Go

This query returns the 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.
Result:

Counter	Occurrence	Value
optimizations	107	1
elapsed time	107	0.053327
final cost	107	0.179417
trivial plan	30	1
tasks	77	878.6234
no plan	0	NULL
search 0	13	1
search 0 time	13	0.069538
search 0 tasks	13	2291.231
search 1	64	1
search 1 time	64	0.020813
search 1 tasks	64	591.6875
search 2	0	NULL
search 2 time	0	NULL
search 2 tasks	0	NULL
gain stage 0 to stage 1	0	NULL
gain stage 1 to stage 2	0	NULL
timeout	4	1
memory limit exceeded	0	NULL
insert stmt	3	1
delete stmt	23	1
update stmt	7	1
merge stmt	0	NULL
contains subquery	22	1
unnest failed	14	1
tables	107	4.233645
hints	0	NULL
order hint	0	NULL
join hint	0	NULL
view reference	49	1
remote query	1	1
maximum DOP	107	0
maximum recursion level	0	NULL
indexed views loaded	0	NULL
indexed views matched	0	NULL
indexed views used	0	NULL
indexed views updated	0	NULL
dynamic cursor request	0	NULL
fast forward cursor request	0	NULL

Now run the following Script

Select * From AdventureWorks.Person.Contact  

Delete Person.Contact where Person.Contact.ContactID ='2343'
Then Again run this script

SELECT *

FROM sys.dm_exec_query_optimizer_info
Now see the difference between above in Resultset and above resultset -

Counter	Occurrence	Value
optimizations	110	1
elapsed time	110	0.052018
final cost	110	0.202649
trivial plan	31	1
tasks	79	864.1013
no plan	0	NULL
search 0	13	1
search 0 time	13	0.069538
search 0 tasks	13	2291.231
search 1	66	1
search 1 time	66	0.020333
search 1 tasks	66	583
search 2	0	NULL
search 2 time	0	NULL
search 2 tasks	0	NULL
gain stage 0 to stage 1	0	NULL
gain stage 1 to stage 2	0	NULL
timeout	4	1
memory limit exceeded	0	NULL
insert stmt	3	1
delete stmt	24	1
update stmt	7	1
merge stmt	0	NULL
contains subquery	22	1
unnest failed	15	1
tables	110	4.145455
hints	0	NULL
order hint	0	NULL
join hint	0	NULL
view reference	50	1
remote query	1	1
maximum DOP	110	0
maximum recursion level	0	NULL
indexed views loaded	0	NULL
indexed views matched	0	NULL
indexed views used	0	NULL
indexed views updated	0	NULL
dynamic cursor request	0	NULL
fast forward cursor request	0	NULL