索引碎片

最新推荐文章于 2025-05-16 15:21:58 发布

原创最新推荐文章于 2025-05-16 15:21:58 发布 · 314 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #join #schema #go

本文提供了一种SQL Server中检测索引碎片的方法，并生成相应的修复语句。通过查询系统目录视图和动态管理视图，可以获取索引的逻辑碎片百分比及建议执行的动作。

--检测索引碎片，生成修复语句
WITH indexes AS
(
SELECT si.object_id, si.index_id, si.Name, so.Name AS TableName
    ,SCHEMA_NAME(so.schema_id) AS SchemaName
FROM sys.indexes si JOIN
   sys.all_objects so ON si.object_id=so.object_id
)
SELECT b.name AS [索引名称]
   ,avg_fragmentation_in_percent AS [索引的逻辑碎片%]
   ,PAGE_COUNT AS [索引或数据页的总数]
   ,case when avg_fragmentation_in_percent<=30 THEN char(10)+'ALTER INDEX ['+Name+'] ON ['+SchemaName+'].['+TableName+'] REORGANIZE'+char(10)+'GO'
         ELSE char(10)+'ALTER INDEX ['+Name+'] ON ['+SchemaName+'].['+TableName+'] REBUILD WITH (ONLINE=ON)'+char(10)+'GO'
    END AS [建议执行的动作]
FROM sys.dm_db_index_physical_stats (DB_ID('fyData3.0'), NULL, NULL, NULL, NULL) AS a
    JOIN indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent>0
ORDER BY avg_fragmentation_in_percent