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 12»»

Index reorg VS rebuild, round 1 Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 8:45 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 611, Visits: 516
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.


.
Post #1399023
Posted Thursday, December 20, 2012 10:09 AM


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: 2 days ago @ 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1399043
Posted Thursday, December 20, 2012 10:24 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 611, Visits: 516
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.


.
Post #1399053
Posted Thursday, December 20, 2012 10:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
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.
Post #1399063
Posted Thursday, December 20, 2012 11:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:02 AM
Points: 217, Visits: 1,010
fluffydeadangel (12/20/2012)
fill factor 80%!
Very stupid advice.



Alex Suprun
Post #1399074
Posted Thursday, December 20, 2012 11:21 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 611, Visits: 516
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.


.
Post #1399079
Posted Thursday, December 20, 2012 11:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
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.
Post #1399081
Posted Thursday, December 20, 2012 1:49 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 611, Visits: 516
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.

.
Post #1399099
Posted Friday, December 21, 2012 2:55 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 889, Visits: 2,460
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
Post #1399601
Posted Sunday, December 23, 2012 2:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 93, Visits: 253
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
Post #1399743
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse