Find index fragmentation in table

As a DBA, you often get question from developers and users- what is indexing and fragmentation? Well, in simple words indexing is the process of creating index in the table to enhance the performance of queries. Index is created as the subset of different columns in the table hence it is subset of indexed table. More frequent dml operation in table, more fragmentation in tables. One of the common cause of index fragmentation is page-split. It happens when there is no free space on data page to accommodate new insert or updates. Similarly when data get deleted more randomly, more empty pages formed creating logical fragmentation. Index fragmentation increase i/o from the queries which slow down performance.
You can use following queries to find the fragmentation in the table.

SELECT ips.index_id, name, avg_fragmentation_in_percent FROM 
sys.dm_db_index_physical_stats (DB_ID(N'DBName'), OBJECT_ID(N'dbo.tableName'), NULL, NULL, NULL) AS ips 
inner JOIN sys.indexes AS ix ON ips.object_id = ix.object_id AND ips.index_id = ix.index_id;

Leave a Reply

Your email address will not be published. Required fields are marked *