March 4, 2009 at 8:44 pm
Hi Everyone. At one point I was rebuilding the indexes on our main database once a week but it was recommended that I stop due to some other locking issues we were having. It seems we have the locking issues figured and now i am wondering about rebuilding indexes again. AT what point should I decide to rebuild or should I just carry on with once a week?
March 4, 2009 at 9:30 pm
On our systems that are used primarily for monthly reporting we reindex after the data has been loaded each month; others that are transactional systems but with a peak at month end we reindex the weekend before month end; but most we just run a weekly reindex against.
If the process that you're using to rebuild your indices only rebuilds those that are fragmented beyond a certain threshold then you can run it as often as you need to. How often that is depends on your environment; mainly on what your system is being used for, how quickly the tables get fragmented and how much fragmentation impacts performance. Weekly is overkill for most systems but it fits in neatly with business-hours-only systems.
If you're running Enterprise Edition you can look at online reindexing too: it takes longer but any blocking is hugely reduced both in frequency and duration.
March 5, 2009 at 8:14 am
Rebuild indexes when avg_fragmentation_in_percent > 30
Check-->http://blogs.msdn.com/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
For script-->http://sql-server-performance.com/Community/forums/p/27772/149694.aspx
HTH!
MJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply