理解SQL Server是如何執行查詢的 (2/3)

查詢執行的記憶體授予(Query Execution Memory Grant)

有些操作符需要較多的記憶體才能完成操作。例如，SORT、HASH、HAS聚合等。執行計劃通過操作符需要處理數據量的預估值(通過統計信息獲得的預估行數)、操作符類型和列大小來獲得所需要的記憶體總量。這個執行計劃的記憶體總量，也叫做記憶體授予(Memory Grant)。

當多併發查詢的環境中，如果 查詢中有很多這種需要大量記憶體授予的操作符時，併發的執行計劃所需的記憶體授予總量可能會超過伺服器記憶體總量。SQL Server使用 資源信號量(Resource Semaphore) 來避免這種情況發生。當記憶體授予量超過可用記憶體量時，當前請求記憶體授予的查詢必須等待其它查詢完成並釋放出它占用的記憶體授予量。

可通過 sys.dm_exec_query_memory_grants 查詢當前查詢記憶體授予的狀態。當查詢等待記憶體授予時會產生 Execution Warnings 事件。

• 分配小於請求量的記憶體，查詢執行也能順利完成
• 按請求量分配後，記憶體授予還是不夠用。這會導致操作符的數據被 Spill Out到磁碟(tempdb)。當這種情況發生事，會產生一些警告事件:
  • Exchange Spill 事件
  • Sort Warnings 事件
  • Hash Warning 事件

對於查詢的記憶體授予記憶體是預先保留出來，而不是按需分配。所以在查詢執執行中可能出現實現使用的記憶體量比授予理要少的情況，這部分多來的記憶體會用做數據緩存。因為有資源信號量的限制，所以過大記憶體授予量會讓其它的查詢缺少授予記憶體而等待。

SQL Server 中有一個相似的概念叫 查詢編譯資源信號量(query compile resource semaphore) 。它的作用機制跟資源信號量一樣，但是它只針對查詢的編譯行為生效。通常查詢編譯不會成為系統的瓶頸，如果發生很可能是查詢計劃重用出問題了。

需要註意一點，並不是所有查詢都需要記憶體授予才能執行。包含排序、大型掃描、HASH連接和聚合等操作的複雜查詢才需要記憶體授予。在需要快速響應的系統中(如OLTP)，如果發生記憶體授予問題，應當考慮從數據模型設計上做出調整。OLAP系統中的記憶體授予導致的延遲，通賞是正常的。

關於記憶體的相關資源：理解SQL Server的查詢記憶體授予

數據組織方式(Data Organization)

1. 堆表：堆表中的數據是無序的。

2. 聚集索引：聚集索引表中的數據是有序的，順序跟聚集索引鍵相關。聚集索引是B-Tree結構。

3. 非聚集索引：它的數據是表中數據子集，並且數據是有序的，順序跟索引鍵相關

SQL Server 2012之後，還有列存儲索引的數據組織方式。

數據訪問(Data Access)

前面說到，在執行樹葉級的操作符會訪問實際的數據行。它們訪問數據的操作符通常有三種：

數據讀取 (Data Reading)

數據訪問操作符會只從緩存池(Buffer Pool)讀取數據。如果緩存池中沒有所需要的數據，則需要將數據從存儲子系統讀取到緩存池。緩存池的數據被所有查詢共用。SQL Server會盡將儘可能多的數據緩存到緩存池，以備使用，直到用盡所有分配給SQL Server的記憶體量。可以通過 max server memory 選項控制緩存池的記憶體使用上限。無論是緩存池，還是磁碟IO，它們數據請求的都以8KB頁為單位。

讓我們來看看掃描(Scan)數據訪問操作符是如何從堆上讀取數據的：

1. 當在掃描操作符上每一次調用next()時，它去找到第一條數據並返回。SQL Server 內部的元數據信息表存儲著哪些頁屬於哪一個表的信息。數據訪問操作符會請求一個指針，此指針指向此數據頁在緩存池中副本的地址。如果此頁在豐緩存池中，則請求會被阻塞，直到頁被載入到緩存池。頁包含一個由單個數據記錄(Data Record)組成的數組。一個數據記錄不一定是一行，有些變長和大型數據列，會跨多個頁存儲。數據訪問操作符會定位頁上的第一行數據，然後讀取相關的列值並返回。操作符內部保存著當前的狀態信息，此狀態信息讓它能夠高效地返回到當前行的位置。
2. 父級操作符取走返回的第一行數據。
3. 當再次next()，數據訪問操作符會利用之前保存的狀態信息，快速地返回先前的位置，並向前移動一行定位到第二行，然後讀取相關的列值並返回。
4. 父級操作符取走返回的第二行數據。
5. 當再次調用next()，發現數據行已經讀取完了，操作符會向緩存池請求“下一頁”。操作符定位到新頁後，會定位到每一行數據，然後讀取相關的列值並返回。
6. 父級操作符取走返回的數據。
7. 如此往複，直到讀取完表中最後一頁的最一行數據。操作符的保存的狀態信息會指向“已超出表尾”，沒有可以返回的數據了。
8. 當年數據訪問操作符完成它的工作，不再返回數據，父級操作符會開始處理自己的操作(如排序，HASH等)。
9. 數據訪問操作能被重繞(Rewind)。例如，掃描操作符作為嵌套迴圈連接的內表，當從外表中輸入一行，就需要重繞內表的掃描操作符。重繞會導致數據訪問操作符重置內部狀態信息，即重新從第一頁的第一行開始讀取數據。

作為對比，再來看看數據訪問操作如何在B-Tree上訪問數據的：

1. 當第一次調用 next()，數據訪問操作符根據請求的鍵找到第一行數據並返回。SQL Server 有元數據表保存哪些頁屬於哪一個索引的信息。它不像堆表直接定位到第一頁，而是通過元數據獲取到B-Tree根頁的ID，然後根據ID引用緩存池中的根頁副本。根據搜索的鍵值，數據訪問操作符定位到B-Tree中包含第一行數據的葉級頁或者當前搜索鍵值“後續”行的葉級頁。在樹中檢索路徑經過的頁都需要從緩存池中讀取出來，同樣的，如果這些頁在緩存池中，就需要等待將它們從磁碟載入到緩存池中來。數據訪問操作符在葉級頁中檢索並定位到與查找鍵值匹配的行並返回。

這個“後續”的意思，檢索B-Tree可以是雙向的(ASC OR DESC)，而且根據查詢提供的鍵值，可能找到完全匹配鍵值的行，也可能找不到匹配鍵值的行。匹配鍵值的行可以沒有，但是葉級頁總是存在的，並且頁上的行與鍵值所指向的行在同一個B-Tree查找區間內，所以稱之為“後續”。“後續”行位於鍵值匹配行的前面還是後面，由檢索方向決定。檢索方向與創建索引時指定的ASC或者DESC的排序方式是不同，前者就是指樹中的檢索方向，或者是指索引行的實際順序。BY Joe .TJ

1. 父級操作符取走返回的數據。

2. 如果操作符用作範圍掃描，則會再次調用next()，讀取已返回行的後一行。操作符會保存前一個返回行的鍵值和位置信息。然後再上一點拒描述的B-Tree檢索過程。如果當前葉級頁的數據已經被取完了，則會定位到下一頁的第一行並返回。索引頁通過雙向鏈表連接，每一頁上都會前一頁和下一頁的指針。

3. 父級操作符再次取走返回的數據。

4. 因為範圍掃描會包含範圍結束位置的鍵值，所以當調用next()從當前行移動到後一行，而後一行的鍵值超過了結束位置的鍵值，會返回false。此處的“超過”與B-Tree檢索方向和索引排序有關。

5. B-Tree操作符除能被重繞(Rewind)，還能被重新綁定(Rebind)。重繞會重置操作符的狀態，使其使用同樣的鍵值重新開始查找或者範圍掃描。重新綁定會改變用於查找的鍵值，也就是說之前的結果可能無效了。

預讀(Read Ahead)

掃描操作每次讀取完一頁上的所有數據後，再去讀取下一頁，如果下一頁不在緩存池，就需要等待將頁從磁碟載入到緩存池。如果每讀一頁，都需要等待載入，那性能就太差了。SQL Server 使用預計的機制來優化這種操作。掃描操作符通過非同步IO將現在還沒有被使用到，但是很快會被用到的頁提前載入到緩存池中來。

嵌套迴圈中還有一種特殊的預讀叫隨機預讀(Random Prefetching)，它是為了減少查找操作等待而提前將頁載入到緩存池。

預讀一般會預先讀取500頁左右的數據，如果頁連續，則每一次非同步IO最多可以讀取64頁(512KB)的數據，也就是說理想情況下8次IO可以完成一次預讀。讀取數據頁時，根據IAM獲取要預計的頁地址，然後把連續的頁合併到同一次IO中。索引頁(指葉級頁)，則是通過B-Tree的中間級的索引頁，獲取需要預讀的頁地址，然後合併連續的頁到同一次IO。兩者中不連續的頁，會單獨執行IO。這裡的“連續”都指的是邏輯上的連續。 BY Joe .TJ