Due to the holiday week, this part will be shorter than usual. In Parts 1 through 4 of this blog series the author discussed various aspects of SQL Server indices such as:
Part 5 covers the following topic:
Overlapping indices duplicate the functionality of one another in some way. The easiest way to understand this concept is to see an actual example as shown in Table 6. The simplest example is Inx4, which completely duplicates Inx3. Since the table contained almost 17 million records, this is a very undesirable situation. Table 7 highlights the fact that SQL Server only used Inx3 ONCE, so this provides even more incentive to eliminate Inx3. This data also indicates that Inx1 can be removed because it was never used. Note: the usage data was obtained using two different index DMVs/DMFs, which will be discussed later.
Examining the index layouts further we can see clearly that Inx1 through Inx4 could be consolidated into one index that would handle all usage. The composite index is shown below. It may be possible to exclude CurrCode and ECID from the composite index, but more research would need to be conducted before that choice could be committed to safely. Specifically, since the number of User Lookups was quite high on the table, some queries do not have all the columns they require and two of these may be CurrCode and ECID. Query plans are invaluable in answering these kinds of questions.
At first glance, it appears that Inx7 and Inx8 might be combined as well. However, since the secondary keys are different and both are used for range scanning only (see Table 7), there is no certain way to combine these and also handle the queries that use them properly. Therefore, it is best to leave these alone.
The information above was determined using the following system views: sys.all_columns, sys.databases, sys.dm_db_partition_stats, sys.index_columns, sys.indexes, and sys.tables.
The next article in this blog series will cover dynamic management views. Until then….happy tuning and please contact us with any questions.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.