SQL Server 2005 動態管理視圖 (DMV) 與函數 (DMF) 效能優化實戰

前言

SQL Server 2005 引入 DMV/DMF，提供伺服器狀態的即時可見性，是效能調優、故障診斷的核心工具。配合 Profiler、效能儀表板，構成完整觀測體系。

核心 DMV 分類

實戰 1：找出 CPU 耗用最高的查詢

SELECT TOP 100
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_elapsed_time / qs.execution_count AS AvgElapsedTime,
    DB_NAME(st.dbid) AS DatabaseName,
    OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
    SUBSTRING(st.text, 
        (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset) / 2 + 1
    ) AS StatementText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 100
ORDER BY qs.total_worker_time / qs.execution_count DESC;  -- 平均 CPU 時間排序

關鍵點解析

• CROSS APPLY dm_exec_sql_text(sql_handle)：將控制代碼轉為 SQL 文本
• statement_start_offset/2：位移單位為**字元** (2 bytes)，需除以 2 轉為字串索引
• execution_count > 100：過濾一次性臨時查詢，聚焦高頻熱點
• 排序依據：total_worker_time/execution_count (平均 CPU) 或 total_elapsed_time/execution_count (平均耗時) 或 total_logical_reads/execution_count (平均邏輯讀取)

實戰 2：I/O 統計追蹤

SET STATISTICS IO ON;
GO
SELECT TOP 1 * FROM Sales.Customer WHERE CustomerType <> 'S';
GO
-- 輸出範例：
-- Table 'Customer'. Scan count 1, logical reads 6, physical reads 0, read-ahead 0...

-- 清除緩衝區 (僅測試環境使用)
DBCC DROPCLEANBUFFERS;

輸出欄位含義

• Scan count：掃描/搜尋次數
• Logical reads：記憶體中讀取頁數 (核心指標)
• Physical reads：磁碟實際讀取頁數
• Read-ahead：預讀頁數
• LOB logical/physical reads：大物件讀取

實戰 3：CPU 時間測量

DECLARE @x INT = 1, @cpu_start BIGINT = @@CPU_BUSY;

WHILE @x < 10000 SET @x = @x + 1;
PRINT 'ms of cpu for loop1: ' + CAST((@@CPU_BUSY - @cpu_start) * @@TIMETICKS / 1000 AS VARCHAR);

SET @cpu_start = @@CPU_BUSY;
WHILE @x < 100000 SET @x = @x + 1;
PRINT 'ms of cpu for loop2: ' + CAST((@@CPU_BUSY - @cpu_start) * @@TIMETICKS / 1000 AS VARCHAR);

-- 關鍵系統函數：
-- @@CPU_BUSY：啟動以來 CPU 忙碌的時間 (單位：timeticks)
-- @@TIMETICKS：每秒 timeticks 數 (通常 31.25ms/tick，即 32 ticks/秒)

實戰 4：找出執行最頻繁的預存程序

SELECT 
    CASE WHEN cp.dbid = 32767 THEN 'Resource' ELSE DB_NAME(cp.dbid) END AS DBName,
    OBJECT_SCHEMA_NAME(cp.objectid, cp.dbid) AS SchemaName,
    OBJECT_NAME(cp.objectid, cp.dbid) AS ObjectName,
    SUM(cp.usecounts) AS UseCount,
    SUM(qs.total_elapsed_time) AS TotalElapsedTime,
    SUM(qs.total_elapsed_time) * 1.0 / SUM(cp.usecounts) AS AvgElapsedTime,
    SUBSTRING(CONVERT(CHAR(23), DATEADD(MS, SUM(qs.total_elapsed_time)/1000, 0), 121), 12, 23) AS TotalElapsedTime_ms,
    cp.dbid, cp.objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
JOIN (
    SELECT SUM(total_elapsed_time) AS total_elapsed_time, plan_handle
    FROM sys.dm_exec_query_stats
    GROUP BY plan_handle
) qs ON cp.plan_handle = qs.plan_handle
WHERE cp.objtype = 'Proc'
  AND UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(text, ' ', ''), CHAR(13), ''), CHAR(10), ''), CHAR(9), ''), '  ', ''), '   ', ''), '    ', '')) LIKE '%CREATE PROC%'
GROUP BY cp.dbid, cp.objectid
ORDER BY AvgElapsedTime DESC;

關鍵點：dbid = 32767

資源資料庫 (mssqlsystemresource) 的固定 ID，隱藏在系統資料庫中，儲存系統物件定義。查詢系統預存程序時會出現此 ID。

常用 DMV 快速查詢卡

結語

DMV/DMF 是 SQL Server 內建的「X 光機」，善用它們能從癥狀定位到根因。建立基準線、持續監控、關聯多個 DMV 交叉驗證，是從「調參」進化到「系統效能工程」的關鍵。

1. 上一篇文章： SQL Server 2005/2008 修改 sa 密碼完整步驟圖解
2. 下一篇文章： SQL Server 備份檔案驗證完整指南：RESTORE VERIFYONLY 與 CHECKSUM 機制

關於SqlServer

XML 資料庫市場爆發式增長：微軟、IBM、Oracle 三巨頭佈局解析

市場趨勢 IDC 調查顯示：29% 受訪企業大量使用 XML

Windows Server 2008 安裝 SQL Server 2005 遠程連線失敗完整排查指南

典型錯誤現象 Provider: Named Pipes P

SQL Server PARSENAME 函數巧用：點分隔字串擷取與 IP 解析

函數語法與原理 PARSENAME ( object_nam

SQL Server 資料表結構圖解：欄位、索引、約束與關聯的視覺化文件化

前言 資料表結構文件化是資料庫設計、移交與維護的關鍵環節。透