One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one particular database. For SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command. DBCC SHOWCONTIG has some limitation for SQL Server 2000.
SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database.
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test_contig'), NULL, NULL , 'DETAILED')
Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
The results will help DBA to make necessary reports.
Reference : Pinal Dave (http://blog.SQLAuthority.com)