Archive

Posts Tagged ‘index fragmentation’

Viewing index fragmentation

November 7, 2013 Leave a comment

All tables in a given database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),null,null,null,null );

Single given table in a database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),OBJECT_ID('[table_name]'),null,null,'DETAILED' );