微软SQL Server有几款内置工具可以帮助DBA更好地管理SQL Server环境。在SQL Server 2005中，微软公司引入了SQL 动态管理视图(简称DMV)，还引入了动态管理函数(简称DMF)。它们提供了大量有关服务器和数据库状态的信息，你可以利用这些信息监控SQL Server实例的健康状态，进行性能诊断和调优。

有两种类型的DMV和DMF：服务器范围和数据库范围。要查询服务器范围的SQL DMV或者DMF，用户需要“VIEW SERVER STATE”权限。要查看数据库范围的DMV和DMF需要“VIEW DATABASE STATE”权限。这些DMV和DMF存在于sys schema中，遵从“dm_*”的命名规则。此外，所有的DMV和DMF都按其用途进行分组：在SQL Server 2005中，有12个初始分组;在SQL Server 2008中有15个分组;在SQL Server 2012中现在有20个分组。这些分组中有一些是与某些SQL Server函数绑定的，比如AlwaysOn可用性、数据库镜像和安全。

SQL DMV和DMF的常见应用场景

本文不会在这里把所有的DMV和DMF都过一遍，我会展示一些可能需要使用的常见情况。要了解所有DMV和DMF的细节，请参考微软公司提供的文档资源。

提取连接信息。我们可以使用“sys.dm_exec_connections”查看当前与SQL Server建立的连接。它帮助你查找哪些进程目前正在连接到该实例。下面是“sys.dm_exec_connections”常用的列信息：“session_id, most_recent_session_id, connection_time, client_net_address, last_read, last_write, auth_scheme, and most_recent_sql_handle.”。例如，下面的查询语句可以查询连接到SQL Server上每个会话最近执行的SQL文本：

SELECT  ec.[session_id]
      ,ec.[connect_time]
      ,ec.[client_net_address]
      ,ec.[last_read]
      ,ec.[last_write]
      ,ec.[auth_scheme]
      ,qt.[text]
FROM [sys].[dm_exec_connections] ec
CROSS APPLY [sys].[dm_exec_sql_text](ec.[most_recent_sql_handle]) AS qt

下图展示了该查询执行后的输出结果：

提取当前执行的查询和阻塞信息。该功能常用来查找在任何给定时间在SQL Server上当时有哪些请求正在执行。要实现该功能，你可以使用“sys.dm_exec_requests”动态管理视图。这个SQL DMV包括该查询和查询计划有关的详细信息，还包括请求状态和关于查询执行时间的信息。下面是最常用到的列：

• blocking_session_id：阻塞会话的服务配置识别符
• wait_type：等待类型
• wait_time：等待时间
• last_wait_type:：如果等待已经结束，该列会显示其类型。
• wait_resource：请求等待的资源名称
• lock_timeout：在超时之前锁等待的时长

DMV是阻塞故障排除方法的理想选择，请看下面示例：

SELECT  [session_id]
       ,[blocking_session_id]
       ,[status]
       ,[wait_time]
       ,[wait_type]
       ,[wait_resource]
       ,[transaction_id]
       ,[lock_timeout]
FROM [sys].[dm_exec_requests]
WHERE [blocking_session_id] <> 0

下图展示了阻塞会话查询结果样例：

我们还可以使用“sys.dm_os_waiting_tasks”DMV获取关于当前等待资源任务的信息。阻塞进程会显示在“session_id”列。阻塞显示在“blocking_session_id”列。例如，使用下面查询语句可以看到那些阻塞进程在等待那些资源：

SELECT  [session_id]
       ,[blocking_session_id]
       ,[wait_type]
       ,[resource_description]
FROM [sys].[dm_os_waiting_tasks]
WHERE [blocking_session_id] <> 0

下图展示了阻塞会话查询结果：

如果你在数据库中遇到了锁的问题，那么你可以使用“sys.dm_trans_lock”DMV获取数据库中锁状态的信息。该DMV的“request_status”列有三种可能的值：“GRANT, WAIT 或者 CONVERT”。“CONVERT”值的意思是请求者已经被授权请求，但是正在等待升级到初始请求被授权。要定位信息，你可以执行下面的SQL：

SELECT  l.[request_session_id]
       ,DB_NAME(l.[resource_database_id]) AS [DatabaseName]
       ,OBJECT_NAME(p.[object_id]) AS [ObjectName]
       ,l.[resource_type]
       ,l.[request_status]
       ,l.[request_mode]
       ,l.[request_type]
       ,l.[request_owner_type]
FROM [sys].[dm_tran_locks] l
LEFT JOIN [sys].[partitions] p
      ON p.[hobt_id] = l.[resource_associated_entity_id]
WHERE [request_status] <> 'GRANT'

下图展示了该查询的执行结果：

你可以使用“sys. dm_os_wait_stats”查找到SQL Server实例中自上次重启或统计清理以来等待时间最长的记录。你还可以使用这个DMV计算信号等待，通常信号等待超过20%都是CPU压力的表现。

SELECT CAST(100.0 * SUM([signal_wait_time_ms])
      / SUM([wait_time_ms]) AS [numeric](20, 2)) AS [SignalWaitsPercent]
FROM [sys].[dm_os_wait_stats]

查询SQL Server配置信息。你可以使用“sys.dm_os_sys_info”DMV获取SQL Server的基本硬件信息，请看下面示例：

SELECT  [cpu_count] / [hyperthread_ratio] AS [PhysicalCPU]
       ,[cpu_count] AS [LogicalCPU]
       ,[hyperthread_ratio] AS [HyperThreadRatio]
       ,[physical_memory_kb] / 1024 AS [PhysicalMemoryInMB]
       ,[virtual_memory_kb] / 1024 AS [VirtualMemoryInMB]
       ,[affinity_type_desc]
       ,[sqlserver_start_time]
FROM [sys].[dm_os_sys_info]

要了解SQL Server实例可用的内存数量或者页文件大小，我们可以使用“sys.dm_os_sys_memory”DMV。这个SQL DMV帮助评估SQL Server内存用量。

SELECT [total_physical_memory_kb] / 1024 AS [PhysicalMemoryInMB]
      ,[available_page_file_kb] / 1024 AS [AvailablePhysicalMemoryInMB]
      ,[total_page_file_kb] / 1024 AS [TotalPageFileInMB]
      ,[available_page_file_kb] / 1024 AS [AvailablePageFileMB]
      ,[kernel_paged_pool_kb] / 1024 AS [KernelPagedPoolMB]
      ,[kernel_nonpaged_pool_kb] / 1024 AS [KernelNonpagedPoolMB]
      ,[system_memory_state_desc] AS [SystemMemoryStateDesc]
FROM [master].[sys].[dm_os_sys_memory]

同样，你可以使用下面的DMV提取关于SQL Server服务和错误日志配置的信息，它们分别是：

SELECT * FROM [sys].[dm_server_services]

SELECT * FROM [sys].[dm_os_server_diagnostics_log_configurations]

掌握这些信息对于查错很有用。

获取SQL Server Windows集群信息。要获取关于SQL Server Windows集群及其节点的配置信息，你可以分别使用“sys.dm_os_cluster_properties”和“sys.dm_os_cluster_nodes”。这两个DMV提供的信息在你安装Windows或者SQL Server更新时很有用。

索引相关的SQL DMV和DMF

微软SQL Server还提供给DBA和开发者们几个索引相关的动态管理视图和函数，它们可以用来维护、修改、优化和识别缺失的索引。你可能用到的一些动态管理视图和函数如下：

• sys.dm_db_index_operational_stats：返回关于低级别IO操作、锁和锁争夺、访问方法的信息。
• sys.dm_db_index_physical_stats：返回关于表盒索引大小以及碎片的信息，包括行内碎片和LOB数据碎片。
• sys.dm_db_index_usage_stats：返回索引操作计数，识别执行的操作以及每个操作的最后执行时间。
• sys.dm_db_missing_index_columns：返回索引使用查询优化器的信息，如果它们还可用，则包括该列如何被查询优化器使用的信息。
• sys.dm_db_missing_index_details：返回关于缺失索引的详细信息，包括表、列的等于运算、不等于运算以及列的包含运算。
• sys.dm_db_missing_index_group_stats：返回关于缺失索引的组信息，SQL Server更新需要的每个查询执行(不是基于查询编译或重编译)。
• sys.dm_db_missing_index_groups：返回关于缺失索引组包含缺失索引的信息。