数据库监控方案

来源：微智科技网

数据库监控方案

初稿

版本号：1.0.0.1 日期：2011年3月7号作者：张磊

目录 ........................................................................................................................................................ 2 1 引言 ...................................................................................................................................................... 3

1.1 1.2 1.3 2

2.1

目的 ..................................................................................................................................... 3 定义 ..................................................................................................................................... 3 补充 ..................................................................................................................................... 3 监控方法 .................................................................................................................................... 3

分析实例级别的等待 ......................................................................................................... 4 2.1.1 2.1.2 2.1.3 2.2

sys.dm_os_wait_stats描述 ..................................................................................... 4

分离重量级的等待 ................................................................................................ 5 收集等待信息 ........................................................................................................ 6

关联等待和资源 ................................................................................................................. 9 2.2.1 2.2.2

sys. dm_os_performance_counters描述 ................................................................ 9

收集计数器信息 .................................................................................................. 10

2.3 细化到进程级别 ............................................................................................................... 10 2.3.1 2.3.2 2.3.3

跟踪需要遵循的一些重要准则........................................................................... 10 收集跟踪的具体办法 .......................................................................................... 10 分析跟踪数据（暂略） ...................................................................................... 14

2.4

优化语句 ........................................................................................................................... 14

1 引言

1.1 目的

为解决一卡通服务项目遇到的性能问题，而编写该解决方案

1.2 定义

必须要说明的是：

（1）该监控方案主要提供如何提取可分析的监控数据，这些监控数据是解决项目所遇到问题的

依据。

（2）该监控方案是一种思路，并不是SqlServer疑难问解答字典，但任然会根据监控数据简单

的列出几种常见问题（并包含这些问题背后的原因）。

（3）要想彻底的解决一卡通项目所面临的性能问题。必须经过大量实验研究，并反复验证核实。

同时也必须不断提高我们对数据库知识能力和积累相关经验。

1.3 补充

（1）该监控方案不光只是针对一卡通服务项目，它可以适应任何一个项目的数据库监控（2）本方案信息来源于《Microsoft SQL Server 2005 技术内幕：T-SQL 查询》一书的“查询

优化”章节。换句话讲就是对这一章节的内容进行了个人整理。

2 监控方法

接下来将会介绍一系列监控步骤，帮助我们监控系统中可能遇到的各项瓶颈。通过这些步骤不断将其细化，直到找出系统中导致大量等待的进程/组件。步骤如下： 1，分析实例级别的等待 2，关联等待和资源 3，细化到进程级 4，优化语句

提示：所有步骤名称都与“查询优化”章节所描述的步骤名称是一直的

准备工作：

（1）对需监控的SQL Server实例重新启动，这样做的原因是重置一些相关统计信息，保障我们提

取的数据不会因以前的信息受到误导。

（2）理解DMV/DMF（动态管理视图/动态管理函数）：这些对象主要用来对数据库进行故障诊断，

存储和进程调校，以及涉及多个会话的监视。本方案正是使用DMV/DMF得到监控信息，所

以了解DMV/DMF是至关重要的（通过网上微软SqlServer联机丛书可得到更多相关信息）。

2.1 分析实例级别的等待

这一步骤任务是找出什么类型的等待占用了系统大部分的等待时间，主要通过sys.dm_os_wait_stats（DMV中的一种，该DMV包含400多种等待类型）来完成。以下为基本思路： 1， sys.dm_os_wait_stats简单介绍 2，分离重量级的等待 3，收集等待信息

2.1.1 sys.dm_os_wait_stats描述

运行以下语句： SELECT wait_type,

waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_type;

可能得到结果是（这是举例）：

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms --------------------------------------------------------------------------------------------------------------------------------ASYNC_IO_COMPLETION 3 1710 658 0 ASYNC_NETWORK_IO 299875 176144 959 21377 。。。

LCK_M_S 652 40492 1598 165 。。。

注意：以上数值是从SqlServer服务最后一次重新启动开始累积值。

DMV sys.dm_os_wait_stats包含以下基本属性：

   

wait_type 等待类型

waiting_tasks_count 该类型等待的任务数量

wait_time_ms 以毫秒为单位的该等待的总等待时间

max_wait_time_ms 以毫秒为单位的该等待任务中最大的等待时间

 signal_wait_time_ms 等待线程从收到信号通知到其开始运行之间的时差

提示：更多信息参照http://msdn.microsoft.com/zh-cn/library/ms179984.aspx

针对sys.dm_os_wait_stats所提供的信息，可以列出几种常见情况，并加以分析： 1. 2. 3. 4. 5.

signal_wait_time_ms值过高，通常是cpu出现问题。

与IO相关的等待类型是最常见，因为IO是最昂贵的资源，也有可能是查询或索引没有经过良好的设计。

如果出现ASYNC_NETWORK_IO等待类型的值过高，则表明可能存在网络问题。

如果出现语句调用过度频繁，有可能代码编译和重新编译时瓶颈的主要原因，如果这些语句又是特定查询，就会因为这些特定的执行计划而消耗大量的内存。如果出现WRITELOG等待类型的值过高，则表明日志出现了瓶颈

。。。

2.1.2 分离重量级的等待

通过以下查询语句分离出累积等待时间最长的前10位等待（可以根据实际需要调整前10个等待还是前20个等待）： WITH Waits AS (

SELECT wait_type,

wait_time_ms,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn, 100. * signal_wait_time_ms / wait_time_ms as signal_pct FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0

AND wait_type NOT LIKE N'%SLEEP%' AND wait_type NOT LIKE N'%IDLE%' AND wait_type NOT LIKE N'%QUEUE%' AND wait_type NOT IN( N'CLR_AUTO_EVENT'

, N'REQUEST_FOR_DEADLOCK_SEARCH' , N'SQLTRACE_BUFFER_FLUSH' ) SELECT

W1.wait_type, W1.wait_time_ms,

CAST(W1.pct AS NUMERIC(5, 2)) AS pct,

CAST(SUM(W2.pct) AS NUMERIC(5, 2)) AS running_pct, CAST(W1.signal_pct AS NUMERIC(5, 2)) AS signal_pct FROM Waits AS W1 JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct, W1.signal_pct HAVING SUM(W2.pct) - W1.pct < 80 -- percentage threshold OR W1.rn <= 10 ORDER BY W1.rn; GO

可能得到的结果（这是举例）：

wait_type wait_time_ms pct running_pct signal_pct

PAGEIOLATCH_SH 230585 34.50 34,50 1.68 ASYNC_NETWORK_IO 157281 23.53 58.03 18.22 WRITELOG 16028 2.40 60.43 11.53

该查询返回以下信息：    

wait_time_ms：从系统最后一次重新启动或计数器清空以来，该等待类型的总等待时间 pct：该类型的等待时间占总等待时间的百分比

running_pct：从最重量级的等待类型到当前等待类型的连续百分比 signal_pct：信号等待时间占等待时间的百分比（signal_wait_time_ms）

2.1.3 收集等待信息

把等待信息收集到一张表中，然后定时更新（建议，每隔一小时把重量级的等待信息保存到这张表中）。接下来通过一个Function把每次间隔内发生的等待信息进行比较，并返回一个结果集。最后把返回的结果集保存到Excel中，并通过数据透视表功能把等待信息图形化。

创建WaitStats表

IF OBJECT_ID('dbo.WaitStats', 'U') IS NOT NULL DROP TABLE dbo.WaitStats;

CREATE TABLE dbo.WaitStats (

dt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),

wait_type NVARCHAR(60) NOT NULL, waiting_tasks_count BIGINT NOT NULL, wait_time_ms BIGINT NOT NULL, max_wait_time_ms BIGINT NOT NULL, signal_wait_time_ms BIGINT NOT NULL );

定义一个按固定时间间隔运行的任务，并使用以下代码从DMV加载当前数据 INSERT INTO dbo.WaitStats

(wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms) SELECT

wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN (N'MISCELLANEOUS');

创建一个IntervalWaits函数计算出所有等待类型每次间隔内的等待时间差，并已结果集的形式返回（该函数以要分析的日期为边界作为参数）

IF OBJECT_ID('dbo.IntervalWaits', 'IF') IS NOT NULL DROP FUNCTION dbo.IntervalWaits; GO

CREATE FUNCTION dbo.IntervalWaits

(@fromdt AS DATETIME, @todt AS DATETIME) RETURNS TABLE AS RETURN WITH Waits AS (

SELECT dt, wait_type, wait_time_ms,

ROW_NUMBER() OVER(PARTITION BY wait_type ORDER BY dt) AS rn FROM dbo.WaitStats )

SELECT Prv.wait_type, Prv.dt AS start_time, CAST((Cur.wait_time_ms - Prv.wait_time_ms)

/ 1000. AS NUMERIC(12, 2)) AS interval_wait_s FROM Waits AS Cur JOIN Waits AS Prv

ON Cur.wait_type = Prv.wait_type AND Cur.rn = Prv.rn + 1 AND Prv.dt >= @fromdt

AND Prv.dt < DATEADD(day, 1, @todt) GO

查询该函数，并把结果集保存到Excel中 SELECT wait_type, start_time, interval_wait_s FROM dbo.IntervalWaits('20090212', '20090213') AS F

ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC, wait_type, start_time; GO

根据保存好的Excel文件过滤出2.1.2章节所查询出来的前10个重量级等待的等待信息。并已图形化的方式展现，如图（只是举例）：

2.2 关联等待和资源

当找出重量级的等待之后，接下该把它们与之相关联得资源联系起来，找出有问题的资源分析它。这一步主要是用性能监视器的计数器来完成这项任务。

根据需要关联的等待类型，在通过sys. dm_os_performance_counters（DMV中的一种）返回的结果信息中查找与该等待类型相关的资源使用情况。

2.2.1 sys. dm_os_performance_counters描述

运行如下代码： SELECT object_name, counter_name, instance_name, cntr_value, cntr_type

FROM sys.dm_os_performance_counters;

可能得到结果：

。。。

举例说明其中一条，如：

例如，SQLServer:Buffer Manager对象的“Page life expectancy”这个计数器可以告诉我们没有被引用的页在缓冲池中平均停留时间为7995秒。

更多信息参照http://msdn.microsoft.com/zh-cn/library/ms190382.aspx

DMV sys.dm_os_performance_counters包含以下基本属性：

参考http://msdn.microsoft.com/zh-cn/library/ms187743.aspx

2.2.2 收集计数器信息

和等待信息一样，可以按一定时间间隔（建议，每天一次），将性能计时器的数据收集到一张表中，在使用查询语句和其他工具（如Excel的数据透视表）来分析这些数据的变化，并与等待信息关联起来发现背后的性能问题。

2.3 细化到进程级别

既然已经知道了哪些等待信息和资源对象存在性能瓶颈，那就根据这些反馈信息作为过滤条件细化到进程级别，也就是说找出哪些SQL语句存在问题。然后分析这些跟踪数据，并找出原因进行优化

2.3.1 跟踪需要遵循的一些重要准则

跟踪进程级的数据本身会对系统性能造成影响，所以尽可能的减少这些影响。

以下为别人提出几点建议

  

不要使用SQL Server Profiler GUI进行跟踪。不要把跟踪文件直接写到表中。

不要把目标跟踪文件放到包含数据库文件的磁盘上

 谨慎选择事件类型和数据列，只选择相关事件

2.3.2 收集跟踪的具体办法

首先需创建一个存储过程接受一个数据库ID和文件名称作为输入参数，它以编码的形式定义一个跟踪（不需使用SQL Server Profiler），并把指定的文件作为跟踪数据的目标文件。启动它开始跟踪，通过输出参数来返回新生成的跟踪ID（都最后如果需停止跟踪，则需要这个跟踪ID）。

创建PerfworkloadTraceStart存储过程

IF OBJECT_ID('dbo.PerfworkloadTraceStart', 'P') IS NOT NULL DROP PROC dbo.PerfworkloadTraceStart; GO

CREATE PROC dbo.PerfworkloadTraceStart

@dbid AS INT,

@tracefile AS NVARCHAR(245), @traceid AS INT OUTPUT AS

-- Create a Queue

DECLARE @rc AS INT; DECLARE @maxfilesize AS BIGINT;

SET @maxfilesize = 5;

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL IF (@rc != 0) GOTO error;

-- Set the events DECLARE @on AS BIT; SET @on = 1;

-- RPC:Completed

exec sp_trace_setevent @traceid, 10, 15, @on; exec sp_trace_setevent @traceid, 10, 8, @on; exec sp_trace_setevent @traceid, 10, 16, @on; exec sp_trace_setevent @traceid, 10, 48, @on; exec sp_trace_setevent @traceid, 10, 1, @on; exec sp_trace_setevent @traceid, 10, 17, @on; exec sp_trace_setevent @traceid, 10, 10, @on; exec sp_trace_setevent @traceid, 10, 18, @on; exec sp_trace_setevent @traceid, 10, 11, @on; exec sp_trace_setevent @traceid, 10, 12, @on; exec sp_trace_setevent @traceid, 10, 13, @on; exec sp_trace_setevent @traceid, 10, 6, @on; exec sp_trace_setevent @traceid, 10, 14, @on;

-- SP:Completed

exec sp_trace_setevent @traceid, 43, 15, @on; exec sp_trace_setevent @traceid, 43, 8, @on;

exec sp_trace_setevent @traceid, 43, 48, @on; exec sp_trace_setevent @traceid, 43, 1, @on; exec sp_trace_setevent @traceid, 43, 10, @on; exec sp_trace_setevent @traceid, 43, 11, @on; exec sp_trace_setevent @traceid, 43, 12, @on; exec sp_trace_setevent @traceid, 43, 13, @on; exec sp_trace_setevent @traceid, 43, 6, @on; exec sp_trace_setevent @traceid, 43, 14, @on;

-- SP:StmtCompleted

exec sp_trace_setevent @traceid, 45, 8, @on; exec sp_trace_setevent @traceid, 45, 16, @on; exec sp_trace_setevent @traceid, 45, 48, @on; exec sp_trace_setevent @traceid, 45, 1, @on; exec sp_trace_setevent @traceid, 45, 17, @on; exec sp_trace_setevent @traceid, 45, 10, @on; exec sp_trace_setevent @traceid, 45, 18, @on; exec sp_trace_setevent @traceid, 45, 11, @on; exec sp_trace_setevent @traceid, 45, 12, @on; exec sp_trace_setevent @traceid, 45, 13, @on; exec sp_trace_setevent @traceid, 45, 6, @on; exec sp_trace_setevent @traceid, 45, 14, @on; exec sp_trace_setevent @traceid, 45, 15, @on;

-- SQL:BatchCompleted

exec sp_trace_setevent @traceid, 12, 15, @on; exec sp_trace_setevent @traceid, 12, 8, @on; exec sp_trace_setevent @traceid, 12, 16, @on; exec sp_trace_setevent @traceid, 12, 48, @on; exec sp_trace_setevent @traceid, 12, 1, @on; exec sp_trace_setevent @traceid, 12, 17, @on; exec sp_trace_setevent @traceid, 12, 6, @on; exec sp_trace_setevent @traceid, 12, 10, @on; exec sp_trace_setevent @traceid, 12, 14, @on; exec sp_trace_setevent @traceid, 12, 18, @on; exec sp_trace_setevent @traceid, 12, 11, @on;

exec sp_trace_setevent @traceid, 12, 12, @on; exec sp_trace_setevent @traceid, 12, 13, @on;

-- SQL:StmtCompleted

exec sp_trace_setevent @traceid, 41, 15, @on; exec sp_trace_setevent @traceid, 41, 8, @on; exec sp_trace_setevent @traceid, 41, 16, @on; exec sp_trace_setevent @traceid, 41, 48, @on; exec sp_trace_setevent @traceid, 41, 1, @on; exec sp_trace_setevent @traceid, 41, 17, @on; exec sp_trace_setevent @traceid, 41, 10, @on; exec sp_trace_setevent @traceid, 41, 18, @on; exec sp_trace_setevent @traceid, 41, 11, @on; exec sp_trace_setevent @traceid, 41, 12, @on; exec sp_trace_setevent @traceid, 41, 13, @on; exec sp_trace_setevent @traceid, 41, 6, @on; exec sp_trace_setevent @traceid, 41, 14, @on;

-- Set the Filters

-- Application name filter

EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%'; -- Database ID filter

EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

-- Set the trace status to start EXEC sp_trace_setstatus @traceid, 1;

-- Print trace id and file name for future references PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10)) + ', Trace File: ''' + @tracefile + '.trc''';

GOTO finish; error:

PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish: GO

运行跟踪：

DECLARE @dbid AS INT, @traceid AS INT; SET @dbid = DB_ID('ykt_20110301');

EXEC master.dbo.PerfworkloadTraceStart @dbid = @dbid,

@tracefile = 'c:\emp\\ ykt_20110301', @traceid = @traceid OUTPUT; GO

返回的信息如下：

Trace ID：2， Trace File：‘c:\emp\\ ykt_2011030.trc’

跟踪信息以保存到c:\emp\\ ykt_2011030.trc文件中，可通过SQL Server Profiler进行查看注意：需保留红色加粗值，此值为跟踪ID。每次返回的跟踪ID可能不一样。

如果还回的跟踪ID（@traceid）为2，停止跟踪：

EXEC sp_trace_setstatus 2, 0; EXEC sp_trace_setstatus 2, 2;

注意：红色加粗值表示为跟踪ID（Trace ID），只有通过跟踪ID才能停止跟踪进程。

2.3.3 分析跟踪数据（暂略） 2.4 优化语句

这一步主要是根据分析跟踪数据找到需要优化的SQL语句，但是优化SQL语句是一个庞大的工程，这里就涉及到分析索引，研究执行计划，检验事务和锁（同时也要对代码进行检验）。然后本方案最主要的目的是发现系统存在的问题，所以对如何解决这些问题的描述就暂时忽略掉。在此只提空以下简单的语句优化工具供以参考

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文