Almost all of us know what fragmentation in SQL indexes are and how it can affect the performance. For those who are new Index fragmentation generally happens when any of the following conditions are met:
The logical order of pages in an index do not match with the physical order present in the data file.
By deletion of existing rows or updating existing values of the indexed columns.
To check if your MS SQL Server contains fragmentation in your indexes, execute the below query.
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,ind.name AS IndexName
,indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,page_count , DB_NAME(database_id)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Generally any fragmentation between 10 to 30 percent is considered fine. If the result shows fragmentation more than 30% on indexes than you really need to fix this as it might be main root cause for affecting application performance or slowness.
The Index Fragmentation issue can be solved by,
If the fragmentation levels are low, than you can defragment the indexes.
If the fragmentation levels are high, then ideally you should rebuild the indexes.
