Databases Reference
In-Depth Information
Although the
sys.dm_exec_query_optimizer_info
DMV was completely
documented in the original version of SQL Server 2005 Books Online, more recent
versions omit descriptions of nearly half (18 out of 39) of the counters, and instead label
them as "Internal only."
Therefore, in Table 5-2, I am including the current Books Online documentation
plus descriptions of the 18 undocumented counters, according to their original
documentation, which is still valid for SQL Server 2008 R2. The additional descriptions
are shown in italics.
Counter
Occurrence
Value
Total number of
optimizations.
optimizations
Not applicable.
Total number of
optimizations.
Average elapsed time per optimization of an
individual statement (query), in seconds.
elapsed time
Total number of
optimizations.
Average estimated cost for an optimized plan,
in internal cost units.
inal cost
Total number of trivial plans
(used as final plan).
trivial plan
Not applicable.
Number of optimizations
that applied tasks (explo-
ration, implementation,
property derivation).
tasks
Average number of tasks executed.
Number of optimizations
for which no plan was found
after a full optimization
was run, and where no other
errors were issued during
query compilation.
no plan
Not applicable.
