SQL点滴13—收集SQLServer线程等待信息

要知道线程等待时间是制约SQL Server效率的重要原因，这一个随笔中将学习怎样收集SQL Server中的线程等待时间，类型等信息，这些信息是进行数据库优化的依据。 sys.dm_os_wait_stats 这是一个系统视图，里面存储线程所遇到的所有的等待信息，具体的列如下表

要知道线程等待时间是制约SQL Server效率的重要原因，这一个随笔中将学习怎样收集SQL Server中的线程等待时间，类型等信息，这些信息是进行数据库优化的依据。

　　

sys.dm_os_wait_stats

这是一个系统视图，里面存储线程所遇到的所有的等待信息，具体的列如下表

列名	数据类型	说明
Wait_type	Nvarchar(60)	等待类型名称
waiting_tasks_count	Bigint	等待类型的等待数。该计数器在每开始一个等待时便会增加。
Wait_time_ms	Bigint	该等待类型的总等待时间。
Max_wait_time_ms	Bigint	该等待类型的最长等待时间。
Signal_wait_time_ms	Bigint	正在等待的线程从收到信号通知到开始运行之间的时差。

要注意的是，这个视图的信息每次关闭SQL Server的时候都会自动清除，下次打开SQL Server的时候又会重新开始统计。

　　

新建线程等待信息表

如果想得到连续的信息，在固定时间间隔内收集信息比如一个小时一次，这样就可以分析系统分配的等待时间，识别出繁忙时间段。这里我们将这些信息收集到一个数据表中保存并进行分析。使用下面的语句新建一个表：

use AdventureWorks

CREATETABLE dbo.WaitStats

(

dt DATETIMENOTNULLDEFAULT (CURRENT_TIMESTAMP),

wait_type NVARCHAR(60) NOTNULL,

waiting_tasks_count BIGINTNOTNULL,

wait_time_ms BIGINTNOTNULL,

max_wait_time_ms BIGINTNOTNULL,

signal_wait_time_ms BIGINTNOTNULL

);

CREATEUNIQUECLUSTEREDINDEX idx_dt_type ON dbo.WaitStats(dt, wait_type);

CREATEINDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);

　　

新建job填充数据

要收集信息最好是用一个job来定时地执行insert语句填充数据，下面介绍步骤

1. 点击数据库中的SQL Server Agent，展开，右击jobs文件夹，右击新建。如图1
   
   
   图1
2. 
   
   图2
3. 
   
   
   图3
   Command属性是设置要执行的SQL语句或存储过程等等，这里设置如下的SQL Script:
   

   INSERTINTO Performance.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

4. 
   
   图4

上述步骤包含了新建一个job的主要设置，其他的细节信息没有包含，遇到具体问题再具体分析吧。

收集等待信息数据

过一段时间之后在表WaitStats中就会有一些数据，每隔5分钟就会在这个表中写入一些数据，这些数据会不断的增加。这里为了得到相邻间隔之间线程等待时间的变化就要使用自连接，连接条件是等待类型相同，当前行号等于上一个的行号加上1，然后就可以用上一次等待时间减去这一次的等待时间得到这个变化值，下面使用一个函数来实现这个逻辑：

IFOBJECT_ID('dbo.IntervalWaits', 'IF') ISNOTNULL
DROPFUNCTION dbo.IntervalWaits;
GO
CREATEFUNCTION dbo.IntervalWaits
(@fromdtASDATETIME, @todtASDATETIME)
RETURNSTABLE
AS
RETURN
WITH Waits AS
(
SELECT dt, wait_type, wait_time_ms,
ROW_NUMBER() OVER(PARTITION BY wait_type
ORDERBY 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

这个函数接受两个参数，开始统计时间，结束统计时间，返回等待变化的时间，并按照类型排序。调用这个函数如下：

SELECT wait_type, start_time, interval_wait_s

FROM dbo.IntervalWaits('20110509', '20110510') AS F

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

但是我们不能每次都去调用这个函数，所以可以吧这个查询放在一个视图里面，外部只需要使用视图来查询数据就可以了：

IFOBJECT_ID('dbo.IntervalWaitsSample', 'V') ISNOTNULL
DROPVIEW dbo.IntervalWaitsSample;
GO
CREATEVIEW dbo.IntervalWaitsSample
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215') AS F;
GO

从视图中查询得到的数据就是我们要得到的数据。但是这些并不明显，先写到这里，下一个随笔我将在EXECL中把这些数据制作成一个直方图或者连线图，横轴是时间，纵轴是等待间隔时间。这样就会更加直观地看到在那些时间SQL Server的线程等待时间最长，也就是最繁忙的时候。