Ac-Hoc Query 與記憶體快取
c
什麼是 Cache Plan
每當使用者執行一個查詢語句Query 的時候，SQL Server 就會對該語法進行解析、
運算最佳的執行方式並且執行，
執行完畢的時候SQL Server 會將該Query 執行的 Query Plan 存放到記憶體中，
如果下次再執行一次時，就不需要重新計算query Plan，直接從記憶體讀取執行即可
如下圖所示：
Ad-Hoc Query
SQL Server 為了讓 Query 的執行更有效率，讓重複執行的 Query 能夠更有效率的存取與執行﹑，因此會對每一個執行過的 Query 進行 Cached Query plan
問題來了，許多的 Query 都是只有執行一次，因此存放到記憶體中，
像這樣只有執行一次 query (稱為 ad-hoc query)的越來越多，時間一久，
整個記憶體慢慢被這些只有執行一次的 query 佔據，造成記憶體的浪費
因此，定期的檢視 ad-hoc query 是否占用記憶體也是效能條件重要的一環。
記憶體的使用狀況
SQL server 內建報表，可以讓系統管理員記憶體整體的狀況
Reports > Standard reports > Memory Consumption
Cached plan 占用多少記憶體呢?
可以利用 DMV “sys.dm_exec_cached_plans” 計算，如下：
/* Get memory used for execution plan cache */
SELECT SUM(size_in_bytes)/1024.0/1024.0 AS [Total MB used]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
個別的 Query 執行次數與占用記憶體空間?
透過 “sys.dm_exec_cached_plans” 與 “sys.dm_exec_sql_text”
可以查詢每一個 query 語句(text)所執行的次數 (usecounts)，占用的記憶體空間 (size_in_bytes)，如下：
— query execution plan cache
SELECT usecounts, size_in_bytes, cacheobjtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != ‘Parse Tree’
如何清除整個記憶體的快取?
這個指令僅建議在測試的環境下使用，因為該指令會清除記憶體中所有的快取資訊，
主要用來測試快取的執行狀態
DBCC FreeProcCache
DBCC DropCleanBuffers
GO
Ad-hoc 最佳化
當我們確認記憶體被 ad-hoc query 佔據大量的記憶體之後，
SQL Server 2008 之後提供一個設定show advanced options
可以讓ad-hoc query第一次執行的時候僅占用很小的記憶體空間，
只有當同樣的 ad-hoc query 語句執行第二次之後，
才會完整地將該 compiled query plan 儲存到記憶體，
因此，可以更有效的管理 ad-hoc 所造成記憶體空間的問題
該設定值預設為 off：不會對 ad-hoc query 記憶體最佳化
如何設定啟動Adhoc query 的最佳化“optimize for ad hoc workloads”
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
EXEC sp_configure ‘optimize for ad hoc workloads’, 0
RECONFIGURE
GO
“optimize for ad hoc workloads”執行效果
對於第一次執行的 ad-hoc query，
SQL 會用很小的記憶體紀錄該 query為 “Compiled Plan Stub”
當該 ad-hoc query 第二次執行的時候，
該query 就會從 “Compiled Plan Stub”轉換為 “Compiled Plan.” (cacheObjtype)
並且完整的紀錄該 Query plan所占用的記憶體(size_in_bytes)也會變大，如下圖所示：
總結
這篇文章介紹兩種觀察記憶體的方法
- Reports
- DMV “sys.dm_exec_cached_plans”
介紹每一次 Query 所需要的運算與 Cached Plan
如何觀察Ad-hoc query 一次性的執行對SQL Server 記憶體效能的影響
最後建議，將’optimize for ad hoc workloads’ 啟動
因為這個設定值預設為 off，多半情況下其實可以將該設定值設為 on
有助於 SQL Server 對於 ad-hoc query 的記憶體管理。