Find Index Fragmentation Details

Câu truy vấn thống kê thông tin về phân mảnh index cho mỗi database. Đã thử nghiệm và chạy ok trên SQL Server 2008R2 và 2012.

SELECT db_name(ps.database_id) as DatabaseName, 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()
ORDER BY ps.avg_fragmentation_in_percent desc
GO

Here is a simpler script, get fragmentation of a specific table, from MSDN:

SELECT a.index_id, name, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b 
ON a.object_id = b.object_id AND a.index_id = b.index_id; 

Tham khảo:

• http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/
• http://msdn.microsoft.com/en-us/library/ms189858.aspx