October 21, 2008 at 8:58 am
Hi all,
quick question.
Assuming I had a maintenance plan (all user databases) to deal with index fragmentation (all tables) that performs an index re-org and then an index rebuild.
Does the re-org task automatically only deal with indexes that have a fragmentation level less than X and the rebuild deals with those that have a value greater than X?
Or is it a case that the index defrag is going to do everything (even the ones that are heavily fragmented) and then the rebuild is going to re-build all the indexes, thus meaning it more effective to run the re-org only on the lightly fragmented tables and the rebuild on the more heavily fragmented tables’.
Hope the above makes sense.
Jackal
October 21, 2008 at 2:10 pm
As a rule of thumb, the indexes need to be re-organised when the fragmentation(logical scan) is higher tha 10% and the Rebuild is done when the fragmentation is greater than 30%.
Well, if you rebuild the indexes that are heavily fragmented, the value of logical scan fragmentation would fall below 10% thereby there wouldn't be any need to reorganise them all over again.
However, for indexes with high fragmentation, the reorganize would not be as effective..
Hope this helps..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 21, 2008 at 4:25 pm
Jackal,
Is your question, are the maintenance tasks aware of the index fragmentation levels and rebuild/reorg only the indexes as necessary? I haven't found anything definitive, but I don't think so. Running a reorg and then a rebuild (or vise versa) will touch each index twice. My opinion here is based on generating the TSQL script from the maintenance plan and seeing that it references each index in the selected database(s). Experts, please correct me if I'm wrong 😀
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply