October 14, 2011 at 2:44 pm
If I have a table with 10 indexes on it and every index is fragmented by %50 and it takes 1 hour (60 minutes) to rebuild all those indexes then if I rebuild the indexes again so that the conditions are the same (no one is in the system and no backups are running) should it take
A) LESS time to complete since the fragmentation is now minimal
B) The SAME amount of time to complete - fragmentation does not affect duration or time to rebuild an index
C) MORE Time to complete - because my database design was done by a VB developer who’s SQL training is limited to reading "SQL Server in 24 hours or less"
D) Other
I'm just curious if the fragmentation percentage of an index has any bearing on how long it takes to rebuild the index.
I currently have a very large and very volatile (depending on the time of the month) table with a dozen indexes that takes just under 90 minutes to rebuild no matter when I execute the Rebuild or even how frequently. The index rebuild started out taking 30 minutes to complete when run once a week at 2 AM during a weekend night when nothing was going on in the system. Since that time which is about 3 years ago the job has increased to where its now almost 90 minutes. I had hoped the executing the rebuild more often would reduce the time by some amount but it has not.
Is that just how Index Rebuilds work? I guess the biggest factor involved (when rebuilding in an environment with little to no activity as it is late at night) is the size of data, specifically the rows that the indexes cover.
If thats true then is there any way one can reduce the time it takes to do Index maintenance? I have not tried Reorganizing yet but if the process that reorganize uses is affected by actual fragmentation then perhaps it would be better to reorganize often and seldom rebuild?
Thoughts?
Kindest Regards,
Just say No to Facebook!October 14, 2011 at 11:02 pm
10 indexes.... design done by a developer with limited sql experience... It makes me wonder if all those indexes are needed? If you want to post the table and index schema, we can dig into that. What does sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats return for that table?
Regarding your original question, an index rebuild should be quicker the second time as the scan of the leaf level pages will have less random IO. Additionally,if your page density was low before the first reindex (say 50%) and you rebuild it with a fill factor of 90%, the index will take up fewer pages on disk, so, less read IO required as well.
Reorganize is slower than a rebuild. It is online, and you can stop it without losing work, so trade-offs abound.
You will always have the write time of the data.
Are you rebuilding all the indexes on the table, or just the ones that are heavily fragmented?
October 18, 2011 at 11:06 am
SpringTownDBA (10/14/2011)
10 indexes.... design done by a developer with limited sql experience... It makes me wonder if all those indexes are needed? If you want to post the table and index schema, we can dig into that. What does sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats return for that table?Regarding your original question, an index rebuild should be quicker the second time as the scan of the leaf level pages will have less random IO. Additionally,if your page density was low before the first reindex (say 50%) and you rebuild it with a fill factor of 90%, the index will take up fewer pages on disk, so, less read IO required as well.
Reorganize is slower than a rebuild. It is online, and you can stop it without losing work, so trade-offs abound.
You will always have the write time of the data.
Are you rebuilding all the indexes on the table, or just the ones that are heavily fragmented?
Thanks for replying, you're the only one so far.
Sadly it’s a schema/design we can't change else we void our support agreement with the Vendor whose application uses this DB. After I typed out my original question it got me to thinking that the Rebuild would probably be the same each time regardless of the fragmentation since the rebuild is effectively the same as creating the index. That of course assumes I correctly understand what the Rebuild process is doing.
I figure that if I have 2 identical environments and each has the exact same copy of this DB except that COPY A has no indexes in these tables and Copy B does then if I rebuild the index’s in B it’s the same thing as creating the indexes in copy A save for a few meta data operations I assume CREATE INDEX would cause that REBUILD INDEX does not. After all, as far as the info the index stores after its rebuilt is it any different than if the index were dropped and created a new?
Because we can't change the indexes (i.e. drop poor performing ones ) I think I will look at doing a mix of Reorganize with the Rebuild and see if that cuts down my overall time that Index Maintenance is running.
Thanks
Kindest Regards,
Just say No to Facebook!October 19, 2011 at 11:33 am
Indexes fragmented more than 30% really should be rebuilt rather than reorganized.
Todd Fifield
October 19, 2011 at 11:45 am
tfifield (10/19/2011)
Indexes fragmented more than 30% really should be rebuilt rather than reorganized.Todd Fifield
Unless you need to keep the table online and don't mind having it run longer ;-).
But ya rebuild is my default here.
October 19, 2011 at 12:55 pm
Ninja's_RGR'us (10/19/2011)
tfifield (10/19/2011)
Indexes fragmented more than 30% really should be rebuilt rather than reorganized.Todd Fifield
Unless you need to keep the table online and don't mind having it run longer ;-).
But ya rebuild is my default here.
If you need to keep the table online, then you just have to bite the bullet and shell out the bucks for Enterprise edition. Then you can rebuild online.
Todd Fifield
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply