2017年4月29日 星期六

SQL Server 的 Waits

SQL命令(SQL Statement)是由CPU來執行,在執行的過程中,難以避免的會因為資源的調用、鎖定等的因素,造成執行過程中可能發生多次的「等待waits)」。因此一個命令的執行,從開始到完成,其回應時間,可以簡單的用下列公式來表示:

response time = service timeCPU + total wait time
回應時間 = 服務時間 + 合計等待時間服務時間指CPU真正用於執行命令的時間

SQLSERVER效能調校,最終無非是為了加快回應速度,降低回應時間。一個命令的執行,要不就是在CPU中運算,要不就是在「等待(Waits)」。瞭解系統到底在等什麼,是系統效能問題處理的基本。但它卻不常被人理解並且應用於效能問題排除。

我想先就個人所知,解釋一下SQLSERVER命令執行過程的狀態變化..

一個thread在未執行任何命令時,其狀態(status)會是sleeping。當執行命令時,系統會分配CPU資源給thread執行命令。當一個thread正在使用CPU時,狀態為RUNNING。直到它需要等待資源(如I/O,或將頁面從磁碟中讀取到buffer pool中,或遇到block需等待),它會被移動到一個無序的waiter list中,此時thread狀態將被標示為SUSPENDED。一個狀態為SUSPENDEDthread,代表它正在等候某個資源。

另外在系統中有個FIFO(先進先出)的隊列(Queue),其中存放著所有在等待CPUthread,這些存在於Queue中的thread,其狀態為RUNNABLE
當一個thread由於執行過程需要等待其它資源,將會被移至「waiter list」,並將其狀態標示為SUSPENDED,於此同時,處於隊列中(Queue)的下一個thread被賦予CPU並且變為RUNNING。如果SUSPENDED列表上的thread被通知資源可用,則它將變為RUNNABLE,並放在RUNNABLE隊列的後面,加入排隊。整個執行過程是螺旋狀的從RUNNINGSUSPENDEDRUNNABLE,直到任務完成為止。

SQLSERVER會給一個處於RUNNINGthread一個限量的時間(4ms),如果這個thread在這個限量的時間內沒有執行完畢。如果這個thread不需等待任何資源,則會將這個thread退回到Queue中(RUNNABLE,即使目前沒有其它的thread在等侯CPU也是一樣;然後系統會從Queue中取得下一個thread並執行。

如果一個thread4ms的限量執行時間內,因等待資源而無法繼續執行,那麼它會被標示為SUSPENDED並移到waiter list中,之後SQLSERVER一樣會從Queue中取得下一個thread執行。當那個等待的資源通知完成後,這個waiter listthread將被移至Queue隊列中等待.

一個thread
RUNNING所花費的時間(稱為:執行時間service time
RUNNABLE隊列上等待的時間(稱為:信號等待時間signal wait time
SUSPENDED列表中等待的時間(稱為:資源等待時間resource wait time

SQLSERVERR會永久性的跟蹤程紀錄執行過程中的「等待」資訊,包含「等待什麼(wait type)」及「等待時間(wait time)

一個thread的等待時間可以用下列公式表示:
total wait time = resource wait time + signal wait time
一個thread的回應時間可以用下列公式表示:
response time = service time + total wait time

可以透過sys.dm_os_waiting_taskssys.dm_exec_requests中查詢thread目前在等待什麼(wait type)及等待時間(wait time。當這個wait event結束後,該等待的資訊會從上述兩個DMV中移除(在sys.dm_exec_requests中會被轉移成lastwaittype),並將這個等待的型態及時間,加入sys.dm_os_wait_stats的統計資料中。
註:SOS_SCHEDULER_YIELD不會顯示在sys.dm_os_waiting_tasks中。

而任何的等待歷史,均會被紀錄在sys.dm_os_wait_stats。透過這個DMV,可以查詢各種等待類型(wait type)的累積等待時間,包含了訊號等待及資源等待。
sys.dm_os_wait_stats會在SQLSERVER啟動時歸零,或者我們也可以透過下列指令將其歸零:

dbcc sqlperf('sys.dm_os_wait_stats', clear)

在進行系統層級效能診斷時,可以查詢sys.dm_os_wait_stats,以取得系統自上次歸零以來至今的等待情況統計,根據這些最常發生的等待資訊,可以瞭解系統是否有資源使用上的瓶頸,並進行相對的優化。

在進行會話層級(session level)的效能診斷時可以使用sessiod_id查詢sys.dm_os_waiting_task或者sys.dm_exec_requests,以取得「目前」session的等待資訊。不過由於wait event結束後,該等待資訊即會從上述DMV中移除,所以只能看到目前的等待資訊(或者最後一次的等待資訊)。
註:SQLSERVER 2016有一個新的DMVsys.dm_exec_session_wait_stats」,可以紀錄session的各種等待資訊的彙總。

SQLSERVER 2016以前的版本,如果要知道session在執行命令過程中,究竟遇到了哪些等待,最佳方法是透過extented event來擷取session執行過程中的等待資訊,詳細的script,可以參考下列網址:
https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/


回應時間 = 服務時間 + 合計等待時間服務時間指CPU真正用於執行命令的時間

瞭解WaitsSQLSERVER效能調校重要的一環,但它卻經常被人所忽略。我個人的學習歷程是先知道waits的一些概念後,再逐步的去瞭解一些重要的wait type及其背後的含義,在面臨SQLSERVER系統效能問題時,可以先查出系統的等待統計資訊,如果有正確的理解,這些等待資訊將會告訴你系統瓶頸的所在。

SQLSERVER隨著版本的昇級,其內建的等待類(wait type)數目也跟著增加,以SQL2012而言,應該有5-6佰個之多,我們很難每個等待類型都瞭解。但有些重要且常見的等待類型,建議從事DBA工作者們必需要知道,並且最好能深入的理解。

沒有留言:

張貼留言