January 29, 2010 at 7:50 am
An even better solution would be to selectively reorganize/rebuild indexes based on fragmentation levels. This way you're not incurring unnecessary overhead of defragmenting indexes that aren't fragmented enough to impact performance. It will keep your logs at sizes within your requirements without having to adjust transaction log backup intervals or database recovery models. Two good examples of defragmenting indexes based on fragmentation levels:
http://ola.hallengren.com/ - look for the IndexOptimize link
http://sqlfool.com/2009/06/index-defrag-script-v30/
You shouldn't blindly copy/paste and begin using either of these scripts without making sure you understand the input parameters and scripts, so that you can make adjustments to fit your environment.
January 29, 2010 at 4:11 pm
I agree with beargle ...
why do you want to rebuild all the indexes ? Its smart to rebuild if there is more fragmentation (I normally do it above 30%) otherwise reorganize it ...
HTH !
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 1, 2010 at 7:41 am
OK lets assume the fragmentation level is high and we really need re-building indexes every week then what is the solution ?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply