Index reorg VS rebuild, round 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Index reorg VS rebuild, round 1

11 posts, Page 1 of 2

1 2 »»

Index reorg VS rebuild, round 1

Rate Topic

Display Mode

Topic Options

Author

Message

Bill (fluffydeadangel)

Posted Thursday, December 20, 2012 8:45 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:10 PM
Points: 71, Visits: 238

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

MyDoggieJessie

Posted Thursday, December 20, 2012 10:09 AM

SSCrazy

Group: General Forum Members
Last Login: Saturday, June 15, 2013 11:44 PM
Points: 2,055, Visits: 3,786

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" Wink

Post #1399043

Bill (fluffydeadangel)

Posted Thursday, December 20, 2012 10:24 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:10 PM
Points: 71, Visits: 238

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

George M Parker

Posted Thursday, December 20, 2012 10:41 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:44 PM
Points: 526, Visits: 1,007

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

Alexander Suprun

Posted Thursday, December 20, 2012 11:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:10 PM
Points: 170, Visits: 827

fluffydeadangel (12/20/2012)

fill factor 80%!

Very stupid advice.

Alex Suprun

Post #1399074

Bill (fluffydeadangel)

Posted Thursday, December 20, 2012 11:21 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:10 PM
Points: 71, Visits: 238

George M Parker (12/20/2012)

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

George M Parker

Posted Thursday, December 20, 2012 11:34 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:44 PM
Points: 526, Visits: 1,007

fluffydeadangel (12/20/2012)

George M Parker (12/20/2012)

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

Bill (fluffydeadangel)

Posted Thursday, December 20, 2012 1:49 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:10 PM
Points: 71, Visits: 238

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

Nadrek

Posted Friday, December 21, 2012 2:55 PM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:08 PM
Points: 679, Visits: 2,038

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 specfically 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

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76, Visits: 188

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions