SQLServer 动态管理视图详解

概述

动态管理视图 (Dynamic Management Views, DMV) 和动态管理函数 (Dynamic Management Functions, DMF) 是 SQL Server 提供的强大工具，用于实时监控、诊断和优化数据库性能。它们直接从 SQL Server 内部结构中获取数据，提供了深入了解系统运行状态的窗口。

对于 DBA 来说，熟练掌握 DMV 是必备技能，可以帮助快速定位性能瓶颈、排查故障、优化查询和规划容量。

DMV 分类

SQL Server 中的 DMV 主要分为以下几类：

分类	前缀	描述
服务器相关	sys.dm_os_*	操作系统相关信息，如内存、CPU、进程等
数据库相关	sys.dm_db_*	数据库内部对象信息，如索引、查询统计等
执行相关	sys.dm_exec_*	查询执行相关信息，如执行计划、会话等
事务相关	sys.dm_tran_*	事务和锁相关信息
存储引擎相关	sys.dm_io_*	I/O 相关信息

权限要求

使用 DMV 通常需要以下权限：

• VIEW SERVER STATE：查看服务器级 DMV（如 sys.dm_os_wait_stats）
• VIEW DATABASE STATE：查看数据库级 DMV（如 sys.dm_db_index_usage_stats）

可以通过以下命令授予权限：

-- 授予服务器级权限
GRANT VIEW SERVER STATE TO [DBAUser];

-- 授予数据库级权限
GRANT VIEW DATABASE STATE TO [DBUser];

核心 DMV 详解

性能监控核心 DMV

sys.dm_os_wait_stats

用途：返回 SQL Server 等待统计信息，是识别性能瓶颈的首选工具

版本支持：SQL Server 2005 及以上

关键列说明：

• wait_type：等待类型，标识等待资源或事件
• wait_time_ms：总等待时间（毫秒），反映瓶颈严重程度
• waiting_tasks_count：等待任务数，反映问题频率
• signal_wait_time_ms：信号等待时间，反映 CPU 调度延迟

生产场景示例：

-- 生产环境中识别主要等待类型
SELECT TOP 15
    wait_type,
    -- 计算总等待时间占比
    CAST(wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS DECIMAL(10, 2)) AS wait_time_pct,
    waiting_tasks_count,
    -- 计算平均等待时间
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
-- 过滤掉系统正常等待
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
    'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH'
)
ORDER BY wait_time_ms DESC;

最佳实践：

• 定期重置等待统计信息（DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)），便于观察特定时间段的等待情况
• 结合 sys.dm_exec_requests 分析当前正在等待的请求
• 重点关注 PAGEIOLATCH_*（I/O 等待）、LCK_M_*（锁等待）、CXPACKET（并行等待）等常见瓶颈

sys.dm_os_schedulers

用途：监控 CPU 调度器状态，识别 CPU 压力

版本支持：SQL Server 2005 及以上

关键列说明：

• scheduler_id：调度器 ID
• cpu_id：关联的 CPU 核心
• status：调度器状态（VISIBLE ONLINE 表示正常）
• runnable_tasks_count：可运行任务数，直接反映 CPU 压力
• current_tasks_count：当前任务数

生产场景示例：

-- 检查 CPU 压力
SELECT 
    scheduler_id, cpu_id, 
    runnable_tasks_count AS 'CPU 就绪队列长度',
    current_tasks_count AS '当前任务数',
    work_queue_count AS '工作队列长度'
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC;

最佳实践：

• 持续 runnable_tasks_count > 1 表示 CPU 压力较大
• 结合 sys.dm_exec_requests 查看消耗 CPU 最多的查询
• 考虑调整 MAXDOP 设置或增加 CPU 资源

内存管理核心 DMV

sys.dm_os_memory_clerks

用途：查看 SQL Server 内存分配情况，识别内存瓶颈

版本支持：SQL Server 2005 及以上

关键列说明：

• type：内存分配器类型，如 MEMORYCLERK_SQLBUFFERPOOL（缓冲池）、MEMORYCLERK_SQLQUERYPLAN（查询计划）
• single_pages_kb：使用单页分配器的内存（<=8KB）
• multi_pages_kb：使用多页分配器的内存（>8KB）
• virtual_memory_committed_kb：已提交的虚拟内存

生产场景示例：

-- 分析内存使用分布
SELECT 
    type AS '内存分配器类型',
    -- 计算总内存使用
    (single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) / 1024 AS '总内存使用 (MB)',
    single_pages_kb / 1024 AS '单页内存 (MB)',
    multi_pages_kb / 1024 AS '多页内存 (MB)',
    virtual_memory_committed_kb / 1024 AS '虚拟内存 (MB)'
FROM sys.dm_os_memory_clerks
ORDER BY (single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) DESC;

最佳实践：

• 监控 MEMORYCLERK_SQLBUFFERPOOL 大小，确保缓冲池设置合理
• 关注 MEMORYCLERK_SQLQUERYPLAN 增长，可能表示计划缓存膨胀
• 结合 sys.dm_os_sys_memory 查看系统内存状态

查询性能核心 DMV

sys.dm_exec_query_stats

用途：分析查询执行统计信息，识别资源消耗最多的查询

版本支持：SQL Server 2005 及以上

关键列说明：

• sql_handle：SQL 语句句柄
• plan_handle：执行计划句柄
• execution_count：执行次数
• total_worker_time：总 CPU 时间（微秒）
• total_logical_reads：总逻辑读取次数，反映查询效率
• last_execution_time：最后执行时间

生产场景示例：

-- 识别消耗 CPU 最多的查询
SELECT TOP 10
    execution_count,
    -- 转换为秒
    total_worker_time / 1000000 AS total_cpu_time_s,
    -- 计算平均 CPU 时间
    total_worker_time / execution_count / 1000 AS avg_cpu_time_ms,
    total_logical_reads,
    -- 获取完整查询文本
    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 query_text,
    -- 获取查询计划（可选）
    -- qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
-- CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC;

最佳实践：

• 结合 sys.dm_exec_query_plan 查看执行计划
• 关注 avg_cpu_time_ms 和 total_logical_reads 较高的查询
• 定期清理低效查询的执行计划：DBCC FREEPROCCACHE(plan_handle)

sys.dm_exec_requests

用途：查看当前正在执行的请求，诊断阻塞和慢查询

版本支持：SQL Server 2005 及以上

关键列说明：

• session_id：会话 ID
• blocking_session_id：阻塞会话 ID，0 表示未被阻塞
• wait_type：当前等待类型
• wait_time：当前等待时间（毫秒）
• command：命令类型（如 SELECT、UPDATE）
• cpu_time：已使用 CPU 时间
• reads/writes：已执行的读写次数

生产场景示例：

-- 诊断生产环境中的阻塞问题
SELECT
    r.session_id AS '阻塞会话ID',
    r.blocking_session_id AS '被阻塞会话ID',
    r.wait_type AS '等待类型',
    r.wait_time / 1000 AS '等待时间(秒)',
    r.cpu_time / 1000 AS 'CPU时间(秒)',
    r.reads AS '读取次数',
    r.writes AS '写入次数',
    -- 获取阻塞会话执行的SQL
    blocking_sql.text AS '阻塞SQL',
    -- 获取被阻塞会话执行的SQL
    request_sql.text AS '被阻塞SQL'
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) request_sql
LEFT JOIN sys.dm_exec_requests blocking_r ON r.blocking_session_id = blocking_r.session_id
OUTER APPLY sys.dm_exec_sql_text(blocking_r.sql_handle) blocking_sql
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

最佳实践：

• 使用 sys.dm_exec_sessions 关联获取登录名和主机信息
• 结合 sys.dm_tran_locks 查看锁详情
• 对于长时间运行的查询，考虑使用 KILL 命令终止

索引相关核心 DMV

sys.dm_db_index_usage_stats

用途：分析索引使用情况，识别未使用或低效索引

版本支持：SQL Server 2005 及以上

关键列说明：

• object_id：表 ID
• index_id：索引 ID
• user_seeks/user_scans/user_lookups：用户查询使用索引的方式
• user_updates：更新操作导致的索引维护成本

生产场景示例：

