Index Hoarding

Your indexes just can’t let go. Literally. We all go through rough times in life — both people and indexes alike. Some things happen to us that weigh us down and make getting through the day harder.

This alert is saying that your indexes have some baggage. In fact, they have so much baggage that they may well be hoarders.

Hoarding is something that happens by degrees. If you’ve reached this landing page then you want to look at all  your diagnoses and determine how much they’re impacting your database, and what it’s worth it to you to change.

There are lots of special cases in index configuration. We look for signs of over-inflated indexes that are indicative of a larger pattern. You take these indicators and use deeper analysis to find out if it really is a problem.

Many NC indexes on a single table

How many nonclustered indexes is too many indexes? This varies by database usage and design, so we had to draw an arbitrary number in the sand.

If you get this diagnosis, you have a table that has 10 or more nonclustered indexes on it. 10 is very likely too many indexes for read-write tables in most databases.

When you get this diagnosis, use the command in the more_info column to closely examine all the indexes on the table and their usage statistics. Here’s an example of that command:

More than 5% of indexes are unused

sp_BlitzIndex® looks at the total number of indexes in your database and their usage statistics. If 5% or more of your nonclustered indexes show as unused, this diagnosis is made.

If you get this diagnosis, look carefully at all the indexes that have 0 reads. To make that easy, those indexes are listed by the next diagnosis….

Unused NC index

An unused NC index is a sad thing. Or is it?

To interpret this information, you need to know how usage is calculated. Index usage information is persisted since SQL Server’s restart (there’s a gotcha on SQL Server 2012– see below).  But of course,  if I create a new index, its usage is only going to be persisted after the time it was created.

You may also have cases where indexes are only used at special times, but are still very important. (Think end-of-quarter and end-of-year reports  run by the CFO, CTO, and other acronyms.)

Unused indexes may also be performing other important functions, like serving as primary keys. If an index is a primary key, the index_definition column will contain a “[PK]”, like the index in this example: [PK] [KEYS]  BusinessEntityID

Borderline: wide indexes (7 or more columns)

Are indexes measured by weight, or by volume? I think weight is the amount of GB the pages take up, and volume is the rowcount, so I guess they’re measured by both.

The more columns you add to an index, the “heavier” that index gets. This is particularly important if writing to the table happens. The more indexes I add, the more work my writes have to do. The more columns are in each index, the more that gets magnified.

One important thing to note for this check is that we add up the key and include columns and look for any index with a total of seven or more.

Are there cases where it’s just fine to have indexes this wide or wider? Certainly. But generally speaking if you make a practice of this, your database is going to be bogged down by all the index data it’s dragging around.

Clustered indexes with more than one column

Your nonclustered indexes have a secret: they’re hiding the clustering key of the table as a special type of included column! This is functionally necessary so that you can quickly use a nonclustered index to look up related index in the clustered index.  (The one exception to this is if your table does not have a clustered index– in that case the nonclustered indexes hide something called a RID, or row identifier instead.)

This means that if you create a wide clustering key on a table with many columns, every nonclustered index you create on that table is going to contain every column that is in the clustering key.

Wide clustering keys add up to database bloat. While we’re not arguing that composite keys are always wrong (because them’s fighting words in some circles), always be aware if you have multiple columns in your clustered index and keep track of the impact.

Non-Unique clustered indexes

You don’t have to make your clustered index unique. But if you don’t, SQL Server has to do it for you. When you create a non-unique clustered index, SQL Server needs to check if a row is a duplicate. When duplicates exist, a four byte uniquifier (plus extra bytes on the row for the overhead of describing what’s on the row and where it is) comes may be written. If a key doesn’t have a duplicate value then the uniquifier is left null.

Don’t think about this just in terms of space used on the row. The process of figuring out when you need to have a uniquifier and then applying it to the all the rows doesn’t come free! Plus, since the clustered index sneaks into those “secret columns” in your nonclustered index, this uniquifier can really get around.

The best practice for a clustered index in SQL Server is that it be unique, as well as narrow, unchanging, and supporting healthy insert patterns. If your clustered indexes aren’t unique, take a very close look at why that’s the case.

NC Index with High Writes:Reads

This looks at nonclustered indexes that have 10x more writes than reads associated with them. This means that you’re doing a lot of work to maintain an index, and it isn’t necessarily helping a lot of read queries. This warning should be used alongside other warnings about duplicate, unused, and wide indexes when determining which to keep and which to disable. It could also be that someone just made a bad index all by its lonesome.