Database Reference
In-Depth Information
Analyzing the Query Execution Plan
To see the execution plan, click the Show Actual Execution Plan button to enable it and then run stored procedure.
Be sure you're doing these types of tests on a nonproduction system. For more details on reading execution plans,
check out my book
SQL Server Execution Plans
(Red Gate Publishing, 2013). Figure
25-7
shows the graphical
execution plan of the worst-performing query.
Figure 25-7.
The actual execution plan of the worst-performing query
The graphic of this plan is somewhat difficult to read. I'll break down a few of the interesting details in case you're
not following along with code. Reading execution plans was explained in Chapter 15. You could observe the following
from this execution plan:
•
SELECT
properties:
•
Optimization Level: Full
•
Reason for Early Termination: Good enough plan found
Data access:
•
•
Index seek on nonclustered index,
Person.IX_Person_LastName_FirstName_MiddleName
•
Clustered index scan on,
PurchaseOrderHeader.PK_PruchaseOrderHeader_
PurchaseOrderID
Clustered index seek on
•
PurchaseOrderDetail.PK_PurchaseOrderDetail_
PurchaseOrderDetailID
Index seek on clustered index,
•
Product.PK_Product_ProductID
•
Index seek on clustered index Employee.PK_Employee_BusinessEntityID
Join strategy:
•
•
Nested loop join between the constant scan and
Person.Person
table with the
Person.Person
table as the outer table
Nested loop join between the output of the previous join and
•
Purchasing.PurchaseOrderHeader
with the
Purchasing.PurchaseOrderHeader
table as
the outer table
