CODEWITHSUNDEEP
sql-server-2008indexingrebuild
nanonerd
nanonerd

Reputation: 1984

SQL Server 2008 Indexes - Just rebuild it?

I've been researching rebuilding indexes as part of maintenance for SQL Server 2008.

There's a bunch of postings that explain Reorganize and Rebuild. I think I get it.

Practical question: If our DB is not used on the weekend, would it be ok to just run a Rebuild on all frags > 10?

This site recommends a Reorg if frag is 10-40 and a Rebuild for frags > 40.

http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/

I understand that a Rebuild locks the table (sans Enterprise version) but it is the cleaner option, correct (drops index and creates new one)? So if we don't have to worry about being locked out of DB access, is there any reason why we shouldn't just Rebuild or is there a reason to still Reorg on frags 10-40?

Upvotes: 1

Views: 189

Answers (1)

Jirka Hubáček
Jirka Hubáček

Reputation: 187

if your database is not dozens of gigs large and you have whole weekend as a maintenance window you are ok with rebuild. If you have EE you can get rid of nearly all locking by ONLINE option.

Try to think of your problem in larger scope. If you have shared array for multiple SQL Instances then doing intensive IO operations like index rebuild would not be helpful. If you use SAN then you might not need to do such aggressive index maintenance because you might not help anything. Brent had a great webcast on this topic: http://www.youtube.com/watch?v=fD1CZVc6oUk

Upvotes: 1

Related Questions