SQL Server 2005 效能調優工具：Database Engine Tuning Advisor 實戰

前言

面對日增五位數資料量的龐大系統，資料庫效能是專案成敗關鍵。SQL Server 2005 提供 Database Engine Tuning Advisor (DTA)，配合 SQL Server Profiler 工作負載擷取，可自動推薦索引與分區策略，大幅降低手工調優門檻。

工具鏈總覽

標準調優流程

步驟 1：擷取代表性工作負載

1. 開啟 SQL Server Profiler（SSMS → Tools → SQL Server Profiler）
2. 新增追蹤 → 連線目標伺服器
3. 事件選擇 建議勾選：

• TSQL：SQL:BatchCompleted, RPC:Completed, SP:StmtCompleted
• Errors and Warnings：ErrorLog, Exception
• Performance：Showplan XML（含執行計畫）、Showplan All

4. 設定篩選器（可選）：限制 DatabaseName、ApplicationName、Duration ≥ 100ms
5. 執行追蹤 → 在生產/測試環境運行典型業務流程 30-60 分鐘
6. 停止追蹤 → File → Save As → Trace File 儲存為 workload.trc

步驟 2：啟動 DTA 分析

1. SSMS → Tools → Database Engine Tuning Advisor
2. 連線伺服器 → 新增工作階段
3. 工作負載：選擇 workload.trc 或追蹤資料表
4. 要調整的資料庫：勾選目標資料庫（可多選）
5. 調優選項（進階）：

• 推薦索引：預設勾選
• 推薦分區：大資料表勾選
• 保留現有索引：建議勾選（避免建議刪除必要索引）
• 最大推薦數/空間限制：依磁碟容量設定

6. 點擊 開始分析 → 等待完成（視工作負載大小，數分鐘至數小時）

步驟 3：審查與套用建議

1. 建議報表：Partition Recommendations, Index Recommendations
2. 每項建議包含：CREATE INDEX/CREATE PARTITION 腳本、預估改善 %、空間成本
3. 關鍵審查點：

• 索引欄位順序是否符合查詢模式？
• 是否包含 INCLUDE 欄位以涵蓋查詢？
• 分區欄位是否為查詢常用篩選條件？
• 寫入密集表新增索引是否會嚴重影響 INSERT/UPDATE 效能？

4. 可直接在 DTA 點擊 套用建議，或匯出腳本人工審查後部署

實戰案例：索引碎片與缺失索引

問題場景

• 單表 320 萬筆，資料 800MB，索引 500MB
• 所有索引碎片 > 80%，部分 100%
• 查詢緩慢、I/O 高

DTA 建議與執行

-- DTA 建議重建索引（等同 ALTER INDEX ... REBUILD）
ALTER INDEX index_id ON tbTest REBUILD

-- 或線上重建（Enterprise 版，不鎖定資料表）
ALTER INDEX index_id ON tbTest REBUILD WITH (ONLINE = ON)

執行後效果

• 索引空間從 500MB 降至 200MB（-60%）
• 碎片率歸零
• 查詢邏輯讀取數大幅下降

手動檢查索引碎片腳本

SELECT 
    OBJECT_NAME(ips.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC

維護計畫自動化

SSMS → Management → Maintenance Plans → 建立計畫：

• 重建索引：碎片 > 30%（離線）或 > 10%（線上，Enterprise）
• 重組索引：碎片 5-30%（輕量、線上）
• 更新統計資料：每日/每週
• 清除歷程記錄：保留 4 週

工具限制與補充

結語

DTA 是 SQL Server 2005 將「專家經驗」工具化的產物。善用 Profiler 擷取真實負載 → DTA 分析推薦 → 人工審查套用 → 維護計畫自動化 閉環，能將調優工作從「經驗駭客」轉為「標準化工程」。記住：工具建議需人工把關，盲目套用可能適得其反。