Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Reorg vs Rebuild Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 5:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 79, Visits: 671
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.


Post #1592381
Posted Monday, July 14, 2014 6:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 23, Visits: 243
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
Post #1592389
Posted Tuesday, July 15, 2014 10:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1592668
Posted Tuesday, July 15, 2014 11:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 463, Visits: 1,025
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
Post #1592707
Posted Tuesday, July 15, 2014 12:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 3,198, Visits: 2,297
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."
Post #1592744
Posted Tuesday, July 15, 2014 12:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 23,081, Visits: 31,612
Also remember that a reorg only touches the leaf pages. It does not touch the higher nodes of the index.



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)
Post #1592758
Posted Tuesday, July 15, 2014 2:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
... 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.
Post #1592810
Posted Friday, July 18, 2014 8:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:10 AM
Points: 861, Visits: 2,360
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.
Post #1594087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse