Databases Reference
In-Depth Information
One benefit of the graphical plans, compared to text and XML plans, is that you can easily
see which operators are being executed in parallel by looking at the parallelism symbol
(a small yellow circle with arrows) included in the operator icon. In this case, it's shown
in Figure 2-31 for the Sort and Hash Join operators.
To see why a parallel plan was considered and selected, you can look at the cost of the
serial plan. One way to do this is by using the
MAXDOP
hint to force a serial plan, as in the
following query:
SELECT
I
.
CustomerID
,
C
.
FirstName
,
C
.
LastName
,
A
.
City
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
ORDER
BY
I
.
CustomerID
OPTION
(
MAXDOP
1
)
Listing 2-34.
The forced serial plan has a cost of 5.31282 and, given that the default cost threshold for
parallelism configuration option is 5, this clearly crosses that threshold. An interesting
test you can perform in your own test environment is to change the
cost
threshold
for
parallelism
option to 6 by running the following statements:
sp_configure
'cost threshold for parallelism'
,
6
GO
RECONFIGURE
GO
Listing 2-35.
