以動態管理物件觀察SQL Server(3)
監控資料庫運用運算與IO資源狀態
以動態管理物件觀察 SQL Server(3) 監控資料庫運用運算與IO資源狀態 上一篇文章中，筆者介紹了如何透過「sys.dm_exec_cached_plans 動態管理檢視」分析 SQL Server 所快取的執行計畫，並查詢 sys.dm_tran_session_transactions、sys.dm_tran_active_transactions…等動態管理檢視；了解當下的交易狀況。本文，將繼續介紹透過動態管理檢視觀察 CPU 和硬碟的使用與儲存狀況。

若要了解CPU的使用狀況，一般利用 Windows 作業系統提供的「工作管理員」、「效能監視器」或 SQL Server Management Studio 的「活動監視器」等，皆有類似「%處理器時間」計數器呈現 CPU 的忙碌狀況，你可以輕易識別 sqlservr.exe(SQL Server 服務執行檔)是否耗用了大量的 CPU。但除了知道 CPU 忙碌與否外，在此，透過 sys.dm_os_schedulers 動態管理檢視進一步觀察 SQL Server 如何配置 CPU 工作。

SQL Server 藉由 User Mode Scheduler (UMS)來控制如何執行使用者需求。SQL Server 會為每個處理器配置一個 UMS，每個 UMS 使用多種資料結構(例如：佇列、清單和工作執行緒)以管理並執行需求。

在當下時間中，每個 UMS 只能有一個正在運行的需求，其他需求不是落在「可運行佇列(runnable queue) 」以等待 CPU，就是處於「waiter list」以等待資源(如 I/O、鎖定、記憶體…等)，或是在「工作佇列(work queue)」中等待工作執行緒。

「可運行佇列」可類比結帳櫃台前的排隊。服務生是 CPU。同一時間只能有一個客戶結帳，因此隊排得越長，表示 CPU 壓力越大。以一個 CPU 配一個 UMS 為例，假若 SPID 51 正在運行。於「可運行佇列」中等待的有 Spid 52、53。「Waiter List」中有 SPID 54、55。「工作佇列」為空。

現在，假定正在運行的 SPID 51 需要實體的 I/O，則 SPID 51 移到 Waiter List 中，直到完成 I/O。CPU 開始執行「可運行佇列」中的下一個 SPID 52。當 SPID 51 所需的 I/O 完成，SPID 51 將會移到可運行佇列的底部，該佇列也就變成 SPID 53、51。

若要觀察上述的「排程器(Scheduler)」可透過 sys.dm_os_schedulers 動態管理檢視，它針對 SQL Server 中的每個「排程器」各傳回一筆資料列，其中每個排程器都會對應至個別的邏輯 CPU(不管是多核或是模擬出來的 CPU，都有一個排程器)。你可利用 sys.dm_os_schedulers 動態管理檢視來監視排程器的狀況或識別失控的工作。

在此簡單做個實驗，透過 Join 與多次迴圈來消耗 CPU，以此檢視 SQL Server 是否在忙碌地使用 CPU，比較 sys.dm_os_schedulers 動態管理檢視所觀察到的差異。首先是寫一段重複耗 CPU 運算的迴圈，如範例程式碼 1 所示。


▲範例程式碼 1：透過迴圈搭配資料表 Join，耗費 CPU 運算

在範例的一開始先檢查 tempdb 系統資料庫內 tblFlag 資料表是否有資料，若無，則藉由迴圈重複地耗用 CPU 資源。簡單利用如下的語法在 tempdb 系統資料庫內建立一個當作旗標的資料表：




接著利用命令提示列的指令可叫起多個 sqlcmd 工具程式，以執行 T-SQL 的批次檔案，在此透過雙迴圈的方式，在內層經由 sqlcmd 工具程式執行範例程式碼 1 的 T-SQL 語法：「FOR %%i IN (1 2 3 4 5) DO FOR %%j IN (1 2 3 4 5 6 7 8) DO start sqlcmd -i waste.sql」。

你可以自行修改內圈或外圈的迴圈數量，因為筆者的筆電是 8 顆 CPU，我想試試是否會平均分派每個CPU 執行個別的工作，故內圈產生 8 個 sqlcmd，再乘以外圈的數量。上述執行批次的方式可以營造非常大 SQL Server 的 CPU 壓力。

在實際執行上述語法前，先透過 sys.dm_os_schedulers 動態管理檢視查詢每個排程器的工工作量：




執行結果如圖 1 所示：


▲圖1：SQL Server 閒著無事時，觀察 sys.dm_os_schedulers 所呈現排程器的工作量

圖 1 中，所有用來執行一般查詢的排程器，其排程器ID都小於 1048576。大於或等於 1048576 的排程器是 SQL Server 內部使用，例如為「專用管理員連接(dedicated administrator connection DAC) 」保留的排程器，其status 欄位(在此，筆者呈現為「使用者或內部」)的值為「VISIBLE ONLINE(DAC)」。

從圖 1 可以看到雖然筆者並未存取本機上的 SQL Server，但「關聯的工作(current_tasks_count)」依然有著大於零的數值。與某個排程器關聯的工作數量包含下列項目：
●等候工作者(Worker，參考註1)執行的工作(Task)。
●目前正在等候或執行的工作(處於 SUSPENDED 或 RUNNABLE 狀態)。

圖 1中共有 41 個工作。除了使用者要求外，SQL Server 自身也有許多常態地管理資源工作，例如：RESOURCE MONITOR (每個 NUMA 節點一個)、LAZY WRITER (每個 NUMA 節點一個)、LOCK MONITOR、CHECKPOINT 和 LOG WRITER…等。

代表 DAC 的排程器有 3 個與其相關聯的工作者。這些工作者是在 SQL Server 啟動時配置，而且不會變更。這些工作者只能用來處理 DAC 查詢。此排程器上的兩項工作代表連接管理員和閒置工作者。

從圖 1 可觀察到，一般閒置時間雖然仍有許多背景工作存取 SQL Server，並有許多的工作連接和執行緒，但大部分的 CPU 是閒著無事(is_idle 欄位值為 1)。接下來，執行上述的批次指令，執行大量的 sqlcmd 工具程式傳遞命令給 SQL Server，並再次透過 sys.dm_os_schedulers動態管理檢視觀察，得到的結果如圖 2 所示：


▲圖2：SQL Server 忙碌時，觀察 sys.dm_os_schedulers 所呈現排程器的工作量

你同時可以簡單觀察 Windows 系統的「工作管理員」，將會看到 SQL Server 幾乎耗掉了所有的 CPU。

圖 2 中，排程器的工作者(current_tasks_count)呈現當前運行的工作數量。但是，更要注意的是等候CPU 的工作者(runnable_tasks_count)。如果這個數值高表示大量的工作正在等待執行，圖 2 呈現了明確的 CPU 壓力。加總等候CPU 的工作者(runnable_tasks_count)欄位可得到 33，而先前批次執行 sqlcmd.exe 的量是 5*8=40。

筆者的電腦有 8 顆邏輯 CPU，因此當下共有 8+33=41 個工作需要 CPU 執行，8 個工作正在執行，有 33 個在等待。

此外，sys.dm_os_schedulers 動態管理檢視的 pending_disk_io_count 欄位呈現等待 I/O 的數量，這個數值預期不會很高。而這次測試模擬的壓力並未造成 I/O 壓力，形成壓力的前後該欄位都是 0，筆者也就不列在圖中。

一般當我們說這些數字是高還是低並無太大意義，除非建立比較的基準線，或任何其他已知的計數，才能判斷數字。你最好在關鍵的時間點觀察基準值，以了解工作量的分布。 另一個觀察 CPU 執行狀況的指標是等待到資源後，要等待多久才能被 CPU 執行，這可以查詢 sys.dm_os_wait_stats 動態管理檢：




若透過上一篇文章所介紹的 sys.dm_os_wait_stats 動態管理檢視觀察最耗資源的等待，將會發現 SOS_SCHEDULER_YIELD 等候類型大幅增加，相關說明請參閱：「http://blogs.msdn.com/b/sqlcat/archive/2005/09/05/461199.aspx」。

由於在執行此測試之前，可能 SQL Server 已經累積了許多其他的等待，所以等待 CPU% 可能不高，你可以持續一段時間重複執行，將會看到比例持續增加。當然，若你批次測試加的壓力不夠大，也就不會有 CPU 等待。

最後，若要停掉前述消耗 CPU 的測試工作，僅需簡單新增一筆記錄到 tblFlag 資料表即可：「insert tblFlag values(1)」。

硬碟使用

檢視完 CPU 的使用後，接下來看硬碟的執行。首先檢視存放在硬碟上的資料表(堆積heap或叢集索引，參考註2)和索引的片段(fragmentation)程度。

資料片段

SQL Server 的資料庫用了一段時間後，因為增、刪、修而導致存放在硬碟的資料破碎不連續，資料散在硬碟磁盤上將會導致讀取的效能不佳。資料不連續分為兩種，一是內部不連續(Internal fragmentation)，意思是實體的 8K 位元組資料分頁中有許多空間沒有擺放資料記錄。

另一是外部不連續(External fragmentation，也成邏輯不連續)，代表實際硬碟上擺放分頁與延伸(Extend，連續 8 個分頁，是 SQL 實際的讀取單位)不連續，也就是索引或資料表可能散落在多個延伸中(註3)，以及存放索引或資料表的分頁不是實體連續地配置在一起。另一種外部不連續的狀況是邏輯上的資料順序與實體在硬碟擺放的分頁順序不同，兩種外部不連續都可能會讓硬碟讀取時沒有效率，讀出來的資料還需要重新整理。

資料不連續對於異動的行為並無不妥，因為要對資料做新增、修改時可以直接使用該頁的空間，也因此才會在建立索引時透過 FILLFACTOR 與 PAD_INDEX 選項來增加索引中的空間，藉以避免在新增、修改時因為分頁沒有空間而需要做分割(split)的動作。但若使用資料表主要的方式是查詢，則不連續代表讀取分頁時會存取到不具內容的空白，徒增搜尋硬碟的成本。

外部不連續是在 SQL Server 需要做大量的資料循序掃描(Scan)時才會影響效率，若僅僅是透過索引搜尋某些記錄，則反正都是透過指標取得分頁，不會對搜尋有太大的影響。

以往，可以透過 DBCC SHOWCONTIG 指令觀察資料片段的狀況。SQL Server 2005 以後，改以 sys.dm_db_index_physical_stats 動態管理函數來分析資料不連續的嚴重程度，傳回指定資料表或檢視之資料和索引的大小和片段資訊。

如果是索引，則針對每個資料分割中每個層級的 B-Tree 目錄，各傳回一筆資料列。如果是堆積，則針對每個資料分割中的 IN_ROW_DATA 配置單位，各傳回一筆資料列。如果是大型物件 (LOB)，則針對每個資料分割中的 LOB_DATA 配置單位，各傳回一筆資料列。

如果資料表中有資料列溢位資料，則針對每個資料分割中的 ROW_OVERFLOW_DATA 配置單位，各傳回一筆資料列。詳細的 sys.dm_db_index_physical_stats 動態管理函數之用法與回傳的資料欄位定義，請參照 SQL Server 的線上說明，在此不重複贅述。

我們依然透過練習來驗證資料不連續的狀況。首先藉由以下的語法建立資料表與索引，並產生一定的資料量：




為了要營造很大的索引資料結構，在此故意宣告固定長度字元欄位 c1 為 894 byte，加上整數欄位 id 為 4 byte，故索引 idx 一筆記錄為 SQL Server 2008 R2 版本為止，索引可接受的最大長度 900 byte。

新增了 4000 筆記錄後，透過 sys.dm_db_index_physical_stats 動態管理函數觀察資料片段的狀況：




建完資料表並新增一定量的記錄數後，直接查詢上述的資料不連續狀況，可以發現已經有了邏輯資料不連續，並因為資料分頁預設保留空間存放新增、修改的記錄，所以兩種資料不連續的狀況皆有。


▲圖3：直接新增資料所產生的資料不連續

圖 3 所傳回來的記錄中，自訂的索引 idx 從最底層的索引資料頁(子葉層) 0 到最上層 4，共有 5 層，所以索引 idx 傳回 5 筆記錄。就算指定 100% 的 fillfactor 和 pad_index，在資料表擺放資料或建置索引時，每個資料頁依然會保留空間。從「頁面資料平均%(avg_page_space_used_in_percent)」欄位可以看到資料分頁內的片段，而「邏輯片段%( avg_fragmentation_in_percent)」則呈現了外部分頁間的片段狀況。

「片段數」代表同一個物件上一個分頁到下一個分頁並非連續。例如，上一個分頁 ID 是 100，下一個是 103，接著是 104、90。則四個分頁中有兩個不連續，分別是 100 到 103 和 104 到 90。針對範例中的資料表 t，可以藉由 DBCC IND 指令(參考註4)觀察「片段數(fragment_count)」：「DBCC IND(tempdb,t,1)」。

要消除不連續，可以透過 ALTER 語法搭配 REBUILD 選項，範例如下：




以上述語法移除邏輯和資料頁內部的不連續後，再查詢一次 sys.dm_db_index_physical_stats 動態管理函數，可以發現兩種資料片段都已經移除，統計資訊如圖 4 所示：


▲圖4：移除資料不連續後的統計

由於 Rebuild 讓資料分頁盡量填滿，造成所需要的索引頁減少，進一步減低了非子頁層索引的層級數，因此圖 3 索引的四層非子頁層到圖 4 時減少了一層。 接著，透過刪除和新增製造邏輯不連續，指令碼如下：




或是刪除頁內個別幾筆記錄，造成每個頁面的內部資料不連續：「delete t where ID%3 =0」，重新查詢資料不連續的狀況如圖 5 所示：


▲圖5：增刪資料造成存放資料不連續的狀況

從圖 5 可以看到兩種資料片段的狀況都變嚴重了。

轉送指標記錄

當資料表為堆積，也就是沒有叢集索引，會造成另一種轉送指標記錄(forwarded record)類型的資料破碎。將資料記錄新增的資料表堆積時，沒有任何順序，放滿了一個 8 K 位元組的資料分頁，就再配置下一個分頁，持續地附加資料。

做個簡單的練習如下：




透過sys.dm_db_index_physical_stats 動態管理函數，可以觀察到連續新增記錄，並未有產生轉送指標記錄：




查詢結果如圖 6 所示：


▲圖6：剛新增完資料時，堆積內沒有轉送指標的記錄況

圖 6 中雖有片段，但 forwarded_record_count 欄位為 0，代表堆積內沒有轉送指標的記錄。由於範例中 C2 欄位是宣告成變動長度 varchar(100)，所以當長度改變但該資料分頁無法存放時，在堆積形式的資料表會改以轉送指標的記錄，將該筆記錄的內容指向到另外一個實際擺放變大後記錄資料之分頁。

透過語法改變資料內容長度：「update t2 set c2=replicate('a',100)」，再一次以 sys.dm_db_index_physical_stats 動態管理函數觀察轉送指標的記錄量，其結果如圖 7 所示：


▲圖7：更新後，因資料分頁無法擺放變大的記錄，大量產生轉送指標記錄

透過 Windows 系統「效能監視器」的效能計數器「SQLServer:Access Methods」下的「Forward Record/sec」；可以觀察當上述修改，或簡單地如下查詢時，都會需要大量地存取轉送的資料分頁：「select * from t2」。

我們可以 DBCC PAGE 指令實際觀察轉送指標記錄是如何存放在資料分頁中。首先，透過 sys.sysindexes 系統檢視可以查到資料表或索引的第一個資料分頁，由於其檢視的 first 欄位是以 binary(6) 格式存放，前四個 byte 代表資料庫檔案內第幾個分頁，後兩個 byte 代表資料庫的第幾個檔案。因此透過如下的語法解析其資料分頁位置：




上述語法的查詢結果如圖 8 所示：


▲圖8：查詢資料表或索引實際存放記錄的第一個分頁

知道實體擺放資料的第一個分頁所在位置，也就是圖 8 的「第一個資料頁」欄位所呈現的：資料庫所屬第一個檔案，其上的第 375 個資料分頁。就可以透過 DBCC PAGE 指令檢視該分頁內容，指令如下：




透過 DBCC TRACEON(3604) 開啟 3604 旗標，才會讓 DBCC PAGE 指令執行的結果輸出到銀幕。透過 DBCC PAGE(tempdb,1,375,3) 指令呈現 tempdb 資料庫內，第 1 個檔案、第 375 個資料分頁，第 3 種檢視方式，可以輸出如圖 9 的結果：


▲圖9：透過 DBCC 實際檢視資料分頁的內容，觀察轉送指標記錄

圖 9 的上半部呈現資料頁的第 5 筆記錄(slot 4，slot 從 0 開始計數) c2 欄位依然是放在分頁內，但 slot 5 便透過「Forwarding to」記載 c2 欄位放在「file 1 page 248 slot 145」的位置。

若針對資料表建立叢集索引，則在上述將既有記錄內容變大，超過原來分頁可以存放的空間時，會發生「Page Split」，也就是重新配一個資料分頁，將原來分頁的記錄分一半到新的分頁，以空出空間存放新資料。你可以針對上述的測試資料表建立叢集索引，新增大量記錄放滿多個分頁後，再將可變動欄位的資料長度從一開始新增的1位元組改成較長的位元組，同時觀察效能計數器 SQLServer:Access Methods 下的Page Split/sec，將會看到大量的 Page Split 事件。

換句話說，在大量修改資料時，若單一分頁無法容納可變動資料長度欄位擴增，因為堆積僅是在另一個分頁寫入新值，並在原記錄位置放轉送指標，相對於叢集索引要做 page split，堆積的效能較佳。但若要查詢該記錄，則因為要多讀取轉送指標記錄而效能較差。

最後，提醒一點的是由於重建或重整資料表/索引是很耗資源的動作，故SQL Server 線上說明中，針對 sys.dm_db_index_physical_stats 動態管理函數的「範例D」提供以指令碼來重建或重新組織索引，它以資料分割的平均片段(依據 avg_fragmentation_in_percent 欄位) 10% 為門檻，大於等於 10% 小於 30% 的索引進行重整，大於 30% 則進行重建。

註釋
1. 關於工作者的細節，可以參看 sys.dm_os_workers 動態管理檢視。
2. 若資料表有建立叢集索引，則資料紀錄照著叢集索引的鍵值有序擺放，該叢集索引就是資料表本身。若資料表沒有建索引，資料紀錄任意擺放，則稱為堆積。
3. 不是資料表或索引依順序放滿一個延伸(8 個資料分頁)後，就接續放下一個延伸。而是延伸內混著不同物件，或是順序錯亂。
4. 參考：http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/what-exactly-is-fragment_count。