Technical Article

Index fragmentation on all databases

,

The script returns all the fragmented indexes on all databases on the instance you run it. Can be run on multiple instances in the same time using central management capability.

create table #temp_if(
 [DBName] nvarchar(128) NULL,
 [SchemaName] nvarchar(128) NULL,
 [ObjectName] nvarchar(128) NULL,
 [IndexName] nvarchar(128) NULL,
 [fragmentation] numeric(38,35) NULL,
 [page_count] bigint NULL);

 exec master.sys.sp_msforeachdb 'USE [?];
insert into  #temp_if
select ''?'' as [DbName],
dbschemas.[name], 
dbtables.[name], 
dbindexes.[name],
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() and indexstats.page_count > 1000
and indexstats.avg_fragmentation_in_percent > 5
and dbindexes.[name] IS NOT NULL;'

select * from  #temp_if
order by page_count desc;

drop table  #temp_if;

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating