At the moment I am tuning the indexes with high fragmentation ..I created a maintenance plan for rebuild /reorganize ( set the logic for fragmentation above 50% then I will do rebuild , otherwise I will reorganize ... fill factor = 80 . I also filtered by the number of page count.
noticed there is 1 index which is fragmented very quickly starting from 2 am until 7 am .
I did rebuild / reorganize every 2 am ( after log backup at 12 am)
I believe after rebuild it will become 0% but after 3 hours it will become 80% I guess...
I check the unused index data ( using the script ) and for that index i got this data :
User_Seek : 0
User_Scans : 16
User_lookup : 0
User_updates : 1.128.932
I wonder that I should just drop this index or keep maintain it with rebuild it AGAIN after a few hours later ...
Please kindly advice...thanks heaps
Hi, Just posted this in the 2012 section but same applies. Ask your self the below question before you start setting up Index rebuild maintenance plans. DBA's have noted in the past that this can be one of the main causes of bring a SQL server offline.
Before you start reorg'ing or rebuilding your indexes, ask yourself:
Are we actually having any performance issues with the database? What have your users said?
What is the profile of the data in the table? How many inserts / deletes / updates?
When were the statistics last updated on the tables?
If rebuilding, when can you do this? Controlled outage? Online or Offline rebuild? How will this affect the disk space on the server?
Have a read of these two articles before you proceed as it requires a bit more thought other than which option you should choose.
SQL Server Best Practices Supporter