Database Reference
In-Depth Information
Figure 14-6.
SELECT operator property sheet
Starting at the top, you can see information directly related to the creation and optimization of this execution plan.
The size of the cached plan, which is 64 bytes
•
•
The number of CPU cycles used to compile the plan, which is 30ms
•
The amount of memory used, which is 1144KB
•
The Optimization Level property (
StatementOptmLevel
in the XML plan) shows what type of processing occurred
within the optimizer. In this case,
FULL
means that the optimizer did a full optimization. This is further displayed
in the property Reason for Early Termination of Statement, which is Good Enough Plan Found. So, the optimizer
took 42ms to track down a plan that it deemed good enough in this situation. You can also see the
QueryPlanHash
value, also known as the
fingerprint
, for the execution plan (you can find more details on this in the section “Query
Plan Hash and Query Hash”). The properties of the
SELECT
(and the
INSERT
,
UPDATE
, and
DELETE
) operators are an
important first stopping point when evaluating any execution plan because of this information.
The second source for optimizer information is the dynamic management view
sys.Dm_exec_query_optimizer_info
.
This DMV is an aggregation of the optimization events over time. It won't show the individual optimizations for a
given query, but it will track the optimizations performed. This isn't as immediately handy for tuning an individual
query, but if you are working on reducing the costs of a workload over time, being able to track this information can
help you determine whether your query tuning is making a positive difference, at least in terms of optimization time.
Some of the data returned is for internal SQL Server use only. Figure
14-7
shows a truncated example of the useful
data returned in the results from the following query:
The compile time, which is 42ms
SELECT deqoi.counter,
deqoi.occurrence,
deqoi.value
FROM sys.dm_exec_query_optimizer_info AS deqoi;
