I’ve just wrapped up a quick bit of work with a client who was just getting moving on their DW journey, and I found some fun things that the rookies had done.
I was also partly inspired to talk about this particular issue by this timely DBA Reactions post: When the developers hear my warning about 27 indexes on a single table
Part of what I was looking at was applying compression to their tables to reduce the disk space consumption. So I ran my handy query to size the databases and found an 8GB table with 12GB of indexes! Yoinks – something was clearly astray… it turned out that one table – with about 30 columns – had 8 indexes on it.
Why not have 27 indexes?
So there’s a few good reasons:
- That many indexes take a long time to build / update, slowing load performance (in this case it took about 5 minutes to load the table and a further 29 minutes to build the indexes….)
- The Query Optimiser is going to struggle to pick the right index if any of them overlap
- If you are on 2012+ you can just stick the entire table in a columnstore index, massively boost performance and consume less disk space (applying a columnstore on the whole table took a whopping 1 minute to build)
As a broad rule I’d consider these rules of thumb following when looking at a tables indexes:
- If I have more than 2 indexes, I’m probably not indexing well, my table design is wrong or my queries need re-evaluating
- If my indexes are more than 20% of the size of the table, I should probably look at replacing them with a whole table columnstore
- Are my indexes making use of included columns to increase their coverage?
If any readers have some good index management tips, please share them in the comments!