sql Index Fragmentation

DECLARE
	@database_id INT = DB_ID(),
	@object_id INT = OBJECT_ID(N'Production.Product');

SELECT
	a.index_id,
	b.name As [indexname],
	a.avg_fragmentation_in_percent, 
	s.name as schemaname,
	o.name as tablename,
	'ALTER INDEX ' + b.name + ' ON [' + s.name + '].[' + o.name + '] REBUILD' as [rebuild]
FROM sys.dm_db_index_physical_stats (@database_id, @object_id, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.objects AS o ON a.object_id = o.object_id  
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE
	a.avg_fragmentation_in_percent > 0
	AND b.name IS NOT NULL
	AND page_count > 25--Ignore small tables
ORDER BY a.avg_fragmentation_in_percent DESC
Identifies fragmentation percentage of indexes and supplies the corresponding query to rebuild the index.

Updated: Wednesday 25th October 2017, 11:36am

There are 0 comments

Leave a comment of your own

Comments are currently closed.