要知道线程等待时间是制约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的时候又会重新开始统计。
新建线程等待信息表
如果想得到连续的信息，在固定时间间隔内收集信息比如一个小时一次，这样就可以分析系统分配的等待时间，识别出繁忙时间段。这里我们将这些信息收集到一个数据表中保存并进行分析。使用下面的语句新建一个表：
<p><span>use</span><span> AdventureWorks<br><br></span><span>CREATE</span><span>TABLE</span><span> dbo.WaitStats<br><br>(<br><br>dt </span><span>DATETIME</span><span>NOT</span><span>NULL</span><span>DEFAULT</span><span> (</span><span>CURRENT_TIMESTAMP</span><span>),<br><br>wait_type </span><span>NVARCHAR</span><span>(</span><span>60</span><span>) </span><span>NOT</span><span>NULL</span><span>,<br><br>waiting_tasks_count </span><span>BIGINT</span><span>NOT</span><span>NULL</span><span>,<br><br>wait_time_ms </span><span>BIGINT</span><span>NOT</span><span>NULL</span><span>,<br><br>max_wait_time_ms </span><span>BIGINT</span><span>NOT</span><span>NULL</span><span>,<br><br>signal_wait_time_ms </span><span>BIGINT</span><span>NOT</span><span>NULL</span><span><br><br>);<br><br></span><span>CREATE</span><span>UNIQUE</span><span>CLUSTERED</span><span>INDEX</span><span> idx_dt_type </span><span>ON</span><span> dbo.WaitStats(dt, wait_type);<br><br></span><span>CREATE</span><span>INDEX</span><span> idx_type_dt </span><span>ON</span><span> dbo.WaitStats(wait_type, dt);</span></p>
新建job填充数据
要收集信息最好是用一个job来定时地执行insert语句填充数据，下面介绍步骤
<p><span>INSERT</span><span>INTO</span><span> Performance.dbo.WaitStats<br>(wait_type, waiting_tasks_count, wait_time_ms,<br>max_wait_time_ms, signal_wait_time_ms)<br></span><span>SELECT</span><span><br>wait_type, waiting_tasks_count, wait_time_ms,<br>max_wait_time_ms, signal_wait_time_ms<br></span><span>FROM</span><span> sys.dm_os_wait_stats</span></p>
上述步骤包含了新建一个job的主要设置，其他的细节信息没有包含，遇到具体问题再具体分析吧。
收集等待信息数据
过一段时间之后在表WaitStats中就会有一些数据，每隔5分钟就会在这个表中写入一些数据，这些数据会不断的增加。这里为了得到相邻间隔之间线程等待时间的变化就要使用自连接，连接条件是等待类型相同，当前行号等于上一个的行号加上1，然后就可以用上一次等待时间减去这一次的等待时间得到这个变化值，下面使用一个函数来实现这个逻辑：
<p><span>IF</span><span>OBJECT_ID</span><span>(</span><span>'</span><span>dbo.IntervalWaits</span><span>'</span><span>, </span><span>'</span><span>IF</span><span>'</span><span>) </span><span>IS</span><span>NOT</span><span>NULL</span><span><br></span><span>DROP</span><span>FUNCTION</span><span> dbo.IntervalWaits;<br></span><span>GO</span><span><br></span><span>CREATE</span><span>FUNCTION</span><span> dbo.IntervalWaits<br>(</span><span>@fromdt</span><span>AS</span><span>DATETIME</span><span>, </span><span>@todt</span><span>AS</span><span>DATETIME</span><span>)<br></span><span>RETURNS</span><span>TABLE</span><span><br></span><span>AS</span><span><br></span><span>RETURN</span><span><br></span><span>WITH</span><span> Waits </span><span>AS</span><span><br>(<br></span><span>SELECT</span><span> dt, wait_type, wait_time_ms,<br>ROW_NUMBER() </span><span>OVER</span><span>(PARTITION </span><span>BY</span><span> wait_type<br></span><span>ORDER</span><span>BY</span><span> dt) </span><span>AS</span><span> rn<br></span><span>FROM</span><span> dbo.WaitStats<br>)<br></span><span>SELECT</span><span> Prv.wait_type, Prv.dt </span><span>AS</span><span> start_time,<br></span><span>CAST</span><span>((Cur.wait_time_ms </span><span>-</span><span> Prv.wait_time_ms)<br></span><span>/</span><span>1000</span><span>. </span><span>AS</span><span> NUMERIC(</span><span>12</span><span>, </span><span>2</span><span>)) </span><span>AS</span><span> interval_wait_s<br></span><span>FROM</span><span> Waits </span><span>AS</span><span> Cur<br></span><span>JOIN</span><span> Waits </span><span>AS</span><span> Prv<br></span><span>ON</span><span> Cur.wait_type </span><span>=</span><span> Prv.wait_type<br></span><span>AND</span><span> Cur.rn </span><span>=</span><span> Prv.rn </span><span>+</span><span>1</span><span><br></span><span>AND</span><span> Prv.dt </span><span>>=</span><span>@fromdt</span><span><br></span><span>AND</span><span> Prv.dt </span><span><span>DATEADD</span><span>(</span><span>day</span><span>, </span><span>1</span><span>, </span><span>@todt</span><span>)<br></span><span>GO</span></span></p>
这个函数接受两个参数，开始统计时间，结束统计时间，返回等待变化的时间，并按照类型排序。调用这个函数如下：
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;
但是我们不能每次都去调用这个函数，所以可以吧这个查询放在一个视图里面，外部只需要使用视图来查询数据就可以了：
<p><span>IF</span><span>OBJECT_ID</span><span>(</span><span>'</span><span>dbo.IntervalWaitsSample</span><span>'</span><span>, </span><span>'</span><span>V</span><span>'</span><span>) </span><span>IS</span><span>NOT</span><span>NULL</span><span><br></span><span>DROP</span><span>VIEW</span><span> dbo.IntervalWaitsSample;<br></span><span>GO</span><span><br></span><span>CREATE</span><span>VIEW</span><span> dbo.IntervalWaitsSample<br></span><span>AS</span><span><br></span><span>SELECT</span><span> wait_type, start_time, interval_wait_s<br></span><span>FROM</span><span> dbo.IntervalWaits(</span><span>'</span><span>20090212</span><span>'</span><span>, </span><span>'</span><span>20090215</span><span>'</span><span>) </span><span>AS</span><span> F;<br></span><span>GO</span></p>
