June 28, 2007 at 6:04 am
The question is, for sql server 2005 Enterprise, if fill factors are set appropriately should we be able to rebuild/reorganize only on Sundays. We're using a commonly found stored procedure which checks fragmentation using the new management views and reorganizes/rebuilds based upon frag level. Recently moved to online reindexing which takes longer -- was averaging an hour per night but ran 3 hours last night.
On Sunday only we could probably do them offline. We're working on fill factors as the perception is that nightly reindexing should not be necessary if those are set correctly. Our mdf is now at 130GB, on a passive/active cluster with attached SAN -- I'd be interested in hearing from people with larger databases as ours will be expanding towards 500GB over the next year.
Randy
June 28, 2007 at 6:19 am
Yes even when fill factors are set rightly Reindexing/Reorganization has to be done periodically as fragmentation happens whenever data gets changed/added/removed. IN SQL 2005 ONLINE INDEXING can be done but that is not the right thing to be done in production hours for each and every database. So better schedule the index reorganization jobs to run in non-production hours mostly week ends nights is a good choice for them.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 9:35 am
I think you're starting from the wrong assumption, it's highly unlikely you can mess with fill factors to eliminate fragmentation - and none of your hard work will offset the effects of deletes - updates may well catch you out too unless you fully understand the difference between inplace and deferred updates.
Fill factors also increase database size, scanning and thus io - you may also find otherwise useful indexes cease to be used due to excessive fill factors. I can't see your original post but I think I saw 500gb - that's really quite a small database.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 28, 2007 at 11:43 am
We're not trying to eliminate fragmentation, just keep it to a level where weekly reindexing will be enough -- rather than nightly reindexing. I believe the team experimenting with fill factors is looking at 90 or 95. If you have 100 as fill factor, as soon as updates begin you get splits and wind up with data on different pages/extents -- isnt' that what leads to excessive IO?
As far as database size, I just meant that I'm seeing the full backup take about an hour now at 130GB and online reindexing/reorganizing taking 90 minutes on average on weeknights. Even if 500GB is small by industry standards, if nothing changes we could see an extremely long "maintenance window" in the middle of the night, which would not be acceptable.
This is an extremely normalized database -- structure initially designed in Visio. I don't know if that is part of the issue here ( I wasn't on the design team ) but I'm expecting that as it grows, we will need to have multiple databases on several sql clusters, rather than all on one.
June 28, 2007 at 1:18 pm
this depends on your local environment
i try to run it every night since sometimes things happen and you miss a few days. this way it gives you some overhead
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply