More On Column Order Indexes – Part 2

Question

More On Column Order Indexes – Part 2

Mike Byrd

Default port

Points: 1458
More actions
April 24, 2019 at 1:43 pm

#3045213

Comments posted to this topic are about the item More On Column Order Indexes – Part 2
Mike Byrd

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike Byrd Default port Points: 1458 More actions · Answer 1

The article is missing the last 2 pages of Figures and text. Not sure how SQLServerCentral lost that, but I will try and put rest of article back if they let me.

Mike Byrd

Mike Byrd Default port Points: 1458 More actions · Answer 2

The rest of the article (minus Fig 16 & Fig 17) is below:

with now a clustered index seek on CIX_SalesOrderHeaderBig_OrderDateSalesOrderID having a query plan cost of 83.0901, 3595 logical reads, and cpu = 640ms.

Summary

Results of all the above combinations of queries and indexes are summarized in the chart below:

Figure 17: Performance Summary

Obviously the suggested covering nonclustered indexes have the best performance for their specific query. In many cases when I am trying to remove near-duplicate indexes I try to make several indexes (with same first column definition) into a single overall covering index. This requires some testing because making a covering index too large (width) increases the logical IOs (more pages to define this index) and may defeat a performance gain. If you examine closely the modified clustered index data (now based on OrderDate) its performance is nearly as good as the covering index performance. If there are other queries that use OrderDate in their WHERE clause then they also can benefit from the revised clustered index. So, when confronted with a suggested index, try it, but also consider its cost in storage, IO requests, maintenance, and perhaps a revised clustered index might just be better when looking at the big picture. In any case, as Microsoft always says, TEST, TEST, TEST!

Sometime being a good DBA requires a delicate balancing act – do I create the perfect nonclustered index (for a single query), or do I create a large covering index for several queries, or can I still get adequate performance (and less storage space) revising a clustered index. There is no correct answer here, and the decision has to be made in the context of the table in question usage. Please consider this article as a starting point not only for initial table architecture, but also for existing table architecture.

I look forward to discussion on this topic and welcome any and all comments.

Mike

Mike Byrd