查找具有重度碎片索引的表格
Find Tables With Heavily Fragmented Indexes
发布-日期: 2015年3月30日 (评论)
Contents
中文
下面是查找重度碎片索引的表格的快速方法。用来查找超过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 > 70 order by
sddips.avg_fragmentation_in_percent desc;
Build-Info
Build Quality | Build History | ||||
---|---|---|---|---|---|
Author
- 李聪明的数据库 Lee's Clever Data
- Mike的数据库宝典 Mikes Database Collection
- 李聪明的数据库 "Lee Songming"