監測查詢效能（1）

標籤：style   blog   http   color   使用   strong   

在考慮採取最佳化行動之前（比如添加索引或非範式化），應該知道當前的查詢是怎樣被處理的，還應該有一些效能測量基準，這樣才能比較改動前後的效能。SQL Server提供了一些工具（SET 選項）來支援對查詢效能的監測；

• IO統計
• TIME統計
• PROFILE統計
• XML統計

在執行查詢前啟用SET選項，他們會產生相應的輸出。SQL Server Management Studio的工具|選項中可以設定IO統計和時間統計的開關。

也可以通過下面語句實現相同的功能。

SET STATISTICS IO ONGOSET STATISTICS TIME ONGO

注意：開啟統計之後，查詢結果需以文字格式設定顯示才能看見統計；

IO統計

“IO統計”這個選項統計了SQL Server為處理查詢做了多少工作。當這個選項開啟時，對一批查詢中的每一個有資料對象返回的查詢都有單獨一行的輸出（不進行資料訪問的查詢語句不會產生任何輸出，比如PRINT、SELECT變數的值或調用一個系統方法）。當IO統計選項啟用時，會輸出邏輯讀的次數、物理讀的次數、預讀的次數和掃描計數。

邏輯讀

這個數值指查詢所需反問頁的次數。對於任何給定的讀取操作，資料緩衝中的每一頁都會被讀取，但並不是所有的頁都是必須的，因為資料緩衝中的頁來至磁碟。此值總是至少和物理讀的值一樣大，但通常比物理讀的值大。同一頁可以被讀取多次（例如當一個查詢使用了索引時），所以對某一個表邏輯都的次數可以比該表的頁數大。

物理讀

這個數值指從磁碟讀取的頁數，該值總是小於或等於邏輯讀的值。由系統監測器顯示的快取命中率就是由邏輯讀和物理讀的次數計算得出的，公式如下：

快取命中率 = （邏輯讀 - 物理讀） / 邏輯讀

不要忘記物理讀的次數可以差別很大，並且第二次及後續執行時物理讀的次數會大幅減少，因為緩衝在第一次執行時就完成了載入，物理讀的次數就會比較小。出於這個原因，你可能認為沒有必要為基於預查詢的物理讀做大量的分析。當查看單個的查詢時，通常邏輯讀的次數更令人關注，因為資訊是一致的。物理I/O和快取命中率很重要，但他們在伺服器的層級更值得關注。

IO統計作用於每一個表和每一個查詢。可能需要審查某些列，用sys.dm_exec_query_stats DMV追蹤其物理讀的次數。得到的資訊包括最小物理讀、最大物理讀和物理讀總次數，可以為每一個查詢計劃累計，還可以用sys.dm_exec_sessions獲得每個會話的讀取資訊。

預讀

預讀數指在處理某個查詢時，運用預讀機制讀到緩衝中的頁數。這些預讀的頁不一定被查詢用到。如果用到了，只增加邏輯讀的次數而不增加物理讀的次數。一個較高的預讀值意味著不進行預讀處理時相比，物理讀次數可能會更低，而快取命中率可能會更高。在此情形下，不能由高快取命中率斷定系統不會從追加的記憶體獲益。高命中率可能是由於預讀機制讀了很多查詢所需的資料到緩衝中。這是件好事，但是在緩衝中簡單地保留之前用過的資料可能會更好，這樣，可能獲得同樣高甚至更高的命中率而不是用預讀機制。

預讀是物理IO的理想狀態。在全表掃描或部分表掃描中，通過表的索引分配圖來確定對象的分區。分區按塊讀取，單塊64KB。索引分配圖的組織方式決定了分區按磁碟序讀取。如果表遍布檔案組的多個檔案，預讀機制不再按順序處理檔案，而是嘗試保持至少8個檔案出於忙態。預讀由執行查詢的線程進行非同步請求。正因為是非同步，所以掃描時不會因預讀而被阻塞。只有要掃描的頁恰巧是預讀進緩衝的頁時才會阻塞。這樣，預讀既不至於比掃描太超前，也不至於太落後。

掃描計數

掃描計數顯示了表被訪問的次數。嵌套迴圈串連的外表掃描計數值為1，內表的掃描計數值可能為迴圈的次數，即表被訪問的次數。邏輯讀的次數取決於每次掃描訪問的頁次數的綜合。然而，即便是嵌套迴圈串連，內表的掃描計數也有可能是1。SQL Server可能會在內表拷貝需要的行到緩衝的工作表，使用工作表來訪問實際的資料行。通常IO統計的輸出不會執行程序表執行了這一步驟。需要使用TIME統計的結果及實際處理計劃所用時間的資訊來確定執行查詢所涉及的實際工作。雜湊串連和合并串連的掃描計數值通常為1，因為兩個表都涉及串連，但這類串連涉及更多記憶體。在執行查詢時，可以使用sys.dm_exec_requests觀察granted_query_memory的值，但請記住這不是一個累計計數器，只對當次執行的查詢有效。相應地，用可以用sys.dm_exec_sessions監察memory_usage欄位，以便觀察每個會話使用了多個個8K記憶體頁。