65.9K
CodeProject 正在变化。 阅读更多。
Home

动态管理视图 [DMV] – SQL Server 2005 功能

Hari Prasad K
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.94/5 (26投票s)

2006 年 12 月 21 日

CPOL

6分钟阅读

viewsIcon

215034

DMV 是 SQL Server 2005 中新引入的功能，它向数据库管理员提供有关 SQL Server 机器当前状态的信息。

引言

DMV 是 SQL Server 2005 中新引入的功能，它向数据库管理员提供有关 SQL Server 机器当前状态的信息。这些值将帮助管理员诊断问题并优化服务器以获得最佳性能。DMV 旨在替代 SQL Server 2000 中提供的系统表和各种其他功能。在本文中，我将只解释常用的 DMV。

两种类型的动态管理视图

  1. 服务器范围的 DMV：存储在 Master 数据库中
  2. 数据库范围的 DMV：特定于每个数据库

执行 DMV 的权限 [安全]

要查询服务器范围的 DMV，数据库用户必须对 VIEW SERVER STATE 具有 SELECT 权限；对于数据库范围的 DMV，用户必须对 VIEW DATABASE STATE 具有 SELECT 权限。

  • GRANT VIEW SERVER STATE to <Login>
  • GRANT VIEW DATABASE STATE to <User>

如果您想拒绝用户查询某些 DMV 的权限，您可以使用 DENY 命令限制对特定 DMV 的访问。

入门

所有 DMV 都存在于 SYS 架构中，它们的名称以 DM_ 开头。因此，当您需要查询 DMV 时，您应该在视图名称前加上 SYS。例如，如果您需要查看 SQL Server 机器的总物理内存；那么执行下面的 TSQL 命令

SELECT 
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb 
FROM 
sys.dm_os_sys_info

在本文中，我将解释一些可以经常使用的 DMV，以了解 SQL Server 的当前行为

  1. SQL Server 相关 [硬件资源] DMV
  2. 数据库相关 DMV
  3. 索引相关 DMV
  4. 执行相关 DMV

1. SQL Server 相关 DMV

本节详细介绍与 SQL Server 系统相关的 DMV。SQL DMV 负责管理特定于 SQL Server 实例的服务器级别资源。

本节涵盖与操作系统、磁盘和内存相关的 DMV。

a. sys.dm_os_sys_info

此视图返回有关 SQL Server 机器、可用资源和资源消耗的信息。

此视图返回以下信息

  1. CPU 计数：服务器中的逻辑 CPU 数量
  2. 超线程比：逻辑 CPU 和物理 CPU 的比率
  3. Physical_memory_in_bytes：可用物理内存量
  4. Virtual_memory_in_bytes：可用虚拟内存量
  5. Bpool_commited：缓冲池中已提交的物理内存
  6. OS_Priority_class：SQL Server 进程的优先级类别
  7. Max_workers_thread：可以创建的最大工作线程数

b. sys.dm_os_hosts

此视图返回在 SQL Server 2005 中注册的所有主机。此视图还提供每个主机使用的资源。

  1. Name：注册的主机名称
  2. Type：托管组件的类型 [SQL Native Interface/OLE DB/MSDART]
  3. Active_tasks_count：主机放置的活动任务数
  4. Active_ios_count：主机等待的 I/O 请求数

c. sys.dm_os_schedulers

Sys.dm_os_schedulers 视图将帮助您识别 SQL Server 机器中是否存在任何 CPU 瓶颈。可运行任务的数量通常是非零值；非零值表示任务必须等待它们的时间片才能运行。如果可运行任务计数显示高值，则表明存在 CPU 瓶颈。

SELECT 
scheduler_id,current_tasks_count,runnable_tasks_count 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255

上述查询将列出 SQL Server 机器中所有可用的调度程序以及每个调度程序的可运行任务数。

d. sys.dm_io_pending_io_requests

此动态视图将返回 SQL Server 端挂起的 I/O 请求。它为您提供以下信息

  1. Io_type：挂起的 I/O 请求类型
  2. Io_pending：指示 I/O 请求是挂起还是已由 Windows 完成
  3. Scheduler_address：发出此 I/O 请求的调度程序

e. sys.dm_io_virtual_file_stats

此视图返回数据和日志文件 [MDF 和 LDF 文件] 的 I/O 统计信息。此视图是常用视图之一，将帮助您识别 I/O 文件级别。它将返回以下信息

  1. Sample_ms：自 SQL Server 实例启动以来的毫秒数
  2. Num_of_reads：文件上发出的读取次数
  3. Num_of_bytes_read：此文件上读取的总字节数
  4. Io_stall_read_ms：用户等待文件上发出的读取的总时间（以毫秒为单位）
  5. Num_of_writes：此文件上的写入次数
  6. Num_of_bytes_written：写入文件的总字节数
  7. Io_stall_write_ms：用户等待文件上写入完成的总时间（以毫秒为单位）
  8. Io_stall：用户等待 I/O 完成的总时间（以毫秒为单位）
  9. Size_on_disk_bytes：此文件在磁盘上使用的字节数

f. sys.dm_os_memory_clerks

此 DMV 将帮助了解 SQL Server 通过 AWE 分配了多少内存。

SELECT 
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
FROM sys.dm_os_memory_clerks

可以使用相同的 DMV 获取 SQL Server 2005 内部组件的内存消耗。

SELECT TOP 10 type, 
SUM(single_pages_kb) as [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY SUM(single_pages_kb) DESC

g. sys.dm_os_ring_buffers

此 DMV 使用 RING_BUFFER_RESOURCE_MONITOR 并提供来自资源监视器通知的信息，以识别内存状态变化。在内部，SQL Server 有一个框架来监视不同的内存压力。当内存状态改变时，资源监视器任务会生成一个通知。此通知由组件内部使用，以根据内存状态调整其内存使用情况。

SELECT 
Record FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

上述查询的输出将是 XML 格式。输出将帮助您检测任何低内存通知。

RING_BUFFER_OOM：环形缓冲区 OOM 包含指示服务器内存不足情况的记录。

SELECT
record FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = 'RING_BUFFER_OOM'

2. 数据库相关 DMV

本节详细介绍与 SQL Server 数据库相关的 DMV。这些 DMV 将帮助识别数据库空间使用情况、分区使用情况、会话信息使用情况等...

a. sys.dm_db_file_space_usage

此 DMV 提供 TEMPDB 数据库的空间使用信息。

b. sys.dm_db_session_space_usage

此 DMV 提供每个会话为数据库分配和释放的页数

c. sys.dm_db_partition_stats

此 DMV 为当前数据库中的每个分区提供页和行计数信息。

下面的查询显示 MSDB 数据库中所有索引和堆的所有分区的计数

USE MSDB;
GO
SELECT * FROM sys.dm_db_partition_stats;

以下查询显示备份集表及其索引的所有分区的计数

USE MSDB
GO
SELECT * FROM sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('backupset');

d. sys.dm_os_performance_counters

返回服务器维护的 SQL Server / 数据库相关计数器。

下面的示例查询使用 dm_os_performance_counters DMV 以 KB 为单位获取所有数据库的日志文件使用情况。

SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Log File(s) Used Size (KB)'

3. 索引相关 DMV

本节详细介绍与 SQL Server 数据库相关的 DMV。这些 DMV 将帮助识别数据库空间使用情况、分区使用情况、会话信息使用情况等。

a. sys.dm_db_index_usage_stats

此 DMV 用于获取所有数据库中所有对象的索引使用情况的有用信息。它还显示每个索引的查找和扫描量。

SELECT object_id, index_id, user_seeks, user_scans, user_lookups 
FROM sys.dm_db_index_usage_stats 
ORDER BY object_id, index_id

可以使用以下查询识别数据库中迄今未使用的所有索引

SELECT object_name(i.object_id), 
i.name, 
s.user_updates, 
s.user_seeks, 
s.user_scans, 
s.user_lookups
from sys.indexes i 
left join sys.dm_db_index_usage_stats s 
on s.object_id = i.object_id and 
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0 
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)

将 Database_id 替换为您正在查看的数据库。

4. 执行相关 DMV

执行相关的 DMV 将提供有关会话、连接以及进入 SQL Server 的各种请求的信息。

a. sys.dm_exec_sessions

此 DMV 将提供有关连接到 SQL Server 的每个会话的信息。此 DMV 类似于运行 sp_who2 或查询 Master..sysprocesses 表。

SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 – All user Sessions

b. sys.dm_exec_connections

此 DMV 显示所有连接到 SQL Server 的连接。下面的查询使用 sys.dm_exec_connections DMV 获取连接信息。此视图为每个用户连接返回一行 (Sessionid >= 51)。

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

c. sys.dm_exec_requests

此 DMV 将详细说明每个连接在 SQL Server 中实际执行的操作。

SELECT
session_id,status,
command,sql_handle,database_id
FROM sys.dm_exec_requests
WHERE session_id >= 51

d. sys.dm_exec_sql_text

此动态管理函数返回给定 SQL 句柄的 SQL 语句文本。

SELECT 
st.text
FROM
sys.dm_exec_requests r 
CROSS APPLY 
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51

结论

SQL Server 2005 中的动态管理视图 (DMV) 和动态管理函数 (DMF) 提供了一个透明的视图，可以看到 SQL Server 各个区域内部正在发生什么。通过使用它们，我们将能够以更有效的方式查询系统以获取其当前状态的信息，并更快地提供解决方案。DMV 可用于性能调优以及服务器和查询的故障排除。本文概述了它们是什么以及我们如何使用它们。

© . All rights reserved.