September 15, 2011 at 6:56 am
I am planning index maintenance on a SQL Server 2005 Enterprise Edition system with some large database tables (the largest is over 25 million rows, about 6.5GB including data and indexes), and trying to decide how often to run this since it does have some performance impact on the system while it is running. I have been following the Microsoft recommendation and using ALTER INDEX REORGANIZE if the index is between 5% and 30% fragmented.
My question is really - is there a benefit to reorganizing when the index is less fragmented? For example, will it run faster/take less resources when it is only 8 percent fragmented, than if I wait until it is 15 percent fragmented before running the reorganize?
Thank you very much for any assistance.
September 15, 2011 at 6:59 am
The difference is in # of page read, hence also ram + cpu + disks.
The difference might be minimal or very noticable depending on what type of queries you are running.
The only way to know is to monitor and compare.
September 15, 2011 at 9:56 am
Thank you very much for your response.
So does this mean that given the same RAM + CPU + Disks, an index with more pages but say 10% fragmented, should take more time to reorganize than the same index with less pages, but 20% fragmented?
I will keep an eye on the page_counts for these indexes, and monitor as you suggest.
September 15, 2011 at 9:58 am
April Clough (9/15/2011)
Thank you very much for your response.So does this mean that given the same RAM + CPU + Disks, an index with more pages but say 10% fragmented, should take more time to reorganize than the same index with less pages, but 20% fragmented?
I will keep an eye on the page_counts for these indexes, and monitor as you suggest.
I was refferring to normal day to day use.
Obviously more fragmentation is likely to mean more work to "repair".
What you need to monitor is maybe 10 execs of each of the normal queries hitting the fragmented tables. Log the figures (cpu, reads). Then rerun the same code after defrag and see if there's any difference and if so how much.
I'm sure you'll see some difference, but the question now becomes is it really worth all the troubles.
September 16, 2011 at 5:28 am
Just to clarify - my question is more about how long the ALTER INDEX REORGANIZE command itself will take to run, rather than if there is an improvement in the speed of other queries after the index has been reorganized. This is because the system does experience slow query performance while the reorganize command is running on the largest tables. We have been running the index maintenance every 2 weeks, but I am wondering if we run it more often (say once a day, or once a week) if it will take less time to run because there will have been less changes to the table and the indexes will be less fragmented, so like you said "less work to repair".
September 16, 2011 at 5:37 am
Yes it should be faster to run when there's less fragmentation.
I would add that you can consider doing alter index... WITH MAXDOP
That way you could keep most of the processing power online for normal activities. That way even if the index reorg takes 2 hours, maybe nobody will notice.
I also suggest you read through this. This is how I've come to do my setup here. It might give you a few more pointers. http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx
September 16, 2011 at 6:08 am
Thanks for that - very useful information!
September 16, 2011 at 6:11 am
Ninja's_RGR'us (9/16/2011)
I would add that you can consider doing alter index... WITH MAXDOP
Index reorganise can't parallel. It always runs on one thread, no matter what the maxdop settings. It's rebuild that can parallel.
http://technet.microsoft.com/en-us/magazine/hh395481.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2011 at 7:45 pm
Generally Index maintenance should not be done during the business hours. Yes, less fragmented indexes will take less time for the Reorg. Also Index maintenance should be done after the business hours. Usually Index Reorg\Rebuild doesn't have much impact on the fragmentation on small indexes with pages less than 1000.
So when doing the Reorg\Rebuild, you need to take Fragmentation, Number of Pages and the Index depth factors into consideration. Use excellent scripts from http://ola.hallengren.com/ for index maintenance
Do the index reorgs as regularly as possible. The longer the interval between the Reorgs the greater will be the fragmenation. If the interval between the reorgs is more and if the fragmentation increases more than 30% then reorgs will be less effective than the Rebuilds. Regarding how long it takes to do Reorg, you need to test it at least once like may be during Friday Night to get an approximation of the Time.
Generally it is recommended to do Reorg and update stats nightly and weekly rebuilds. But for very large databases the frequecy may be bit more like monthly rebuilds or once in 15 days.. Atleast you should try to do the Index Maintenance for the critical indexes more frequently than the less important ones..
Each environment and business is different and you need to adapt these general recommendations accordingly..
Thank You,
Best Regards,
SQLBuddy
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply