Dave Heavy Industries Dave Heavy Industries – blog

29Apr/110

MS SQL Server – index fragmentation – amended

FromĀ http://wp.daveheavyindustries.com/2011/02/07/ms-sql-server-index-fragmentation/

Having had to use this on a very large database, I've made some small changes to the index fragmentation statistics script. this time to include table names (object_name) as well as database_name.

SELECT
ps.database_id,
ps.OBJECT_ID,
OBJECT_NAME(ps.OBJECT_ID,db_id()) as [object_name],
DB_NAME(ps.database_id) as [database_name],
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 b.index_id = ps.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent
GO
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.