点击(此处)折叠或打开

select 'ALTER INDEX ['+ind.name+'] ON [dbo].['+OBJECT_NAME(ind.OBJECT_ID)+'] REBUILD',
OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id= indexstats.object_id AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >30
and ind.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC

点击(此处)折叠或打开

select a.name as tabname
,ind.name as idname,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
,rr.row_num as rownum
from sys.objects as a with (nolock)
right join sys.indexes as ind with (nolock)
on a.object_id=ind.object_id
inner join sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
on ind.object_id= indexstats.object_id AND ind.index_id = indexstats.index_id
join ( select a.name as tbname,max(b.rows) as row_num
from sysobjects a ,sysindexes b
where a.id=b.id and a.xtype='u' --and a.name like 'TRP%'
group by a.name
) rr
on a.name=rr.tbname
where a.type<>'s' --and a.name like 'TRP%'
--and indexstats.avg_fragmentation_in_percent >30
and rr.row_num>10000 and ind.name is not null
order by indexstats.avg_fragmentation_in_percent desc,rr.row_num desc
go

ITPub博客

rebuild index which avg_fragmentation_in_percent >30