Có thể nói hiệu năng SQL Server được phản ánh bởi trạng thái của thread khi chúng đang thực hiện request. Nếu bạn chưa biết có những trạng thái nào hãy tìm hiểu lại ở bài viết trước, chúng sẽ là một trong ba trạng thái runnable, running và suspended. Runnable là trạng thái xếp hàng chờ đến lượt dùng CPU, running là đang được xử lý trên CPU (processor) và suspended tức là đang chờ tài nguyên nào đó. Theo lý mà nói những request đang chờ tài nguyên là đối tượng ảnh hưởng lớn đến hiệu năng SQL Server, vì chúng phí thời gian vào việc chờ đợi.

Phân tích hiệu năng SQL Server với wait statistics là việc xác định những tài nguyên gây chờ (wait type) cùng với thời gian chờ (wait time) đủ lớn. Từ đó truy tìm lý do và hướng xử lý. Có nhiều cách xác định wait type của một request, trong bài viết này mình chỉ tập trung vào hai DMV chính. Một cái là nơi SQL Server lưu thông những threads đang nằm trong hàng đợi waiter list (có trạng thái suspended), và cái khác là tổng thời gian chờ tài nguyên trên SQL Server instance.

Phân tích hiệu năng SQL Server với DMV sys.dm_os_waiting_tasks

Đây chính là nơi lưu giữ những threads đang nằm trong runnable queue và waiter list, hay nói cách khác, DMV này cung cấp thông tin cho bạn biết SQL Server đang chờ cái gì? Chờ tài nguyên hay là chờ CPU? Những thông tin này sẽ cho bạn có cái nhìn tổng quan SQL Server đang bị gì.

SELECT * 
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50


phân tích hiệu năng SQL Server với wait statistics
Hình 1: sys.dm_os_waiting_tasks

Câu truy vấn trên loại những session_id nhỏ hơn 50 (là những system processes). Kết quả trả về của sys.dm_os_waiting_tasks trên máy của mình như trong hình 1. Cột wait_type thể hiện loại tài nguyên mà các threads đang chờ, session_id 123 đang chờ lock LCK_M_U (và cột resource_description cung cấp thông tin cụ thể lock trên object nào). Session 79 đang dùng cơ chế OLE DB để thực thiện request, session 156 đang chờ client nhận data. Chúng ta sẽ tìm hiểu một số loại wait types phổ biến ở phần sau, trước hết hãy tìm hiểu ý nghĩa các cột từ DMV này.

  • waiting_task_address địa chỉ trên memory của tasks đang đợi tài nguyên.
  • session_id chính là id của session liên quan tới task, thread này.
  • exec_context_id thường là 0, đối với những câu truy vấn song song (sử dụng cùng lúc nhiều threads cho 1 request) sẽ có giá trị khác 0.
  • wait_duration_ms thời gian task đã chờ wait type hiện tại, tính bằng đơn vị milliseconds.
  • wait type loại tài nguyên thread đang chờ.
  • resource_address địa chỉ trên memory của tài nguyên chờ, không phải wait type nào cũng có thông tin này.
  • blocking_task_address, blocking_session_id, blocking_exec_context_id nếu thread đang đợi tài nguyên này bị block bởi một thread khác, ba thông tin này sẽ có giá trị tương ứng. Nếu không phải là tình huống blocking thì ba giá trị này sẽ NULL.
  • resource_description một số loại wait type sẽ cung cấp thêm thông tin về tài nguyên đang đợi, vì những thông tin này là quan trọng để xác định cụ thể tài nguyên nào. Ví dụ nếu thread đang chờ một lock, cột này sẽ cho ta biết lock trên row hay page, table và ID để xác định tài nguyên đó.

Như đã đề cập ở trên, khi SQL Server đang gặp vấn đề hiệu năng, bất kể là có một câu truy vấn nào đó chạy chậm hay cả hệ thống bỗng dưng ì ạch, bạn đều có thể tìm thấy thông tin gì đó từ DMV này. Nếu lý do chậm là do các requests phức tạp đòi hỏi nhiều xử lý sẽ thể hiện ở thông số %Processor Time. Còn nếu requests chậm phản hồi là do đang kẹt trong việc đợi tài nguyên nào đó thì sẽ nằm trong waiter list, và bạn sẽ thấy rõ nó đang chờ resource gì và đã chờ bao lâu rồi từ DMV này. Quả là tuyệt vời, mọi thứ đều trưng bày ra hết chỉ đợi bạn khám phá.

Chúng ta có thể sử dụng kết hợp với những DMVs khác để lấy thêm thông tin như nội dung statement đang chờ, thông số reads (I/O) và cpu time, client…của thread đang chờ tài nguyên. Đây đều là những thông tin cần thiết khi phân tích hiệu năng SQL Server. Dưới đây là câu truy vấn mình thường dùng cho việc này.

SELECT [spid] = er.session_Id, ecid, [blockedBy] = er.blocking_session_id 
	, "database[Login]" = REPLACE(DB_NAME(sp.dbid) + ' [' + RTRIM(LTRIM(loginame)) + ']','[]','[--]')
	, "CMD[status]" = RTRIM(sp.cmd) + ' [' +er.status + ']', [wait] = er.wait_type,wt.wait_type,sp.waitresource
	, [current stmt] = SUBSTRING (qt.text, er.statement_start_offset/2,
												(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)  
													ELSE er.statement_end_offset END - er.statement_start_offset)/2)
	,sp.lastwaittype,wt.resource_description , wait_duration_ms
    ,[current batch] = qt.text, logical_reads, cpu, program = program_name, hostname
