SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. These modifications cause the information within the index to become scattered in the database. Fragmentation exists when indexes within the index to become scattered in the database.
Fragmentation exists when indexes have pages where the logical ordering does not match the physical ordering within the data file. Heavily fragmented indexes can degrade the query performance and cause your application to respond slowly.
Fragmentation normally occurs when a large number of insert and update operations are performed on the table. Fragmented data can cause the SQL Server to perform unnecessary data reads. This affects the performance of the query. Therefore, index defragmentation is necessary to speed up the query performance.
The first step in deciding which defragmentation method to use is to determine the degree of index fragmentation. You can detect index fragmentation by using the sys.dm_db_index_physical_stats system function.
The following statement displays a list of all the indexes on the HumanResources.Employee table with their fragmentation level:
SELCET a.index_id AS IndexID, name AS IndexName,
Avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID (N’AdventureWorks’),
OBJECT_ID (‘HumanREsources.Employee’), NULL, NULL, NULL ) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id ORDER BY Fragmentation desc
In the preceding output given by this query, you can notice that the AK_Employee_LoginID index shows a high level of fragmentation.
After the degree of fragmentation is known, the fragmentation needs to be corrected. The following table lists the actions to be taken at different fragmentation levels to defragment the index.
Fragmentation Level Action to be Taken
>5% and <=30% ALTER INDEX REORGANIZE
>30% ALTER INDEX REBUILD WITH (ONLINE – ON)
ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD
After executing the preceding command, the degree of fragmentation is reduced.
Fragmentation exists when indexes have pages where the logical ordering does not match the physical ordering within the data file. Heavily fragmented indexes can degrade the query performance and cause your application to respond slowly.
Fragmentation normally occurs when a large number of insert and update operations are performed on the table. Fragmented data can cause the SQL Server to perform unnecessary data reads. This affects the performance of the query. Therefore, index defragmentation is necessary to speed up the query performance.
The first step in deciding which defragmentation method to use is to determine the degree of index fragmentation. You can detect index fragmentation by using the sys.dm_db_index_physical_stats system function.
The following statement displays a list of all the indexes on the HumanResources.Employee table with their fragmentation level:
SELCET a.index_id AS IndexID, name AS IndexName,
Avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID (N’AdventureWorks’),
OBJECT_ID (‘HumanREsources.Employee’), NULL, NULL, NULL ) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id ORDER BY Fragmentation desc
In the preceding output given by this query, you can notice that the AK_Employee_LoginID index shows a high level of fragmentation.
After the degree of fragmentation is known, the fragmentation needs to be corrected. The following table lists the actions to be taken at different fragmentation levels to defragment the index.
Fragmentation Level Action to be Taken
>5% and <=30% ALTER INDEX REORGANIZE
>30% ALTER INDEX REBUILD WITH (ONLINE – ON)
Method of Degragmentation
You can execute the following command to defragment the AK_Employee_LoginID index:ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD
After executing the preceding command, the degree of fragmentation is reduced.