您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Azure 故障排查與查詢優化實戰：善用 DMV 破解雲端資料庫效能謎題

編輯：關於SqlServer

SQL Azure 架構特性與 DMV 差異

核心引擎基於 SQL Server 2008，支援 DMV (Dynamic Management Views)。
早期版本多數 DMV 關閉，後續 Service Update 逐步開放。
共享多租戶架構 → DMV 僅暴露 當前邏輯資料庫 範圍內的資訊，實例級欄位回傳 NULL。
權限模型調整：
本地 SQL Server 需 VIEW SERVER STATE；
SQL Azure 需 VIEW DATABASE STATE。

核心 DMV 分類速查表

交易與鎖相關

DMV	用途
`sys.dm_tran_active_transactions`	當前邏輯資料庫的作用中交易資訊
`sys.dm_tran_database_transactions`	資料庫層級交易詳情
`sys.dm_tran_locks`	鎖管理器資源：資源組 (被鎖物件) + 請求組 (鎖模式/狀態)，排查阻塞/死鎖首選
`sys.dm_tran_session_transactions`	Session 與 Transaction 關聯對應

執行與查詢計劃相關

DMV / Function	用途
`sys.dm_exec_connections`	連線資訊 (Client IP, Net Transport, Auth Scheme 等)
`sys.dm_exec_query_plan(plan_handle)`	回傳 XML 格式顯示計劃 (緩存或執行中)
`sys.dm_exec_query_stats`	緩存查詢計劃聚合效能統計 (CPU、Duration、Reads、Writes、執行次數)，找出昂貴查詢核心來源
`sys.dm_exec_requests`	當前執行中請求的即時狀態 (Wait Type, Blocking Session, Progress %)
`sys.dm_exec_sessions`	所有作用中使用者連線與內部任務
`sys.dm_exec_sql_text(sql_handle)`	由 sql_handle 取得批次完整 T-SQL 文本，取代 `fn_get_sql`
`sys.dm_exec_text_query_plan`	文字格式顯示計劃，無大小限制，適合超大計劃

資料庫儲存與分區統計

DMV	用途
`sys.dm_db_partition_stats`	每個分區的頁數/列數統計。實例級欄位回傳 NULL，舊查詢可直接遷移執行。

三大經典「昂貴查詢」識別查詢

1. 過度重新編譯 (Plan Generation > 1)

SELECT TOP 25
    sql_text.text,
    qs.sql_handle,
    qs.plan_generation_num,
    qs.execution_count,
    qs.dbid,
    qs.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS sql_text
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;

plan_generation_num 表示該計劃被重新編譯的次數。>1 代表至少重編一次，頻繁重編消耗 CPU 並導致計劃不穩定。

2. CPU 消耗最高的查詢 (Total Worker Time)

SELECT 
    hcq.plan_handle,
    hcq.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.text
FROM (
    SELECT TOP 50 qs.plan_handle, qs.total_worker_time
    FROM sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time DESC
) AS hcq
CROSS APPLY sys.dm_exec_sql_text(hcq.plan_handle) AS q
ORDER BY hcq.total_worker_time DESC;

聚焦 total_worker_time (累積 CPU 毫秒數)，找出佔用排程器時間最久的查詢，優化這些可立即降低 CPU 壓力。

3. I/O 瓶頸查詢 (邏輯讀/寫 + 物理讀平均值)

SELECT TOP 25
    (total_logical_reads / execution_count) AS avg_logical_reads,
    (total_logical_writes / execution_count) AS avg_logical_writes,
    (total_physical_reads / execution_count) AS avg_phys_reads,
    execution_count,
    statement_start_offset AS stmt_start_offset,
    sql_handle,
    plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;

以 平均邏輯 I/O 排序，過濾掉執行次數極低的偶發大查詢，鎖定高頻高 I/O 的「隱形殺手」。

雲端優化實戰建議

啟用 Query Store (Azure SQL Database 預設開啟) — 持久化計劃與效能歷史，支援強制計劃、回歸偵測。
定期檢視 sys.dm_db_missing_index_details — 雲端自動索引管理 (Automatic Tuning) 可輔助，但人工審核不可或缺。
參數化查詢 — 減少 Plan Cache 膨脹，提升重用率；必要時用 OPTIMIZE FOR 或 OPTION (RECOMPILE) 處理參數嗅探。
監控 Wait Stats — sys.dm_os_wait_stats (需 VIEW DATABASE STATE) 識別資源等待類型 (PAGEIOLATCH, LCK_M, SOS_SCHEDULER_YIELD 等)。
彈性調度 — 依 DTU/vCore 使用率調整服務層級，避免長期資源爭用導致查詢排隊。