How To Check & Fix Index Fragmentation on SQL Server via Script & ssms

Before we learn to fix Index fragmentation on SQL Server, let us understand why this happens. Indexes can be fragmented primarily due to data modifications over shorter or longer periods of time. It does not matter how fast your underlying disks are or how good your server hardware is optimized.

In SQL Server, since its inception, comes with 8 KB data pages and 8 pages make 1 Extent of 64 KB in size. These are 8 contiguous pages that form the extent. The data pages (both in clustered or non-clustered indexes) contain pointers to the next and previous pages in a B-Tree structure.

All this is fine until the data in a table is updated. When data is inserted in a table on SQL Server the following set of events occur:

1. SQL Server dynamically allocates a new 8 KB data page or even form a new extent.
2. A chunk of data from the existing old data page partially transfers to a newly allocated data page.
3. To keep the binary logical sorting order in the B-Tree index, pointers on both pages are updated on the bitmap.

As a result of this behavior, we see two types of Index fragmentation on SQL Server:

1. Logical (External) fragmentation
2. Internal fragmentation

In the case of Logical fragmentation, the extents are fragmented, and there is a mismatch between the logical order vs. physical order. It results in escalated seek times on the disks, thereby affecting the overall performance.

In the case of Internal fragmentation, there are some free spaces on the data pages and causes more logical reads to fetch the same amount of data.

In short, throughout the daily operations of inserts and deletes, the SQL Server will keep allocating more pages dynamically, and the same data might be split across more pages than it was before.

It makes it essential for the Database Administrator to manage and maintain the Index fragmentation levels for optimal performance.

How To Fix Index Fragmentation in SQL Server

Before you try to fix the fragmentation levels, you would need to monitor, find and isolate the issues first. Here are some easy methods, using which you would be able to check the fragmentation levels on the indexes:

1. DBCC SHOWCONTIG
2. Dynamic Management Views (DMVs)

You can use the DBCC SHOWCONTIG (expected to be deprecated) command to see internal statistics like “Average Pages per Extent” or “Extent Fragmentation” etc. Refer to the screenshot below:

Using DMVs, you can issue the following command:

SELECT DB_NAME() AS DBName,

OBJECT_NAME(ind.object_id) AS TableName,

ind.name AS IndexName,

indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent,

indexstats.fragment_count,

indexstats.avg_fragment_size_in_pages,

SUM(p.rows) AS Rows 

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.indexes AS ind ON ( ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id)

INNER JOIN sys.partitions AS p ON ( ind.object_id = p.object_id

AND ind.index_id = p.index_id)

WHERE indexstats.avg_fragmentation_in_percent > 30

GROUP BY

OBJECT_NAME(ind.object_id),

ind.name,

indexstats.index_type_desc,

indexstats.avg_fragmentation_in_percent,

indexstats.fragment_count,

indexstats.avg_fragment_size_in_pages 

ORDER BY indexstats.avg_fragmentation_in_percent DESC

The output of the above script would be the following:

Now, how to fix Index fragmentation? Here are some options:

1. Choose the appropriate SQL Server recovery models to avoid escalation of log file size.
2. Drop or disable** the Index’s highlighted before any Bulk-Load operations and then load the incremental updates
3. Change the Index Fill-Factor to 80 (Also experiment with 70) from the default value to leave free space on index leaf pages.
4. 5% to 30% — Do  a REORGANIZE and the rest – Rebuild

For best performance, Bulk Inserts into HEAP and then create the KEYs would be ideal.

** When you disable an Index, they are not maintained, or considered in Query execution plans and can be dropped. Ensure that you enable them back if they are frequently used.

SQL Server Maintenance plans may not be the best option for you since it will REBUILD ALL indexes without the option to specify any particular index(s). A custom task would be more suitable and SQL Server 2016 onwards, there are some options to manage Fragmentation as well.

Conclusion

Managing and maintaining Index Fragmentation is essential for all production SQL Servers, and we expect this to be automated future releases of SQL Server. Azure SQL already gives you the option to create or drop indexes automatically. But, we don’t have a way to manage fragmentation yet and is still a custom task.

We hope this changes for the greater good. Have questions or suggestions? Please use the comment section down below. Thanks for your visit!

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.