Question about DBCC DBREINDEX

[sqlcasey]

Hi,

I have a weekly job that examines the index fragmentation of all tables in a given database.

If the index fragmentation reaches a certain threshhold, then it runs DBCC DBREINDEX against the table, with a fillfactor of 75 (the decision to use 75 was somewhat arbitrary on my part).

However, I am noticing that the same tables are being reindexed on a weekly basis, which would indicate to me that 75 is not a sufficient fill factor. That is, maybe it should be lower? - like 60 or 50

This seems to be a rather trial and error process... but does my reasoning to lower the fill factor to 50 or 60 sound right? (and keep monitoring of course)

Thanks

 

[ptheriault]

Casey,
Keep in mind here that the lower you make the fill factor the more disk space you need. That is because the database will need more pages to index the table. But you are on the right track. By reducing the fill factor you will also decrease the amount of page splits. Page splits use up I/O and can cause performance slow downs. Finding the correct fill factor is trial and error you will need to find a happy medium cost of space, number of page splits, fragmentation and time to reindex. You might want to check out the DEFRAG Index command from BOL. It can be a nice alternative to REINDEX.

- Paul
- If at first you don't succeed, find out if the loser gets anything.