Rx for Demystifying Index Tuning Decisions – Part 5

By Jeffry Schwartz | Missing Indexes

Nov 30

Review

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:

  • Types of indices and their variations, e.g., clustered, nonclustered, filtered, and covering
  • Physical versus logical index access methods, e.g., seeks, scans, key/RID lookups, singleton lookups, and range scans
  • Why index tuning should begin with queries
  • Determining an Appropriate Strategy for Index Tuning
  • Determining Queries that Need Indices the Most
  • Capturing query performance information using Extended Events
  • Using query plans to determine relationships between queries and indices
  • Query Optimizer
  • Statistics and how they affect index usage and query performance
  • Consequences of too many indices

 

Part 5 covers the following topic:

  • Overlapping Indices

Overlapping Indices

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.

Blog_20171130_1a

Table 6: Overlapping Index Examples

 

Blog_20171130_2a

Table 7: Overlapping Index Usage

 

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.

Blog_20171130_3a

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!

About the Author

>