Database re-indexing - best practices advice sought

I have a 15GB database which has a daily maintenance plan to optimise the tables by setting their fill factors back to 90%.

The problem is that it creates a huge amount of database transactions. The next transaction log backup after the reindex comes to 11GB!

What is the best way to run reindexing? Should I write a script to put the database into simple recovery model and then run the reindex? But if I did that, how would that affect the continuity of any potential restore operation I may need to run? Because I would be switching the database back and forth between simple and bulk-logged recovery model.

First question: What is the best way to make reindexing and transaction log backups co-exist without the reindexing operation blowing out the transaction log size?

Second question: Should I even be running a reindex every day or would once a week suffice? I'm thinking daily is best because the database is a major production system which is hit by many users every day.
LVL 3
meumaxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VeluNCommented:
Rebuilding Index online is not a issue from Oracle9i use ONLINE option with ALTER INDEX REBUILD.

SQL> ALTER INDEX IDX_DEPT_NO REBUILD NOLOGGING PARALLEL 16 ONLINE;

This will allow you rebuild indexes without distrubing the usage of IDX_DEPT_NO and adding NOLOGGING option will avoide from generating redo logs. PARALLEL option will enable parallel executution.

Rebuilding index daily is a painful job. better you can use

SQL> ALTER INDEX IDX_DEPT_NO VALIDATE STRUCTURE:

Now you can verify the index LMODE from index_stats dictionary view and decide for rebuilding indexes.


Rgds,

Velu N
0
meumaxAuthor Commented:
Oops. Should have mentioned which rdbms I'm using. It's actually SQL Server 2000.
0
pettmansCommented:
I think you may be running your maintenance operation a little too frequently.

The need to do this operation depends on:
- frequency of index updates
- observered application performance

If your application users aren't reporting performance issues then you could consider dropping the frequency down to weekly (or even monthly), then setup some monitoring to see if performance is significantly impacted.

Consider dropping the fill factor down (say to 75% or 80%). That will increase the space used for indexes and increase the number of pages that may need to be traversed fro a query but will reduce the level of fragmentation that occurs - so can have less frequent index rebuilds.

Consider the use of DBCC INDEXDEFRAG as an alternative to ReIndex. It's an online operation, and is more efficient on lightly fragmented indexes. It will  take longer for a heavily fragmented index but
- no locks are held so user transactions are not blocked.
- the use of short transactions allows log file size to be minimised through use of frequent log backups during the operation or use of SIMPLE recovery model.

As both INDEXDEFRAG and REINDEX allow you to specify individual tables to target, you could mix and match use of these utilities based on your knowledge of application usage.

Regards,
Scott Pettman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.