How to check Index Fragmentation

How to check the average percentage of Index Fragmentation

When the index is not performing as expected, we should look at the average index fragmentation rate. There is always a potential of fragmentation because the non-clustered index data is stored on different pages.

Index When the physical and logical page ordering in an index's page allocation don't match up, the fragmentation % changes. Information on the data page can be resized using the data modification in the table. Before the table update procedure, the page was nearly full. Yet, after updating the table, there was space on the data page. With the big deletion operation on the table, users can see the unsettling page arrangement. The data page won't be top-full or empty in addition to the update and remove operations. Consequently, unutilized empty space increases the order discrepancy between logical pages and physical pages while also increasing fragmentation.

I created a script to check index fragmentation; should it be identified, you should run INDEX REBUILD or INDEX REORGANIZE on highly fragmented indexes to optimize index performance.

SELECT object_name(ips.object_id) AS TableName

            ,i.name AS IndexName

            ,ips.index_type_desc AS IndexType

            ,ips.avg_fragmentation_in_percent

            ,ips.fragment_count

            ,ips.page_count

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

INNER JOIN sys.indexes AS i ON ips.OBJECT_ID = i.OBJECT_ID

            AND ips.index_id = i.index_id

            order by avg_fragmentation_in_percent desc

 

 Result:

Here, we can see that the maximum average fragmentation percentage is noticeable as 54%, which must be engaged with an action to reduce the fragmentation with the choices of either REBUILD or REORGANIZE.

As we know when maximum average fragmentation percentage is greater than 30%,  we choose REBUILD Index , if it is between 15-30% only then REORGANIZE

Syntex

ALTER INDEX Index_Name ON Table_Name REBUILD;

ALTER INDEX Index_Name ON Table_Name REORGANIZE;