TSQL to find index fragmentation in SQL Server

To determine index fragmentation in SQL Server, you can use the following steps:

Connect to the SQL Server instance using a tool like SQL Server Management Studio (SSMS) or any other database management tool.
Open a new query window.
Select the database for which you want to check index fragmentation. You can do this by executing the following command. This query retrieves information from the dynamic management view sys.dm_db_index_physical_stats, which provides information about index fragmentation.

SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME ,
SI.NAME AS INDEX_NAME ,
avg_fragmentation_in_percent AS FRAGMENT_PERCENT ,
DMV.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
AS DMV
LEFT OUTER JOIN SYS.INDEXES AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID
AND DMV.INDEX_ID = SI.INDEX_ID
--WHERE avg_fragmentation_in_percent > 10
AND index_type_desc IN ( 'CLUSTERED INDEX', 'NONCLUSTERED INDEX' )
AND DMV.record_count >= 2000
--ORDER BY TABLE_NAME DESC
ORDER BY FRAGMENT_PERCENT desc

We can also check the individual index fragmentation of an index by using SSMS.

To determine index fragmentation in SQL Server, you can use the following steps:

Connect to the SQL Server instance using a tool like SQL Server Management Studio (SSMS)
Navigate to the index you want to check
Right click and goto properties
Select Fragmentation, it will show the fragmentation in %