sys.dm_os_wait_stats 查看数据库等待信息

当用户告诉你数据库很慢的时候，你要怎么开始Narrowdown问题呢？SQL Server提供了sys.dm_os_wait_stats可以帮助我们查看CPU,内存或者IO的等待状况。SQL Server执行过程中中等待信息会被记录到这个View中。

 

通过下面的语句我们可以抓取一段时间内SQL Server等待的累积信息，通过对这些信息进行排序可以找出资源瓶颈。

 

先看一下各个Wait等待的占比：

 

WITH Waits AS

 (

 SELECT 

   wait_type, 

   wait_time_ms /1000. AS wait_time_s,

   100. *wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

 FROM sys.dm_os_wait_stats

 WHEREwait_type 

   NOT IN

     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',

   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',

   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','BROKER_TASK_STOP')

   ) -- filter out additional irrelevant waits

   

SELECT W1.wait_type,

 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, 

 W1.wait_type, 

 W1.wait_time_s, 

 W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

 

由于这个View是累积的，所以我们需要定期的收集统计信息，通过前后两次数据的比较获得这一段时间的等待状况。

 

 --Create table topersist wait stats information:

 

CREATE TABLE ColWaitInfo

(

 [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,

 [capture_time] [datetime] NOT NULL,

 [increment_id] [int] NOTNULL

);

 

ALTER TABLE ColWaitInfo ADD DEFAULT (GETDATE()) FOR [capture_time];

 

--Insert waitstats info in a datestamped format for later querying:

DECLARE @DT DATETIME ;

SET @DT = GETDATE() ;

DECLARE@increment_id INT;

 

SELECT@increment_id = MAX(increment_id) + 1 FROM ColWaitInfo

SELECT@increment_id = ISNULL(@increment_id, 1)

 

 

INSERT INTO ColWaitInfo

 ([wait_type], [waiting_tasks_count],[wait_time_ms], [max_wait_time_ms],

 [signal_wait_time_ms],[capture_time], [increment_id])

SELECT[wait_type], [waiting_tasks_count], [wait_time_ms],[max_wait_time_ms], 

 [signal_wait_time_ms],@DT, @increment_id

FROM sys.dm_os_wait_stats;

 

 

下面的语句用来查询间隔内的累积信息：

DECLARE@max_increment_id INT

 

------------------------------------------------------------------

--Determinemost-recent increment_id

------------------------------------------------------------------

SELECT@max_increment_id = MAX(increment_id)

FROM  ColWaitInfo

   

------------------------------------------------------------------

--Present Waitsresults for period

------------------------------------------------------------------

SELECT DOWS1.wait_type, 

 (DOWS1.waiting_tasks_count -DOWS2.waiting_tasks_count) AS[waiting_tasks_count],

 (DOWS1.wait_time_ms - DOWS2.wait_time_ms) AS [wait_time_ms],

 DOWS1.max_wait_time_ms, 

 (DOWS1.signal_wait_time_ms -DOWS2.signal_wait_time_ms) AS[signal_wait_time_ms],

 DATEDIFF(ms, DOWS2.capture_time, DOWS1.capture_time) AS [elapsed_time_ms],

 DOWS1.capture_timeAS [last_time_stamp],DOWS2.capture_time AS[previous_time_stamp]

FROM 

 (

 SELECT  wait_type,waiting_tasks_count, wait_time_ms, max_wait_time_ms,

         signal_wait_time_ms, capture_time,increment_id

 FROMColWaitInfo

 WHEREincrement_id = @max_increment_id

 )AS DOWS1 

 INNER JOIN 

 (

 SELECT  wait_type,waiting_tasks_count, wait_time_ms, max_wait_time_ms,

         signal_wait_time_ms, capture_time,increment_id

 FROMColWaitInfo

 WHEREincrement_id =(@max_increment_id -1)

 )AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type

WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0 

  AND DOWS1.wait_type    NOT IN

     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',

   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',

   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','BROKER_TASK_STOP')  -- filter out additional irrelevant waits

ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC;