1 0 .2 用于监视的工具和技术

第 3 章从功能的角度介绍了很多可用的工具，而本章将从实现的角度介绍这些工具，
并讨论如何使用它们来实际执行一些关键的数据库监视任务。本章还涉及了一些第3 章中
没有介绍的工具，因为它们杂乱地结合到了 SQL Server Management Studio中。

1 0 .2 .1 日志文件查看器
日志文件査看器(Log Hie Viewer)是一个很出色的工具，可以用它在一个一次性关联的 视图中査看SQL Server和操作系统日志。例如，来自于系统日志的内存子系统错误可以和 SQL Server错误关联在一起，指示内存不足的情况，并允许将问题与SQL Server隔离开来。 要打开日志文件査看器，可 在 SQL Server Management Studio中 展 开 “管理”文件夹，然 后 展 开 “SQL Server日志”文件夹，右击想査看的日志，然 后 选 择 “查 看 SQL Server日志” 命令。在日志文件查看器打开之后，可以选择打开其他SQL Server 0 志和/或操作系统曰志, 方法是展幵并选择想查看的日志(如图10-2所示)。可注意到，还可以打开SQL Server代理 和数据库邮件的日志文件。

每 次 重 启 SQL Server和 SQL Server代理服务时，会关闭各自的日志文件并打开一个新 的日志。在一个生产系统中，这种情况可能不会经常发生，于是就会形成一个较大的日志
文件。要想避免出现过于庞大的日志文件，必须导出日志文件的内容，然后再循环使用该
文件。要想循环使用SQL Server日志，可 以 执 行 spjyclejrrodog存储过程。要想循环使 用代理日志，可 以 使 用 sp_CyCk_agen t_errOrk>g存储过程。这些过程可以清除日志的内容而 不要求重启服务。
可 以 右 击 “SQLServer日志”文件夹，然 后 选 择 “配置”命令来配置SQL Server保留 的日志数量，如 图 10-3所示。最小的(也是默认的)数量是6 , 但可以增加到99。日志数量
不能小于6。

10.2.2活动监视器
Microsoft在 SQL Server 2008的最后一个候选发布版中包含了一个完全革新的活动监 视器(Activity Monitor)。这 令 SQL Server社区感到意外，因为活动监视器已经不再位于其 原来的位置上，许多感到担优的DBA在支持论坛上发布了大量的相关帖子。显然，这也
让 Microsoft联机丛书团队感到意外，因为在编写本书时，他们仍在引用原来的活动监视器 的文档。
SQL Server 2008中的活动监视器现在是一个功能丰富的、接近实时的图形化性能面
板。新的活动监视器的外观与Windows Vista和 Windows Server 2008系统中的可靠性和性
能监视器类似。有经验的DBA将注意到的第一件事是活动监视器不再位于SQL Server Management的 “管理”节点下，而是在SQL Server实例的上下文菜单中。 活动监视器是一个可以帮助对服务器的整体运行状况和性能有更深入理解的优秀工
具。与前面的版本相比，它不再只显示简单的进程和锁定信息。它现在显示直观的图形、
详细的进程和锁定信息、文件I/O统计信息和长时间运行的査询的信息。另外，现在可以
对所有的网格视图进行排序和筛选。活动监视器不能取代有经验的DBA所掌握的一组优
秀的数据管理视图，但它可以很好地回答“为什么服务器运行如此缓慢”这个基本问题。
要运行活动监视器，需 要 具 有 view server state权限。要终止任何进程，还需要是
sysadmin或 processadmin服务器角色的成员。新的活动监视器可以在SQL Server 2005上工
作，但在之前的版本上不能运行。

活动监视器由5 个主要部分构成，分别是：概述、进程、资源等待、数据文件I/O和
磁近耗费大量资源的査询。
• 概述—— “概述”部分显示了 4 个表示关键性能度量指标的接近实时的图形。右
击图形将可以调整刷新率或暂停数据收集。
• 进程一 “进程”部分为每个与SQL Server的连接列出了一行，并有一些列来描 述该进程(如与该连接关联的用户、数据库t 下文、当前运行的命令以及所有的等 待状态和阻塞信息)。右击一个进程并选择“详细信息”命令将显示在该连接上最
后一个执行的命令，并提供了在必要时终止该进程的功能。右击一个进程所显示
的上下文菜单中有一个“SQL Server Profiler中的跟踪进程”的选项。图 10-4演示 了这一行为。另外在图10-4中可看到，进 程 59是挂起的，因为它在等待被进程
57锁定的资源，而后者又在等待被进程60锁定的资源。

资源等待—— “资源等待”部分妞示一个所有资源等待(CPU、Latch、Memory、
Buffer I/O等)的完整列表。这个列表并不提供任何钻取功能，但可以筛选和排序结果。 • 数据文件I/O—— “数据文件I/O” 部分显示了数据库文件的活动总计信息。可以
对这个列表进行筛选和排序。
• 最近耗费大量资源的查询—— 这是活动监视器中新添加的-个非常优秀的选项。
“最近耗费大量资源的査询”部分显示了所有最近、最耗费资源的査询，允许在
一个新的查询窗U中打开某个完整的査询语句或详细的执行计划。图 10-5显示了
这个部分以及一个耗费大量资源的査询的文菜单选项。

默认情况下，活动监视器将每10秒刷新一次显示结果。要将活动监视器配置为另外
一个刷新率，可右击任意图形，选择所需的刷新间隔或选择“暂停”命令禁用刷新。记住，
频繁刷新进程信息会导致SQL Server性能下降。
1 0 .2 .3 系 统 存 储 过 程
虽然就査看进程以及它们使用的资源而言，活动监视器是一个很好的图形工具，但通
常来说，系统存储过程的输出更简单，更适合用来识别当前进程以及任意资源竞争。
1. sp_who 和 sp_who2
sp_who2存储过程是一个未归档的系统过程，和已归档的同类存储过程sp_who相比， 它有显著的优势。它们都返回当前SQL Server进程的信息，但是sp_who2过程返回的信息更 加全面。
这些存储过程本质上等同于活动监视器的“进程”页。sp_who或 sp_who2的输出可以通 过指定一个进程ID作为输入参数加以限制。sp_who和 sp_who2过程的语法如下所示：
s p _ w h o [p ro c e s s _ _ I D ] , l o g i n n am e | (A CTIV E]
s p _ w h o 2 [ p r o c e s s _ I D ] I [ACTIV E]
sp who相储过程返回表10-3中描述的9 列。

sp_who2存储过程返回了 13列，但它返冋了 spid列两次，一次在结果集的左边， 次在结果集的右边，以使结果集的读取更加容易。这些列如表10-4所示。

当 把 Active选 项 添 加 到 sp_who或 sp_who2时 ，SQL Server不会返回任何状态为
Awaiting Command的会话，该状态指明会话正在等待一个用户进程的输入。
2. sp_lock
spJock存储过程返回活动数据库进程所持有的锁的数目和类型。已锁定的或将要被锁 定的对象会和锁定状态以及任何标识信息(例如对象的整型标识符)一起返冋，另外还会返
回 索 引 ID(如果有的话)。
3. SQL Server 锁定
要 解 读 spjock返回的信息，了解可锁定的资源类型和这些锁可以采用的模式是很重 要的。可能的资源类型如表10-5所示。

资源类型上的锁通过模式请求和授予。spjock存储过程返回标识锁模式的信息(例如锁 是一个共享锁还是排他锁)。表 10-6列出了最常见的模式。

4. KILL命令
虽然KILL命令不是一个存储过程，但它使数据库管理员可以终止一个违反规则的进程,
就像图10-4所示的“进程属性”对话框中的“终止进程”按钮一样。KILL命令的语法如

下所示:
KILL s p id
KJLL命令很有用，但使用时需要相当小心。虽然有时候有必要终止一个中断的进程,
但是在终止它之前搜集与它有关的尽可能多的信息还是很重要的。例如，终止一个已更新
了 1000行的事务会导致这1000行冋滚，这会产生一些不希望看到的结果，例如事务日志
填满或数据丢失。

试一 试系统存储过程
现在看系统存储过程返回了什么信息，以及如何使用它们隔离有问题的进程。
(1)打开一个查询窗口。输入并执行下列代码：
USE AdventureWorks2008; GO
BEGIN TRAN
UPDATE Person.Person SET LastName =*Gates'
WHERE BusinessEntitylD =1;
(2) 打开另一个査询窗口。输入并执行下列代码：
USE AdventureWorks2008; GO
SELECT * FROM Person.Person WHERE BusinessEntitylD =1;
在执行此语句时不会看到任何返冋的结果。因为在第一个查询释放其锁定之前，此语
句不会完成执行。
(3) 现在打开第三个査询窗口，执行下列命令运行sp_who系统存储过程：
EXEC spwho;
注意，其中一个进程显示它被另一个会话阻塞。如图10-6所示，SPID 59被 SPID 60阻塞。

 

(4) 执 行 sp_who2存储过程，但将结果集限制为阻塞会话的服务器进程ID(Server
Process ID, SPID)。在这里，spid 是 60。
EXEC sp_who2 60;
执 行 sp_who2存储过程所得到的更加全面的结果会返回非常有用的信息(例如造成阻 塞的程序和用户，以及会话执行负责锁争用的命令的时间)。
(5) 确定哪个对象在被两个进程竞争。执 行 sp lock存储过程。和 sp_who及 sp_who2 存储过程一样，可以通过传递合适的进程ID限制过程的结果。 _ _
(6) 输入并执行下列命令以显示有关被阻塞的SPID的信息。该 SPID在 sp_who2结果 的 BlkBy列中返回了一个值。在本例中这个值是5 9 ,但是您的SHD很有可能不一样：
EXEC sp_lock 59;
结果如图10-7所示。

在 图 10-7中可以注意到，已经请求和授予了一些锁，但聚集索引键010086470766(表
示 Person.Person表 中 BusinessEntitylD为 1 的联系人)上的共享锁处于WAIT状态。这是因
为 spid 60当前正在修改该行，并在该键上持有一个排他锁。
要终止阻塞进程，需要执行KJLL命令并指定合适的SPID,在这里是60:
KILL 60;
注意：
在终止进程时要小心。SPID 60是我的机器上的进程。您的结果可能和我的不一样!
10.2.4 使用 Profiler
第 3 章介绍了 Profiler的基本功能。本节将介绍如何搜集性能信息来隔离和纠正数据 库应用程序的问题。对所提供跟踪的指导原则可以结合到一个综合性跟踪中或者单独实施。
使用Profiler时另一个重要的考虑事项是开销。交互式运行Profiler会导致大量服务器 开销，以及较大的不确定因素。Profiler只是一个查看SQL跟踪的图形化界面。它是一个 很好的工具，但对于有着繁重事务负荷的大型数据库来说，可能需要使用sp_trace_setevent,
sp_trace_setfilter、sp_trace_setstatus和 sp」race_create存储过程，通过文件中搜集的跟踪数
据创建、配置和运行跟踪。然后可以使用Profiler直接通过搜集的文件查看这些数据，或 者可以将它们导入到一个数据库中以供分析。

 

试一试 使 用 Profiler分 析 死 锁
如前所述，使用性能监视器检测死锁很容易。但要找出死锁发生的原因则较为困难，
需要通过Profiler运行跟踪和检査收集的数据。
(1 )打开 SQL Server Management Studio,连接到驻留 AdventureWorks2008 数据库的服
务器。在连接后通过“工具”菜单启动SQL Server Profiler,创建一个基于“空白”模板的 新跟踪，如 图 10-8所示。.

 

 

(2 )在 “事件选择”选项卡上，选择Deadlock graph和 Lock:Deadlock Chain事件，如
图 10-9所示。注意，如果选择了 Deadlock graph事件，则会出现“事件提取设置”选项卡。

(3 )要限制返回给Profiler的数据，单击“列筛选器”按钮，然后选择DatabaseName。在 “不类似于”文本框中，输入MSDB以避免跟踪SQL代理和计划的监视活动。单击“确定”

按钮。
图 10-10显示了想要使用的配置。筛选数据库时要小心。您可能认为最佳筛选器应该
是通过数据库ID或数据库名称指定特定数据库的筛选器。但是，有很多Profiler事件没有 一个特定的数据库上下文，所以如果这样设置筛选器的话，它们将不会显示。因此，必须
告诉Profiler不要监视哪些数据库。deadlock graph就是这样一个事件。

(4)在 “事件提取设置”选项卡上，选中“分别保存死锁XML事件”复选框，然后输
入一个位置来保存文件(如图10-11所示)。选 中 “不同文件中的每个死锁XML批”单选按
钮，然后单击“运行”按钮。

(5 )在 SQL Server Management Studio中，打幵两个新的査询窗口。
⑹在第一个査询窗 口 中 (可 能 叫 做 SQLQueryl.sql),输入如下代码并执行:
—— Connection 1 USE AdventureWorks2008; GO
BEGIN TRAN
UPDATE Person.Address SET City = * Redmond * WHERE AddressID =1;

(7)在第二个查询窗口中输入如下代码并执行:
—— Connection 2 USE AdventureWorks2008; GO
BEGIN TRAN
UPDATE Person.Person SET LastName =*Gates'
WHERE BusinessEntitylD =1; UPDATE Person.Address SET AddressLinel = * 1 Microsoft Way* WHERE AddressID =1;
此更新将不会完成，因 为 Connection 1 中的事务在想要更新的Person.Person表的行上 有一个排他锁。此时发生的是一个阻塞锁。Connection2中的事务想要更新被Connection 1 阻塞的行。阻塞锁是允许的，而且除非设置了锁定超时值、阻塞的事务完成或者管理员终
止了阻塞的事务，否则此锁就会一直存在。
(8 )在第一个连接上，编写并执行如下代码以更新Person.Person表：
--Connection 1
UPDATE P erson.P erson
SET FirstName = *Bill'
WHERE BusinessEntitylD =1;
此更新会导致一个死锁发生，因为两个连接在对立事务完成所需的资源上都持有排他锁。
死锁会被检测到，其中一个死锁的进程将被终止。留下的进程将成功运行。
(9 )返回到Profiler,停止跟踪，然后选择Deadlock graph事件类行。死锁图形显示了
被死锁的服务器进程ID和已锁定资源。将鼠标指针悬停在一个进程上面会显示参与死锁的
进程，如 图 10-12所示。

提示：
要将Person.Person表还原至初始状态，需要在没有被死锁终止的事务上执行ROLLBACK 语句。
(10)要捕捉用来运行这个跟踪的脚本，单击SQL Profiler中 的 “文件”菜单，然后选 择 “导出” | “编写跟踪定义的脚本”丨“用于 SQL Server 2005-2008” 选项，如 图 10-13所 示。这时 将 出 现 -个 “另存为”对话框。将该脚本保存为DeadLockTrace.SQL。

(11)打开使用 SQL Server Management Studio 保存的 DeadLockTrace.SQL 文件。SQL
Server运行此脚本来创建刚才执行的跟踪。保存这个脚本之后，可以在任意时间运行它， 而不需要启动和运行Profiler。要知道有关每个存储过程的更多信息，请 参 阅 SQL Server 联机丛书，里面有相当详细的讨论。
在捕捉到跟踪文件后，可以使用SQL Profiler打开它，或者如果跟踪较大，可以把它
插入表，以便使用传统的T-SQL査询分析。要把数据移动至表中，可以使用fh_trace_gettable
表值函数。此表值函数需要两个值：需要导入的跟踪文件的名称和需要搜集的滚动更新文
件的最大值。默认的文件数目是为跟踪设置的最人文件数。下面的例子显示了如何将之前
搜集的跟踪添加到AdventureWorks2008数 据 库 中 的 个 叫 做 DeadLockTraceTable的表里：
USE AdventureWorks2008; GO
SELECT * INTO DeadLockTraceTable FROM fn_trace_gettable(C:\ProfilerTraces\DeadLocks.trc1, NULL);
使用Profiler检测和分析长吋间运行的查询 Profiler是一个很好的工具，可以用于分析锁，以及调试存储过程和数据库应用程序。

 

试一试
它也可用于检测和分析影响SQL Server性能的长时间运行的査 询 。Profiler可以返回査询 执行信息，数据库管理员可以通过这些信息找出导致冗长査询的原因。是代码写得不好吗？
是否没有索引支持査询？或者这只是一种奇怪的查询？
分析查询
要分析查询，需要遵循下列步骤：
(1) 启 动 Profiler,使 用 “空白”模板创建一个叫做QueryTuning的新跟踪。在 “事件 选择”选项卡上选择下列事件：
• Performance： ShowPlan XML • Stored Procedures： SP: Completed
• TSQL： SQL: BatchCompleted
(2) 单击“列筛选器”按钮创建一个筛选器，其中数据库名称“类似于”AdventureWOrks2008, 然后单击“确定”按钮应用该筛选器。
(3) 单 击 “组织列”按 钮 。找 到 Duration列 ，将其移至列列表的顶部，使得易于阅读 持久数据。
(4) 在 “事件提取设置”选项卡上，选 中 “分别保存XML显示计划事件”复选框。选
择 一 个 保 存 ShowPlan信息的位置，将文件命名为Query Tuning,然 后 选 择 “不同文件中的 每 个 XML显示计划批”选 项 。SQLPlan是 给 予 ShowPlan数据的文件扩展。ShowPlan数据 存 储 为 XML格式，并且可以使用Management Studio査看(后面将会介紹)。当将査询计划 保存在单独的文件中时，每个文件都采用在目标位置中定义的名称，并且名称后面附加有
数字标识符。
(5) 单 击 “运行”按钮运行此跟踪。
(6) 接下来，在 SQL Server Management Studio中 打 开 一 个 新查询窗口。输入并执行下
列代码：
USE AdventureWorks2008; GO
SELECT P.ProductID, P.name AS Product, TH.TransactionDate, SUM(TH.Quantity), SUM(TH.ActualCost), SUM(P.StandardCost) FROM Production.Product P INNER JOIN Production.TransactionHistory TH ON P.ProductID = TH.ProductID GROUP BY P.ProductID, P.Name, TH.TransactionDate;
GO
EXEC dbo.uspGetManagerEmployees 109;
GO
EXEC dbo.uspGetEmployeeManagers 1;
GO
SELECT P.name AS Product, SUM(SOD.OrderQty) AS SumQty , SUM(SOD.UnitPrice) AS SumPrice, SUM(SOD.LineTotal) AS SumTotal , CONVERT(char(10), SOH•OrderDate,101) AS orderDate , CONVERT(char(10), SOH.ShipDate,101) AS ShipDate , CONVERT(char(10)r SOH.DueDate,101) AS DueDate

FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHeader SOH
ON SOH.SalesOrderlD = SOD.SalesOrderlD INNER JOIN Production.Product P ON P.ProductID = SOD.ProductID
GROUP BY P.Name, SOH.OrderDate, SOH.ShipDate, SOH.DueDate;
査询完成之后，停止跟踪并检查结果。可注意到，运行时间最长的进程是最后一个进
程 ，它引用了 Sales.SalesOrderHeader 表、Sales.SalesOrderdetail 和 Production. Product 表 0
(7) 导 航 至 ShowPlan目标文件夹并检 査 内 容 。应 该 看 到 4 个 文 件 ，分别从
QueryTuning l.SQLPlan 到 QueryTuning_4.SQLPlan 进行命名。 (8) 双击 QueryTuning_4.SQLPlan 文件。这将启动 SQL Server Management Studio，并
把该文件作为一个图形 化 &行计划打开，如图10-14所示。

在评估数据库引擎用来优化査询的实际进程和识别可改进的区域方面，ShowPlan文件 非常有用。对于ShowPlan,应该从右到左阅读。把鼠标指针悬停在一个图标上会显示该图 标描述的进程的附加信息，这有助于决定如何优化该进程。例如，如果一个进程显示了一
个不必要的隐式转换，则可以对传递的数据类型执行更严格的检査以避免隐式转换。
提示：
图 10-14中显示的信息实际上被保存为XML。这对于那些想在用于分析查询计划和识别
要改进的区域的分析应用程序(例如数据库优化顾问）中使用ShowPlan数据的组织来说特别有
吸引力。将名称 QueryTuning_4.SQLPlan 改为 QueryTuning_4.XML„ 右击 QueryTuning_4.XML 文件，选择“打开方式… Internet Explorer”命 令 。显 示 的 ShowPlan文件通过Internet Explorer
内置的XML分析器呈现，并很容易被标识为一个XML文件•