Over indexing

Question

Hi All,

How to tell if the index is useful for my reads or it’s a burden for my DML operations?
What is the optimal number of indexes that should be created on a table ? I know having too many indexes is like increasing the weight of the database.

Best Regards,
Bob

Answer 1

Indexes are used to support queries. There is not an "optimal" number of indexes for a table. The number of indexes completely depends on how you query the table.

Having unused indexes wastes disks space and resources to maintain the index. As well as user time during insert/update/deletes.

I suggest you use this script. However, the script uses DMVs which are reset every time the server is restarted. So you need to monitor the results over time to get a full picture of indexes which are truly not used.

https://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

Answer 2

Hi @Bob sql ,

Agree with Tom.
It depends on the operations that occur on the table. If there's lots of SELECTs and very few changes, you can create indexes on every column which you like which will (potentially) speed the SELECT statements up.
In contract, if there are many UPDATEs, INSERTs + DELETEs you need to do, these will be very slow with lots of indexes since they all need to be modified each time one of these operations takes place.
Add a lot of pointless indexes to a table that won't do anything. Adding B-Tree indexes to a column with 2 distinct values will be pointless since it doesn't add anything in terms of looking the data up. The more unique the values in a column, the more it will benefit from an index.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.