SQL server数据库表碎片比例查询语句

最新推荐文章于 2025-06-27 17:00:22 发布

转载最新推荐文章于 2025-06-27 17:00:22 发布 · 397 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/shuzehui/p/10149660.html

文章标签：

#数据库

本文提供了一段SQL脚本，用于检测数据库中的索引碎片，并根据碎片百分比决定是否需要重建或重组索引。当平均碎片百分比超过30%时，建议使用ALTER INDEX REBUILD重建索引；如果碎片比例在5%到30%之间，则推荐使用ALTER INDEX REORGANIZE进行索引重组。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

For rebuilding index, here is also a script to figure out the fragmentation and decide whether rebuilding index is in need:

use [database_name]

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

When the avg_fragmentation_in_percent >30, please rebuild the index (alter index rebuild). If the 5 < avg_fragmentation_in_percent < 30, please reorgnize the index (alter index reorganize)

However, as you mentioned that it finished quickly, maybe you can manage it before you run the job each time. Just arrange it as the preparation for you job. Please update statistics each time you want to run the job.

转载于:https://www.cnblogs.com/shuzehui/p/10149660.html