Factors to consider while Rebuild/Reorganize of index in SQL Server 2012

Question

I am getting confused with choosing Index Reorganize/Rebuilding of indexes based on avg_fragmentation_in_percent returned by sys.dm_db_index_physical_stats dm function.

Msdn says:

And also, avg_fragmentation_in_percent value says percentage of logical fragmentation (This is the percentage of out-of-order pages in the leaf pages of an index) existed in index.

Reorganize of index will always fixes physical ordering of pages. so even avg_fragmentation_in_percent is greater than 30% also I can consider Reorganize of index. It will be decreasing the percentage of fragmentation in index.

Please look below example

SELECT 
    database_id, object_id,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count,
    avg_fragment_size_in_pages
FROM
    SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED') 

Result

 Database_id Object_id  avg_fragmentation_in_percent  avg_page_space_used_in_percent    page_count  avg_fragment_size_in_pages
    37       913490383        99.36                           60.15                      314           1.003

In above example, We can see 99 % of average fragmentation it means index is having more logical fragmentation(Unordering of pages is more).

So I am going to reorganizing of index which we have seen in above image.

Please look below example for further.

ALTER INDEX PK_NM_PPA_PROJECTION_MASTER_PROJECTION_DETAILS_SID_RS_CONTRACT_SID ON NM_PPA_PROJECTION_MASTER REORGANIZE
GO

SELECT database_id,
       object_id,
       avg_fragmentation_in_percent,
       avg_page_space_used_in_percent,
       page_count,
       avg_fragment_size_in_pages
FROM   SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED') 

Result

Database_id Object_id   avg_fragmentation_in_percent    avg_page_space_used_in_percent  page_count  avg_fragment_size_in_pages
37           913490383           4.18                                       98.91           191       9.55

Above, we have re-organized the index so after reorganize average_fragmentation reduced to 4 percent and also space used is increased from 60 percent to 98 percent.

Msdn Suggesting Avg_Fragmentation_in_percent value to be consider for Reorganize/Rebuild of index.

But Avg_Fragmentation_In_Percent value says only logical fragmentation not amount of space which is not used in index.

So, I am a little bit confused about choosing between Re-organize/Re-build of index.

Can we consider both Avg_fragmentation_in_percent and Avg_page_space_used_in_percent values for choosing Rebuild an index or Reorganize an index?

Any one please suggest me the exact parameters to be consider for choosing Rebuild or Re-Organize of an index?

Answer 1

I'm going to go in a different direction than other answers and ask: After your defragmentation routine has run for however many minutes or hours, and used all sorts of resources (CPU, memory, disk, probably tempdb), how do you quantify and compare the expenditure of time and server resources to defragment indexes to any improvements you've gained from doing so?

I realize this doesn't answer your question, but I want you to think carefully about what you're doing rather than get yourself all worked up and your server all beat up about a non-issue.

There's a weird obsession (and believe me, I used to have it, too) with SQL Server users around index fragmentation. It's usually based on advice from Microsoft from 15 years ago and lots of hysterical responses on Q&A forums (this one included).

People have had the numbers you reference (5% to reorg, 30% to rebuild) pounded into their head with religious fervor forever. So they keep doing it. I've seen it cause all sorts of problems, too. Blocking and deadlocks, corruption, interruptions to production workload (long running maintenance), and worst of all: performing index maintenance at the exclusion of more important maintenance, like DBCC CHECKDB.

The one good thing that index rebuilds do is update statistics, which (simplifying greatly) will usually give the optimizer much better information about the data it's working with, and (again, simplifying greatly) can chase a bad execution plan out of the cache.

People will also do counterproductive things in maintenance plans like rebuild all their indexes, reorganize all their indexes, and then update statistics. I realize this likely isn't you, just adding it for completeness.

There are also more considerations than just fragmentation percent to rebuild or reorg:

• Do queries even use this index?
• Do I really want to reorg and LOB compact a 50+ GB index (single threaded, ew)?
• Am I on Standard Edition where rebuilds are OFFLINE and can cause blocking?

For a bit of background, check out the blog posts here. Full disclosure; it's the company I work for, though people other than me have written a lot about it. There's even a post in there about when index fragmentation does matter.

Other database platforms have had better advice about this topic for a long time.

EDIT: The best way to have index fragmentation never ever in a million billion years matter: cache all your data in RAM.

Answer 2

Here are some of the differences that could help you decide which one to use

• Rebuild can use multiple CPU whereas Reorganize is always single threaded
• Rebuild Update index Statistics, Reorganize does not
• Rebuild is faster for heavily Fragmented indexes compared to Reorganize and vice versa.
• Can set FILLFACTOR with Rebuild, but cant with Reorganize
• Can reduce transaction log usage by switching to BULK_Logged recovery Model when using index rebuild, where as Reorganize is fully logged
• Reorganize is always online, Rebuild can choose between online and offline (online is an Enterprise only feature, but will still require a small index outage while the newly rebuilt one is swapped in)
• Rebuild has to create a new index before dropping the old one, so it needs more space(approx 1.2 times the space index) compared to Reorganize which works on one page at a time so only required 8KB of free space
• Reorganize can be stopped half way through and you do not lose the work done till then, where as rebuild has to rollback the entire transaction if stopped in the middle.
• Rebuild does not operate on LOB data , but Reorganize compacts LOB data by default

As long as avg_fragmentation_in_percent is concerned, I wouldn't give much importance to that number. Even if you rebuild the index and get rid of fragmentation how do you make sure the pages are next to each other on your storage. Whereas if you pages are only half full then its bad cause it might not cost that much to store it on disk but it will waste precious memory when its read on to memory. That is wasted memory.

Answer 3

You're right to consider both Avg_fragmentation_in_percent and Avg_page_space_used_in_percent when considering whether to do reorganise, rebuild, or nothing.

Even if Avg_fragmentation_in_percent is low, a low Avg_page_space_used_in_percent may gain benefit from reorganisation (due to the extra IO and cache resources used by unfilled pages).

And the "greater than 30% = rebuild" advise may be better read as "if Avg_fragmentation_in_percent > 30% and you have been regularly reorganising, then rebuild". If you haven't been reorganising, then try that first, since as your test shows, that may be all that's needed even with very high Avg_fragmentation_in_percent.

As for the exact trigger levels to use, that really depends on your data and how it's used, and on various other factors like those jesijesi posted.

Answer 4

I wouldn't be too concerned about the Avg_page_space_used_in_percent column with regards to deciding whether to Rebuild or Reorganize and index.

According to Reorganize and Rebuild Indexes, both Rebuild and Reorganize honor the FILLFACTOR for the index and try to fill up the pages accordingly.

So, immediately after a Rebuild or Reorganize, you should see Avg_page_space_used_in_percent fairly close the the FILLFACTOR of the index.

Have a look at the following links for more information:

Reorganize and Rebuild Indexes

sys.dm_db_index_physical_stats