SQL Server 等待的資源

感謝承修提供關於 SQL Server 等待資源的分析,我稍微修改了一些

–統計資料在 SQL Server 重新啟動之後都不會保存下來,而且所有的資料都是從上次統計資料重設或伺服器啟動之後開始累加計算。
–DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
GO

WITH Waits AS

(SELECT

wait_type,

wait_time_ms / 1000.0 AS WaitS,

(wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,

signal_wait_time_ms / 1000.0 AS SignalS,

waiting_tasks_count AS WaitCount,

100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage

FROM sys.dm_os_wait_stats

–/* 不須觀察的系統等待事件

WHERE wait_type NOT IN (

‘BROKER_EVENTHANDLER’,’BROKER_RECEIVE_WAITFOR’,’BROKER_TASK_STOP’,’BROKER_TO_FLUSH’,’BROKER_TRANSMITTER’,

‘CHECKPOINT_QUEUE’, ‘CLR_AUTO_EVENT’,’CLR_MANUAL_EVENT’,’CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’,

‘DBMIRRORING_CMD’,’DBMIRROR_EVENTS_QUEUE’, ‘DISPATCHER_QUEUE_SEMAPHORE’,

‘FT_IFTS_SCHEDULER_IDLE_WAIT’, ‘FT_IFTSHC_MUTEX’,

‘LOGMGR_QUEUE’, ‘ONDEMAND_TASK_QUEUE’,

‘REQUEST_FOR_DEADLOCK_SEARCH’,’RESOURCE_QUEUE’,

‘SLEEP_BPOOL_FLUSH’,’SLEEP_TASK’,’SLEEP_SYSTEMTASK’,’SQLTRACE_BUFFER_FLUSH’,’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,’SQLTRACE_LOCK’,’SQLTRACE_WAIT_ENTRIES’,

‘TRACEWRITE’,

‘WAITFOR’,

‘XE_DISPATCHER_JOIN’, ‘XE_DISPATCHER_WAIT’,’XE_TIMER_EVENT’)

–*/

)

SELECT

W1.wait_type AS 等待類型,

CAST (W1.WaitS AS DECIMAL(14, 2)) AS [等待(秒)],

CAST (W1.ResourceS AS DECIMAL(14, 2)) AS [等待資源(秒)],

CAST (W1.SignalS AS DECIMAL(14, 2)) AS [執行緒獲得資源進入 runnable queue到開始執行的時間(秒)],

W1.WaitCount AS 等待次數,

CAST (W1.Percentage AS DECIMAL(4, 2)) AS 等待時間所占全部等待百分率,

CAST ((W1.WaitS / CASE WHEN W1.WaitCount=0 THEN 1 ELSE W1.WaitCount END) AS DECIMAL (14, 4)) AS [平均等待時間(秒)],

CAST ((W1.ResourceS / CASE WHEN W1.WaitCount=0 THEN 1 ELSE W1.WaitCount END) AS DECIMAL (14, 4)) AS [平均等待資源(秒)],

CAST ((W1.SignalS / CASE WHEN W1.WaitCount=0 THEN 1 ELSE W1.WaitCount END) AS DECIMAL (14, 4)) AS [平均執行緒獲得資源進入 runnable queue到開始執行的時間(秒)]

FROM Waits AS W1

ORDER BY W1.WaitS DESC

 

各種等待的說明

http://cid-bf14192bd27975cb.office.live.com/view.aspx/Source/SQL/SQL%20Server%20%20Wait%20Type%20Table.xlsx

一篇非常好的白皮書SQL Server 2005 Waits and Queues

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: