Làm việc với công cụ Activity Monitor, tình trạng Blocking trên SQL Server Phần 2

Trong bài viết này Stanford sẽ tiếp tục hướng dẫn các bạn học quản trị SQL tìm hiểu chi tiết bảng Processes và theo dõi tình huống blocking trên Activity Monitor nhiều quá trong SQL Server.

Ở bài trước đã giúp bạn nắm được tình trạng hiện tại của SQL Server, có thể nó đang có lượng request nhiều hơn bình thường hoặc database I/O tăng cao bất thường, hoặc cũng có thể số lượng process trong biểu đồ waiting tasks nhiều quá. Nếu chưa đọc bài viết này bạn có thể xem ở đây: Tìm hiểu công cụ Activity Monitor trong SQL Server. Bước tiếp theo bạn nên làm là nhìn vào bảng Processes để tìm xem thành phần nào có khả năng gây ra những hiện tượng này.

Bảng Processes

Chức năng của bảng Processes này là cung cấp chi tiết về những processes đang chạy dưới SQL Server. Nó sẽ cho bạn biết có bao nhiêu requests? Những requests này đến từ client nào? Connect vào database gì? Thực hiện việc UPDATE hay SELECT? Trạng thái hiện tại như thế nào? Hiệu suất truy vấn có hiệu quả hay không (thông qua thời gian chờ)? Có tranh chấp tài nguyên lẫn nhau không? Và sử dụng bao nhiêu memory? Bạn có thể lấy được nhiều thông tin từ bảng này để giải đáp cho kết quả bạn thấy trên các biểu đồ của bảng Overview.

Giả sử bạn thấy Waiting tasks tăng cao và muốn biết nó là do tình huống blocking hay là wait type (sẽ đề cập bên dưới) khác. Nếu bạn chưa quen thuộc với thuật ngữ blocking thì hãy xét ví dụ sau. Process A cần tài nguyên là data của pageID 178 và yêu cầu SQL Server cấp phát lock (ví dụ lock X) trên page này để truy cập, nhưng SQL Server không thể cấp phát vì process B đã giữ lock X (xung đột với request của A) nên process A phải vào hàng đợi, chờ process B thực hiện xong, ta nói process A bị blocked bởi process B hoặc process B block process A.

Hình 1: Thông tin các process đang chạy dưới SQL Server

Trong đó từng cột thông tin có ý nghĩa như sau:

• Session ID – Mỗi một connection được khởi tạo đến SQL Server sẽ được gán một giá trị gọi là session_id.
• User Process – Chỉ định process này là của user hay của system. Có giá trị là 0 nếu là process của system process. Mặc định khi bạn mở Activity Monitor bảng Processes chỉ lấy những process của user với giá trị bằng 1.
• Login – Tên của login đang sử dụng session này.
• Database – Tên của database mà connection trỏ tới.
• Task State – Trạng thái của tasks, có các giá trị như PENDING, RUNABLE, RUNNING, SUSPENDED, DONE, SPINLOOP.
• Command – Loại command mà task đang được thực thi. Sẽ là một trong các giá trị SELECT, INSERT, UPDATE, DELETE, BACKUP DATABASE.
• Application – Tên ứng dụng connect tới SQL Server trên session này. Trên máy lab của mình thường thấy SSMS, SQLQueryStress.
• Wait Time (ms) – Tổng thời gian chờ (wait_duration_ms) tài nguyên mà task này đã thực hiện, theo đơn vị milliseconds.
• Wait Type – Mỗi tình huống chờ sẽ được SQL Server gán cho một tên để phân biệt, có thể là WRITE_LOG, PAGEIOLATCH_SH
• Wait Resource – Mô tả về tài nguyên mà session này đang chờ (resource_description).
• Blocked By – Proccess này có bị blocked bởi process nào hay không, nếu có thì session_id của process đó sẽ hiển thị ở cột này.
• Head Blocker – Nếu process A này block process B và không bị blocked bởi một process nào khác thì giá trị này là 1.
• Memory Use (KB) – Lượng memory cấp phát cho câu lệnh T-SQL của process này.
• Host Name – Tên machine (client) thực hiện connection này.
• Workload Group – Tên workload group của Resource Governor mà session này được gán vào.
  

Theo dõi tình huống blocking trên Activity Monitor

Bây giờ chúng ta hãy tạo ra một tình huống blocking như sau. Để giống với ví dụ ở trên ta có process B thực hiện update data của bảng Users nhưng chưa commit transaction. Tiếp đó process A truy vấn dòng dữ liệu này nhưng lock không tương thích nên chưa được cấp phát và phải đợi process B. Ta sẽ thấy process B block process A. Tiếp nữa process C nhảy vào thực hiện việc update trên cùng dòng dữ liệu này nhưng khác cột, ta hãy xem Activity Monitor sẽ thể hiện như thế nào. Các bạn chạy ba đoạn code dưới đây trên ba session khác nhau trong SSMS, theo đúng thứ tự đề cập trong ví dụ.
Process B cập nhật cột data1 cho bảng Test có Id bằng 10.

Hình 2: Phân tích blocking issue trên Activity Monitor

Kết quả trên máy tính của mình cho thấy process C bị blocked bởi process A, mặc dù A chưa được cấp phát lock để thực thi. Trong hình trên session_id 69 là process B, chạy update bảng Test và vì mình không chạy lệnh COMMIT nên nó chưa thật sự kết thúc câu lệnh update. Kế tiếp session_id 70 là process A truy vấn data của Test có Id bằng 10. Nhưng process này phải đợi lock LCK_M_S (cột Wait Type) và đánh dấu là bị blocked bởi session_id 69. Kế tiếp session_id 87 là process C vào update giá trị createdDate cho Test có Id bằng 10. Nhưng kết quả lại bị block bởi session_id 69 (process B) và có Wait Type là LCK_M_U. Trong chuỗi blocking này 69–>70–>87 thì session_id 69 dẫn đầu nên các bạn thấy cột Head Blocker của session_id 69 bật lên 1.

Đây là một tình huống blocking thường thấy trong các hệ thống OLTP(Online Transaction Processing). Trong thực tế chuỗi blocking có thể lên đến hàng chục thậm chí hàng trăm sessions tham gia. Từ kết quả phân tích như trên các bạn phát hiện session nào là header và tập trung phân tích vào nó để tìm giải pháp xử lý.

Bên cạnh đó nếu bạn đang muốn được đào tạo bài bản từ cơ bản đến nâng cao có thể tham gia ngay khóa học sql cùng chuyên gia giàu kinh nghiệm Stanford tại đây: http://bit.ly/2SLPYFF và nhận ưu đãi hấp dẫn của Stanford trong thời gian này. Bạn có thể gọi theo hotline: 0963 723 236 - 0866 586 366 để được gọi lại tư vấn trực tiếp nhé.

=============================
☎ STANFORD – ĐÀO TẠO VÀ PHÁT TRIỂN CÔNG NGHỆ
Hotline: 0963 723 236 - 0866 586 366
Website: https://stanford.com.vn
Facebook: http://bit.ly/2FN0TYb
Youtube: http://bit.ly/2TkKT7I

Tags: công cụ activity monitor sql server, phân tích hiệu năng sql server