Làm việc với công cụ Activity Monitor trên SQL Server Phần 5

Câu truy vấn tốn tài nguyên nhất khác với câu truy vấn hiệu năng thấp. Có thể trong SQL server của bạn câu truy vấn nào cũng có hiệu năng cao nhưng sẽ có câu truy vấn tốn tài nguyên nhất.

Bản thân Activity Monitor không giúp chúng ta xác định những câu truy vấn tệ hại mà chỉ liệt kê những câu truy vấn nào tốn kém nhất trong khoảng thời gian theo dõi. Và danh sách này chính là những câu truy vấn bạn có thể hiệu chỉnh để cải thiện hiệu năng chung của SQL Server.

Bảng Recent Expensive Queries

Mặc định bảng này sẽ liệt kê những câu truy vấn tốn tài nguyên nhất trong 20 giây vừa qua (mặc dù giá trị Refresh Interval mình set là 10). Từ giao diện chính bạn có thể nhấn chuột phải lên bất kì câu truy vấn nào và chọn “Edit Query Text” để xem đầy đủ nội dung câu truy vấn. Hoặc chọn “Show Execution Plan” để xem cách SQL Server thực thi câu truy vấn này.

Chúng ta sử dụng script đếm số row từng bảng trong database stackoverflow như sau:

Ý nghĩa của các cột trong bảng trên như sau:

• Query: Câu lệnh truy vấn đang bị theo dõi.
• Executions/min: Số lần thực thi trong 1 phút của câu truy vấn này.
• CPU (ms/sec): Giá trị trung bình tài nguyên CPU dùng bởi câu truy vấn này trong 1 giây.
• Physical Reads/sec: Giá trị trung bình lượng physcial read trong 1 giây.
• Logical Writes/sec: Giá trị trung bình lượng logical write trong 1 giây.
• Logical Reads/sec: Giá trị trung bình lượng logical read trong 1 giây.
• Average Duration (ms): Giá trị trung bình thời gian mỗi lần thực thi.
• Plan Count: Số lượng query plan của câu truy vấn dạng này trong plan cache.
  

Xem thông tin Execution Plan của một câu lệnh truy vấn tốn tài nguyên trong bảng bằng việc chuột phải câu lệnh chọn "Show Execution Plan" như hình dưới:

Activity Monitor xác định câu truy vấn tốn tài nguyên nhất như thế nào?

Chúng ta lại sử dụng SQL Profiler để thấy cách tổ chức và thu thập thông tin giống như Wait Statistics. Activity Monitor sẽ tạo bảng tạm #am_get_querystats và stored procedure để mỗi lần thực thi lấy data từ hai DMVs sys.dm_exec_query_stats và sys.dm_exec_requests tính toán rồi đổ vào bạn tạm đó. Lần sau thực thi sẽ lấy data mới trừ data cũ sẽ ra data phát sinh trong khoảng interval 10 giây. DMV sys.dm_exec_query_stats lưu lại các thông số về hiệu suất các lần thực thi trước của một câu truy vấn, còn sys.dm_exec_requests chứa thông tin về request đang được thực thi.

Sử dụng Activity Monitor và hiểu được cách nó thu thập danh sách những câu truy vấn tốn tài nguyên này giúp chúng ta hiểu hơn về nguyên nhân tạo ra workload tương ứng (trong 10 giây vừa rồi) trong SQL Server. Sắp xếp theo tiêu chí tài nguyên mà bạn quan tâm (CPU, logical read, logical write, duration) rồi chọn một trong những câu truy vấn và áp dụng các kĩ thuật tối ưu truy vấn sau đó quan sát workload để thấy sự khác biệt. Thông thường top 3-5 câu truy vấn tốn kém nhất sử dụng phần lớn tài nguyên của server.

Bài sau chúng ta sẽ tìm hiểu bảng cuối cùng trong Activity Monitor – Active Expensive queries. Đây là nơi cho bạn biết những câu truy vấn nào thật sự đang được thực thi.

Hy vọng qua bài viết này các bạn học quản trị SQL Server đã hiểu rõ hơn về bảng Recent Expensive Queries giúp bạn tìm thấy được câu lệnh tốn tài nguyên nhất trong hệ SQL Server để tìm hiểu và xử lý các vấn đề liên quan. Bạn cũng có thể xem ở đây bảng Disk I/O latency trong  công cụ Activity Monitor trên SQL Server phần 4.

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