Reindexing how often

[zircon06]

We have 78 gb of database. what is the best practice and how ofen to run reindexing on the database.

Thanks in advance

 

[ptheriault]

It depends on how transactional your database is. For example I have 238 tables in my primary db. About 50 of these are very transactional, meaning the data changes by a lot throughout the day. I re-index these tables weekly. The rest of the database I do bi-weekly. I also update stats nightly.

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

 

[SQLBill]

This is where it is really important to know your data, as Paul says. I would find out how fragged your tables are and run the same query every day for a week or two. This will show you how fragmented each table is getting (or not). If the fragmentation values stay the same for the whole week or two, you know that table doesn't need defragged or the index rebuilt often. If you see tables that are having large changes in the fragmentation, they should have their indexes rebuilt. By getting the data daily, you can see if there are specific days that see the fragmentation grow the most, reindex after those days.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875