About fragmentation in SQL Server

- Extent = 8 pages = 64K.
- On a page split, SQL Server generally moves half the total number of rows in the original page to the new page.
- If new rows are added in the order of the clustered index, then the index rows will be always added at the trailing end of the index, preventing the page splits otherwise caused by the INSERT statements.
- For queries that don’t have to traverse a series of pages to retrieve the data, fragmentation can have minimal impact.
- For a table with a clustered index, the fragmentation of the clustered index is the same with the fragmentation of the data pages, since the leaf pages of the clustered index and data pages are the same.
- A small table (or index) with fewer than eight pages is simply unlikely to benefit from efforts to remove the fragmentation because it will be stored on mixed extents.

Internal fragmentation
- When data is fragmented within the same extent (= 8 pages).
- A little internal fragmentation can be beneficial, because it allows you to perform INSERT and UPDATE queries without causing page splits.

External fragmentation
- When data is fragmented over 2 extents.
- A range scan on an index will need more switches between the corresponding extents than ideally required. A range scan on an index will be unable to benefit from read-ahead operations performed on the disk.
- For better performance, it is preferable to use sequential I/O, since this can read a whole extent (8 x 8KB) in a single disk I/O operation. By contrast, a noncontiguous layout of pages requires nonsequential or random I/O operations to retrieve the index pages from the disk, and a random I/O operation can read only 8KB of data in a single disk operation (this may be acceptable, however, if you are retrieving only one.

Common myths about index fragmentation in SQL Server

Rebuild all the indexes every night.Not necessary as not all indexes are fragmented => waste of server resources. Just thing to transaction log activity and disk activity. Some indexes fragmentation can be done with INDEX REORGANIZE.

Add more memory to the server.More memory doesn't stop index fragmentation happening.

Index fragmentation is irrelevant when using SSD.SSD allow index scans to be faster, but SSD doesn't prevent index fragmentation.

Online index rebuilds do not causes blocking.Online index rebuilds acquire locks that can cause long-term blocking.

Use the same fill factor for all indexes.Some indexes aren't going to have any fragmentation => waste space.

What are the implications of index fragmentation?

Slower index scans.

Increased disk space usage.The density of rows on the pages is lower => store fewer rows per page => wasting space

Increased buffer pool usage.

Causes increased transaction log generation.

Index fragmentation resolutions in SQL Server

After you detected fragmentation of an index, you can "resolve" fragmentation in an index by rearranging the index rows and pages so that their physical and logical orders match.

You achieve this through the following techniques:
1. Dropping and re-creating the index
If the index being dropped is a clustered index, then all the nonclustered indexes on the table have to be rebuilt after the cluster is dropped.

2. Creating the index with the DROP_EXISTING clause

3. Executing the ALTER INDEX REBUILD statement on the index
To be performed in case the fragmentation is over 30%.

ALTER INDEX <IX_IndexName> ON <TableName>

or rebuild all indexes of a specified table:


4. Executing the ALTER INDEX REORGANIZE statement on the index
To be performed in case the index fragmentation is between 5% and 30%

ALTER INDEX <IX_IndexName> ON <TableName>

Characteristics of Four Defragmentation Techniques

Blocking High High Medium Low
Defragment index with constraints Highly complex Moderately Easy Easy
Defragment multiple indexes together No No Yes Yes
Degree of defragmentation High High High Moderate to low
Statistics are updated Yes Yes Yes No
Apply new fill factor Yes Yes Yes No

Detecting fragmentation for all indexes in SQL Server

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using sys.dm_db_index_physical_stats DMV, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats dynamic management function includes the following columns:
avg_fragmentation_in_percent: the percent of logical fragmentation (out-of-order pages in the index)
fragment_count: the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages: average number of pages in one fragment in an index
avg_record_size_in_bytes: This number simply represents a useful measure for the amount of data stored within the record (index or heap).

     OBJECT_NAME(S.OBJECT_ID) as TableName, as IndexName,
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as S
     INNER JOIN sys.indexes AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id
     S.database_id = DB_ID()
     TableName, IndexName

After the degree of fragmentation is known, check how you can "resolve" the fragmentation.

Subscribe to RSS - index-fragmentation