-- 识别未使用的索引（过去30天）
SELECT
    OBJECT_NAME(i.object_id) AS '表名',
    i.name AS '索引名',
    i.type_desc AS '索引类型',
    -- 计算索引使用次数
    COALESCE(ius.user_seeks + ius.user_scans + ius.user_lookups, 0) AS '总使用次数',
    -- 索引维护成本
    COALESCE(ius.user_updates, 0) AS '更新次数',
    -- 最后使用时间
    COALESCE(MAX(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup), '1900-01-01') AS '最后使用时间'
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE i.type > 0 -- 排除堆
-- 仅显示未使用或很少使用的索引
AND (COALESCE(ius.user_seeks + ius.user_scans + ius.user_lookups, 0) < 10
     OR ius.last_user_seek IS NULL)
ORDER BY COALESCE(ius.user_seeks + ius.user_scans + ius.user_lookups, 0) ASC;

最佳实践：

• 结合 sys.dm_db_index_physical_stats 分析索引碎片
• 对于更新频繁但很少使用的索引，考虑删除
• 定期（如每月）运行此查询，清理低效索引

sys.dm_db_missing_index_details

用途：查看 SQL Server 建议的缺失索引，优化查询性能

版本支持：SQL Server 2008 及以上

关键列说明：

• object_id：表 ID
• equality_columns：等值条件列（如 column1 = @value）
• inequality_columns：非等值条件列（如 column2 > @value）
• included_columns：建议包含的列，避免书签查找

生产场景示例：

-- 获取缺失索引建议及预计收益
SELECT
    OBJECT_NAME(mid.object_id) AS '表名',
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    -- 计算预计收益（总影响）
    migs.avg_user_impact * migs.user_seeks AS '预计总收益',
    migs.avg_user_impact AS '平均性能提升(%)',
    migs.user_seeks + migs.user_scans AS '受影响查询次数'
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;

最佳实践：

• 关注 avg_user_impact 高（>50%）且查询次数多的建议
• 避免创建过多索引，考虑现有索引的覆盖范围
• 测试索引创建后的实际性能提升

事务与锁核心 DMV

sys.dm_tran_locks

用途：监控当前锁信息，诊断锁争用问题

版本支持：SQL Server 2005 及以上

关键列说明：

• resource_type：锁定的资源类型（如 OBJECT、PAGE、KEY）
• resource_associated_entity_id：资源关联的对象 ID
• request_mode：请求的锁模式（如 S 共享锁、X 排他锁）
• request_status：锁状态（GRANT 已授予、WAIT 等待中）
• request_session_id：请求锁的会话 ID

生产场景示例：

-- 查看当前锁争用情况
SELECT
    resource_type,
    OBJECT_NAME(resource_associated_entity_id) AS '表名',
    request_mode,
    request_status,
    request_session_id AS '会话ID',
    -- 获取锁等待时间
    wt.wait_duration_ms / 1000 AS '等待时间(秒)',
    -- 获取会话执行的SQL
    st.text AS '执行SQL'
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_requests r ON tl.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE tl.resource_database_id = DB_ID()
ORDER BY wt.wait_duration_ms DESC;

最佳实践：

• 关注 request_status = 'WAIT' 的锁请求
• 结合 sys.dm_exec_requests 查看导致锁争用的 SQL
• 考虑调整事务隔离级别或优化查询逻辑

DMV 使用最佳实践

性能影响最小化

• 避免在生产环境执行过于复杂的 DMV 查询，尤其是涉及大量数据的查询
• 使用 TOP 限制返回行数
• 合理设置监控采样间隔，避免频繁查询
• 考虑使用专用监控服务器收集 DMV 数据

数据准确性

• 注意 DMV 数据在 SQL Server 重启后会重置
• 建立基线数据，便于识别异常变化
• 结合多个 DMV 进行综合分析，避免单一指标误判
• 考虑使用 Query Store 作为长期性能数据存储

权限管理

• 遵循最小权限原则，只为必要人员授予 DMV 访问权限
• 考虑创建专用监控账户，仅授予 VIEW SERVER STATE 权限
• 定期审查 DMV 访问权限

自动化监控

• 结合 PowerShell 或其他脚本工具，定期收集 DMV 数据
• 设置告警阈值，当关键指标异常时自动通知
• 建立历史数据仓库，用于趋势分析和容量规划

版本差异

DMV 名称	版本支持	主要差异
sys.dm_db_missing_index_details	SQL Server 2008+	2008 引入，2016+ 增强了索引建议算法
sys.dm_exec_query_stats	SQL Server 2005+	2016+ 增加了 query_hash 和 query_plan_hash 列
sys.dm_db_stats_properties	SQL Server 2008R2+	2008R2 引入，用于查看统计信息属性
sys.dm_db_index_physical_stats	SQL Server 2005+	2012+ 增强了分区索引的统计信息
sys.dm_sql_referencing_entities	SQL Server 2008+	用于查看对象依赖关系
sys.dm_tran_active_snapshot_database_transactions	SQL Server 2005+	用于监控快照隔离级别的事务

常见问题 (FAQ)

如何区分正常等待和异常等待？

答：

• 建立基线：在系统正常运行时收集等待统计信息，了解正常等待模式
• 关注比例：等待时间占比超过 10% 的等待类型需要重点关注
• 结合业务：等待模式的突然变化通常与业务变化或性能问题相关
• 参考阈值：例如 CXPACKET 等待时间超过总等待时间的 20% 可能表示并行设置不合理

DMV 数据在重启后丢失怎么办？

答：

• 定期快照：使用作业定期收集关键 DMV 数据到历史表
• 使用 Query Store：SQL Server 2016+ 可以使用 Query Store 长期存储查询性能数据
• 结合第三方工具：使用专业监控工具（如 SolarWinds、Redgate SQL Monitor）持续收集数据

如何使用 DMV 诊断死锁问题？

答：

• 使用 sys.dm_tran_locks 查看当前锁信息
• 结合 sys.dm_os_waiting_tasks 查看等待关系
• 启用死锁跟踪：

  -- 启用 XML 死锁图跟踪
  ALTER EVENT SESSION system_health ON SERVER 
  STATE = START;
  -- 查看死锁信息
  SELECT 
      XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
  FROM (
      SELECT 
          XEvent.query('.') AS XEvent
      FROM (
          SELECT 
              CAST(target_data AS XML) AS TargetData
          FROM sys.dm_xe_session_targets st
          JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
          WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
      ) AS Data
      CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
  ) AS DeadlockReports
  ORDER BY DeadlockGraph DESC;

如何使用 DMV 监控 TempDB 性能？

答：

• 查看 TempDB 文件使用情况：

  SELECT 
      name AS '文件名称',
      size * 8 / 1024 AS '大小 (MB)',
      FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS '已使用 (MB)',
      (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS '可用 (MB)'
  FROM tempdb.sys.database_files;

• 监控 TempDB 等待：

  SELECT 
      wait_type,
      wait_time_ms,
      waiting_tasks_count
  FROM sys.dm_os_wait_stats
  WHERE wait_type LIKE '%TEMPDB%'
  ORDER BY wait_time_ms DESC;

如何使用 DMV 查看数据库连接情况？

答：

-- 查看当前连接数和状态
SELECT
    login_name,
    host_name,
    program_name,
    COUNT(*) AS '连接数',
    SUM(cpu_time) / 1000 AS '总CPU时间(秒)',
    SUM(reads) AS '总读取次数',
    SUM(writes) AS '总写入次数'
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 -- 仅用户进程
GROUP BY login_name, host_name, program_name
ORDER BY COUNT(*) DESC;

总结

动态管理视图是 DBA 监控和优化 SQL Server 的强大工具。通过熟练掌握核心 DMV 的使用，可以快速定位性能瓶颈、诊断故障、优化查询和规划容量。

在实际生产环境中，建议：

1. 建立基线数据，了解系统正常运行状态
2. 结合多个 DMV 进行综合分析
3. 定期自动化收集 DMV 数据，用于趋势分析
4. 关注版本差异，合理使用不同版本的 DMV 功能
5. 测试索引创建等优化操作的实际效果

通过持续学习和实践，不断提高 DMV 使用技巧，将帮助 DBA 更高效地管理和优化 SQL Server 环境。