告訴我, 究竟我的SQL Server慢在哪裡?

標籤：des   style   blog   http   ar   io   color   os   使用   

你可以使用下面的語句來使用sys.dm_os_wait_stats這個DMV得到線程的等待資訊(線程在等什麼, 等了多久)的統計數值.

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],        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]    FROM sys.dm_os_wait_stats    WHERE [wait_type] NOT IN (        N‘BROKER_EVENTHANDLER‘,             N‘BROKER_RECEIVE_WAITFOR‘,        N‘BROKER_TASK_STOP‘,                N‘BROKER_TO_FLUSH‘,        N‘BROKER_TRANSMITTER‘,              N‘CHECKPOINT_QUEUE‘,        N‘CHKPT‘,                           N‘CLR_AUTO_EVENT‘,        N‘CLR_MANUAL_EVENT‘,                N‘CLR_SEMAPHORE‘,        N‘DBMIRROR_DBM_EVENT‘,              N‘DBMIRROR_EVENTS_QUEUE‘,        N‘DBMIRROR_WORKER_QUEUE‘,           N‘DBMIRRORING_CMD‘,        N‘DIRTY_PAGE_POLL‘,                 N‘DISPATCHER_QUEUE_SEMAPHORE‘,        N‘EXECSYNC‘,                        N‘FSAGENT‘,        N‘FT_IFTS_SCHEDULER_IDLE_WAIT‘,     N‘FT_IFTSHC_MUTEX‘,        N‘HADR_CLUSAPI_CALL‘,               N‘HADR_FILESTREAM_IOMGR_IOCOMPLETION‘,        N‘HADR_LOGCAPTURE_WAIT‘,            N‘HADR_NOTIFICATION_DEQUEUE‘,        N‘HADR_TIMER_TASK‘,                 N‘HADR_WORK_QUEUE‘,        N‘KSOURCE_WAKEUP‘,                  N‘LAZYWRITER_SLEEP‘,        N‘LOGMGR_QUEUE‘,                    N‘ONDEMAND_TASK_QUEUE‘,        N‘PWAIT_ALL_COMPONENTS_INITIALIZED‘,        N‘QDS_PERSIST_TASK_MAIN_LOOP_SLEEP‘,        N‘QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP‘,        N‘REQUEST_FOR_DEADLOCK_SEARCH‘,     N‘RESOURCE_QUEUE‘,        N‘SERVER_IDLE_CHECK‘,               N‘SLEEP_BPOOL_FLUSH‘,        N‘SLEEP_DBSTARTUP‘,                 N‘SLEEP_DCOMSTARTUP‘,        N‘SLEEP_MASTERDBREADY‘,             N‘SLEEP_MASTERMDREADY‘,        N‘SLEEP_MASTERUPGRADED‘,            N‘SLEEP_MSDBSTARTUP‘,        N‘SLEEP_SYSTEMTASK‘,                N‘SLEEP_TASK‘,        N‘SLEEP_TEMPDBSTARTUP‘,             N‘SNI_HTTP_ACCEPT‘,        N‘SP_SERVER_DIAGNOSTICS_SLEEP‘,     N‘SQLTRACE_BUFFER_FLUSH‘,        N‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP‘,        N‘SQLTRACE_WAIT_ENTRIES‘,           N‘WAIT_FOR_RESULTS‘,        N‘WAITFOR‘,                         N‘WAITFOR_TASKSHUTDOWN‘,        N‘WAIT_XTP_HOST_WAIT‘,              N‘WAIT_XTP_OFFLINE_CKPT_NEW_LOG‘,        N‘WAIT_XTP_CKPT_CLOSE‘,             N‘XE_DISPATCHER_JOIN‘,        N‘XE_DISPATCHER_WAIT‘,              N‘XE_TIMER_EVENT‘)    AND [waiting_tasks_count] > 0 )SELECT    MAX ([W1].[wait_type]) AS [WaitType],    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],    MAX ([W1].[WaitCount]) AS [WaitCount],    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]FROM [Waits] AS [W1]INNER JOIN [Waits] AS [W2]    ON [W2].[RowNum] <= [W1].[RowNum]GROUP BY [W1].[RowNum]HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage thresholdGO

一些說明:

SQL Server會永久地跟蹤為什麼執行的線程會處於等待狀態. 你可以從SQL Server中得到這些資訊, 然後縮小效能問題的調查範圍.

有些人一開始調查效能問題就會看幾次當下的線程在等待著什麼, 然後嘗試去弄清楚為什麼. 而事實上是, ‘等待‘這回事兒是always發生的, SQL的scheduleing系統就是這麼工作的. 下面讓我們來看一下scheduleing大致的工作流程吧.

正在使用CPU的線程(處於RUNNING狀態)會在它需要某種資源的時候停下來. 該線程會被移到一個叫做SUSPENDED的線程的無序列表中. 同時, 在RUNNABLE隊列(先進先出)中等待CPU的下一個線程會變為RUNNING狀態. 如果在SUSPENDED列表中的一個線程得到通知說它想要的資源可用了, 它就會轉到RUNNABLE隊列中的底部. 就這樣, 線程們像鐘錶一樣的從RUNNING到SUSPENDED, 在到RUNNABLE, 再到RUNNING的迴圈, 直到任務完成.

SQL Server跟蹤從離開RUNNING狀態到再回到RUNNING狀態的時間(叫做wait time), 和花在RUNNABLE隊列中的時間(叫做signal wait time). 我們需要得到在SUSPENDED隊列中的時間, 方法就是從整個的wait time中減掉signal wait time.

 

下面節選了一些常見的等待類型, 並作出解釋. 更多等待類型的資訊請看原文.

• PAGEIOLATCH_XX: 線程正在等待一個資料page從磁碟中讀到buffer pool中.
• LCK_M_X: 線程正在等待被在某資源上賦予一個獨佔鎖定.
• CXPACKET: 並行線程中某一個線程在執行時, 整個query都會被block, 這時該數值會被累加. 具體見原文.
• WRITELOG: 日誌管理系統在等待log被flush到磁碟上.

 

資訊來源

=========================

Wait statistics, or please tell me where it hurts

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

告訴我, 究竟我的SQL Server慢在哪裡?