index fragmentation vs index reorganize

Index fragmentation and index reorganize are closely related concepts in SQL Server (and other RDBMS systems), particularly when dealing with database performance.

Index Fragmentation

When the logical order of the data in an index doesn't match the physical order on disc, this is called index fragmentation. This happens over time as data is changed (added, updated, or deleted) and index pages become split or have empty space. There are two basic kinds of index fragmentation:

• Internal Fragmentation: This is the space that isn't used in the data pages of an index. When you add data that doesn't fill up a whole page or delete data, it leaves empty space. High internal fragmentation means that more pages need to be read to get the same amount of data. This makes I/O slower and performance worse.
• External (or Logical) Fragmentation: This happens when the physical order of the index leaf pages on disc is not the same as their logical (key) order. For instance, logically successive index pages could be spread out over several different physical locations on the storage. This makes the database conduct more random I/O to read the index, which is slower than sequential I/O.

• Causes:
  • Frequent INSERTs, UPDATEs, DELETEs.
  • Page splits due to changes in data size.
• Impact:
  • Slower query performance, especially for range scans or large reads.

Index reorganize

Index reorganize is a maintenance task/solution that fixes index fragmentation by physically rearranging the leaf-level pages of the index to fit the logical order. 

• A lightweight, online operation that defragments the leaf level of the index.
• Does not rebuild the entire index; instead, it reorders pages to reduce fragmentation.

• Main Points:
  •  By default, it runs online, and there are no locks on the table.
  •  Less disruptive but slower than rebuilding for large fragmentation.
  • Preserves index statistics (does not update them).
  • If halted, it can be resumed in SQL Server 2019 or later.

• How index reorganise does:
  •  Reorders Leaf Pages: It moves the leaf pages of the index on disc around so that they are in the right logical order depending on the index key. This makes the I/O more sequential, which helps index scans and range searches work better.
  • Compacts Pages (to some extent): By making pages denser by moving records around to fill in empty spots. But it doesn't free up disc space by deleting pages.

• Benefit of reorganise 
  • Online Operation: One big benefit is that it's an online operation, which means that database operations won't be disrupted as much. It works on a page-by-page basis and doesn't need a lot of locking.

In Short:

• Index fragmentation is the problem – the undesirable state of an index that negatively impacts performance.
• Index reorganize is one of the solutions – a maintenance operation to reduce fragmentation by reordering the existing index structure online.

Index Rebuild (for comparison)

• Rebuild can be done online or offline
• Disadvantage of Rebuild 
  • Removes all fragmentation and compacts pages.
  • A heavier operation that drops and recreates the entire index.

DATABASE SHRINK

• Check before Shrink
  • sp_helpdb [ShrinkDb]
  • 
• Check the % of fragmentation

•  SELECT 
      [avg_fragmentation_in_percent]
  FROM
      sys.dm_db_index_physical_stats(
          db_id(N'ShrinkDb'), object_id(N'second'),1, null, 'limited'
      );
  GO

 

• 
• The fragmentation is virtually zero at this point. which is a very good sign

• DBCC SHRINKDATABASE (ShrinkDb);
  • Causing fragmentation of that index.
• Check DB stats
  • sp_helpdb [ShrinkDb]
  • 
• When you shrink the database, it rearranges all the index pages in an organized fashion.
• shrink is going to cause massive defragmentation of this because it rearranges all the pages and it compacts
• Check the % of fragmentation

• SELECT 
      [avg_fragmentation_in_percent]
  FROM
      sys.dm_db_index_physical_stats(
          db_id(N'ShrinkDb'), object_id(N'second'),1, null, 'limited'
      );
  GO
• This now increase to 6.6.
  • 

So Shrink action causes a tremendous amount of fragmentation. And when you have fragmentation, it causes a massive amounts of immense amounts of  performance issue. So now the question becomes, should you rebuild the index or reorganize the index? We can do both using a Query 

Rebuild TABLE

• this reduce our fragmentation again

ALTER INDEX [col1] on second REBUILD;

SELECT 
    [avg_fragmentation_in_percent]
FROM
    sys.dm_db_index_physical_stats(
        db_id(N'ShrinkDb'), object_id(N'second'),1, null, 'limited'
    );
GO

REORGANIZE TABLE

• using following query

ALTER INDEX [col1] on second REORGANIZE;

SELECT 
    [avg_fragmentation_in_percent]
FROM
    sys.dm_db_index_physical_stats(
        db_id(N'ShrinkDb'), object_id(N'second'),1, null, 'limited'
    );
GO

How to Decide - DO REBUILD OR REORGANIZE

SELECT
   DB_ID() AS DatabaseID,
   OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
   OBJECT_NAME(ips.object_id) AS TableName,
   i.name AS IndexName,
   ips.index_type_desc AS IndexType,
   ips.avg_fragmentation_in_percent,
   ips.page_count,
   ips.avg_page_space_used_in_percent
FROM
   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN
   sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
   ips.avg_fragmentation_in_percent > 10 -- Optional: Filter for indexes with significant fragmentation
ORDER BY
   ips.avg_fragmentation_in_percent DESC;

OR ON SPECIFIC TABLE

SELECT   
   avg_fragmentation_in_percent    
FROM
   sys.dm_db_index_physical_stats(
    DB_ID(N'SQL2'), OBJECT_ID(N'People4'),1, NULL, 'LIMITED')

• Do Reorganize - IF avg_fragmentation_in_percent Column value is in between 5% to 30%
• Do Rebuild - IF avg_fragmentation_in_percent Column value is greater than 30%