How to check and rebuild MSSQL Index fragmentation

Indexes do not end with the creation of an index on a table to improve performance, but must be optimized periodically. In other words, if periodic management is not performed, performance degradation occurs again or unused indexes only take up storage space.

Index fragmentation?

• When an index is first created, it is composed of pages with consecutively related data, but it occurs when there are pages in the index that do not match the physical order of the index pages in the logical order.
• The database automatically changes the index whenever an INSERT, UPDATE, or DELETE operation is performed on the table. - However, when adding a row to a table, the existing page of the rowstore index may be split to create space to insert a new row. If this modification is repeated, the data in the index becomes fragmented, and the physical order of the index pages in the database becomes scattered.
• If it is distributed across the index pages, more disk I/O occurs when searching for the corresponding data, which reduces the performance of the database.

Rowstore Index, Column Store Index Features

Index fragmentation can be checked by dividing it into rowstore and columnstore. The rowstore index is suitable for searching a small range of data, and the columnstore index is useful for processing and analyzing large amounts of data.

Rowstore Index Features.

• Physically stores the row-based data of the table.
• Suitable for queries that mainly search for a small range of data.
• It uses row-based storage, so it is fast when searching for specific values.
• Since data is stored in rows, the compression effect is small.
• If ALL is specified, all indexes in the table are deleted and recreated in a single operation.

Columnstore Index Features.

• It uses column-based data storage.
• It efficiently processes large amounts of data by compressing and storing data in columns, and is suitable for analysis query optimization and real-time aggregation.
• It saves storage space and provides fast query performance by compressing and storing data in columns.

How to check index fragmentation

How to check rowstore index.

• avg_fragmentation_in_percent: Indicates the degree of logical fragmentation (out-of-order pages in the index).
• avg_page_space_used_in_percent: Indicates the average page density.
• total_rows: Indicates the number of rows physically stored in the row group, including rows marked as deleted for compressed row groups.
• deleted_rows: Indicates the number of rows physically stored in the compressed row group that are marked as deleted.
• Check with source code.

-- Check fragmentation and page density of rowstore indexes using Transact-SQL
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND ips.index_id = i.index_id
ORDER BY page_count DESC;

How to check columnstore index.

• The degree of fragmentation of the columnstore index method is defined as the ratio of deleted rows to total rows and is expressed as a percentage.
• You can check it through the sys.dm_db_column_stroe_row_group_physical_stats() method.
• Check with the source code.

-- Checking the fragmentation of the columnstore index using Transact-SQL
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

How to rebuild an index

How to rebuild an index.

Rebuild the IX_AurumGuide_Fragmentation_01 index of the AurumGuide_Fragmentation table.

 ALTER INDEX IX_AurumGuide_Fragmentation_01
    ON dbo.AurumGuide_Fragmentation
    REORGANIZE;

How to rebuild all indexes on a table.

Rebuild all indexes on table dbo.AurumGuide_Fragmentation in the database.

ALTER INDEX ALL ON dbo.AurumGuide_Fragmentation
   REORGANIZE;

How to rebuild an index.

Rebuild a single index on the AurumGuide_Fragmentation table.

ALTER INDEX PK_AurumGuide_Fragmentation_ID ON dbo.AurumGuide_Fragmentation
REBUILD
;

How to rebuild all indexes on a table.

ALTER INDEX ALL ON dbo.AurumGuide_Fragmentation
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;