当前位置：首页 > 数据库 > MSSQL教程 > rebuild index which avg_fragmentation_in_percent >30 > 正文

rebuild index which avg_fragmentation_in_percent >30

来源：这里教程网 时间：2026-03-02 11:17:13 作者：

点击(此处)折叠或打开

    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


编辑推荐：

相关推荐

最新软件资讯

9164288 misaligned log IOs which required falling back to synchronous IO
9164288 misaligned log IOs which required falling back to synchronous IO

热文推荐

天极热推