Factors to consider while Rebuild/Reorganize of index in SQL 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:

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

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.

StackExchange Database Administrators Q#164382, answer score: 8