My Adventures in Coding

May 27, 2013

SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Filed under: SQL,SQL Server — Brian @ 11:46 am
Tags: , , , ,

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

That should do the trick!

Create a free website or blog at WordPress.com.