I have searched the Internet a lot about this but I'm still not comfortable making a decision.
One of our reporting databases is 350 GB (it acts as a subscriber for transaction replication where indexes are not moved during replication), the index file group was created almost 4 years ago residing on its own drive.
This drive is now almost full with index files occupying 200 GB created for various tables involved on subscribed database.
My main goal is to start cleaning the 3 most used tables. Below are the details:
Table 1: 88 GB with 98659075 rows. Number of nonclustered indexes=88
Table 2 : 52 GB with 17793712 rows. Number of nonclustered indexes=52
table 3: 48 GB with 8666642 rows. Number of nonclustered indexes=44
The number of indexes definitely seems large, but since this is a reporting db server does that sound reasonable? Is there a specific number of indexes on the same table which raises a red flag for performance (just want to know what you guys think)?
Secondly, I have gathered a months data from the dmvs
dm_db_missing_index_groups to decide which indexes I can safety remove. So, just want to know what should be my strategy here based on above gathered data to delete the indexes for better performance and to save the disk space as well?