rebuild index which avg_fragmentation_in_percent >30

点击(此处)折叠或打开

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

点击(此处)折叠或打开

1. select a.name as tabname
   
2.        ,ind.name as idname,
   
3.      indexstats.index_type_desc AS IndexType,
   
4. indexstats.avg_fragmentation_in_percent
   
5.     ,rr.row_num as rownum
   
6. from sys.objects as a with (nolock)
   
7. right join sys.indexes as ind with (nolock)
   
8.        on a.object_id=ind.object_id
   
9. inner join     sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
   
10. on ind.object_id= indexstats.object_id AND ind.index_id = indexstats.index_id
    
11. join ( select a.name as tbname,max(b.rows) as row_num
    
12.                 from sysobjects a ,sysindexes b
    
13.                 where a.id=b.id and a.xtype='u' --and a.name like 'TRP%'
    
14.        group by a.name
    
15.        ) rr
    
16. on a.name=rr.tbname
    
17. where a.type<>'s' --and a.name like 'TRP%'
    
18. --and indexstats.avg_fragmentation_in_percent >30
    
19. and rr.row_num>10000 and ind.name is not null
    
20. order by indexstats.avg_fragmentation_in_percent desc,rr.row_num desc
    
21.  go

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16131092/viewspace-2151048/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16131092/viewspace-2151048/