search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges
MCSE
Community

Cozumpark Bilisim Portali
MSSQL Database Index Fragmentation Reports
Posted in Windows Server | No Comment | 2,200 views | 10/11/2015 15:10

You can get Database Index Fragmentation Reports with following query:

SELECT object_name(ps.object_id) AS [name], 
	ps.index_id,
	i.name AS IndexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ps
LEFT JOIN sys.indexes i ON i.object_id=ps.object_id AND i.index_id=ps.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ps.page_count  > 100
ORDER BY ps.avg_fragmentation_in_percent DESC

You can check MSDN for more information about sys.dm_db_index_physical_stats.



Leave a Reply