June 13, 2008 at 7:45 am
Hello,
I'm new to rebuilding and reorg indexes. I used the standard report feature in 2005 to look up Index Physical Statistics on one db. I found recommendation to rebuild a few indexes that contain a number of fragments. I created a maint. task, I know maint. tasks are not a cure-all but so far I am just testing the waters. I created two tasks, one to rebuild and another to reorg. After I ran the job, I looked at the report and it still showed recommendation to rebuild the indexes. What is a better solution for my case?
June 13, 2008 at 2:13 pm
I think the thing about index rebuilds etc. is what window do you have to do this. If you have a smallish database and a suitable window I'd probably go for blanket rebuild - e.g. I have a small database 15GB, it takes approx 9 mins to rebuild every index. If you want to do selective index rebuilds then you have to run a query to extract that information, this is less intrusive in sql 2005 but it still takes time - then you need to decide the logic on what basis you'll rebuild. I don't normally ever bother with re-orgs, going for full rebuilds.
It's all a case of available window and database size.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 13, 2008 at 3:59 pm
I think that small indexes often show a high fragmentation also after you've just rebuilt them. As I understand it fragmentation on small indexes has a very low performance impact. I wouldn't be concerned about that.
Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Kalen Delaney has also written a blog post about this.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
I have a stored procedure that you could use to dynamically rebuild or reorganize indexes. It is using the dynamic management view sys.dm_db_index_physical_stats.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
You could also use the script in Books Online.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
Even if you could do a rebuild of all indexes in your maintenance window it could still be beneficial to only rebuild the indexes with a high fragmentation. That is because of that it reduces the amount of log records that is generated and thereby the size of the log backups.
Ola Hallengren
June 13, 2008 at 10:35 pm
Great comments ola!
I'm sure you already know this, but for those that may not, the reason why smaller indexes become fragmented quickly is because there are fewer index levels and pages in the index tree. This means that even a single page split (as the result of an INSERT perhaps), can represent as much as a 50% increase in fragmentation, when in actual fact there may only be 2 leaf level pages in the index tree. Hence ola's comments regarding why Microsoft reccommend that you negate index rebuilds on smaller tables/indexes.
Cheers,
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply