等待事件介紹 關於等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE，官方的介紹如下： Occurs when the number of concurrent query compilations reaches a throttling limit. High waits ... ...
等待事件介紹
關於等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE，官方的介紹如下：
Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.
該等待事件在併發查詢編譯的數量達到閥值限制時出現。 等待時間較長或等待次數較多可能表明編譯、重新編譯或不能緩存的計划過多。
等待事件分析
記憶體授予的等待類型叫做“RESOURCE_SEMAPHORE”.在理解這個等待事件前，我們先來瞭解一下查詢記憶體授予(query memory grant)，它是用於在排序或連接時存儲臨時數據的伺服器記憶體的一部分。查詢在實際執行前需要先請求保留記憶體，所以會存在一個授予的動作。這樣的好處是提高查詢的可靠性和避免單個查詢占用所有的記憶體。
SQL Server在收到查詢時，會執行3個被定義好的步驟來返回用戶所請求的結果集。
1. 生成編譯計劃(compiled plan)。它包括各種邏輯指令，如怎麼聯接數據行。
2. 生成執行計劃(execution plan)，它包含將編譯計劃中的各種邏輯引用轉換成實際的對象的指令和查詢執行的跟蹤機制。
3. 從指令樹的頂端開始執行。
生成編譯計劃是件開銷較大的事情，因為它需要在數以百計的編譯計劃中找出較優的一個。它的時間通常很短，因為優化器會在找到最優的編譯計劃後便馬上釋放記憶體。編譯主要使用記憶體和CPU資源。缺少可用記憶體可能會導致編譯延遲和得到非最優的編譯計劃。
當SQL Server創建編譯計劃時，會計算兩個參數：必須記憶體(Requeried memory)和額外記憶體(Additional memory)。
必須記憶體：執行排序和哈希聯接所需的最少記憶體。這部分記憶體是“必須”的，它用來創建處理排序和哈希所需要的內部數據結構。
額外記憶體：存儲所有臨時數據行所需的記憶體。它的大小由基數評估(Cardinality estimate,如行數和行大小)決定。“額外”，顧名思義在缺少這部分記憶體時，將會將臨時數據行存到硬碟上，並不會導致查詢失敗。一個查詢的額外記憶體大小如果超過預設的限制，它實際得到的記憶體量並不一定會跟請求量一樣。
例如，對行大小為10byte的100萬行數據進行排序，此查詢的必須記憶體為為512KB(此值是SQL Server處理一個排序操作創建內部數據結構所需的最小記憶體量)。為了存儲所有數據行，額外記憶體可能是10MB。
當編譯計劃中含有多個排序和聯接操作時，額外記憶體的計算就變得複雜了。因為SQL Server要考慮所有操作符如何高效地使用記憶體。可以查看ShowPlan XML中的<MemoryFractions>標記部分內容，獲取更多記憶體使用的信息。
RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件一般是查詢正在等待授予記憶體以開始進行編譯時發生。編譯記憶體來自緩衝池（buffer pool），並需要保留足夠的時間以完成編譯過程。 對於多個併發編譯而言，占用太多記憶體頁可能會導致記憶體壓力。 為了緩解這種情況，SQL Server啟動編譯過程，確定哪些查詢需要大量的頁面，並迫使某一些查詢會話等待。 同樣，如果記憶體壓力已經存在，SQL Server將限制可以同時編譯的資源密集型查詢的數量。
如果你的資料庫經常看到這種等待事件或此等待類型過多，那麼你的資料庫可能會有太多記憶體密集型查詢（大型查詢），或者其他進程可能正在從緩衝池中竊取記憶體頁面.
減少等待事件方案
- Decrease query complexity 降低查詢語句的複雜度。
- Appropriate indexing could reduce plan complexity 合理創建索引減少執行計劃複雜度
- Improve plan reuse (therefore compilation can be avoided) 改善執行計劃重用（因此可以避免編譯）
- kill掉一些糟糕的SQL語句（記憶體資源密集型SQL），當然這個要看是否可行。
個人曾遇到過這樣一個案例，由於過度靈活設計，導致很多報表需要在SQL中大量關聯相關表，更糟糕的是，由於開發人員大量使用視圖，尤其是還存在視圖嵌套視圖的情況，所以在這樣一個系統中，一些查詢語句往往需要授予大量的記憶體，尤其是當出現一個或一些寫的很糟糕的SQL語句時，就會經常看到一些會話處於RESOURCE_SEMAPHORE_QUERY_COMPILE的等待狀態，而且當大量會話處於RESOURCE_SEMAPHORE_QUERY_COMPILE等待時，還有一個特殊現象就是活動的會話數量會彪增，此時，可以找到消耗記憶體最多的SQL，然後Kill掉後，活動的會話就會立即降下來。下麵就是我遇到案例的一個截圖。
SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
另外，記憶體緊張也會導致RESOURCE_SEMAPHORE_QUERY_COMPILE的出現的概率增加，那麼是否增加記憶體就有效解決RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件呢？答案是否定的，但是能緩解。如下描述：
This wait occurs when queries cannot be compiled due to the amount of compile memory currently available. This mostly occurs due to large queries requiring an excessive amount of memory. SQL Server caps the amount of complex queries that can be compiled at once, so increasing the memory allocation will not solve the problem effectively (it will only increase the amount of memory that can be allocated, not the number of queries)
參考資料：
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql
https://documentation.red-gate.com/sm4/working-with-overviews/using-performance-diagnostics/list-of-common-wait-types/resource_semaphore_query_compile
https://www.sqlskills.com/help/waits/resource_semaphore_query_compile/
