Working with Tables is the developer's daily routine. But SQL Development is not very often. In some cases, I saw in my past. That many developers create tables with one index.

The Problem

While the product is used in production, it will be going slow when the data amount increases. Every developer say “Yo! Create an index”.

This can result that the index will be larger than the table itself.

What causes the index size?

There are several causes that increase the size of indexes.

Too many indexes in the same columns.

First of all, analyze your indexes. Drop unused indexes. Also, reduce the number of columns in existing ones.

Remember that the clustered index column(s) is a “hidden” included column in all non clustered indexes

To identify the usage of indexes, you can use the following query:

The output will result like this:

This will display when and who the index will be used.

Overlapping indexes

If you have an index on a,b,c,d and an index on a,b,c you might consider dropping the second one as the first one covers the second one. So try to aggregate overlapping columns into one index.

Index Fragmentation

Indexes can get fragmented, you can get an overview about the fragmentation of the actual indexes with the following query.

In this case, you can rebuild the index with the following command

ALTER INDEX INDEX_NAME; ON TABLE_NAME REBUILD

After that, the index will be rebuilt and the fragmentation will be decreased

Identify what is stored in an index file?

First, you must know, that an index will store whatever you tell to store. This query will help you tell which indexes are using the most space and for what reason (in row data, lob data)

Find the best solution for you

As you can see in the diagram when you create more indexes, you get a higher consumption of index space, and hard to maintain these. But fewer indexes will decrease the query performance. It will be hard to tell anyone which practice fits. So it can be said, “It depends on your data”. When you want a rule of thumb:

Use the “5 and 5 rule”. This means that you can create 5 indexes with 5 max. 5 columns per table.