Database Reference
In-Depth Information
Figure 25-11.
Execution plan for the other query in the procedure
This new query has different behaviors because of the differences in the query. The main issue here is a clustered
index scan against the
PurchaseOrderHeader
table. You're seeing a scan despite that there is an index on
VendorID
.
Again, you can look to see what the output of the operator includes. This time, it's more than just two columns:
OrderDate
,
EmployeeID
,
PurchaseOrderID
. These are not very large columns, but they will add to the size of the index.
You'll need to evaluate whether this increase in index size is worth the performance benefits of the elimination of the
scan of the index. I'm going to go ahead and try it by modifying the index as follows:
CREATE NONCLUSTERED INDEX IX_PurchaseOrderHeader_VendorID ON Purchasing.PurchaseOrderHeader
(
VendorID ASC
)
INCLUDE(OrderDate,EmployeeID,PurchaseOrderID)
WITH DROP_EXISTING;
GO
Prior to applying the index, the execution time was around 340ms. After applying the index, the execution time
dropped to 154ms. The execution plan now looks like Figure
25-12
.
Figure 25-12.
The second execution plan after modifying the index
