December 20, 2012 at 8:45 am
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.
.
December 20, 2012 at 10:09 am
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; Theyll drag you down to their level and beat you with experience
December 20, 2012 at 10:24 am
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.
.
December 20, 2012 at 10:41 am
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.
December 20, 2012 at 11:12 am
fluffydeadangel (12/20/2012)
fill factor 80%!
Very stupid advice.
December 20, 2012 at 11:21 am
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.
.
December 20, 2012 at 11:34 am
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.
December 20, 2012 at 1:49 pm
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.
.
December 21, 2012 at 2:55 pm
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
December 23, 2012 at 2:56 am
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
December 23, 2012 at 9:55 am
zi (12/23/2012)
Kindly,what should I do to know this percentetge for the Database not for a certain table ?
this is used for index maintenance. need for details .... we need more explanation adiditinally alwasy use new thread/post to ask any question. see my signature
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply