以動態管理物件觀察 SQL Server(2)─
找到最耗資源的執行計畫
在上一篇文章中，筆者介紹了如何透過 sys.dm_os_memory_clerks 動態管理檢視分析 SQL Server 的記憶使用分布，接下來繼續分析會大量使用記憶體的執行計畫快取當下耗用的情形。

首先，藉由 sys.dm_exec_cached_plans 動態管理檢視可以看到各個執行計畫快取。若要了解這些執行計畫的內容與 T-SQL 語法，可以透過以下的查詢：




透過上述三個動態管理物件可以傳回執行計畫的使用狀況、執行計畫內容與產生該執行計畫的T-SQL批次文字，其功能簡述如下：
●sys.dm_exec_cached_plans 動態管理檢視：針對快取的各個執行計畫分別傳回一筆記錄，呈現快取計畫的類型、所使用的記憶體、重複使用計數…等。
●sys.dm_exec_query_plan 動態管理函數：針對計畫控制代碼指定的批次，以 XML 格式傳回顯示計畫。


▲ 圖1：透過 SQL Server 2008 R2 Management Studio，直接圖形化呈現查詢結果回傳的 XML 格式描述之執行計畫

●sys.dm_exec_sql_text：傳回指定的 sql_handle 的 T-SQL 批次文字。

若要進行對執行計畫快取的實驗，可以透過如下的 DBCC 語法先清空記憶體中全部的執行計畫快取，或是跟某個資料庫內物件有關的執行計畫快取：




在此簡單做一個實驗，先在資料庫內建立一個執行動態組 T-SQL 語法的預存程序：




而後清掉執行計畫快取，再分別執行兩次預存程序；以及相同查詢語法但大小寫不同的 T-SQL 查詢：




其查詢 SQL Server 執行個體當下執行計畫的結果如圖 2 所示。


▲ 圖2：分析執行計畫快取的內容

圖 2中可以看到預存程序 sp 只在記憶體中產生一份計畫快取，並被叫用兩次(參考 usecounts 欄位)，但不管是在預存程序內組字串，或是前端應用程式組字串，只要內容稍有不同，如範例中select count(*) from customers 語法因為 select 關鍵字大小寫不同，或是在預存程序內組字串搭配不同的 where 條件，而產生不同的執行計畫。

結論是，若你的應用程式常常組字串，或是在預存程序內透過組字串的方式，交由 SQL Server 執行，則有可能會耗用掉大量的 CPU 運算力與執行計畫快取，以找尋和儲存執行計畫。

接下來，若想要了解你的 SQL Server 使用各個執行計畫次數的分布，可以執行如下的語法：




執行結果類似圖 3 所示。


▲ 圖3：分析重複使用執行計畫次數的分布

圖 3 代表在筆者的機器上，僅執行過一次的 SQL 語法執行計畫共有 174 個，執行過 2 次的有115 個，最高執行次數的某個執行計畫被叫用過 4059 次。也可以透過如下的查詢，檢視各物件類型的執行計畫重用狀況：




執行結果類似圖 4 所示。


▲ 圖4：檢視各物件類型的執行計畫重用狀況

若你的執行計畫大量是來自動態組的語法，且都是使用一次就不再用的，為了避免佔執行計畫快取空間，可以藉由「optimize for ad hoc workloads」設定減少記憶體耗用：




「optimize for ad hoc workloads」設定讓 SQL Server 在第一次執行 ad-hoc 查詢語法時，原本執行計畫快取區要放置找到的執行計畫定義，若設為 1 會使 SQL Server 只在快取區放個旗標，當再次執行到該語法時，才放入真的執行計畫內容。簡單實驗如下：




在上述範例中，透過 go 可以讓相同的批次重複執行 n 次，上述同樣的 select 語法，僅是大小寫不同，將會產生兩個執行計畫，而重複執行 2 次的查詢，才會在 query_plan 欄位有定義，如圖 5 所示。


▲ 圖5：相同的 ad-hoc 查詢語法執行兩次以上才會快取其執行計畫

圖 5 中可以看到大寫 SELECT 的 T-SQL 語法因為只執行過一次，所以在快取區中並未放置計畫內容，因此 query_plan 欄位是空的。但若重複執行第二次，則會將計畫放入。所以，若你的執行計畫大多有重複使用，則不需要啟動這個設定。若多是僅由前端程式組織語法執行一次即不會再傳入相同語法，則可以啟動這個設定。

交易管理

SQL Server 一般最常見的效能問題，一是索引，另一是交易管理。索引的建置與使用要分析先前所討論的執行計畫，以及本系列之後要討論的累積執行狀況統計中，關於索引的使用分析，是否過多或不足。在此，先討論如何觀察當下的交易。

SQL Server 提供了許多觀察交易狀況的動態管理物件，它們的名稱以 sys.dm_tran_ 為字首。例如，若要檢視當下各連接與交易的關係，可簡單執行如下的查詢：
select * from sys.dm_tran_session_transactions

先利用 SQL Server Management Studio 的另外一條連接執行如下開啟交易的語法：




查詢sys.dm_tran_session_transactions 動態管理檢視的結果如圖 6 所示。


▲ 圖6：透過sys.dm_tran_session_transactions 動態管理檢視查詢 Session 上的交易

從圖 6 可以看到 sys.dm_tran_session_transactions 動態管理檢視傳回當下有交易的 session，透過此欄可以查到前端程式的行為。以及是使用者發起還是系統發起的交易、本地交易還是分散式交易等。

若要查詢某個交易的執行狀況，可以透過 sys.dm_tran_active_transactions 系統動態檢視：




上述查詢的執行結果如圖 7 所示。


▲ 圖7：檢視交易的執行狀況

由於前述開啟交易的批次語法中，有賦予交易名稱 testTran，所以圖 7 查詢結果的 name 欄位可以透過該名稱辨識交易。圖 7 中「name」欄位內容為「worktable」的交易是 SQL Server 內部使用的資料結構(例如用在排序、連結…等)，由 SQL Server 自己維護，若不想看到這些記錄，可以透過 where 條件過濾掉。

若要查詢某個交易所耗用資料庫交易記錄(Transaction Log)的量，可以透過sys.dm_tran_database_transactions 系統檢視。在此，先執行一段用來產生測試效果的 T-SQL 語法：




上述語法開啟交易後，大量新增資料，但不執行 Commit 或 Rollback 語法結束交易，則這些資料都會因為使用的是暫存資料表而留在 tempdb 系統資料庫的交易記錄內，並非是連接所落在的 Northwind 資料庫。結合先前的動態管理物件，整合的查詢語法如下：




執行結果如圖 8 所示。


▲ 圖8：查詢當下交易所耗資料庫的交易記錄空間

從圖 8 中可以看到先前的交易約耗用了 tempdb 資料庫 63 K 的資料量。

交易管理為了滿足ACID的需求，必須以鎖定來同步多個同時執行的前端存取，這往往造成被鎖定，而讓部分的程式長時間等待資源。（ACID 簡而言之是：完整性(Atomicity)，不是全部執行，就是全部不執行。一致性 (Consistency)，交易完成時，全部的資料必須維持一致的狀態。隔離性(Isolation)保證並行的交易不互相干擾。耐久性(Durability)，當交易完成後便永遠存在於系統之中，即使發生災難仍會保存。）

鎖定

若要了解當下的鎖定狀況，可以透過 sp_lock 系統預存程序，或是 sp_lock 其內查詢的master.dbo.syslockinfo 系統檢視。但線上說明強調此 SQL Server 2000 以來提供的系統資料表；在 SQL Server 2005/2008/R2 以同名檢視提供是為了回溯相容性，建議改用sys.dm_tran_locks 動態管理檢視。未來的 SQL Server 版本將移除 syslockinfo，請避免在新的開發工作中使用這項功能。

sys.dm_tran_locks 動態管理檢視傳回的每筆資料列，都代表一個已經授與或者等待授與的鎖定。其欄位眾多，有著豐富的鎖定資訊，你可以參看線上說明。在此，先用兩條連接執行造成鎖定與被鎖定的情形：




用另外一條連接執行將會被鎖定的查詢：
SELECT * FROM Customers

透過如下的查詢，以 sys.dm_tran_locks 動態管理檢視觀察前兩條連接的執行狀況：




執行結果如圖 9 所示。


▲ 圖9：透過 sys.dm_tran_locks 動態管理檢視觀察 SQL Server 當下的鎖定狀況

若要檢視與前端應用程式的關係，則可以連結 dm_exec_requests、dm_exec_sessions、sys.dm_exec_connections…等動態管理物件，以獲得綜合的資訊。例如，分析鎖定與被鎖定之間的關係，此種一個 session 鎖住另一些 session，被鎖住的 session 又鎖住其他的 session 狀況往往是系統效能的大忌：




透過 sys.dm_exec_requests 動態管理檢視的 session_id, blocking_session_id 兩個欄位可以取得正在執行語法的 session，以及被哪個 session 鎖住，藉由通用資料表運算式(CTE)將兩者間的互相參照遞迴關係展開，可以分析連串的鎖定鏈結，上述語法執行結果如圖 10 所示。


▲ 圖10：結合多個動態管理物件分析鎖定與被鎖定之間的關係

由於筆者故意利用多條連接執行互相鎖定的查詢，從圖 10 可以看到 Session 66 同時鎖住了 60 和 67，而 Session 70 鎖住了 57，Session 57 又鎖住了 55 等。

為了避免查詢結果過於複雜，筆者將上述查詢中關於 sys.dm_tran_locks 動態管理檢視的部分標成註解，但你若需要鎖定的細節，可以將其還回成一般語法，以及連結 dm_exec_sessions 等其他動態管理檢視，進一步分析前端應用程式、登入者…等資訊。

鎖定通常是 SQL Server 平行執行多個前端需求時，前端程式彼此等待的資源。但除鎖定外，還有可能等待多種系統資源，而這是效能瓶頸分析中的一環。

SQL Server 等待的資源

若要診斷 SQL Server 的效能問題，懷疑是資源不足或是硬體效能不佳，可以透過sys.dm_os_wait_stats 系統管理檢視傳回執行中之執行緒所遇到之所有等候的相關資訊。憑這份彙總檢視可以了解 SQL Server 執行個體耗在各種等待所用的時間，其範例查詢如下：




上述查詢中先濾掉了一般不須觀察的系統等待事件，而後在筆者的機器上執行結果如圖 11 所示。


▲ 圖11：觀察 SQL Server 各種等待所耗用的時間

sys.dm_os_wait_stats 系統管理檢視之 wait_type 欄位呈現等候的類型，wait_time_ms 是等待被執行到真正執行的總時間。而等待又分為兩階段，一是等待資源(例如：鎖定、硬碟I/O、記憶體 I/O…等)，另一是拿到資源後等待 CPU 執行(累計在signal_wait_time_ms 欄位)。若要檢視等待資源所耗的時間，需以 wait_time_ms 欄位減掉 signal_wait_time_ms 欄位。

SQL Server 等候的類型可大分以下三種：
●資源等候：當某個工作執行緒要求存取無法獲得的資源時，因為該資源正被另一個工作執行緒使用，會發生資源等候。例如等候鎖定、閂鎖(LATCH)、網路、CPU 同步(CXPACKET)和磁碟 I/O 等資源。鎖定、閂鎖、CPU 同步之類的等候是同步處理所需。
●佇列等候：發生在工作者因為等候指派工作而閒置時。佇列等候大多發生在系統背景工作，例如，監視死結和刪除記錄清除工作。這些工作會等候該「工作要求」被置於「工作佇列」。
●外部等候：SQL Server 工作執行緒正在等候外部事件，例如，呼叫擴充預存程序或連結伺服器完成查詢時。當診斷封鎖問題時，外部等候不一定代表工作執行緒正在閒置，因為可能在執行一些外部程式碼。

反之，在下列任何一種情況下，SQL Server 工作執行緒都不算是在等候：
●資源可以使用。
●佇列不是空的。
●外部處理序完成。

若執行緒已經不在等候資源中，執行緒也不會立即開始執行。這類執行緒會先置於可執行之工作者佇列上，等候 CPU 排程器賦予某個配量執行。這時，是拿到資源後在等 CPU 執行，此種等候是累計在 sys.dm_os_wait_stats 系統管理檢視的 signal_wait_time_ms 欄位中。等候時間計數器是 bigint 值。

在執行查詢時，特定類型的等候時間可以指出查詢中的瓶頸。同樣地，等候時間很長或伺服器等候計數很大，也代表伺服器執行個體查詢間的互動瓶頸或熱點。例如，鎖定等候表示查詢在爭用資料；資料頁 I/O 閂鎖等候表示 I/O 回應時間很慢；資料頁閂鎖更新等候表示檔案配置不正確…等。

需注意的是，sys.dm_os_wait_stats 顯示已完成等候的時間。此動態管理檢視不會顯示目前的等候。而在 sys.dm_os_wait_stats 系統管理檢視的線上說明中，有一整個表格說明圖 11 中，各種等待類型的意義為何。

此外，統計資料在SQL Server 重新啟動之後都不會保存下來，而且所有的資料都是從上次統計資料重設或伺服器啟動之後開始累加計算。若你不想重啟 SQL Server 執行個體，但又想清除累計資料，可以執行下列命令，重設這份動態管理檢視的內容：
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

分析完了交易鎖定與資源等待，本文就介紹到此，接下來的文章中，筆者再繼續介紹如何觀察磁碟 I/O 與 CPU 使用狀況。