January 5, 2006 at 11:38 pm
Hi Gurus,
I've a doubt, Please correct me if there any mistake on my questions.
My databases sizes around 75 GB size, the transactions perday around .5 GB.
How often we can use DBCC INDEXDEFRAG / DBCC REINDEX ?
please guide me.
warmest
JK.
Thanks
Jay
http://www.sqldbops.com
January 6, 2006 at 2:20 am
Hi,
it all depends on how much fragmentation you get. It might be weeks or months till you get the level of fragmentation that requires rebuilding or defragging indexes. Or it could be just days.
What I do is check the level of fragmentation on each table in my databases every night and if it is past a certain threshold I run an indexdefrag (because that is an online operation). More often than not the indexdefrag will do the job but sometimes it doesn't, in which case I mark that table for an index rebuild. Subsequently that table's index is either rebuilt on the spot or I just get a mail telling me that it should be rebuilt.
There's a good example of how to use DBCC SHOWCONTIG and DBCC INDEXDEFRAG in BOL (example E) under DBCC SHOWCONTIG. That should get you started.
Hope that helps.
January 6, 2006 at 3:14 am
January 9, 2006 at 7:55 am
REINDEX is far more efficient in my opinion, especially where fragmentation on clustered indexes is concerned. But ofcourse it has a lot of overheads. So normally what I practice is to run the indexdefrag on a regular basis and run the reindex a couple of times a year. But like Karl says, each situation will be different, so best to use DBCC SHOWCONTIG to monitor fragmentaion levels in your DB and then plan how you are going to tackle it.
January 9, 2006 at 4:15 pm
I would suggest run reindex job(s) on wknd basis. You can start implemening that by creating a table which list your table names and subsequent indexes. And then, when scandensity falls below 75%, you run reindex....
January 9, 2006 at 8:40 pm
H!!!,
You had mentioned that the size of your database is 75 Gb ,so you have to first find out the number of records in each table & do reindexing for the table having more number of records ,you can also check the logical and physical fragmentation of the database by using DBCC SHOWCONTIG depending on these you have to defragment or reindex the tables of your database .
regards,
Vinod S.R (DBA)
HTC Global Services Chennai
09840856202
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply