Rebuild or Reorganize Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Rebuild or Reorganize Indexes

Rebuild or Reorganize Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

JMSM

Posted Wednesday, March 10, 2010 9:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:18 AM
Points: 185, Visits: 750

Hello,

I apreciate if anyone can check the .xlsx that i've anexed on this post to answer to my "stupid" question Crazy

The result that i get is from a query that i get here at sqlservercentral and that is very usefull to get all index fragmentation on an instance. The results that i put on it are only 2 of 96 indexes that appears in my list where fragmentation level is greater than 30%
My question is based on Rebuild or Reorganize Indexes.

Whats difference between them?
Does reorganize free all free space from pages?
Whats the impact?
How much space should i add to tempdb?
How can i control what is happening beside?
How much time should i spent when rebuilding the indexes that have a fragemntation >= 80%?
What should i must care about, level of fragmentation or Fragment count?
What is the best sintax to rebuild or reorganize?

Once more i hope that you can help me Blush

Thanks and regards,
JMSM Wink

Post Attachments

Aux-IndexFragmentation.xlsx (38 views, 14.86 KB)

Post #880356

Jack Corbett

Posted Thursday, March 11, 2010 7:26 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571, Visits: 11,871

From BOL (this article):

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

Rebuilding an index, except in Enterprise Edition with ONLINE = ON, is a blocking operation. Meaning that the index and table are not avialable during the Rebuild. Rebuilding an index also causes the statistics for that index to be updated.

From the same BOL entry:

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

Reorganizing is NOT a blocking operation, but it does consume resources. Statistics are NOT updated with a reorganization.

The typical recommendation is to reorganize indexes with 10-30% fragmentation and rebuild indexes with > 30% fragmentation. There are also rules of thumb about only doing maintenance on indexes with at least a certain number of pages. This will vary depending on who you talk to.

For more information you should read the BOL article linked above and then this one as well.

There are many existing scripts out there that do index maintenance. 2 I would recommend evaluating are:

Michelle Ufford's Index Defrag Script

Ola Hallengren's Index Optimize script which is included in his SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization solution

As always, don't use a script without testing it and understanding what it is doing.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #880992