asked on

SQL maintenance index plan

If I'm rebuilding indexes weekly, how useful is the reorganize index and rebuild statistics plan?

The Help in Microsoft SQL Management Studio recommends reorganizing indexes that have fragmentation levels between 5% and 30% and rebuilding index that have fragmentation levels greater than 30%

x-men

Rebuild - data is offline, no user access.
Reorg - data is online, but not as efficient as rebuild.

Statistics - help the Engine "guess" where to find the data.

info: http://blog.idera.com/sql-server/understanding-sql-server-statistics/

barnesco

ASKER

Maybe I can narrow the question: I have hundreds of servers, so I won't monitor each one for "reorganizing indexes that have fragmentation levels between 5% and 30% and rebuilding index that have fragmentation levels greater than 30%."

I need a practical maintenance plan. I had read somewhere that if I rebuild indexes, reorganizing may not be necessary.

Will reorganizing indexes do something that a rebuild index won't do? Please don't answer the reorg does it on the leaf level, etc...I want to know if there is any benefit to doing all three jobs in a maintenance plan in one night.

SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

You will significantly hurt performance by recomputing statistics after a rebuild.

So you definitely don't want to do both for the same table(s).

ASKER CERTIFIED SOLUTION

PortletPaul