Sql-server – Factors to consider while Rebuild/Reorganize of index in SQL Server 2012

indexsql serversql-server-2012

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:

enter image description here

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?

Best Answer

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.

Related Question