Ac-Hoc Query 與記憶體快取

Ac-Hoc Query 與記憶體快取

ID-100125096

c

 

什麼是 Cache Plan

每當使用者執行一個查詢語句Query 的時候,SQL Server 就會對該語法進行解析、

運算最佳的執行方式並且執行,

執行完畢的時候SQL Server 會將該Query 執行的 Query Plan 存放到記憶體中,

如果下次再執行一次時,就不需要重新計算query Plan,直接從記憶體讀取執行即可

如下圖所示:

 

 

CachedPlan

 

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

SQLMemoryreports

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)也會變大,如下圖所示:

 

QueryCachedPlan

 

 

總結

這篇文章介紹兩種觀察記憶體的方法

  • 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 的記憶體管理。

Leave a Reply

Your email address will not be published. Required fields are marked *