Setting Optimize for ad hoc workloads được sử dụng để làm gì ?

Mục đích : 

Mỗi lần thực thi một câu query, Database Engine sẽ tạo ra compiled plan, việc tạo compiled plan tốn rất nhiều chi phí nên compiled plan sau khi được tạo ra sẽ được lưu lại ở cach store để sử dụng cho tiếp theo, tuy nhiên hầu hết những câu query là ad hoc, chỉ được sử dụng một lần, vì thế việc lưu lại compiled plan dường như chỉ tốn thêm vùng nhớ chứ không phục vụ cho mục đích sử dụng lại ở lần chạy sau, vì vậy sql server đã thêm configuration option :  optimize for ad hoc workloads để khắc phục đều này. 

Khi option được bật sql server chỉ lưu lại một “stub” của query plan trong lần đầu tiên của mọi ad hoc query được compiled và chỉ sau lần compilation thứ 2 thì stub được thay thế bằng một full query plan

Ví dụ minh họa

Để giải thích rõ tác động khi bật option này chúng ta cùng đi qua một ví dụ sau

Trước khi thực hiện khảo sát, ta cần bật  setting : optimize for ad hoc workloads

để bật setting optimize for ad hoc workloads ta có thể dùng 1 trong 2 cách 

Cách 1 : sử dụng TSQ 

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Cách 2 : bật trực tiếp bằng SQL Server Management Studio Management Studio 

Ví dụ:

CREATE TABLE Test (Id INT, V CHAR(10))
GO

INSERT INTO Test (Id, V)
VALUES (1, 'V1'), (2, 'V2'), (3, 'V3')
GO

Chúng ta cùng chạy câu query sau

DBCC FREEPROCCACHE
GO

Câu lệnh DBCC FREEPROCCACHE sẽ  xóa tất cả cách thành phần được lưu trong cache plan, lưu ý việc chạy câu lệnh này chỉ nhằm phục vụ cho mục đích test nên chỉ sử dụng nó ở môi trường test

SELECT * FROM Test
GO

SELECT p.*, st.text, q.query_plan
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) AS st
	CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS q
WHERE st.text LIKE '%Test%' AND st.text NOT LIKE '%dm_exec_cached_plans%'

Kết quả lần chạy đầu tiên

Hình 1

Trong lần chạy đầu tiên : cacheobjtype = Compiled Plan Stub và query_plan là NULL, đều này nói cho chúng ta biết Compiled Plan Stub đã được tạo ra và query_plan thực chất không được lưu lại ở lần chạy đầu tiên, mục đích của Compiled Plan Stub thực chất là giữ chỗ cho query plan ở những lần chạy tiếp theo, để giải thích rõ mục đích này chúng ta cùng khảo sát bằng câu query sau đây

SELECT name, type, buckets_count FROM sys.dm_os_memory_cache_hash_tables WHERE name  =  'SQL Plans'

Hình 2

SELECT *
FROM sys.dm_exec_plan_attributes (0x06000D003F038C0700C969DB8401000001000000000000000000000000000000000000000000000000000000)

0x06000D003F038C0790C269DB8401000001000000000000000000000000000000000000000000000000000000 là plan_hanle được lấy từ kết quả ở hình 1

Hình 3

Ad hoc query sau khi được thực thi thì compiled plan sẽ được lưu vào cache store có tên là CACHESTORE_SQLCP, sql server dùng hash table có tên là SQL Plans  để quản lý cache store này

,hash table này gồm nhiều buckets (40009 bucket theo kết quả trả ra ở hình 1), cached plan sẽ được quản lý bởi 1 trong số những bucket này, theo kết quả hình 1 trả ra thì ta thấy cached plan của câu query trên được quản lý bởi bucketId = 2710

Vì một bucket sẽ quản lý 0, 1 hoặc nhiều cached plan nên để tìm một cached plan cụ thể ở trong một bucket thì sql server sẽ dùng một bộ cache key tương ứng, ở hình 3 ta thấy với những attribute có Is_Cache_Key = 1 thì những attribute này được tính là 1 cache key trong bộ cache key, để biết đầy đủ thông tin ý nghĩa từng cache key bạn có thể xem ở link này : Cache Key

Nhìn vào kết quả hình 3  ta thấy với những attribute có Is_Cache_Key = 0 thì chỉ có attribute: sql_handle là có giá trị, những attribute khác mang giá trị mặc định là 0

Quay lại với mục đích của stub complied plan, nó dùng để giữ chỗ tức là khi tạo ra stub complied plan thì sql server sẽ tạo ra bộ cache key tương ứng để lần chạy thứ hai, compiled plan được lưu sẽ sử dụng bộ cache key này, đồng thời stub complied plan cũng sẽ chứa giá trị sql_handle, có tác dụng để xác định được query text ứng với plan này

Chạy tiếp câu query trên lần thứ 2 

SELECT * FROM Test
GO

SELECT p.*, st.text, q.query_plan
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) AS st
	CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS q
WHERE st.text LIKE '%Test%' AND st.text NOT LIKE '%dm_exec_cached_plans%'

Kết quả lần chạy thứ 2 

Hình 4

Từ kết quả lần chạy thứ hai ta thấy cacheobjtype = Compiled Plan và query_plan không còn là NULL nữa, bucketId = 2710 giống với lần chạy đầu

Chạy câu query sau để khảo sát cache key

SELECT *
FROM sys.dm_exec_plan_attributes (0x06000D003F038C0790C269DB8401000001000000000000000000000000000000000000000000000000000000)

Tất cả attribute có Is_Cache_Key = 1 và sql_handle đều giống như giá trị trả ra đối với Compiled Plan Stub, tuy nhiên có một số khác biệt ở attribute : free_exec_context = 1, misses_exec_context = 1 không giống như lần trước vì ở đây đã lưu thông tin tương ứng chứ không còn là giá trị mặc định nữa

So sánh kích thước plan được lưu ở 2 lần chạy

• Lần chạy đầu tiên : size_in_bytes : 456 bytes
• Lần chạy thứ hai : size_in_bytes : 49152 bytes

Vì lần chạy thứ 2 lưu full query plan nên kích thước của nó lớn hơn rất nhiều so với lần chạy đầu tiên

Tổng kết

• Setting : Optimize for ad hoc workloads được sử dụng để giảm kích thước của cache plan được lưu đối với ad hoc query chỉ chạy một lần
• Khi Optimize for ad hoc workloads  được bật, lần chạy đầu tiên câu query, Compiled Plan Stub sẽ được tạo ra với mục đích giữ chỗ cho các lần chạy sau đó, từ lần chạy thứ 2 trở đi Compiled Plan Stub sẽ được thay thế bằng full query plan

Tài liệu tham khảo : 

• https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/plan-cache-concepts-explained
• Sách : Microsoft SQL Server 2012 Internals