記憶體是否被Ad-hoc Query佔滿呢?
資料庫為了存取更有效率，會把每一次 query 執行結果(data) 與該Query 儲存在記憶體中，
如果下次有同樣的query 或是資料存取，就可以直接由記憶體中存取不需要到磁碟讀取
如此一來，就可以大幅減少 Disk I/O 的存取時間
但是問題來了，如果有很多的 Query 都是一次性的查詢(Ad-hoc Query)呢?
這樣一次性這樣的查詢不會重複被使用，
因此會讓記憶體存放許多這樣一次性查詢的 ad-hoc query
這邊介紹兩個方法可以查詢資料庫記憶體中，是否被許多 Ad-hoc query 占滿?
Memory Consumption Reports
Management Studio > Reports > Standard Reports > Memory Consumption
Memory Consumption 報表
如果該”CacheStore” 的比例特別高，就表示 ad-hoc query 很多
另一個方法可以使用 SQL Query
select * from sys.dm_exec_cached_plans
看看大部分的 query 是否為 ObjectType = “Adhoc”
那要如何解決這樣的問題呢?
SQL Server 有一個設定 “optimize for ad hoc workloads ” (預設為 disable)
可以將 Ad-hoc query 的記憶體最佳化，
在第一次執行的query 僅存放一點點記憶體，
只有在第二次執行的 Query才將整個執行計畫儲存於記憶體
可以透過下列指令將該設定值啟動。
該設定值僅適用於 SQL 2008 之後的版本。
sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GOsp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGUR
摘要
這篇文章介紹因為一次性的 query (ad-hoc)而將耗費許多額外記憶體的問題
如何觀察?兩個方法
1. SQL Studio > Reports > Memory consumption ，看”CacheStore”所占記憶體的比例
2. select * from sys.dm_exec_cached_plans，看看 query 的型態是否多半為 “ad-hoc query”
要如何解決呢?
SQL Server 2008之後提供一個設定，optimize for ad hoc workloads，
預設為 off，將該設定值enable ，
SQL Server 之後對於 ad-hoc query 記憶體的儲存就會大量減少，
只有在第二次使用到的ad-hoc query 才會儲存整個 query plan