Databases Reference
In-Depth Information
since this is the last phase, an execution plan must be found here (perhaps with the
exception of the timeout event, as explained later).
The
sys.dm_exec_query_optimizer_info
DMV includes another useful counter,
named "gain stage 1 to stage 2," to show the number of times search 2 was executed after
search 1, together with the average decrease in cost from one stage to the other,
as defined by
:
(MinimumPlanCost(search 1) - MinimumPlanCost(search 2)) / MinimumPlanCost(search 1)
Listing 5-37.
For example, the following query, as taken from Books Online, will create the
optimization information shown in Table 5-14.
1
SELECT
I
.
CustomerID
,
C
.
FirstName
,
C
.
LastName
,
A
.
AddressLine1
,
A
.
City
,
SP
.
Name
AS
State
,
CR
.
Name
AS
CountryRegion
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
CustomerAddress
AS
CA
ON
CA
.
CustomerID
=
I
.
CustomerID
JOIN
Person
.
Address
AS
A
ON
A
.
AddressID
=
CA
.
AddressID
JOIN
Person
.
StateProvince SP
ON
SP
.
StateProvinceID
=
A
.
StateProvinceID
JOIN
Person
.
CountryRegion CR
ON
CR
.
CountryRegionCode
=
SP
.
CountryRegionCode
ORDER
BY
I
.
CustomerID
Listing 5-38.
1 Output for SQL Server 2008 is shown; number of tasks will vary for SQL Server 2008 R2.
