SQL Server Advanced Troubleshooting and Performance Tuning by Dmitri Korotkevitch

Author:Dmitri Korotkevitch [Dmitri Korotkevitch]
Language: eng
Format: epub, pdf
Publisher: O'Reilly Media, Inc.
Published: 2022-04-25T00:00:00+00:00

Figure 5-21. Indexing example: Plan with the index on Customers table

There are three predicates with Orders table columns. Two of them—on the CustomerId and OrderDate columns—are selective, and thus good candidates for the index. You can define the index with either (CustomerId, OrderDate) or (OrderDate, CustomerId) column order.

To choose, consider how data will be sorted in the index. With the first option, (CustomerId, OrderDate), SQL Server sorts the data by CustomerId first. Then the orders for each customer are sorted by OrderDate. With the second option, the data will be sorted by OrderDate across all customers.

Both indexes will allow index seek in the Orders table. However, the first index is more efficient for our query. SQL Server will be able to do a range scan for orders that belong to this single customer for the time interval defined by @StartDate and @EndDate. With the second index, SQL Server would have to read all orders in that time interval for all customers, which would force it to scan more data.

It’s good to add the Fulfilled column to the index as an included column, to evaluate the predicate as part of the seek operation. In this case, I’d also include the Amount column – it is small enough and would not increase the size of the index much.

Figure 5-22 shows the final execution plan after the following index has been created: CREATE INDEX IDX_Orders_CustomerId_OrderDate ON dbo.Orders(CustomerId, OrderDate) INCLUDE (Fulfilled, Amount).

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.