大表建立索引的關鍵考量與效能調優策略

對 TB 級大表建立索引是高風險操作，涉及空間規劃、鎖定管理、I/O 優化、排序資源與日誌控制。本文系統梳理作業前評估、執行期調優與事後驗證的完整檢查清單。

一、作業前必要評估

二、I/O 架構優化

物理層面

• 檔案群組分離：索引置於獨立檔案群組 FG_Indexes，對應不同磁碟陣列 (LUN)
• RAID 選型：索引檔案建議 RAID 10 (高隨機讀寫)；資料檔案 RAID 5/6 (順序讀為主)
• 即時檔案初始化：授予 SQL Server 服務帳號 SE_MANAGE_VOLUME_NAME (Perform Volume Maintenance Tasks)，CREATE INDEX 擴展檔案時跳過零填充

邏輯層面

-- 建立索引指定檔案群組
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders (CustomerId, OrderDate)
ON FG_Indexes  -- 指定索引檔案群組
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, MAXDOP = 4)

三、排序與記憶體調優

多塊讀取優化

-- 會話層級調大多塊讀取 (舊版 Oracle 參考，SQL Server 由優化器自動管理)
-- SQL Server 重點在於：
-- 1. 確保資料頁面在緩衝區 (預熱)
-- 2. 使用大頁面 (Trace Flag 834/845，需測試)
-- 3. 避免排序溢寫 tempdb (足夠記憶體授予)

四、日誌與復原優化

• 最小日誌：BULK_LOGGED + TABLOCK + 無觸發器/外鍵 → 僅記錄頁面配置
• NOLOGGING 等效：SQL Server 無直接 NOLOGGING，改用 WITH (ONLINE = ON) + BULK_LOGGED 組合
• 分階段建立：極大表分分區建立，每階段備份日誌釋放空間

五、分區索引策略 (大表必備)

-- 1. 建立分區函數與配置
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME)
AS RANGE RIGHT FOR VALUES ('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01')

CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate TO (FG_2020, FG_2021, FG_2022, FG_2023, FG_Current)

-- 2. 分區對齊索引 (Aligned Index)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId)
ON PS_OrderDate(OrderDate)  -- 與基表分區對齊
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE ON PARTITIONS(1 TO 4), DATA_COMPRESSION = ROW ON PARTITIONS(5))

分區索引優勢

• 可針對歷史分區單獨重建/壓縮/離線，不影響熱資料
• 支援分區級 ONLINE 操作 (SQL Server 2014+)
• 查詢裁剪只掃描相關分區

六、執行監控與驗證

-- 即時進度監控
SELECT
    r.command,
    r.percent_complete,
    r.estimated_completion_time / 60000.0 AS ETA_Minutes,
    r.cpu_time / 1000.0 AS CPU_Sec,
    r.reads, r.writes,
    t.text AS SqlText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%'

-- 完成後驗證
SELECT
    i.name AS IndexName,
    p.partition_number,
    p.rows,
    au.total_pages * 8.0 / 1024 AS Size_MB,
    i.fill_factor,
    i.is_disabled
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE i.object_id = OBJECT_ID('dbo.Orders')
ORDER BY i.name, p.partition_number

七、檢查清單總結

1. [ ] 空間預估與磁碟規劃完成
2. [ ] 維護窗口確認、利害關係人通知
3. [ ] 切換 BULK_LOGGED、啟用即時檔案初始化
4. [ ] 索引導向獨立檔案群組/磁碟
5. [ ] 設定 MAXDOP、ONLINE = ON、DATA_COMPRESSION
6. [ ] 分區表採用對齊索引
7. [ ] 監控腳本就緒、備份策略確認
8. [ ] 完成後更新統計資料 UPDATE STATISTICS ... WITH FULLSCAN