4. 基于DMV的实时监控查询

动态管理视图（DMV）是SQL Server暴露实例内部运行状态的主要窗口。与性能计数器、扩展事件相比，DMV的优势在于“随查随用”：一条SELECT就能拿到当前会话、请求、等待、内存、I/O的实时快照，非常适合做监控平台的采集源。本节围绕四类最常用的DMV展开：会话与请求、执行计划、内存、I/O，并给出可直接运行的查询与解读方法。

一、会话与请求：sys.dm_exec_sessions 与 sys.dm_exec_requests

这两个视图是实时监控的起点。sys.dm_exec_sessions描述每个已建立连接的会话级信息，如session_id、login_name、host_name、program_name、status、last_request_start_time；sys.dm_exec_requests描述当前正在执行的请求，关键列包括request_id、status（running/suspended/runnable）、command、sql_handle、plan_handle、cpu_time、total_elapsed_time、wait_type、wait_time、blocking_session_id、reads、writes、logical_reads。

一个实用的“当前活跃请求”查询如下，它把会话与请求关联，并取出正在执行的SQL文本：

SELECT r.session_id, r.request_id, r.status, r.command,
       r.cpu_time, r.total_elapsed_time, r.wait_type, r.wait_time,
       r.blocking_session_id, r.logical_reads, r.reads, r.writes,
       s.login_name, s.host_name, s.program_name,
       SUBSTRING(t.text,
                 (r.statement_start_offset/2)+1,
                 ((CASE r.statement_end_offset WHEN -1
                        THEN DATALENGTH(t.text)
                        ELSE r.statement_end_offset END
                   - r.statement_start_offset)/2)+1) AS current_stmt
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

解读要点：status='suspended'且wait_type非空，说明请求在等资源，应结合等待类型判断瓶颈；blocking_session_id非0表示被阻塞，可顺藤摸瓜找到根会话；total_elapsed_time远大于cpu_time，通常意味着大量等待而非计算。注意statement_start_offset与statement_end_offset以字节为单位，除以2才是字符偏移，这是拼接“当前语句”时最容易出错的地方。

二、执行计划：sys.dm_exec_query_plan 与 sys.dm_exec_plan_attributes

拿到plan_handle后，可用sys.dm_exec_query_plan取回XML执行计划，用sys.dm_exec_plan_attributes查看计划属性（如set_options、is_recompile、cacheobjtype）。在监控平台中，通常不直接展示完整XML，而是提取关键算子：扫描/查找、排序、哈希匹配、并行度、预估行数与实际行数偏差。

SELECT r.session_id, r.plan_handle,
       p.query_plan,
       a.attribute, a.value
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
CROSS APPLY sys.dm_exec_plan_attributes(r.plan_handle) a
WHERE r.session_id <> @@SPID
  AND a.attribute IN ('set_options','is_recompile','cacheobjtype');

解读时重点关注：set_options不一致会导致同一语句生成多份计划，是计划缓存膨胀的常见原因；is_recompile=1说明该计划被标记为重编译；XML中若出现Table Scan且预估行数很小、实际行数很大，往往提示统计信息过期或参数嗅探。监控平台可对query_plan做XQuery提取，把“预估行数/实际行数比值”作为告警指标。

三、内存相关DMV：sys.dm_os_memory_clerks 与 sys.dm_os_process_memory

内存监控的核心是区分“SQL Server进程占用”与“内部各内存 clerk 的分布”。sys.dm_os_process_memory给出physical_memory_in_use_kb、large_page_allocations_kb、locked_page_allocations_kb；sys.dm_os_memory_clerks按类型列出pages_kb、virtual_memory_reserved_kb。最常看的是MEMORYCLERK_SQLBUFFERPOOL（缓冲池）、CACHESTORE_SQLCP（计划缓存）、MEMORYCLERK_SQLQERESERVATIONS（排序/哈希的内存授予）。

SELECT type, pages_kb, virtual_memory_reserved_kb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 10240
ORDER BY pages_kb DESC;

SELECT physical_memory_in_use_kb,
       large_page_allocations_kb,
       locked_page_allocations_kb,
       page_fault_count
FROM sys.dm_os_process_memory;

解读要点：缓冲池占比通常最大，属正常；若CACHESTORE_SQLCP异常增长，说明计划缓存未复用，应检查是否大量即席查询未参数化；page_fault_count持续快速增长，提示物理内存不足或存在内存压力。监控平台可对pages_kb做趋势采集，识别缓慢泄漏。

四、I/O相关DMV：sys.dm_io_virtual_file_stats 与 sys.dm_io_pending_io_requests

I/O监控关注“哪个文件慢、慢在读写还是等待”。sys.dm_io_virtual_file_stats按数据库和文件给出num_of_reads、num_of_writes、io_stall_read_ms、io_stall_write_ms、size_on_disk_bytes。用两次采样差值可算出平均每次I/O的延迟：

SELECT DB_NAME(vfs.database_id) AS db_name,
       mf.physical_name,
       vfs.num_of_reads, vfs.num_of_writes,
       vfs.io_stall_read_ms, vfs.io_stall_write_ms,
       CAST(vfs.io_stall_read_ms * 1.0
            / NULLIF(vfs.num_of_reads,0) AS DECIMAL(10,2)) AS avg_read_ms,
       CAST(vfs.io_stall_write_ms * 1.0
            / NULLIF(vfs.num_of_writes,0) AS DECIMAL(10,2)) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

解读要点：avg_read_ms或avg_write_ms持续高于10~20ms，通常说明存储层存在压力；数据文件与日志文件应分开观察，日志写入延迟高往往影响事务提交。配合sys.dm_io_pending_io_requests可看到当前挂起的I/O数量，若长期非零，说明I/O队列积压。

五、把四类DMV串成一条采集链

在监控平台中，建议按固定频率（如每10秒）采集上述四类DMV，并做差值计算。会话与请求用于发现“当前谁在跑、卡在哪”；执行计划用于判断“为什么慢”；内存用于判断“是否因缓存或授予不足导致抖动”；I/O用于判断“是否存储层拖累”。四者结合，才能从“看到慢”走到“知道为什么慢”。

最后给出一个可直接用于监控平台的“活跃请求+等待+阻塞”采集模板，字段覆盖会话、请求、等待与阻塞关系，便于入库后做趋势与告警：

SELECT GETDATE() AS collect_time,
       r.session_id, r.status, r.command,
       r.cpu_time, r.total_elapsed_time,
       r.wait_type, r.wait_time, r.last_wait_type,
       r.blocking_session_id, r.logical_reads,
       s.login_name, s.host_name, s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID;

把这条查询的结果按collect_time写入监控表，再对total_elapsed_time、wait_time、blocking_session_id做聚合，就能形成实时面板与告警规则。DMV的价值不在于“查一次”，而在于“持续查、对比看、按基线判”。