索引碎片是資料庫效能衰退的隱形殺手。隨著 INSERT/UPDATE/DELETE 運作，索引頁面分裂導致邏輯順序與物理順序不符，造成額外 I/O。本文提供完整可執行測試腳本，示範碎片檢測、重建前後對比，並說明 SSMS 圖形化操作。
-- 1. 建立測試環境
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'db_index_test')
DROP DATABASE db_index_test
GO
CREATE DATABASE db_index_test
GO
USE db_index_test
GO
CREATE TABLE tbTest (
rownum INT IDENTITY(1,1),
id VARCHAR(100),
dt DATETIME
)
GO
CREATE INDEX index_id ON tbTest(id)
GO
-- 2. 插入測試資料並製造碎片（交錯插入/刪除）
DECLARE @i INT = 1
WHILE @i < 10
BEGIN
INSERT INTO tbTest (id, dt)
SELECT NEWID(), GETDATE() FROM sys.columns -- 約 900 筆/迴圈
DELETE FROM tbTest WHERE rownum % 2 = 0 -- 刪除一半，製造碎片
SET @i = @i + 1
END
GO
-- 3. 檢查碎片狀態（重建前）
SELECT
OBJECT_NAME(ips.OBJECT_ID) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count,
ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tbTest'), NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
WHERE i.name = 'index_id'
GO
-- 4. 重建索引
ALTER INDEX index_id ON tbTest REBUILD
GO
-- 5. 檢查碎片狀態（重建後）
SELECT
OBJECT_NAME(ips.OBJECT_ID) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count,
ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tbTest'), NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
WHERE i.name = 'index_id'
GO
-- 6. 清理測試環境
USE master
GO
DROP DATABASE db_index_test
GO
sys.dm_db_index_physical_stats (
database_id, -- DB_ID() 目前資料庫
object_id, -- OBJECT_ID('table') 或 NULL 全部
index_id, -- 索引 ID 或 NULL 全部
partition_number, -- 分區號或 NULL 全部
mode -- 'LIMITED'|'SAMPLED'|'DETAILED'
)
| 模式 | 掃描範圍 | 耗時 | 精度 |
|---|---|---|---|
| LIMITED | 僅葉層級，抽樣 1% | 最快 | 粗略 |
| SAMPLED | 所有層級，抽樣 1% | 中等 | 較準 |
| DETAILED | 所有頁面完整掃描 | 最慢 | 最精確 |
| avg_fragmentation_in_percent | 狀態 | 建議動作 |
|---|---|---|
| 0% - 5% | 優秀 | 無需處理 |
| 5% - 30% | 輕度 | ALTER INDEX ... REORGANIZE（線上、輕量） |
| > 30% | 嚴重 | ALTER INDEX ... REBUILD（離線/Enterprise 可 ONLINE） |
| 特性 | REBUILD | REORGANIZE |
|---|---|---|
| 操作 | 卸除重建索引樹 | 就地重排葉頁面 |
| 碎片清除 | 100% | 部分 |
| 統計資料 | 自動更新（FULLSCAN） | 不更新 |
| 鎖定 | Schema-Mod (Sch-M)，離線 Enterprise: ONLINE=ON 可線上 | 共享鎖，線上 |
| 交易記錄 | 大量（可 MIN_LOGGED） | 較少，逐頁記錄 |
| 適用 | 碎片 > 30%、需更新統計 | 碎片 5-30%、24/7 不可停機 |
分散百分比（即 avg_fragmentation_in_percent）線上、填滿因子 等選項
DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME
DECLARE @FragPct FLOAT, @SQL NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT
SCHEMA_NAME(o.schema_id), o.name, i.name,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
JOIN sys.objects o ON ips.OBJECT_ID = o.OBJECT_ID
WHERE ips.avg_fragmentation_in_percent > 5
AND ips.page_count > 1000
AND i.name IS NOT NULL
AND o.type = 'U'
OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @FragPct
WHILE @@FETCH_STATUS = 0
BEGIN
IF @FragPct >= 30
SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' REBUILD'
ELSE
SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' REORGANIZE'
PRINT @SQL
-- EXEC sp_executesql @SQL -- 實際執行請取消註解
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @FragPct
END
CLOSE cur
DEALLOCATE cur
某專案單表 320 萬筆，資料 800MB，索引 500MB，所有索引碎片 > 80%（部分 100%）。執行
ALTER INDEX ... REBUILD後，索引空間降至 200MB（-60%），查詢邏輯讀取大幅下降，效能顯著回升。
索引碎片維護是 DBA 核心職責。掌握 sys.dm_db_index_physical_stats 精準檢測、依碎片程度選擇 REORGANIZE/REBUILD、建立自動化維護計畫，能持續保障查詢效能。記住：統計資料同步更新（REBUILD 自動、REORGANIZE 需手動）同樣關鍵。
