查询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 ColWaitInfoADDDEFAULT (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;