Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Reorg vs Rebuild


Index Reorg vs Rebuild

Author
Message
sjs-36273
sjs-36273
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 723
Can anyone easily explain if there are disadvantages of doing an index reorg vs a rebuild? I know the best practice of doing a reorg for fragmentation between 10 and 30% and a rebuild for over 30%. I have some large tables that are very fragmented on a system that is used 24/7. From what I read, doing a reorg will put less strain on the resources of the hardware over doing a rebuild. This may be necessary because of how busy the system is but I am looking to see if there are any disadvantages of doing a reorg on a table with very high fragmentation. I would assume that there are some advantages of doing a rebuild but I can’t find any documentation on it.



Shaun Finnegan
Shaun Finnegan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 472
Before you start reorg'ing or rebuilding your indexes, ask yourself:

Are we actually having any performance issues with the database? What have your users said?
What is the profile of the data in the table? How many inserts / deletes / updates?
When were the statistics last updated on the tables?
If rebuilding, when can you do this? Controlled outage? Online or Offline rebuild? How will this affect the disk space on the server?

Have a read of these two articles before you proceed as it requires a bit more thought other than which option you should choose.

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx
http://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/

cheers,

Shaun

SQL Server Best Practices Supporter
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17651 Visits: 32271
I can't find the blog post at the moment, but Brad McGehee wrote up a great assessment comparing the benefits of index reorganization with the costs of index reorganization and found that it just wasn't worth it. You're better of either defragmenting the index, or just leaving it alone in the majority of cases. With this knowledge, I'd suggest that, unless you're in a situation where the index rebuild is just failing completely, probably due to the size of your index, the load on the sytem, or both, you skip the reorganization and go with the rebuild.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
@SQLFRNDZ
@SQLFRNDZ
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 1177
There is lot to consider in re-org vs re-index. In simple words, yes, less than 30 % fragmented good to do re-org else re-index.

For example.you have 90% fragmented index, re-org takes more resources than re-index. this answers your question I believe.


If you are not sure still, here is a very great index maintenance job you can create which can understand your indexes and does the best.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


--SQLFRNDZ

Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
this is about as simple as it can get ...

reorg wins over reindex for concurrency. reorg locks only 1-2 pages or 1-2 extents at a time as it executes as opposed to a reindex locking the entire index/table.

reindex wins over reorg in simplicity since it recreates statistics as a result of the reindex process.

so remember always to execute update statistics with a full scan after a reorg.

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24285 Visits: 37993
Also remember that a reorg only touches the leaf pages. It does not touch the higher nodes of the index.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 1930
... and after you finish "digesting" all the good posts you just read on this thread, please vist Ola's website He provides a maintenance solution that takes care of that, and it's free. The logic handles all that for you.

EDIT

I posted to quickly. Someone else mentioned Ola's solution already. So I will only say that you should really check that website.
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 2673
I think you want to read the SSC thread "Index reorg VS rebuild, round 1" first; here's my post from that thread:

"When I have a choice where, based on my constraints, I could reasonably rebuild or reorg, I always rebuild. In general, I've operated with set maintenance windows, where I could easily have large tables locked for hours when they required defragmenting.

Don't bother with "small" tables, where 7 pages < "small" (8 pages per extent; less than an extent and it doesn't matter), and it is generally taken that "small" < 1001 pages (no particular reason).

Note that nonclustered indexes often invalidate the "I always add records sequentially and never update any", since they're usually starting with a field that isn't added sequentially.

Note first that the general (and MSDN) 5-30%, 30%+ thresholds were made up to be reasonable starting points, and the real answer is "it depends" (http://www.sqlskills.com/blogs/paul/post/where-do-the-books-online-index-fragmentation-thresholds-come-from.aspx)

Rebuilds can be done in parallel (edition permitting); reorgs cannot.
Rebuilds update statistics with FULLSCAN (http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx)
For the above two, see Paul Randal's article about heavily skewed updates require updating statistics before the rebuild
http://www.sqlskills.com/blogs/paul/post/index-rebuilds-depend-on-stats-which-are-updated-by-index-rebuilds!.aspx
If you care about Scan Density (most don't), rebuilds improve that, while reorgs do not.

Paul Randal wrote specifically about the differences in http://technet.microsoft.com/en-us/magazine/hh395481.aspx"

ETA: Ola's solutions are really good in general; at some point I'll post my own predictive maintenance window code, too.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search