SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index reorg VS rebuild, round 1


Index reorg VS rebuild, round 1

Author
Message
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 599
So, I read alot of people saying Rebuild over 30% fill factor 80%! reorg under 30% only! What is the actual reason to not reorg only? I have two massive databases that write sequencually, nothing is ever out of order on the table really, very very very few updates at best, 99% of the information is historical only. after a good datacompression and shrink (moving this from 05-08, yes the shrink is a one time thing to regain roughly 1.2TB of space) fragmentation is 99%. I chose to reorg vs reindex.

I've noticed that most times i reindex, it doesnt always fix the problem, but reorg even over 90% fragmentation seems too?

I guess... I'm looking for a solid reason to use a rebuild reindex over a reorganize. What are the downfalls? Limitations? Does it randomly kill a squirrel? I'm not sure.

I can say after running my reorg script, I'm showing zero tables with higher than 5% fragmentation on any table over 25 pages. I just worry there's something I'm missing and a reason a reorg is bad.

SO! I ask of you, any advice is greatly appriciated! I know I have much to learn and here's a good place to start.

.
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12440 Visits: 7444
IMHO the threshold setting is a personal choice of when to chose to rebuild versus reorganize the index. The main difference between the two are basically: when rebuilding you drop and recreate the actual index, this in turn will update all of the statistics for the columns used in the index, and reorganizing the index physically reorganizes the leaf notes of the index (and will not update any stats).

So if you chose to reorg more often that not, what you will find happening is your statistics will become "stale" and SQL Server may start choosing different/similar indexes when determining its execution plan, which could result in poorly performing queries - so you'd probably want to be sure you've got a maintenance job of sorts that regularly updates your stats.

Also, If the data is primarily historical, you could probably gain performance by increasing your fill factor to a much high value.

after a good datacompression and shrink (moving this from 05-08, yes the shrink is a one time thing to regain roughly 1.2TB of space) fragmentation is 99%. I chose to reorg vs reindex.
"Shrinking" will always royally fragment an index...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 599
yes, except certain scenarios, shrinking is bad. ^.^; I'm more wondering why you would rebuild vs reorg first? Every week on our full backups, all statistics are updated, as well as when I run the reorg script, the final step is an update of statistics. My understanding is with Lob Data, *which our developers like varchar(max) in large text field areas* that Rebuild is forced to do this offline, however reorg can complete this online.

When is rebuilding actually preferred? it doesn't seem faster, taking care with statistics.. it doesn't seem much better, and seems to eat into free space to run.

In an emergency where the process has to be killed, my understanding is that they don't actually roll back. They still finish anyways.

.
George M Parker
George M Parker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1604 Visits: 1472
The primary difference is internal fragmentation versus external fragmentation. The Reorganize fixes external fragmentation when the 8k pages are physically out of sequence. The Rebuild fixes internal fragmentation when there is a lot of empty space available on the 8k pages. Unless you are querying the sys.dm_db_index_physical_stats DMV using the DETAILED mode, you won't see the data regarding how much internal fragmenation exists.

avg_page_space_used_in_percent:

Average percentage of available data storage space used in all pages.
For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.
For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.
For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.
NULL when mode = LIMITED.
Alexander Suprun
Alexander Suprun
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1267 Visits: 1516
fluffydeadangel (12/20/2012)
fill factor 80%!
Very stupid advice.


Alex Suprun
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 599
George M Parker (12/20/2012)
The primary difference is internal fragmentation versus external fragmentation. The Reorganize fixes external fragmentation when the 8k pages are physically out of sequence. The Rebuild fixes internal fragmentation when there is a lot of empty space available on the 8k pages.


So, unless I'm pulling detailed reports and doing these by hand, i'm guessing it's best to do a reorg first, and fix what's left with a rebuild? I will say i was unaware of those differences, you've given me something new to look into.

I have no issues doing these by hand, and it's always good to keep an eye on what breaks and how.

.
George M Parker
George M Parker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1604 Visits: 1472
fluffydeadangel (12/20/2012)
George M Parker (12/20/2012)
The primary difference is internal fragmentation versus external fragmentation. The Reorganize fixes external fragmentation when the 8k pages are physically out of sequence. The Rebuild fixes internal fragmentation when there is a lot of empty space available on the 8k pages.


So, unless I'm pulling detailed reports and doing these by hand, i'm guessing it's best to do a reorg first, and fix what's left with a rebuild? I will say i was unaware of those differences, you've given me something new to look into.

I have no issues doing these by hand, and it's always good to keep an eye on what breaks and how.


If you have the luxury of being able to restore the database to another location, then I would run the DMV in DETAILED mode on the restored copy and review the internal fragmentation data there.

Our maintenance process uses a table driven process to determine which indexes to rebuild or reorganize. It's coded in such a way that if an index is reorganized more than 3 times in a row, the next cycle rebuilds the index. We found that we were addressing the external fragmenation but never the internal fragmentation and therefore modified the code accordingly. And of course, any index that is reorganized is then flagged for an UPDATE STATISTICS to be performed since reorganizing an index does nothing to the statistics.

And since we track when an index is rebuilt versus reorganized, we have a better idea of what fillfactors might need to be adjusted.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 599
it would be nice, but as DBA's we don't have a test bed. And even so, we'd need a substantial system to test on.

.
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4602 Visits: 2741
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
zi
zi
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 338
HI

" Rebuild over 30% fill factor 80%! reorg under 30% "

Kindly,what should I do to know this percentetge for the Database not for a certain table ?

Thanks lot
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