Dùng RESOURCE_SEMAPHORE Waittype để chẩn đoán Memory Issues

Có 1 tác giả chia sẻ SQL Server chạy chậm, tìm hiểu các transactions thì thấy hầu hết là wait type RESOURCE_SEMAPHORE. Chứng minh đang có Memory pressure.

Vấn đề tiếp theo là tìm hiểu những câu query nào gây ra Resource_semaphore wait type, dẫn đến memory pressure.

Có 1 tiện ích bên trong SQL Server gọi là RESOURCE_SEMAPHORE làm nhiệm vụ cấp phát requested memory cho 1 query. Nếu 1 câu quey không thể gán lượng memory này bởi RESOURCE_SEMAPHONE, nó sẽ ở trong trạng thái chờ với 1 wait type là RESOURCE_SEMAPHORE. Thông tin này có thể tìm thấy nếu chúng ta query sysprocesses system table or sys.dm_exec_request DMV

Khi Resource_Semaphore nhận đc 1 câu query, nó kiểm tra xem câu query này có ở trong hàng đợi không. Nếu không, nó đặt vào hàng đợi. Nếu đủ bộ nhớ, nó sẽ grant requested memory cho câu query này và câu query có thể bắt đầu chạy. Nếu không, nó đặt câu query vào hàng đợi (wait queue) với 1 Resource_Semaphore wait type và server bắt đầu đối mặt với Memory Pressure

SELECT * FROM sys.sysprocesses
ORDER BY lastwaittype

--Dùng câu này để biết total memory available, total granted memory cho 1 instance
--để ý tham số grantee_count và waiter_count: cho biết total query đã được grant memory và total query đang chờ
SELECT * FROM sys.dm_exec_query_resource_semaphore

--Tiếp theo, đây là bước để tìm ra những câu truy vấn đang có Resource_Semaphore wait type, dựa vào plan_handle ta có thể tìm tiêp thông tin khác như execution plan

SELECT * FROM sys.dm_exec_query_memory_grants

--Để lấy SQL text từ plan_handle
SELECT * FROM sys.dm_exec_sql_text(sql_handle)

--Để lấy SQL execution plan từ plan_handle
SELECT * FROM sys.dm_exec_sql_plan(plan_handle)

Tham khảo: http://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131001