Write below query to find Index fragmentation.
SELECT ps.database_id, ps.OBJECT_ID,object_name(ps.object_id) AS [TableName],
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS
ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and b.name is not null
ORDER BY ps.avg_fragmentation_in_percent desc
GO
It will return TableName, IndexId , Name ,Index Fragmentation.
Now we can easily read the fragmentation and rebuild , reorganize indexes
to get high performance.
If Index fragmentation is more than 20 it is totally disable for sql
table. I think this is a good practice to get fragmentation percentage
and rebuild , reorganize indexes.