查找具有重度碎片索引的表格

Find Tables With Heavily Fragmented Indexes

发布-日期: 2015年3月30日 (评论)

中文
English
SQL Logic
Build Info
Author
License

中文

下面是查找重度碎片索引的表格的快速方法。用来查找超过70％碎片的表格。用其进行定期维护工作中是个不错的主意，你只需重新制作那些超过70％到80％碎片的作业。通过重组，你也可以查询任何低于这个百分比的表格。

English

Here’s quick way to find Tables that have heavily fragmented indexes. This is designed to find tables that are above 70% fragmentation. It’s a good idea to incorporate this into a regularly scheduled maintenance job, and just REBUILD those that are above 70% to 80% fragmentation. Anything below this point can simply be REORGANIZED.

Logic

select
'database_name' = db_name(sddips.database_id)
,   'table_name' = object_name(sddips.object_id)
,   'object type' = ist.table_type
,   'fragmentation' = left(sddips.avg_fragmentation_in_percent, 4) from
sys.dm_db_index_physical_stats (db_id(), null, null, null, null) sddips
join information_schema.TABLES ist on object_name(sddips.object_id) = ist.table_name where
ist.table_type = 'base table' and sddips.avg_fragmentation_in_percent &gt; 70 order by
sddips.avg_fragmentation_in_percent desc;