Bài viết này chúng ta cùng tìm hiểu hai loại wait type phổ biến liên quan đến memory và network đó là RESOURCE_SEMAPHORE và ASYNC_NETWORK_IO.
RESOURCE_SEMAPHORE
Thuộc tính Maximum Server Memory thiết lập lượng memory tối đa SQL Server có thể dùng cho buffer pool. Từ pool này SQL Server sẽ cấp phát cho nhiều đối tượng khác như data cache, plan cache và đặc biệt là workspace memory. Workspace memory là vùng cấp phát cho những câu truy vấn cần memory lưu tạm data trong quá trình thực thi.
Khi một câu truy vấn gửi đến server để yêu cầu thực thi, nó cần memory cho 3 mục đích khác nhau. Thứ nhất là dùng cho việc compile query (query optimization) để tìm ra plan tốt. Thứ 2, sau khi có được compiled plan nó sẽ request một không gian chứa plan này trong plan cache (để tái sử dụng) và release memory compile ở trên. Thứ 3, khi execute nó sẽ cần memory để lưu data tạm thời cho các thao tác sort, hash match và parallelism.
Một vài thuật ngữ khác được dùng để ám chỉ lượng memory cho mục đích thứ 3 này như memory grant, query execution memory, workspace memory và memory reservation.
Có thể xem query execution memory gồm hai phần required memory và additional memory. Required memory được dùng để tạo cấu trúc dữ liệu phục vụ cho quá trình thực thi thao tác sort và hash match, kích thước của lượng memory này là cố định cho từng operator. SQL Server không thể bắt đầu thực thi truy vấn nếu thiếu lượng memory này.
Additional memory là lượng memory cần thiết để lưu các rows trong quá trình xử lý truy vấn. Kích thước của lượng memory này phụ thuộc vào cardinality estimator tính ra số rows và kích thước mỗi row. Nếu số lượng rows thực tế khi xử lý nhiều hơn lượng memory đã được cấp phát SQL Server sẽ lưu một phần các rows này xuống đĩa cứng (tempdb spills)
Không gian workspace memory có hạn, nó không được vượt quá 75% của buffer pool ( và buffer pool lại bị giới hạn bởi maximum server memory). Một câu truy vấn mặc định không được sử dụng quá 25% của workspace memory, nhưng giá trị này có thể thay đổi qua thông số REQUEST_MAX_MEMORY_GRANT_PERCENT trong Resource Governor.
Ví dụ buffer pool của bạn có kích thước 2GB, workspace memory đâu đó khoảng 1.5GB, và câu truy vấn sẽ bị giới hạn lượng memory grant khoảng 375MB, dù nhu cầu của nó vượt quá con số đó SQL Server cũng sẽ giảm xuống mức thiết lập này.
Resource semaphore là đối tượng quản lý việc cấp phát memory grant trong SQL Server. Khi resource semaphore nhận request memory từ một câu truy vấn, nếu có đủ thì câu truy vấn đó sẽ được cấp phát lượng memory đã request và tiếp tục thực thi.
Khi tiếp nhận một request mà không còn đủ memory trống, resource semaphore sẽ đưa request này vào hàng đợi, chờ cho đến khi có đủ lượng memory mà request này yêu cầu mới cấp phát. Đây là hàng đợi có thứ tự request nào vô trước sẽ được phục vụ trước.
Và khi một request đợi để được cấp phát tài nguyên memory grant, SQL Server sẽ gán cho nó wait type RESOURCE_SEMAPHORE, đây chính là loại wait type chúng ta đề cập ở đầu bài. Hãy cùng xem demo dưới đây để hiểu rõ hơn về loại wait type này.
Trước tiên mình sẽ chỉnh Maximum Server Memory trên máy mình xuống 2GB
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO
Tiếp đến, chúng ta tạo một bảng với kích thước tầm khoảng 2 GB, để dễ dàng cho việc ước lượng thì hãy tạo bảng có row với kích thước đủ lớn để nó chiếm cả một page 8KB và insert khoảng 250K dòng.
USE tempdb
GO
CREATE TABLE T (id INT, data1 CHAR(8000), createdDate DATETIME)
DECLARE @i INT = 1
BEGIN TRAN
WHILE @i <= 250000
BEGIN
INSERT T
SELECT @i, CAST(@i AS CHAR(10)), GETDATE()
SET @i += 1
END
COMMIT
Kiểm tra kích thước của bảng T để thấy giống như hình dưới, Data space 1953.125 MB và 250000 dòng.
Chúng ta hãy thực thi câu truy vấn với mệnh đề ORDER BY để thấy nó sử dụng memory như thế nào. Các bạn hãy mở actual plan lên mới có thể kiểm tra các thông số liên quan memory. Để demo đơn giản mình thêm option MAXDOP 1 tránh cho SQL Server sử dụng xử lý song song.
USE tempdb
GO
SELECT *
FROM T
ORDER BY createdDate
OPTION(MAXDOP 1)
Bạn click chuột phải vào operator SELECT trong execution plan, chọn properties để có kết quả như hình dưới
DesiredMemory là lượng memory theo tính toán cần có để chạy câu truy vấn, xấp xỉ 2,4GB, nhưng con số này vượt quá giá trị được set cho một câu truy vấn nên sẽ bị Resource Semaphore kéo về con số 375MB, giống như mình đã tính toán ở ví dụ bên trên. Con số 375MB này là lượng memory request và đã được cấp phát.
Để tạo ra wait type RESOURCE_SEMAPHORE, chúng ta cần sử dụng hết workspace memory xấp xỉ 1,5GB này. Do đó hãy mở bốn cửa sổ query trên SSMS và chạy lại câu truy vấn trên cùng lúc, chúng ta sẽ thấy một session trong đó không được cấp phát memory nên sẽ phải chờ với wait type là RESOURCE_SEMAPHORE.
Hãy dùng lại script phần “Phân tích hiệu năng SQL Server với DMV sys.dm_os_waiting_tasks” để theo dõi session nào có wait type này.
Session 61 đang đợi memory grant nên sẽ có wait type là RESOURCE_SEMAPHORE. SQL Server cung cấp các thông tin cụ thể liên quan đến memory của một câu truy vấn như lượng memory request là bao nhiêu, grant bao nhiêu, khi nào thông qua DMV sys.dm_exec_query_memory_grants. Hãy cùng xem session 61 này khi chưa được grant trông như thế nào.
SELECT OBJECT_NAME(t.objectid, t.dbid) objname,SUBSTRING(t.TEXT, statement_start_offset / 2 + 1, (
(CASE
WHEN r.statement_end_offset = - 1 THEN (LEN(CONVERT(NVARCHAR(MAX), t.TEXT)) * 2)
ELSE r.statement_end_offset END
) - r.statement_start_offset
) / 2 + 1) AS statement_text,CAST(bt.query_plan AS XML) AS batch_plan,
m.session_id, m.dop,m.request_time, m.grant_time,m.ideal_memory_kb, m.requested_memory_kb,
m.required_memory_kb, m.used_memory_kb
FROM sys.dm_exec_query_memory_grants m
INNER JOIN sys.dm_exec_requests r ON m.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_text_query_plan(r.plan_handle,r.statement_start_offset,r.statement_end_offset) bt
WHERE r.session_id <> @@SPID
ORDER BY grant_time
Cột grant_time của session 61 đang có giá trị NULL, tức nó đang chờ cấp phát grant memory. Trong khi các requests khác đã được cấp phát và đang thực thi.
Một số nguyên nhân phổ biến làm cho câu truy vấn của bạn xuất hiện sort và hash match như có sử dụng ORDER BY, GROUP BY, DISTINCT, JOIN (hash join). Bên cạnh đó, nếu câu truy vấn phải xử lý lượng data lớn dễ dẫn tới quyết định sử dụng xử lý song song góp phần hao tổn memory.
Bạn cần tìm hiểu sự xuất hiện của các operators này có hợp lý hay không, đôi khi vì cách viết câu truy vấn mà dẫn tới xuất hiện các operators này chứ không hẳn phải có các mệnh đề trên xuất hiện trong câu truy vấn. Ngoài ra, có index phù hợp sẽ tránh được thao tác sort, hash.
ASYNC_NETWORK_IO
Đây là loại wait type liên quan đến vấn đề throughput trên network connection. Tình huống này thường xảy ra khi client nhận và xử lý data không đủ nhanh so với SQL Server trả về. Có thể là vì xử lý ở client phức tạp nên chậm hoặc lượng data trả về nhiều quá, dẫn đến SQL Server phải chờ client sẵn sàng mới có thể gửi tiếp. Khi đó, SQL Server sẽ gán wait type là ASYNC_NETWORK_IO.
Một tình huống khác mình đã từng gặp đó là khi lượng data transfer giữa SQL Server và client quá nhiều dẫn đến hết băng thông của network. Thông thường NIC có băng thông 1Gb/s chỉ có thể transfer 128MB/s, nếu lượng data vượt quá con số này sẽ dẫn đến tình trạng tương tự.
Chúng ta có thể dễ dàng thấy điều này khi sử dụng SSMS. SSMS cũng đóng vai trò là client nhận data và hiển thị cho người dùng ở tab result. Với câu truy vấn SELECT * FROM T có thể tạo ra được tình huống wait type này.
Wait type này là lý do để chúng ta tuân theo nguyên tắc chỉ lấy data vừa đủ xài, không nên lấy những cột khi không cần thiết.
Như vậy chúng ta đã hiểu được cách hình thành và những nguyên nhân gây hai loại wait type RESOURCE_SEMAPHORE và ASYNC_NETWORK_IO. Hi vọng kiến thức trong bài viết này sẽ giúp bạn giải quyết chúng để tăng hiệu năng của SQL Server.
Nguồn tham khảo
