Databases Reference
In-Depth Information
As mentioned previously, you can use this DMV in two different ways: you can use it
to get information regarding the history of accumulated optimizations on the system
since the instance was started or, rather more usefully, you can use it to get optimization
information for a particular query or a workload.
In order to capture data on the latter, you need to take two snapshots of the DMV - one
before optimizing your query, and another one after the query has been optimized - and
manually find the difference between them. Unfortunately, there is no way to initialize
the values of this DMV.
There are several issues to consider when capturing this information. Firstly, you need
to eliminate the effects of system-generated queries, or queries executed by other users,
which may be running at the same time as your sample query. Try to isolate the query
or workload on your own instance, and make sure that the number of optimizations
reported is the same as the number of optimizations you are requesting. If the former is
greater, then the data probably includes some of those queries submitted by the system or
other users. Of course, it's also possible that your own query against the
sys.dm_exec_
query_optimizer_info
DMV may count as an optimization.
Secondly, you need to make sure that a query optimization is actually taking place. For
example, if you run the same query more than once, then the Query Optimizer may
simply use an existing plan from the plan cache. You can force an optimization by using
the RECOMPILE hint, as shown later, or by clearing the plan cache. For instance, as of
SQL Server 2008, the
DBCC
FREEPROCCACHE
statement can be used to remove a specific
plan, all the plans related to a specific resource pool, or the entire plan cache. But of
course, you should never clear the plan cache of a production environment.
With all of this in mind, the script shown in Listing 5-3 will display the optimization
information for a specific query, while avoiding all of the aforementioned issues. The
script is based on an original idea by Lubor Kollar, and has a section to include the query
which you want to get optimization information about.
