Databases Reference
In-Depth Information
Note that some queries are listed twice in the code. The purpose of this is to optimize
them the first time that they are executed, so that their plan can be available in the plan
cache for all the executions after that. In this way, we aim as far as possible to isolate
the optimization information from the queries we are trying to analyze. Care must be
taken that both queries are exactly the same, including case, comments, and so on, and
separated in their own batch for the GO statements.
If you run this script against the
AdventureWorks
database, the output should look like
what's shown in Table 5-3. Note that the times shown obviously may be different from the
ones you get in your system, (for both this and other examples in this chapter). This table
indicates, among other things, that there was one optimization, referencing one table,
with a cost of 0.230042585.
Counter
Occurrence
Value
elapsed time
1
0
final cost
1
0.230042585
maximum DOP
1
0
optimizations
1
1
tables
1
1
trivial plan
1
1
Table 5-3.
Certainly, for this simple query, we could find the same information in some other places,
such as in an execution plan. However, as I will show later in this chapter, this DMV can
provide optimization information that is not available anywhere else.
The second DMV,
sys.dm_exec_query_transformation_stats
, provides informa-
tion about the existing transformation rules and how they are being used by the Query