FROM sys.dm_exec_requests er
	INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
	INNER JOIN sys.dm_os_waiting_tasks wt ON sp.spid = wt.session_id AND sp.ecid = wt.exec_context_id
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE er.session_Id > 0              -- Ignore system spids 50.
AND er.session_Id NOT IN (@@SPID)     -- Ignore this current statement.
ORDER BY [spid]
GO


Phân tích hiệu năng với DMV sys.dm_os_wait_stats

DMV này lưu trữ danh sách các wait type mà phiên bản SQL Server đang có. Cùng với nó là giá trị cộng dồn của các thông tin về wait statistics như thời gian chờ tài nguyên bao nhiêu, thời gian chờ CPU bao lâu cho từng wait type, kể từ khi SQL Server restart hoặc lần cuối chạy command DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR). Câu lệnh này sẽ xóa hết thông tin wait statistics hiện có và tính lại từ đầu. Thông tin cộng dồn gồm có số lần xảy ra sự kiện chờ tài nguyên, tổng thời gian chờ theo từng wait type như bạn thấy ở câu truy vấn dưới.

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC 

Hình 2: sys.dm_os_wait_stats
  • wait_type loại wait, thường biểu thị cho tài nguyên mà các threads đã chờ.
  • wait_time_ms tổng thời gian cộng dồn các threads đã chờ trên loại wait type này. Giá trị này đã tính luôn signal_wait_time_ms. Có thể hình dung các lần chờ trong DMV sys.dm_os_waiting_tasks được cộng dồn vào cột này, theo từng wait type.
  • singal_wait_time_ms tổng thời gian cộng dồn của các threads chờ trên runnable queue.
  • waiting_tasks_count tổng cộng dồn số lần xảy ra sự kiện chờ trên wait type
  • max_wait_time_ms lần chờ lâu nhất của các threads.

Các bạn có thể tính ra resource wait bằng cách lấy total wait trừ cho signal waite. Nhắc lại signal wait là thời gian chờ CPU, nếu lượng signal wait này mà đủ lớn chứng tỏ SQL Server instance của bạn đang gặp vấn đề CPU pressure.

SELECT wait_type, waiting_tasks_count,
(wait_time_ms - signal_wait_time_ms) AS 'Resource_Wait_Time_ms',
signal_wait_time_ms ,wait_time_ms AS 'Total_Wait_Time_ms',
COALESCE(wait_time_ms / NULLIF(waiting_tasks_count,0), 0) AS 'Average_Wait_Time_ms'
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC 


Với dữ liệu lịch sử như DMV này chúng ta sẽ sử dụng như thế nào để cải thiện hiệu năng SQL Server? Nó không trả lời cho câu hỏi tại một thời điểm cụ thể tuần trước xảy đã ra những wait type nào và bao lâu? Dữ liệu này giúp chúng ta xác định được nhóm wait types và tài nguyên nào làm SQL Server chờ đợi nhiều nhất. Tôi thường sử dụng câu truy vấn của Paul Randal ở link này để kiểm tra top những wait type nhiều nhất trong instance. Câu truy vấn này có loại ra những wait type không quan trọng và đặc biệt nó được chủ nhân cập nhật thường xuyên. Hình dưới là kết quả trên máy mình.

hiệu năng SQL Server - top wait types
Hình 3. Top wait types trong SQL Server instance

Câu truy vấn chỉ lấy ra những wait type có thời gian chờ đủ lớn đóng góp vào danh sách các tài nguyên là điểm nghẽn có thể có trong SQL instance. Chỉ cần bạn tập trung giải quyết những wait type xuất hiện trên danh sách này thì hiệu năng đã cải thiện rất nhiều rồi.

CXPACKET là wait type xuất hiện bởi những câu truy vấn song song (parallelism), nếu xét theo % thì 48% wait time trong instance là bởi parallelism. Điều đó có nghĩa rằng database của bạn có nhiều câu truy vấn phức tạp, đòi hỏi chi phí xử lý nhiều (vượt qua ngưỡng Cost Threshold For Parallelism) nên SQL Server mới sử dụng parallel cho chúng. Nếu đây là tính huống của bạn, điều cần làm là xác định xem thật sự những câu truy vấn mình cần lấy nhiều data từ nhiều bảng hay không, hoặc từ những bảng lớn (thường thấy trong các hệ thống data warehouse). Ngược lại, hay chỉ lấy một phần từ chúng nhưng vì viết chưa tối ưu gây ra tình huống ước ượng quá đà (over estimated) nên SQL Server dùng parallel. Đôi khi do bản thân SQL Server phân chia công việc giữa các threads không đồng đều cũng gây ra kết quả tương tự.

Phân tích kết quả này giúp chúng ta xác định được phạm vi nghẽn cụ thể và dành nhiều thời gian cho nó hơn. Ví dụ nếu phần lớn wait types xuất hiện trong top này thuộc về disk I/O, ta sẽ dùng các tiêu chí liên quan đến disk I/O để đi kiểm tra và tìm nguyên nhân. Nếu top wait types thuộc về locks chúng ta lại giải quyết bài toán kiểu khác. Theo tôi đây là hướng tiếp cận tốt trong quá trình cải thiện hiệu năng SQL Server.

Ở bài sau mình sẽ liệt kê và giải thích một số wait types phổ biến kèm theo nhóm tài nguyên mà nó thuộc về, như vậy sẽ giúp các bạn dễ nhận biết SQL Server instance của mình đang gặp vấn đề bottleneck ở tài nguyên nào (nếu có).