How To Identify And Resolve SQL Server Index Fragmentation

Introduction
Understanding the Causes of Index Fragmentation
Identifying Fragmented Indexes with T-SQL
T-SQL description
Conclusion

Introduction

Index fragmentation is a common issue that can negatively impact database performance in SQL Server. As indexes become fragmented, query performance may degrade, leading to slower response times and reduced efficiency. In this blog, we'll explore how to identify and manage index fragmentation using a SQL query and best practices for index maintenance.

Understanding the Causes of Index Fragmentation

Updates to existing rows in a database can lead to index fragmentation. When a row is updated and no longer fits within its original page, SQL Server must split the page into two new pages, redistributing the rows accordingly. This process occurs differently for clustered indexes and heaps. For clustered indexes, SQL Server moves the updated row to a new page and leaves a pointer in the original page. In contrast, for heaps, SQL Server moves the entire row to a new page, leaving behind only a pointer in the original page.

When indexes experience a high volume of updates, it increases the likelihood of page splits. To mitigate this issue, database administrators can consider setting a lower fill factor. A lower fill factor allows more room for row growth within pages, reducing the frequency of page splits.

External fragmentation occurs when pages are out of order on disk due to page splits. To identify external fragmentation, indexes can be ordered by the avg_fragmentation_in_percent in descending order.

On the other hand, internal fragmentation refers to the presence of empty space within index pages. This can occur when rows are deleted or when pages are not fully utilized. To address internal fragmentation, indexes should be ordered by avg_page_space_used_in_percent in ascending order. Rebuilding the index rather than reorganizing it can help reduce internal fragmentation by optimizing the page layout and eliminating empty space within pages.

Identifying Fragmented Indexes with T-SQL

This SQL query provides detailed insights into index fragmentation within a SQL Server database. Let's break down the key components of the query and its recommendations for index maintenance:
  • The query retrieves information from the sys.dm_db_index_physical_stats dynamic management view, analyzing index fragmentation for each index in the database.
  • It calculates the average fragmentation percentage and recommends whether to rebuild or reorganize each index based on its fragmentation level.
  • Indexes with an average fragmentation percentage of 40 or higher are recommended to be rebuilt, while those below 40 are recommended to be reorganized.
  • The query also considers factors such as index type, page count, and minimum record size to determine which indexes require maintenance.

use dbdocs; --Replace it with your DBNAME

select
    db_name(db_id()) as database_name,
    object_schema_name(idxfra.object_id) + '.' + object_name(idxfra.object_id, db_id()) as object_name,
    i.name as index_name,
    idxfra.index_type_desc,
    idxfra.partition_number,
    idxfra.alloc_unit_type_desc,
    idxfra.index_depth,
    idxfra.index_level,
    round(idxfra.avg_fragmentation_in_percent, 0) as avg_fragmentation_in_percent,
    idxfra.avg_page_space_used_in_percent,
    idxfra.avg_fragment_size_in_pages,
    idxfra.fragment_count,
    idxfra.page_count,
    idxfra.record_count,
    idxfra.min_record_size_in_bytes,
    idxfra.max_record_size_in_bytes,
    idxfra.avg_record_size_in_bytes,
    case
    when round(idxfra.avg_fragmentation_in_percent,0) >= 40 then
        'alter index ' +
        i.name +
        ' on ' +
        object_schema_name(idxfra.object_id) +
        '.' +
        object_name(idxfra.object_id, db_id()) +
        ' rebuild;'
    else
        'alter index ' +
        i.name +
        ' on ' +
        object_schema_name(idxfra.object_id) +
        '.' +
        object_name(idxfra.object_id, db_id()) +
        ' reorganize;'
    end as recommendation
from
    sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') idxfra
    inner join
    sys.indexes i
        on idxfra.object_id = i.object_id
        and idxfra.index_id = i.index_id
where
    idxfra.index_type_desc <> 'HEAP'
and
    idxfra.avg_fragmentation_in_percent > 10
and
    idxfra.page_count > 1000
order by
    idxfra.avg_fragmentation_in_percent desc,
    object_name(idxfra.object_id, db_id()),
    i.name;

T-SQL description

Now, let's break down each component and understand its significance

Column                         | Description
------------------------------ | ------------------------------------------------
database_name                  | Database name.
object_name                    | Object name.
index_name                     | Index name.
index_type_desc                | Index type, CLUSTERED or NONCLUSTERED.
partition_number               | Partition number.
alloc_unit_type_desc           | Allocation unit type, IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA.
index_depth                    | Number of index levels.
index_level                    | Current level of the index.
avg_fragmentation_in_percent   | Percentage of logical fragmentation for indexes (external fragmentation).
avg_page_space_used_in_percent | Percentage of space used in all pages (internal fragmentation).
avg_fragment_size_in_pages     | Average number of pages in one fragment in the leaf level.
fragment_count                 | Nummber of fragments in the leaf level.
page_count                     | Total number of index or data pages.
record_count                   | Total number of records.
min_record_size_in_bytes       | Minimum record size in bytes.
max_record_size_in_bytes       | Maximum record size in bytes.
avg_record_size_in_bytes       | Average record size in bytes.
recommendation                 | Percentation of fragmention > 40% rebuild else reorganize.

Conclusion

By executing this SQL query and following the recommended maintenance actions, database administrators can effectively manage index fragmentation, optimize query performance, and ensure smooth operation of SQL Server databases.

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access