要知道线程等待时间是制约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
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
);
CREATE
UNIQUE
CLUSTERED
INDEX
idx_dt_type
ON
dbo.WaitStats(dt, wait_type);
CREATE
INDEX
idx_type_dt
ON
dbo.WaitStats(wait_type, dt);
新建job 填充数据
要收集信息最好是用一个job来定时地执行insert语句填充数据，下面介绍步骤
-
点击数据库中的SQL Server Agent，展开，右击jobs文件夹，右击新建。如图1
图1 -
在新建job界面，General标签中填写job名称，描述，如图2
图2 -
点击Setps标签，点击新建按钮，在新建step界面内设置Step Name，Type，DataBase，Command等属性，如下图3。这些属性一看就知道是要做什么的，就不一一的解释了。还要说的 是截图是建好的job，如果你自己做的话对话框显示的标题可能不一样。
图3
Command属性是设置要执行的SQL语句或存储过程等等，这里设置如下的SQL Script:
INSERT INTO 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 -
点击Shedules标签，设置job的执行计划，这里设置的是每天的每5分钟执行一次如图4
图4
上述步骤包含了新建一个job的主要设置，其他的细节信息没有包含，遇到具体问题再具体分析吧。
收集等待信息数据
过一段时间之后在表WaitStats中就会有一些数据，每隔5分钟就会在这个表中写入一些数据，这些数据会不断的增加。这里为了得到相邻间隔之间线程等待时间的变化就要使用自连接，连接条件是等待类型相同，当前行号等于上一个的行号加上1，然后就可以用上一次等待时间减去这一次的等待时间得到这个变化值，下面使用一个函数来实现这个逻辑：
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
这个函数接受两个参数，开始统计时间，结束统计时间，返回等待变化的时间，并按照类型排序。调用这个函数如下：
SELECT wait_type, start_time, interval_wait_sFROM dbo.IntervalWaits('20110509', '20110510') AS F
ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC,wait_type,start_time;
但是我们不能每次都去调用这个函数，所以可以吧这个查询放在一个视图里面，外部只需要使用视图来查询数据就可以了：
IF
OBJECT_ID
(
'
dbo.IntervalWaitsSample
'
,
'
V
'
)
IS
NOT
NULL
DROP
VIEW
dbo.IntervalWaitsSample;
GO
CREATE
VIEW
dbo.IntervalWaitsSample
AS
SELECT
wait_type, start_time, interval_wait_s
FROM
dbo.IntervalWaits(
'
20090212
'
,
'
20090215
'
)
AS
F;
GO
