記憶體是否被Ad-hoc Query佔滿呢?

記憶體是否被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

 

ad-hoc3

Memory Consumption 報表

如果該”CacheStore” 的比例特別高,就表示 ad-hoc query 很多

 

ad-hoc2

另一個方法可以使用 SQL Query

select * from sys.dm_exec_cached_plans

看看大部分的 query 是否為 ObjectType = “Adhoc”

 

ad-hoc1

 

 

 

那要如何解決這樣的問題呢?

SQL Server 有一個設定 “optimize for ad hoc workloads ” (預設為 disable)

可以將 Ad-hoc query 的記憶體最佳化,

在第一次執行的query 僅存放一點點記憶體,

只有在第二次執行的 Query才將整個執行計畫儲存於記憶體

可以透過下列指令將該設定值啟動。

該設定值僅適用於 SQL 2008 之後的版本。

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_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

 

Leave a Reply

Your email address will not be published.