How to find Index fragmentation in SQL Server? SQL server Fragmentation
how to find index fragmentation in SQL server?
how to check index fragmentation in SQL server for all tables
Script
SELECT db_name(frag.database_id) as [db_name],
obs.[name] AS ObjectName,
ix.[name] AS IndexName,
frag.avg_fragmentation_in_percent AS AvgFragmentationInPercent,
frag.fragment_count as fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2016'),null,NULL,NULL,'DETAILED') frag
INNER JOIN sys.indexes ix ON frag.[object_id] = ix.[object_id]
AND frag.index_id = ix.index_id
INNER JOIN sys.objects obs ON ix.[object_id] = obs.[object_id]
go
