Microsoft SQL Server – sample script illustrates how to check index fragmentation of a specified database

mswindows_logo.jpg   This script can be used for checking index fragmentation percentage of index tables in SQL Server databases

SELECT b.name, ROUND(ps.avg_fragmentation_in_percent,0)
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 ps.avg_fragmentation_in_percent > 10 AND ps.index_id > 0 AND ps.page_count > 10 * 1024 / 8
ORDER BY b.name

MS_SQL_Index_00

Leave a Reply

You must be logged in to post a comment.