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:
As a result of this behavior, we see two types of Index fragmentation on SQL Server:
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.
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:
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,
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
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:
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.
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!
Comments are closed.