Fragmentation is a common issue in SQL Server that can negatively impact performance. It occurs when data is stored non-contiguously on disk, leading to poor cache utilization and increased I/O. In this article, we will explore the types of fragmentation, how to detect it, and methods to eliminate it.

Types of Fragmentation

There are two types of fragmentation:

1. Internal Fragmentation

Internal fragmentation occurs when records are stored non-contiguously inside a page. This means there is unused space between records, which can result from data modifications such as INSERT, UPDATE, and DELETE statements. The uneven distribution of these modifications among the rows of a table and its indexes can cause varying levels of fullness for each page. This unused space leads to poor cache utilization and increased I/O, ultimately impacting query performance.

2. External Fragmentation

External fragmentation refers to the non-contiguous physical storage of pages and extents on disk. When the extents of a table are not stored contiguously, switching from one extent to another requires higher disk rotations, resulting in extent fragmentation. Additionally, index pages maintain a logical order of pages inside an extent. However, page splits can cause pages to become out-of-order, leading to logical fragmentation. Both extent and logical fragmentation can impact query performance.

Detecting Fragmentation

To detect fragmentation, we can use the DMV (Dynamic Management View) sys.dm_db_index_physical_stats. This DMV provides information about both internal and external fragmentation. The following query can be used:

SELECT OBJECT_NAME(OBJECT_ID), index_id, index_type_desc, index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL, 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

The key columns for detecting fragmentation are:

• avg_fragmentation_in_percent: This percentage value represents external fragmentation for clustered tables and leaf level index pages. For heaps, it represents extent fragmentation. A lower value is better, and if it exceeds 10%, corrective action should be taken.
• avg_page_space_used_in_percent: This average percentage represents internal fragmentation. A higher value is better, and if it falls below 75%, corrective action should be taken.

Reducing Fragmentation

To reduce fragmentation, different approaches can be taken depending on the type and level of fragmentation:

1. Reducing Fragmentation in a Heap

If you have a heap (a table without a clustered index), you can reduce fragmentation by creating a clustered index on the table. This will rearrange the records in a specific order and place the pages contiguously on disk.

2. Reducing Fragmentation in an Index

For indexes, there are three options for reducing fragmentation:

• ALTER INDEX REORGANIZE: If the average fragmentation is between 5% and 30%, you can use this statement to reorder the leaf level pages of the index in a logical order. This operation can be performed online, allowing the index to remain available during the process.
• ALTER INDEX REBUILD: If the average fragmentation exceeds 30%, you can use this statement to rebuild the index online or offline. Alternatively, you can choose to drop and re-create the index. However, this option is strongly not recommended.

By taking appropriate actions to reduce fragmentation, you can improve the performance of your SQL Server database.

Remember, fragmentation is a common issue in SQL Server, but with proper detection and elimination techniques, you can ensure optimal performance for your database.