October 26, 2008 at 5:13 am
victor_bosc (10/23/2008)
And, what do you think about only rebuilding indexes once a week?
that is the strategy i employ
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2008 at 3:02 am
Gail's already answered that - (re)read the last two lines of her last email.
October 28, 2008 at 9:17 am
1) You should take your current data size, figure out how much data space (including indexes) you will need 12-18 months from now, and make your database THAT BIG right now! This free space will allow index defrag evolutions to lay down contiguously. As Jeff said - be PROACTIVE in your file management, do NOT rely on autogrowths for that.
2) I would examine your physical OS-level file fragmentation. This shrink-grow cycle you have been in for however long probably resulted in severe disk fragmentation, especially if you have the default growth increment of 1MB on the data file. If you have frag problems, find a maintenance period and shut down sql server and defrag the partitions. IIRC, Diskkeeper says they can defrag a running sql server if you have a need for 24/7 operations.
3) Use a script to control index maintenance. See BOL under sys.dm_db_index_physical_stats for a starter script for this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply