RESOURCE_SEMAPHORE_QUERY_COMPILE là gì
Định nghĩa :
- RESOURCE_SEMAPHORE_QUERY_COMPILE là một loại wait type, phát sinh khi câu query bắt đầu chạy và cần một vùng nhớ để thực hiện quá trình Compile, tuy nhiên vì một số lý do nào đó sql server không cấp phát vùng nhớ cho câu query này nên nó cần phải chờ, mỗi lần chờ như vậy ta sẽ thấy wait type RESOURCE_SEMAPHORE_QUERY_COMPILE xuất hiện.
Trước khi đi vào chủ đề chính của bài viết này, ta cùng đi qua một số khái niệm cơ bản về memory mà một câu query cần để có thể chạy được.
Khi execute 1 câu query nó phải đi qua những tiến trình sau
SQL statement → Parsing → Binding → Query Optimization → Query Execution → Query Results
- Parsing : Sql server kiếm tra cú pháp của câu query có hợp lệ hay không
- Binding : Sql server kiểm tra các object trong câu query như table, column có hợp lệ hay không
- Query Optimization : từ câu query ban đầu, sql server tạo ra nhiều plan, sau đó tính chi phí (cost) cho tất cả các plan vừa được tạo ra và cuối cùng lựa chọn plan có chi phí thấp nhất trong những plan đó để thực thi (Plan được tạo sau cùng được gọi là compile plan)
- Query Execution : thực thi câu query dựa trên plan đã được tạo ra ở bước Query Optimization
- Query Results : trả về kết quả cho ứng dụng đã gửi yêu cầu
Có 3 vùng nhớ tiêu thụ chính trong quá trình thực thi câu query như trên :
- Compile Memory : là vùng nhớ cần dùng ở giai đoạn Parsing, Binding, Query Optimization giúp xây dựng và tìm kiếm plan tối ưu trong số hàng trăm plan có khả năng xảy ra, vùng nhớ này nhiều hay ít nó phụ thuộc vào số trường hợp xảy ra của 1 câu query. Sau khi giai đoạn Query Optimization kết thúc thì vùng nhớ này cũng được giải phóng, việc thiếu memory có thể làm chậm quá trình compile dẫn đến câu truy vấn kéo dài thời gian thực thi.
- Cache Memory : quá trình tìm kiếm plan tối ưu trong giai đoạn Query Optimization sẽ tốn nhiều CPU time và Compile memory nên mỗi lần tạo ra compile plan, sql server cố gắng lưu lại plan này ở trong cache để sử dụng cho những lần tiếp theo, việc thiếu vùng nhớ này gây ra quá trình recompile không cần thiết
- Memory Grant: vùng nhớ này được dùng bởi những hoạt động như hash, sort, bulk copy, dùng để lưu trữ dữ liệu tạm thời khi thực hiện những hoạt động này, Memory Grant được cấp phát trước khi vào giai đoạn Query Execution, và được giải phóng khi câu query thực hiện xong.
Để dễ hình dung 3 vùng nhớ trên, chúng ta cùng đi qua ví dụ cụ thể sau đây :
USE master
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TableTest (Id INT, Name CHAR(8000))
INSERT INTO TableTest (Id, Name)
VALUES
(1, 'Name 1')
,(2, 'Name 2')
trước khi chạy câu query ta cần setup performance monitor để dễ xem sự thay đổi của các vùng nhớ, ở phần này mình sẽ dùng những counter sau :
- SQLServer:Memory Manager – Optimizer Memory (KB) : lượng compile memory đang dùng
- SQLServer:Memory Manager – Granted Workspace Memory (KB) : lượng memory grant đang dùng
- SQLServer:Plan Cache – Cache Pages : số page (8kb) được sử dụng bởi các cache object (có thể tính bằng Cache Memory chia cho (8 * 1024))
- SQLServer:Workload Group Stats – Active requests : số lượng request hiện tại đang running trên workload group này
- có 2 workgroup mặc định
- internal : dùng cho những hoạt động nội bộ của sql server
- default : dùng cho các hoạt động của người dùng thông thường
- có 2 workgroup mặc định
Để chắc chắn câu query sẽ thực hiện quá trình Compile (không sử dụng plan đã được lưu lại khi chạy câu query này trước đó) ta cần xóa cache Plan (lưu ý chỉ thực hiện câu lệnh này trong môi trường test)
DBCC FREEPROCCACHE
GO
Ta sẽ chạy câu query này 2 lần, lần 1 và lần 2 cách nhau 10s để xem điều gì sẽ xảy ra
;WITH
t0 AS (SELECT t.* FROM dbo.TableTest AS t)
,t1 AS (SELECT t.* FROM t0 AS t CROSS JOIN t0 AS k)
,t2 AS (SELECT t.* FROM t1 AS t CROSS JOIN t1 AS k)
,t3 AS (SELECT t.* FROM t2 AS t CROSS JOIN t2 AS k)
,t4 AS (SELECT t.* FROM t3 AS t CROSS JOIN t3 AS k)
,t5 AS (SELECT t.* FROM t4 AS t CROSS JOIN t4 AS k)
,t6 AS (SELECT t.* FROM t5 AS t CROSS JOIN t5 AS k)
,t7 AS (SELECT t.* FROM t6 AS t CROSS JOIN t6 AS k)
,t8 AS (SELECT t.* FROM t7 AS t CROSS JOIN t7 AS k)
,t9 AS
(
SELECT t.*
FROM
(
SELECT TOP(1000)* FROM t8
) AS t
CROSS JOIN
(
SELECT TOP(1000)* FROM t8
) AS k
)
SELECT t.Id, t.Name
FROM t9 AS t
ORDER BY t.Id, t.Name
GO
Hình 1
Đây là kết quả cho 2 lần chạy của mình
Thời điểm Time 1 :
Sql server bắt đầu cấp phát Compile Memory (đường màu đỏ) để câu query thực hiện quá trình compile, bạn có thể thấy sau thời điểm Time 1, đường màu hồng hiển thị Active requests tăng từ 0 lên 1, chỉ ra rằng câu query hiện tại đang thực thi, Compile Memory (đường màu đỏ) tăng dần đến thời điểm Time 2.
Thời điểm Time 2 :
lúc này quá trình Query Optimization đã được thực hiện nên câu query không có nhu cầu sử dụng Compile memory đã được cấp phát nữa vì vậy nó bắt đầu giải phóng, cũng tại thời điểm Time2 vì Compile Plan vừa mới tạo ra được lưu lại trong cache để sử dụng cho những lần chạy tiếp theo nên làm cho số lượng cache pages trong Plan cached (đường màu xanh lá cây) tăng lên, và tại thời điểm Time 2 ta cũng thấy memory grant (đường màu xanh dương) cũng bắt đầu được cấp phát để câu query thực hiện quá trình Query Execution
Thời điểm Time 3 :
vùng nhớ Compile Plan (đường màu đỏ giảm xuống 0) đã được giải phóng và vùng nhớ memory grant (đường xanh dương) đã được cấp phát đầy đủ lúc này câu query bắt đầu thực thi.
Thời điểm Time 4 :
tại thời điểm này câu query đã thực hiện xong nên Active requests bắt đầu giảm xuống 0 (đường màu hồng) và Memory grant (đường màu xanh dương) cũng được giải phóng, và một điểm cần lưu ý là lúc này tuy câu query đã thực hiện xong nhưng cache pages (đường màu xanh lá cây) ở plan cache vẫn giữ không đổi vì compile plan được lưu trữ lại để dùng cho những lần chạy tiếp theo.
Thời điểm Time 5 :
tại thời điểm này khi bắt đầu chạy lại câu query lần thứ 2 thì lúc này ta không còn thấy đường màu đỏ thể hiện giá trị compile memory như lần chạy đầu tiên vì thực thế plan đã được lưu ở trong plan cache và chỉ cần lấy ra dùng mà không cần thực hiện lại quá trình compile, sau quá trình này câu query cũng bắt đầu cấp phát memory grant và sau khi thực hiện xong nó bắt đầu giải phóng như khi chạy câu query lần đầu, và một điểm cần lưu ý là đường màu xanh lá cây chứa thông tin cache page vẫn ko thay đổi ngay cả sau khi câu query thực thi xong, vì nó vẫn chứa compile plan có thể được sử dụng cho những lần chạy kế tiếp.
Qua ví dụ trên các bạn có thể hình dung được phần nào việc cấp phát vùng nhớ khi thực thi một câu query, giờ chúng ta cùng đi sâu hơn vào Compile Memory
SQL server dùng vùng nhớ chủ yếu vào việc chứa những data pages được load lên từ ổ đĩa với mục đích khi có một câu query cần truy vấn dữ liệu thay vì load dữ liệu lên từ ổ đĩa nó có thể lấy trực tiếp từ memory, tăng tốc độ thực thi cho câu query.
Mặt khác, khi compile một câu query, sql server cần cấp phát Compile Memory (Query Optimization), trong trường hợp một lượng lớn câu query thực thi đồng thời thì lượng memory cần dùng cho việc compile là rất lớn, để đáp ứng cung cấp đủ lượng memory này thì data pages phải bị đẩy ra khỏi memory để nhường chỗ cho quá trình compile và có thể dẫn đến việc thực thi câu query chậm vì phải load lại data từ ổ đĩa nếu một câu query nào đó cần.
Trước tình huống trên, sql server đã sinh ra kiến trúc gateway dùng để giữ cho việc sử dụng memory của quá trình compile nằm dưới sự kiểm soát, bạn có thể xem hình sau để rõ hơn về kiến trúc này.
Kiến trúc Gateway
Hình 2
Sql server dùng kiến trúc gateway để phân loại lượng compile memory của một câu query cần được cấp phát, có 3 gateway là small gateway, medium gateway và big gateway.
Mỗi gateway sẽ 2 thành phần chính để xem một câu query có thuộc gateway này không và nó đang được cấp phát hay đang chờ để được cấp phát Compile memory :
- Threshold (ngưỡng) (bytes) : nếu compile memory của câu query sử dụng vượt qua threshold này thì nó sẽ đi vào các gateway tiếp theo, đối với small gateway ngưỡng này luôn cố định bằng 380000, đối với Medium gateway và big gateway ngưỡng này động nó phụ thuộc vào compile memory mà server dự định cung cấp cho hệ thống.
- Max Count : cho biết số lượng câu query mà gateway có thể xử lý đồng thời, ví dụ small gateway đang có 100 câu query thỏa mãn điều kiện, nhưng vì max count = 48 nên chỉ có 48 câu query được cấp compile memory nên 52 câu query còn lại phải chờ một trong số 48 câu query thực hiện xong quá trình compile thì mới được cấp phát compile memory.
Giá trị max count của từng gateway được tính theo công thức như sau :
- Small gateway : 4 x (số lượng logical CPUs được phân bổ đến SQL SERVER)
- Medium Gateway: Số lượng logical CPUs được phân bổ đến SQL SERVER
- Big Gateway luôn bằng 1
để xem số lượng logical CPU được phân bổ trên sql server bạn có thể chọn server name, sau đó chọn server properties tiếp đến ấn vô mục processors
đây là setting ở máy mình
Hình 3
Setting này đang để mặc định, phần automatically set processor affinity mask for all processor được chọn nên sẽ sử dụng tất cả 12 CPU bên dưới
như vậy max count của từng gateway cho máy mình như sau :
- Small gateway : 12 * 4 = 18
- Medium gateway : 12
- Big gateway : 1
Quá trình phân loại để được cấp phát Compile memory diễn ra như sau :
giả sử ta có câu query Q1 cần được cấp phát lượng memory để thực hiện quá trình compile là M1 :
- Bước 1 : Sql server kiểm tra M1 có lớn hơn hoặc bằng threshold của Small Gateway hay không?
- nếu M1 < threshold of Small Gateway → câu query được cấp phát memory
- nếu M1 >= threshold of Small Gateway → câu query đi vào Small Gateway và thực hiện Bước 2
- Bước 2 : kiểm tra tổng số câu query được phân loại active >= Max Count hay không?
- nếu tổng số câu query được phân loại active >= Max Count → Q1 cần chờ cho đến khi tổng số câu query được phân loại active < Max Count (một số câu query khác trong gateway này thực hiện xong quá trình compile)
- nếu tổng số câu query được phân loại active < Max Count
- Kiểm tra M1 >= threshold của medium gateway hay không ?
- nếu M1 >= threshold của medium gateway → Q1 đi vào Medium Gateway và tiếp tục thực hiện quá trình như ở Bước 2
- nếu M1 < threshold của medium gateway → Q1 được cấp phát memory
- Kiểm tra M1 >= threshold của medium gateway hay không ?
Ta cùng đi qua ví dụ sau đây để hiểu hơn về kiến trúc gateway
Ý tưởng demo của mình như sau : đầu tiên mình có một câu query Q, chạy câu query Q bằng ứng dụng stress test bằng 100 thread, sau đó kiểm tra xem thông số đo được ở các gateway.
câu query mình sử dụng trong phần demo này như sau :
;WITH
t0 AS (SELECT t.* FROM dbo.TableTest AS t)
,t1 AS (SELECT t.* FROM t0 AS t CROSS JOIN t0 AS k)
,t2 AS (SELECT t.* FROM t1 AS t CROSS JOIN t1 AS k)
,t3 AS (SELECT t.* FROM t2 AS t CROSS JOIN t2 AS k)
,t4 AS (SELECT t.* FROM t3 AS t CROSS JOIN t3 AS k)
,t5 AS (SELECT t.* FROM t4 AS t CROSS JOIN t4 AS k)
,t6 AS (SELECT t.* FROM t5 AS t CROSS JOIN t5 AS k)
,t7 AS (SELECT t.* FROM t6 AS t CROSS JOIN t6 AS k)
,t8 AS (SELECT t.* FROM t7 AS t CROSS JOIN t7 AS k)
,t9 AS
(
SELECT t.*
FROM
(
SELECT TOP(1000)* FROM t8
) AS t
CROSS JOIN
(
SELECT TOP(1000)* FROM t8
) AS k
)
SELECT *
FROM t8 AS t
ORDER BY t.Id, t.Name
trước khi chạy câu query, ta cần kiểm tra xem câu query này cần dùng bao nhiêu compile memory, để làm được điều này trên giao diện SSMS ta tô đen câu query sau đó ấn biểu tượng 
Hình 4
Ở Execution Plan Tab ta sẽ thấy compile plan ứng với câu query trên, ta trỏ chuột vào khối SELECT và ấn properties
Hình 5
Nhìn vào thuộc tính CompileMemory ta sẽ biết Compile memory của câu query, như câu query này cần 206856 kb = 206856 * 1024 bytes = 211820544 bytes để thực hiện quá trình compile.
Tiếp theo ta dùng tool SQLQueryStress để chạy đồng thời 100 câu query trên.
Để sử dụng tool này, đầu tiên ta copy câu query cần chạy vào cửa sổ, sau đó ấn vào mục database để thiết lập kết nối với server
Hình 6
Trong phần Database Select ta có các tham số cần chú ý :
- Server : tên server cần connect tới, ở đây server name của mình là DESKTOP-P7JVNG6
- Authentication : cách xác thực, ở đây mình chọn Integrated Authentication vì mình đang xác thực bằng windows account
- Default Database : Chọn database, ở đây mình chọn TestDB
Hình 7
khi điền đầy đủ thông tin chúng ta cần nhấn vào test connection bên dưới để kiểm tra đã kết nối thành công hay chưa, nếu hiển thị Connection succeeded thì bạn đã kết nối thành công, nếu không bạn cần kiểm tra lại thông tin đã nhập.
Sau khi thiết lập settings cho phần database, ta cần chú ý đến 2 settings :
- Number of Iterations : quy định số lần câu query được thực thi lặp lại, ở đây mình chọn là 1
- Number Of Threads : quy định số threads được tạo ra để chạy song song trong cùng một lần, ở đây mình chọn là 100
Hình 8
để việc compile diễn ra thì trong cache không được tồn tại Plan của câu query này, nên bạn cần nhấn vào free cache để xóa cached plan (lưu ý chỉ thực hiện việc này ở môi trường test vì khi ấn sẽ xóa tất cả Plan nằm trong cache của sql server)
Hình 9
Quá trình chuẩn bị đã xong, chúng ta hãy ấn GO để chạy câu query trên
sau khi nhấn xong GO, mình chạy 3 câu lệnh bên dưới để khảo sát các gateway
/*lấy thông tin của các gateway*/
SELECT *
FROM sys.dm_exec_query_optimizer_memory_gateways
WHERE pool_id = 2 -- Default pool : dùng cho user thông thường
GO
/*lấy thông tin Compile memory target*/
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Compile memory target (KB)' AND instance_name = 'default'
GO
/*Kiểm tra trạng thái thực thi câu query*/
SELECT session_id, r.status, command, st.text, r.last_wait_type, p.program_name
FROM sys.dm_exec_requests r
INNER JOIN sys.sysprocesses p ON p.spid = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE p.program_name = 'SQLQueryStress'
Kết quả chạy 3 câu query trên :
Hình 10
Hình 11
Nhìn vào kết quả trả ra ta rất khó để hình dung những gì đang diễn ra ở small gateway, Medium Gateway và Big Gateway cho 100 câu query chạy cùng một lúc, nên mình sẽ tách ra làm 100 lần thực hiện từ câu query số 1 đến câu query số 100 để các bạn dễ hình dung, Quá trình diễn ra như sau:
Câu query số 1 :
Đầu tiên câu query số 1 tới small gateway và bắt đầu kiểm tra xem compile memory của nó có lớn hơn threshold (380000 bytes) hay không và vì 211820544 (bytes) > 380000 (bytes) nên câu query số 1 đi vào small gateway, lúc này active_count của small gateway bằng 1 và waiter_counter bằng 0.
Tiếp theo câu query số 1 tiếp tục đi đến Medium Gateway và kiểm tra threshold (13734485 bytes) lúc này nó nhận thấy 211820544 (bytes) > 13734485 (bytes) nên câu query số 1 tiếp tục đi vào Medium Gateway, lúc này active_count của medium gateway bằng 1 và waiter_counter bằng 0.
Tiếp theo câu query số 1 tiếp tục đi đến Big Gateway và kiểm tra threshold (82406912 bytes) lúc này nó nhận thấy 211820544 (bytes) > 82406912 (bytes) nên câu query số 1 tiếp tục đi vào Big Gateway, lúc này active_count của Big Gateway bằng 1 và waiter_counter bằng 0.
Kết quả sau cùng khi câu query số 1 đi qua 3 Gateway :
| pool_id | name | max_count | active_count | waiter_count | threshold_factor |
|---|---|---|---|---|---|
| 2 | Small Gateway | 12 | 1 | 0 | 380000 |
| 2 | Medium Gateway | 12 | 1 | 0 | 12 |
| 2 | Big Gateway | 1 | 1 | 0 | 8 |
Câu query số 2 :
Câu query số 2 cũng cần lượng compile memory bằng 211820544 (bytes), tương tự như câu query số 1, nó lần lượt đi qua small gateway và medium gateway để đến big gateway, tuy nhiên lúc này ta lại thấy max_count = 1 ở big gateway, điều này cho ta biết tối đa active_count ở big gateway là 1 và lúc này vì câu query số 1 chưa thực hiện xong nên câu query số 2 cần phải chờ để được cấp phát Compile Memory, vì thế giá trị active_count ở big gateway vẫn bằng 1 và waiter_counter bằng 1
Kết quả sau cùng khi câu query số 2 đi qua 3 Gateway :
| pool_id | name | max_count | active_count | waiter_count | threshold_factor |
|---|---|---|---|---|---|
| 2 | Small Gateway | 12 | 1 | 0 | 380000 |
| 2 | Medium Gateway | 12 | 1 | 0 | 12 |
| 2 | Big Gateway | 1 | 1 | 1 | 8 |
Quá trình lặp lại cho đến câu query thứ 12, kết quả sẽ như sau :
| pool_id | name | max_count | active_count | waiter_count | threshold_factor |
|---|---|---|---|---|---|
| 2 | Small Gateway | 12 | 12 | 0 | 380000 |
| 2 | Medium Gateway | 12 | 12 | 0 | 12 |
| 2 | Big Gateway | 1 | 1 | 11 | 8 |
Câu query thứ 13 :
Đến lượt câu query thứ 13, sau khi đi qua small gateway thì nó bắt đầu kiểm tra thấy lớn hơn threshold của medium gateway nhưng lúc này ta thấy max_count của Medium Gateway bằng 12 nên ở Medium Gateway này tối đa active_count là 12 nên lúc này câu query thứ 13 sẽ cần phải chờ và vì vậy waiter_count của medium gateway lúc này bằng 1 như kết quả ở bảng bên dưới.
| pool_id | name | max_count | active_count | waiter_count | threshold_factor |
|---|---|---|---|---|---|
| 2 | Small Gateway | 13 | 13 | 0 | 380000 |
| 2 | Medium Gateway | 12 | 12 | 1 | 12 |
| 2 | Big Gateway | 1 | 1 | 11 | 8 |
quá trình diễn ra như trên cho đến câu query thứ 100 thì ta sẽ có kết quả hình 10
| pool_id | name | max_count | active_count | waiter_count | threshold_factor |
|---|---|---|---|---|---|
| 2 | Small Gateway | 48 | 48 | 52 | 380000 |
| 2 | Medium Gateway | 12 | 12 | 36 | 12 |
| 2 | Big Gateway | 1 | 1 | 11 | 8 |
Một điểm đặc biệt các bạn cần lưu ý ở đây là threshold của small gateway là cố định (380000 (bytes) với phiên bản sql server mình đang cài) và threshold sẽ thay đổi đối với medium gateway và big gateway và được xác định bằng công thức sau :
- Threshold = Compile memory target / (threshold_factor * (waiter_count + active_count))
- Compile memory target :
- trong sql server, Memory broker là object dùng để xác định cần cấp bao nhiêu compile memory cho từng pool và lượng compile memory cần cấp phụ thuộc vào nhiều yếu tố như trạng thái server (có nhiều hoạt động đang dùng memory hay không), số lượng memory mà sql server được cấp để sử dụng,…
- Object : SQLSERVER:Resource Pool Stats có counter là Compile memory target (KB), cho biết lượng Compile memory cần được cấp phát cho từng pool.
Quay lại với kết quả từ Hình 10, ta thấy Compile memory target (KB) của default pool đang là 7725648 (kb) = 7725648 * 1024 = 7911063552 (bytes).
Nên ta có thể tính threshold của medium gateway và big gateway như sau :
- threshold of Medium gateway:
- Compile memory target / (threshold_factor * (waiter_count + active_count)) = 7911063552/ (12 * (12 + 36)) = 13734485.333 (bytes)
- threshold of big gateway:
- (Compile memory target) / (threshold_factor * (waiter_count + active_count)) = 7911063552/ (8 * (1 + 11)) = 82406912 (bytes)
Đúng bằng kết quả mình khảo sát được ở cột threshold của Medium Gateway và Big Gateway bên trên.
Từ cách tính threshold của medium gateway và big gateway ta có thể nhận thấy nếu Compile memory target là không đổi thì số lượng active_count + waiter_counter càng lớn thì threshold ứng với gateway đó sẽ càng nhỏ nên số lượng câu query vào medium gateway và big gateway sẽ nhiều hơn, mặc khác max_count của Big gateway, Medium Gateway đều không đổi nên tình trạng phải chờ cho câu query khác compile xong sẽ ngày càng tăng.
Vì các câu query đang chờ để được cấp phát Compile memory nên ở hình 11 ta thấy loại wait type RESOURCE_SEMAPHORE_QUERY_COMPILE xuất hiện
Vậy làm cách nào để hạn chế wait type RESOURCE_SEMAPHORE_QUERY_COMPILE hay nói cách khác làm cách nào để câu query không cần chờ để được cấp compile memory ?
Đầu tiên ta xét cụ thể câu query ở trên, điểm cần chú ý là compile memory = 211820544 bytes = 202 MB, cần tới 202 MB để compile câu query này, lượng compile memory quá lớn so với một câu query thông thường, ta cần nhìn sâu vào những giai đoạn con trong Query Optimization để biết nguyên nhân.
Các bước thực hiện chi tiết hơn trong hoạt động Query Optimization như sau :
Simplification → Trivial Plan → Full optimization
trong đó :
- Simplification : câu query được viết lại để đơn giản hơn làm cho tiến trình tối ưu dễ hơn (ví dụ : chuyển subqueries thành JOIN, loại bỏ những phép inner join dư thừa)
- Trivial Plan : nếu câu query chỉ có một cách để thực hiện hoặc một cách tốt nhất hiển nhiên để thực hiện thì plan này được lựa chọn và kết thúc quá trình Compile
- Full optimization : trong giai đoạn này query optimizer sẽ tìm kiếm hầu hết các trường hợp có thể thực thi từ câu query ban đầu, sau đó dựa vào những plan đã được tìm thấy, sẽ tính chi phí (cost), và plan nào có chi phí nhỏ nhất sẽ lựa chọn làm kết của cuối cùng của Compile Plan
từ những thông tin trên ta thấy ở giai đoạn Full optimization, nếu một câu query càng có nhiều phép JOIN thì càng có nhiều plan được tìm kiếm.
Ví dụ nếu câu query sử dụng phép JOIN như sau : A JOIN B JOIN C JOIN D
thì ta có thể viết lại thành cách trường hợp sau :
- JOIN (JOIN (JOIN (A, B), C), D)
- JOIN (JOIN ( JOIN (B, C), A, D)
- JOIN (JOIN (JOIN (D, A), C), B)
- JOIN(JOIN(A, B), JOIN (C, D))
- ….
ứng với mỗi cách trên ta lại tìm ra 1 plan tương ứng, và những plan này được lưu lại để tính toán chi phí, càng nhiều phép JOIN như trên thì số plan được lưu càng lớn dẫn đến Compile memory càng cao, như vậy giải pháp để giảm Compile memory ở câu query trên là hạn chế sử dụng những câu query có nhiều phép JOIN, cùng xem câu query mình viết lại bên dưới
DECLARE @t4 TABLE (Id INT, Name CHAR(8000))
;WITH t0 AS (SELECT t.* FROM dbo.TableTest AS t)
,t1 AS (SELECT t.* FROM t0 AS t CROSS JOIN t0 AS k)
,t2 AS (SELECT t.* FROM t1 AS t CROSS JOIN t1 AS k)
,t3 AS (SELECT t.* FROM t2 AS t CROSS JOIN t2 AS k)
,t4 AS (SELECT t.* FROM t3 AS t CROSS JOIN t3 AS k)
INSERT INTO @t4 (Id, Name)
SELECT id, Name
FROM t4
;WITH t5 AS (SELECT t.* FROM @t4 AS t CROSS JOIN @t4 AS k)
,t6 AS (SELECT t.* FROM t5 AS t CROSS JOIN t5 AS k)
,t7 AS (SELECT t.* FROM t6 AS t CROSS JOIN t6 AS k)
,t8 AS (SELECT t.* FROM t7 AS t CROSS JOIN t7 AS k)
,t9 AS
(
SELECT t.*
FROM
(
SELECT TOP(1000)* FROM t8
) AS t
CROSS JOIN
(
SELECT TOP(1000)* FROM t8
) AS k
)
SELECT *
FROM t8 AS t
ORDER BY t.Id, t.Name
tô đen câu query trong SSMS và ấn vào biểu tượng
tổng lượng Compile memory = 1936 + 2136 = 4072 kb nhỏ hơn rất nhiều so với 202MB lúc ban đầu.
Thử chạy lại 100 câu query này:
chạy đồng thời câu query sau để kiểm tra xem có câu query nào đang phải chờ để được cấp phát Compile memory hay không
SELECT *
FROM sys.dm_exec_query_optimizer_memory_gateways
WHERE pool_id = 2
GO
ta thấy cột active_count ở cả ba Gateway đều bằng 0, chứng tỏ không có câu query nào trong 100 câu query trên phải chờ để được cấp phát Compile memory, một điểm cần chú ý ở đây là threshold = -1 đối với Medium Gateway và Big Gateway vì active_count ở đây bằng 0.
Vậy cách xử lý cho trường hợp cụ thể ở trên là tách câu query có nhiều lệnh JOIN thành những câu query nhỏ để giảm Compile memory.
Dưới đây là các cách ta có thể dùng cách cách sau đây để hạn chế wait type RESOURCE_SEMAPHORE_QUERY_COMPILE :
- Thêm RAM : nếu ram quá ít dẫn đến lượng vùng nhớ dùng cho quá trình compile ít theo, thì thêm RAM có thể là biện pháp khả thi
- Giảm số lượng compile xảy ra trong sql server : một số hệ thống dùng quá nhiều lệnh OPTION (RECOMPILE) làm cho mỗi lần chạy, câu query đều phải Compile lại dẫn đến cần nhiều Compile memory hơn
- Tắt những chương trình tốn nhiều RAM : khi dùng nhiều ứng dụng tốn nhiều RAM thì lượng RAM mà sql server dùng sẽ bị giảm xuống dẫn đến Compile memory có thể cấp phát bị giảm đáng kể, nên cần hạn chế những chương trình tốn nhiều RAM
- Tối ưu câu query để giảm Compile memory cần thiết để thực hiện quá trình Compile
Kết luận :
RESOURCE_SEMAPHORE_QUERY_COMPILE xuất hiện khi có một số câu query trong hệ thống cần được cấp phát Compile memory để thực hiện quá trình Compile, nhưng nhìn chung đây là loại wait type khó xảy ra trong thực tế vì thông thường Compile memory của một câu query cần dùng là rất nhỏ, một khi thấy xuất hiện loại wait type này trong hệ thống bạn cần tập trung vào việc kiểm tra liệu RAM có đủ dùng, có ứng dụng khác chiếm RAM của sql server hoặc liệu có một số lượng lớn câu query thực hiện compile và recompile hay không.