SQLServer索引碎片整理

2023年11月29日发(作者：)

SQLServer索引碎⽚整理

--1.查看碎⽚

SELECT DB_NAME() AS DatbaseName ,

SCHEMA_NAME(_ID) AS SchemaName ,

OBJECT_NAME(s.[object_id]) AS TableName ,

AS IndexName ,

ROUND(_fragmentation_in_percent, 2) AS [Fragmentation %] ,

CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎⽚，索引需要重建'

WHEN avg_fragmentation_in_percent >= 5

AND avg_fragmentation_in_percent < 30 THEN '轻度碎⽚，索引需要重新组织'

ELSE '正常状态'

END 提⽰

FROM _db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s

INNER JOIN s i ON s.[object_id] = i.[object_id] AND _id = _id

INNER JOIN s o ON _id = _id

ORDER BY [Fragmentation %] DESC

--2.整理碎⽚（建议在空闲时间运⾏，尤其不要在⽣产环境运⾏）

IF EXISTS ( SELECT *

FROM s

WHERE OBJECT_ID = @Objectid