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:

  1. Table 1: 88 GB with 98659075 rows. Number of nonclustered indexes=88

  2. Table 2 : 52 GB with 17793712 rows. Number of nonclustered indexes=52

  3. 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_index_usage_stats,dm_db_missing_index_columns, dm_db_missing_index_group_stats,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?

  • 2
    We can't really answer this question. Is a month's worth of usage enough for you to make a decision? What if you have reports that are run at end of quarter, end of year, at tax time, seasonally, only after exceptional events, etc.? – Aaron Bertrand Mar 3 '16 at 15:42
  • @AaronBertrand, Agree with you on a decision to make. Although i had this confirmed from reporting team and report owners as well that 99.9 % of the reports which covers all the important ones are either run daily weekly or monthly, hardly there is any which runs in quarter or yearly. The data i have gathered is from Jan1st 2016. Any starting point, because i had to remove now since disk space is all used here. – BeginnerDBA Mar 3 '16 at 15:47
  • 1
    Ok, then it seems obvious, delete the ones that have only write operations and no reads, and then after that, again, I don't know how we can help with strategy - is an index that has a read:write ratio of 20% worth keeping? 25%? 50%? 75%? We have no idea where your trade-off will lie. In general, you want to favor keeping the indexes that have lots of read activity. In specific, you're on your own. – Aaron Bertrand Mar 3 '16 at 15:49
  • Of course an exact duplicate index can always be deleted with prejudice. – Erik Mar 3 '16 at 15:50
  • 3
    The ratio I'm talking about is simply user_seeks + user_scans to user_updates. But keep in mind these are operation counts not row counts. You could have a billion updates to single rows and 50,000 scans; the latter could be worse even though it's a much smaller number of operations. Again, these are all just ballpark guidelines and deleting an index requires your subjective judgment. Another piece of info you might want to have is the opinion of whoever actually requested or created the index you're about to delete - that'll be a more meaningful story than what the DMV tells you. – Aaron Bertrand Mar 3 '16 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.