了解SQL Server 2005五个有用的动态管理对象

发布时间：2015-11-24 00:00 来源：未知

SQL Server 2005的新功能为动态管理对象，它们是在指定时间返回某个数据库实例的特殊状态信息的数据库视图或函数。这些对象允许数据库管理员或开发者对数据库服务器进行高效监控。动态管理对象取代了SQL Server以前版本中的许多系统表格，但也为数据库管理员提供更多观测手段。

服务器上需要监控的内容，几乎都有对应的动态管理对象。既然有这么多动态管理对象，在该文中逐一进行讨论是不实际的。例如，共有12类动态管理对象，但这里我主要讨论我认为相当重要的五个对象，它们分别来自与SQL Server操作系统、目录和执行有关的类中。

这些对象被分为两类：数据库级别和服务器级别。SQL Server 2005的安全构架有些不同，所以你必须采取一些措施保证你能够使用本文讨论的视图和函数。你必须保证使用这些例子的用户能够查看对象的SERVER STATE和DATABASE STATE。可以应用GRANT语句来完成这一点。

在我开始介绍动态管理对象之前，你应该建立一个可用在所有例子里面的表格。列表A是建立这个表格的代码。

列表A

IF OBJECT_ID('DMVTest') IS NOT NULL
DROP TABLE DMVTest
GO
CREATE TABLE DMVTest
(
C1 INT IDENTITY PRIMARY KEY CLUSTERED, 
C2 INT, 
C3 CHAR(5)
) 

SQL Server操作系统相关动态管理对象

sys.dm_os_performance_counters：这个视图返回的信息列举与SQL Server直接相关的性能统计。返回的信息代表通过Windows性能监控器显示的外部性能计数器。在SQL Server 2000中，这些数据以一个叫做sysperfinfo的系统表格来表示，SQL Server 2005中也有这个表格，但仅用于向后兼容。将来的版本可能会删除这个表格。

要了解如何应用这个视图，而不是运行Windows性能监控器来获取系统信息，请运行列表B中的查询代码。这个查询将返回服务器当前时刻的缓存命中率（Buffer Cache Hit Ratio）。缓存命中率指在内存中所发现的SQL Server请求页面的百分比。假如服务器运转正常，这个值一般在90%以上。假如这个值低于90%，则意味着服务器将会去硬盘那里恢复数据页面，这可能是服务器需要更多内存的信号。

列表B

SELECT
(CAST(SUM(CASE LTRIM(RTRIM(counter_name)) 
WHEN 'Buffer cache hit ratio' 
THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /
CAST(SUM(CASE LTRIM(RTRIM(counter_name)) 
WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER)
ELSE NULL END) AS FLOAT)) * 100
AS BufferCacheHitRatio
FROM 
sys.dm_os_performance_counters 
WHERE 
LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND 
[counter_name] LIKE 'Buffer Cache Hit Ratio%' 

假如你为了同样的目的，准备运行上面的查询，并将得到的结果与运行Windows性能监控器显示的结果进行比较（SQL Server：缓冲器管理器缓存命中率），你会发现这些数据几乎是一样的。稍稍研究一下，就可以根据这个表格开发出你自己的脚本库，而不必总是运行Windows性能监控器。

Caveat：这个视图仅限于SQL Server相关的计数器使用。假如你希望获得SQL Server之外的数据，你还是要使用Windows性能监控器。

目录相关动态管理视图和函数

sys.dm_db_index_physical_stats：这个动态管理函数为数据表格和视图返回相关数据及目录信息。

查看列表C。这个函数接受5个参数：数据库的ID、所讨论的表格或视图的ID、表格或视图上的一个特定目录的ID（被我忽略）、分区号（也被我忽略）和一个模式，我将它指定为“DETAILED”。将这些参数提交给这个函数会允许你查看DMVTest表格中的所有目录细节。

在过去不久的文章里定义的表格中运行列表C中的查询，你会发现表格中既没有页面也没有记录。在DMVTest中增加一条记录，再运行查询，就会发现表格中增加了一个页面。

INSERT INTO DMVTest(C2, C3)

VALUES(1,1)

列表C

SELECT 
OBJECT_NAME([object_id]),* 
FROM
sys.dm_db_index_physical_stats (DB_ID(), 
OBJECT_ID('DMVTest'),NULL,NULL, 'DETAILED') 

这个函数返回的另一个特别有趣的域为avg_page_space_used_in_percent域，它说明数据页面有多大。从查询中发现数据页面约为0.25%。你可以应用这个数据来开发程序，查看数据库表格，并根据程序中设定的标准，重新为它们编写目录。

执行相关的动态管理视图和函数

sys.dm_exec_requests：这个视图显示发生在SQL Server实例中的每个请求的有关信息。当你研究服务器阻塞时，由此视图提供的信息特别有用。读、写、会话设置和blocking_session_id是这个视图返回的一些有用数据。blocking_session_id栏指出系统上阻塞数据库请求的会话。稍后，我将举出一个例子，说明如何确定用户运行的阻塞请求的语句。

sys.dm_exec_sessions：SQL Server上每个得到验证的会话由它返回一行。在你希望找出连接到数据库服务器的所有用户时，可用到这个视图。它返回session_id、连接通过它建立会话执行的读和写的程序、以及其它一些会话设置。这个视图还包含一个叫做is_user_process的BIT域，在查询这个表格时你可能会用到它。数值1表示会话为用户会话而非系统定义的会话。

sys.dm_exec_sql_text：这是一个根据sql操作返回SQL Server语句的动态管理函数。对某些动态管理函数而言，SQL Server 2005将正在执行的sql语句以散列值存储。这个散列值可由sys.dm_exec_sql_text动态管理函数解析。但是，任何时候你希望将一个函数与另一个对象连接起来，并从表格或视图中提交一个域值时，就会需要用到新的APPLY运算符。SQL Server 2005以前的版本并不具备这一功能。（欲了解SQL Server 2005中APPLY运算符的详细信息，请参阅该文。）

现在我们讨论一下如何应用这三个动态管理函数。列表D中的查询列举出数据库中所有当前用户请求执行的会话、阻塞会话、程序、主机和SQL Server语句。

列表D

SELECT 
r.session_id, 
r.blocking_session_id, 
s.program_name, 
s.host_name,
t.text
FROM
sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
s.is_user_process = 1 

注意我正在使用dm_exec_sql_text函数，并从sys.dm_exec_requests视图中提交sql_handle域。由sys.dm_exec_requests和sys.dm_exec_sessions视图返回的每一行，都将调用sys.dm_exec_sql_text函数。

测试

对它们进行测试是学习这些新型动态管理对象的最佳方法。SQL Server在线书籍提供了使用这些对象的一些资料，但动手使用要比阅读学到更多内容。投入一点努力，你就会发现许多应用对象的新方法，不仅包括这个例子中列出的对象，还包括SQL Server 2005中的动态管理对象。