July 14, 2014 at 5:24 pm
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.
July 14, 2014 at 6:59 pm
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://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/
cheers,
Shaun
July 15, 2014 at 10:22 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2014 at 11:30 am
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
July 15, 2014 at 12:21 pm
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.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 15, 2014 at 12:52 pm
Also remember that a reorg only touches the leaf pages. It does not touch the higher nodes of the index.
July 15, 2014 at 2:42 pm
... 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.
July 18, 2014 at 8:33 am
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
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply