1. 为什么DBA都爱DMV？
如果把SQL Server比作一辆跑车，动态管理视图（DMV）就是仪表盘上的实时监测系统。它们像是藏在数据库引擎内部的"传感器"，随时告诉你哪些SQL吃光了内存、哪个会话卡住了IO、又是谁偷偷锁住了关键数据表。本文将手把手带你玩转最实用的DMV脚本，解锁生产环境监控黑科技。
2. DMV基础扫盲
技术栈声明：本文所有示例基于SQL Server 2019及以上版本
2.1 什么是动态管理视图？
DMV是一组内存中的虚拟表，通过T-SQL直接查询就能获取实例级或会话级的实时状态数据。其特点就像体检中心的X光机——无侵入、快照式、高时效。
2.2 常用DMV分类
- 内存相关：
sys.dm_os_memory_* - IO相关：
sys.dm_io_virtual_file_stats - 锁相关：
sys.dm_tran_locks - 会话级：
sys.dm_exec_*
注意事项：读取DMV需要VIEW SERVER STATE权限，部分数据具有会话作用域特性
3. 内存监控：揪出吃内存的"大户"
3.1 当前内存压力诊断
-- 内存分配全景图（单位：MB）
SELECT
type AS [内存类型],
pages_kb/1024 AS [占用内存(MB)],
virtual_memory_committed_kb/1024 AS [提交内存(MB)],
page_fault_count AS [页面错误次数]
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;
/* 关键字段解读：
- CACHESTORE_SQLCP：执行计划缓存
- MEMORYCLERK_SQLBUFFERPOOL：数据页缓存
- 页面错误次数突增可能预示内存压力 */
3.2 查询级内存消耗排行
-- TOP 10内存消耗查询（需开启查询存储）
SELECT TOP 10
qt.query_sql_text,
qrs.avg_query_max_used_memory/1024 AS [平均内存(MB)],
qrs.max_query_max_used_memory/1024 AS [峰值内存(MB)],
qrs.count_executions AS [执行次数]
FROM sys.query_store_query q
JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats qrs ON qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
CROSS APPLY sys.dm_exec_sql_text(q.query_hash) qt
ORDER BY qrs.max_query_max_used_memory DESC;
4. IO性能分析：找出拖慢系统的"蜗牛盘"
4.1 数据库文件IO全景扫描
-- 按文件统计IO负载（单位：毫秒）
SELECT
DB_NAME(vfs.database_id) AS [数据库],
mf.physical_name AS [文件路径],
vfs.num_of_reads AS [读取次数],
vfs.num_of_bytes_read/1024/1024 AS [读取量(MB)],
vfs.io_stall_read_ms AS [读取延迟(ms)],
vfs.num_of_writes AS [写入次数],
vfs.num_of_bytes_written/1024/1024 AS [写入量(MB)],
vfs.io_stall_write_ms AS [写入延迟(ms)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY io_stall_read_ms + io_stall_write_ms DESC;
/* 典型问题定位：
- 日志文件写入延迟高 → 检查磁盘RAID配置
- 数据文件读取延迟高 → 索引优化或SSD升级 */
4.2 阻塞IO的会话追踪
-- 当前正在执行物理IO的会话
SELECT
er.session_id,
er.command,
er.wait_type,
er.wait_time,
er.last_wait_type,
st.text AS [执行语句片段],
(er.reads + er.writes) AS [总IO操作数]
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE er.wait_type LIKE 'PAGEIOLATCH%'
ORDER BY er.wait_time DESC;
5. 锁监控：破解死锁迷局
5.1 实时锁状态监控
-- 当前活跃锁阻塞链分析
WITH LockChain AS (
SELECT
tl.request_session_id,
tl.resource_type,
tl.resource_database_id,
tl.resource_associated_entity_id,
tl.request_mode,
tl.request_status,
wt.blocking_session_id,
DB_NAME(tl.resource_database_id) AS [数据库名],
OBJECT_NAME(tl.resource_associated_entity_id) AS [表名]
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_os_waiting_tasks wt ON tl.request_session_id = wt.session_id
WHERE tl.request_session_id <> @@SPID
)
SELECT
lc.request_session_id AS [被阻塞会话],
lc.blocking_session_id AS [阻塞源会话],
lc.[数据库名],
lc.[表名],
lc.request_mode AS [锁类型],
es.host_name AS [客户端主机],
es.program_name AS [客户端程序]
FROM LockChain lc
JOIN sys.dm_exec_sessions es ON lc.blocking_session_id = es.session_id
WHERE lc.blocking_session_id IS NOT NULL;
5.2 历史死锁分析（需结合扩展事件）
-- 解析扩展事件捕获的死锁图
SELECT
deadlock_xml.value('(//process[@id[contains(.,"victim")]]/@spid)[1]', 'INT') AS [牺牲会话],
deadlock_xml.value('(//process[@id[contains(.,"victim")]]/@hostname)[1]', 'VARCHAR(50)') AS [客户端主机],
deadlock_xml.query('//executionStack') AS [执行堆栈],
deadlock_xml.query('//inputbuf') AS [输入命令]
FROM (
SELECT
CAST(target_data AS XML) AS DeadlockData
FROM sys.dm_xe_session_targets xst
JOIN sys.dm_xe_sessions xs ON xs.address = xst.event_session_address
WHERE xs.name = 'system_health'
) AS src
CROSS APPLY DeadlockData.nodes('//event[@name="xml_deadlock_report"]') AS EventData(deadlock_xml);
6. 关联技术：扩展事件联合分析
当DMV无法满足历史追溯需求时，可通过扩展事件进行补充监控：
-- 创建锁超时跟踪事件
CREATE EVENT SESSION [LockTimeout_Tracker] ON SERVER
ADD EVENT sqlserver.lock_timeout(
ACTION(
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.database_id
)
)
ADD TARGET package0.event_file(
SET filename=N'D:\XEvents\LockTimeout.xel'
)
WITH (STARTUP_STATE=OFF);
7. 技术全景分析
应用场景
- 高并发场景下的锁争用分析
- 突发的IO性能降级诊断
- 内存泄漏导致实例不稳定的快速定位
- 生产环境健康检查日报生成
技术优缺点
优势：
- 实时性：毫秒级获取当前状态
- 轻量化：无需部署额外监控工具
- 灵活性：支持自由组合查询条件
局限：
- 非持久化：重启实例后数据丢失
- 瞬时性：无法反映历史趋势
- 复杂度：结果集需要二次分析
注意事项
- 关键指标需设置基线值进行比较
- 长时间监控应结合自动归档机制
- 避免在生产高峰期运行复杂查询
- 权限最小化原则分配DMV访问
8. 总结：让DMV成为你的数据库听诊器
通过本文的脚本工具箱，您现在可以：
- 快速定位内存瓶颈到具体查询语句
- 精确识别拖慢系统的磁盘热点文件
- 可视化呈现复杂的锁阻塞链条 记住，DMV不是银弹，结合Perfmon、Query Store等工具才能构建完整的监控体系。
评论