Let’s explore the methods on how to check index fragmentation in sql server. Bobcares, as a part of our Server Management Service offers solutions to every SQL query that comes our way.

How To Check Index Fragmentation In SQL Server?

The tables and indexes of databases become fragmented over time as a result of records being added, updated, and deleted. This fragmentation may cause the SELECT queries as well as the INSERT, UPDATE, and DELETE operations to perform poorly.

Method 1

By running a query on the built-in sys.dm db index physical stats DMV, we can discover index fragmentation. We must combine the query to additional DMVs like sys.tables and sys.indexes in order to obtain understandable, practical data.

The easy query shown below will list the indexes, record counts, and fragmentation percentage for each table in a database.

USE YourDatabase

GO

SELECT s.[name] +‘.’+t.[name]  AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘SAMPLED’) ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY avg_fragmentation_in_percent DESC

Method 2

Run the script below to get the index fragmentation for the X3 db. We must insert the code into a new query in SQL Server Management Studio and direct the db connection to the X3 db.

SELECT
 Database_ID,
 CONVERT(VARCHAR(35),DB_NAME(idx.database_id)) AS [DB Name],
 sch.name [Schema Name],
 idx.[Object_ID],CONVERT(VARCHAR(35),OBJECT_NAME(idx.[OBJECT_ID])) AS 'OBJECT NAME',
 idx.Partition_Number [Partition Number],
 idx.Index_ID [Index ID],
 idx.Index_Type_Desc [Index Type Desc],
 idx.Alloc_Unit_Type_Desc [Alloc Unit Type Desc],
 idx.Index_Depth,Index_Level [Index Depth Level],
 idx.Avg_Fragmentation_In_Percent [Avg Frag Percent],
 idx.Fragment_Count [Frag Count],
 Avg_Fragment_Size_In_Pages [Avg Frag Size In Pages],
 idx.Page_Count [Page Count],
 idx.Avg_Page_Space_Used_In_Percent [Avg Page Space Used In Percent],
 idx.Record_Count [Record Count],
 Ghost_Record_Count [Ghost Record Count],
 idx.Version_Ghost_Record_Count [Version Ghost Record Count],
 idx.Min_Record_Size_In_Bytes [Min Record Size In Bytes],
 idx.Max_Record_Size_In_Bytes [Max Record Size In Bytes],
 idx.Avg_Record_Size_In_Bytes [Avg Record Size In Bytes],
 Forwarded_Record_Count [Forwarded Record Count]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') idx
 INNER JOIN sys.tables t
 ON idx.object_id = t.object_id
 INNER JOIN sys.schemas sch
 ON sch.schema_id = t.schema_id
 WHERE idx.page_count > 100
 AND idx.avg_fragmentation_in_percent > 15
 AND idx.Index_ID > 0
ORDER BY idx.avg_fragmentation_in_percent desc

Method 3

We can find the Index Fragmentation status using the T-SQL statement. On a single table, several columns joins to form a number of indexes, each of which can have a varied index fragmentation percentage. Users must now find that threshold value from the db before making it appropriate or adding an index to maintenance. To locate it with object information, use the T-SQL command below.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

How to fix it?

We can execute the index maintenance commands, REBUILD or REORGANIZE with the ALTER INDEX statement. Users can perform this command using SSMS as well.

[Need assistance further? Get in touch with us.]

Conclusion

To sum up, our Support team provides three methods to check index fragmentation in SQL server in this article.