Databases Reference
In-Depth Information
Counter
Occurrence
Value
search 1
1
1
search 1 tasks
1
81
search 1 time
1
0
tables
1
1
tasks
1
81
Table 5-12:
Optimization information for search 1 phase.
The
sys.dm_exec_query_optimizer_info
DMV includes a counter named
"gain stage 0 to stage 1" which shows the number of times search 1 was executed after
search 0, and includes the average decrease in cost from one stage to the other, as
defined by:
(MinimumPlanCost(search 0) - MinimumPlanCost(search 1)) / MinimumPlanCost(search 0)
Listing 5-35.
For example, the query we have been using before in this chapter:
SELECT
FirstName
,
LastName
,
CustomerType
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
Customer
AS
Cu
ON
I
.
CustomerID
=
Cu
.
CustomerID
Listing 5-36.
... will show the optimization information seen in Table 5-13.
