November 11, 2003 at 8:04 am
as-salam 'aleykum
Interestingly I used to rebuild indexes every weekend.Each weekend I did not rebuild no one called to say they are locked up.
"Haba " I now stay with what works and will only rebuild again after a while "unfortunately".
It can be said that there is a lot more than tables locking up that has to do with indexes and I agree but that is the one stand out that normally used to "go away" right after a rebuild in my case
I use the undocumented sp_msforeachtable @command1="print
'?' DBCC DBREINDEX('?')"
Mike
November 11, 2003 at 9:15 am
I believe that reindexing on a regular basis is a waste of time (generally speaking). If you have properly defined indexes with appropriate fill factors then you should not need to reindex very often.
If you have a table with 1 million rows and you add 10,000 per month that's only 1% of the total. if you have 90% fill on your indexes, it would suggest the need to reindex once or twice a year - certainly not weekly.
If you have 100,000 records in a table and suddenly add another 100,000 then you probably should reindex the table.
I'm not sure where this fascination with reindexing every week comes from. Maybe it's from the Maintenance Wizard in EM?
I'm sure other people have some views on this and I'm interested in what you have to say.
Jeremy
November 11, 2003 at 2:05 pm
as-salam 'aleykum
If you run dbcc showcontig often ,know which third party the database is for,what the database is used for and by whom it may be easy to see that index rebuilds are not mere fascinations
Mike
November 12, 2003 at 1:58 am
The data warehouse that i work on is defragmented weekly, rather than having the indexes rebuilt. The reason for this is that calling DBCC INDEXDEFRAG produces less locking than DBCC DBREINDEX. DBCC INDEXDEFRAG simply misses out defragging pages that are locked by other processes.
I put code in place to only DEFRAG indexes that are over 30% fragmented. Examples of this are in BOL.
However i discovered that even our largeset table of 80 million rows doesn't become too fragmented even though we insert around 2 million rows per week.
I agree that if you keep your data types tight and plan your fillfactor well then there really shouldn't be that much call for reindexing often at all.
Better to just monitor the situation and only reindex/defrag when necessary.
Sean
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply