Làm việc với công cụ Activity Monitor trên SQL Server Phần 3 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 Server tìm hiểu về bảng Resource Waits trong Activity Monitor cũng như một số khái niệm Wait Statistics trong SQL Server. Nếu Waiting Tasks thể hiện số lượng processes đang chờ tài nguyên và bảng Processes hữu ích trong việc chỉ ra các session block nhau (cũng là một dạng chờ tài nguyên) thì bảng Resource Waits lại cho ta biết tài nguyên nào đang bị nghẽn, hay nói cách khác các processes đang chờ tài nguyên gì nhiều nhất. Wait statistics là thông tin SQL Server thống kê các request đang chờ tài nguyên gì và chờ trong bao lâu. Bảng Resource Waits giúp chúng ta xác định các tài nguyên như Memory, CPU, Network I/O hay Disk I/O đâu là điểm nghẽn ảnh hưởng hiệu năng của SQL Server. Hình 1: Bảng Resource Waits trong Activity Monitor Một số khái niệm về Wait Statistics trong SQL Server SQL Server thực thi một request bằng scheduler và thuật toán điều phối (scheduling ) của riêng nó, hay nói cách khác là nó có OS riêng gọi là SQLOS. Khi ứng dụng yêu cầu một kết nối (connection), SQL Server sẽ xác thực người dùng và tạo cho connection này một session và gán cho một id tương ứng là session_id. Khi user gửi một request thông qua session này, SQL Server sẽ tạo ra một hoặc nhiều tasks tương ứng. Mỗi task sẽ lại được gán một worker thread để có thể sử dụng tài nguyên CPU thực hiện công việc trong task đó. Không phải các worker thread đều có thể sử dụng CPU thẳng cho đến khi thực hiện xong công việc của mình. Trong quá trình thực thi request, vì SQL Server sử dụng thuật toán điều phối cooperative nên mỗi worker thread sẽ có quota cho mỗi lần dùng CPU là 4ms (mặc định) gọi là quantum. Hết lượng quota này worker thread sẽ nhảy vào hàng đợi, nhường CPU cho worker threads khác và chờ đến lượt mình lần nữa, cứ như vậy cho đến lúc thực hiện xong công việc. Nhưng không phải request chỉ cần dùng mỗi CPU time, khi cần tài nguyên khác như truy cập data page mà chưa có trên buffer pool hoặc network I/O chưa sẵn sàng để transfer data về client hoặc memory không đủ để thực hiện công việc thì các request đều chuyển sang trạng thái chờ tài nguyên. Đến khi các tài nguyên này sẵn sàng, worker threads sẽ chuyển sang trạng thái chuẩn bị để có thể sử dụng CPU. Các trạng thái của một worker thread có thể mô tả như sau: RUNNING đang ở trên CPU SUSPENDED bất cứ khi nào worker thread cần resource mà không thể có ngay, nó sẽ rời CPU và chuyển vào hàng đợi (không thứ tự) resource với status là suspended. Đến khi resource này sẵn sàng cho thread thì nó sẽ nhảy sang hàng đợi (có thứ tự) runable RUNABLE là status của các threads không phải đợi resource mà cũng chưa thể dùng CPU. Đây là trạng thái chờ đợi thể hiện áp lực lên CPU thường được gọi là signal wait. Trong quá trình thực hiện các request SQL Server sẽ ghi nhận từng loại tài nguyên mà các threads đã đợi cũng như tổng thời gian chờ. Thời gian thực thi của một request: duration = CPU time + wait time. Bảng Resource Waits Có nhiều loại wait có thể có cho một resource nào đó, ví như lock thì ta có LCK_M_X, LCK_M_IX, LCK_M_S hoặc buffer I/O thì ta có PAGEIO_LATCH_EX, PAGEIO_LATCH_SH, PAGEIO_LATCH_UP,… Activity Monitor gom nhóm các wait type mô tả cùng một loại tài nguyên để quản trị viên dễ dàng phân biệt thành phần nào của SQL Server đang gặp sự cố về hiệu năng và dự đoán được chức năng nào của ứng dụng hay câu truy vấn nào của người dùng có khả năng gây ra. Từ đó giới hạn vùng tìm kiếm để nhanh chóng tìm hiểu rõ vấn đề. Ý nghĩa các cột trong bảng Resource Wait như sau: Wait Category: Nhóm các wait type trên cùng tài nguyên, chúng ta sẽ cùng tìm hiểu Activity Monitor gom nhóm như thế nào ở phía dưới. Wait Time (ms/sec): Thời gian đợi trung bình (đơn vị millisecond) trên giây theo mỗi chu kỳ “Refresh Interval”. con số này được tính trên tất cả các wait type trong category. Recent Wait Time (ms/sec): Trong trường hợp “Refresh Interval” nhỏ hoặc bạn nhấn F5 liên tục, giá trị wait time trong khoảng thời gian ngắn này thể hiện rõ resource nào đang nghẽn nên Activity Monitor sử dụng công thức tính trung bình có trọng số nhằm tăng mức độ tin cậy của giá trị này. Average Waiter Count: Số lượng xuất hiện các lần đợi tài nguyên trong khoảng “refresh interval”. Một task có thể trải qua nhiều trạng thái trong quá trình thực thi chạy->chờ->chờ cpu->chạy->chờ…mỗi một lần một task nhảy vào hàng đợi để chờ tài nguyên là giá trị này tăng lên 1. Cumulative Wait Time (sec): Tổng thời gian chờ (đơn vị giây) của các wait type tính từ lúc SQL Server start. Activity Monitor lấy thông tin wait statistics từ đâu? Để hiểu rõ hơn cách Activity Monitor thu thập các thông tin về wait statistics này chúng ta có thể sử dụng SQL Profiler để bắt xem nó gửi gì xuống SQL Server từ khi quản trị viên mở dashboard cho đến lúc mỗi refresh interval thực thi. Vì profiler ảnh hưởng đến hiệu năng chung của SQL Server nên chỉ thực hiện điều này trên môi trường test. Nếu bạn đã cài SSMS 20.0 trở lên bạn gõ tìm kiếm trên máy từ khóa "SQL Server Profiler 20" để mở công cụ ứng dụng này, cửa sổ SQL Profiler sẽ hiện lên. Tiếp đó từ menu bạn chọn File > New trace.. và đăng nhập vào SQL instance bạn đang dùng. Ở cửa sổ Trace Properties chọn tab Events Selections và bỏ chọn các events khác chỉ chừa lại RPC:Completed và SQL:BatchCompleted như hình bên dưới, sau đó nhấn nút Run. Hình 2: Sử dụng SQL Profiler để tìm hiểu Activity Monitor lấy wait statistics từ đâu Thực hiện chạy một số lệnh SQL trên máy tính của bạn, khi Profiler đã sẵn sàng bạn hãy mở Activity Monitor lên, ngay lập tức bạn sẽ thấy những objects, stored procedures mà Activity Monitor tạo ra dùng để theo dõi wait statistics và tính toán để đưa lên giao diện như hình dưới: Bạn có thể chọn ra vài wait type và tìm hiểu ý nghĩa của chúng để làm quen với khái niệm waits statistics cũng như cách hoạt động. Bạn sẽ hiểu tại sao đây là phương pháp tiếp cận hiệu quả trong các tình huống xử lý sự cố hiệu năng hệ thống. SQL Server lưu giữ thông tin về wait statistics trong hai DMVs sys.dm_os_wait_stats & sys.dm_os_waiting_tasks. Bạn có thể tham khảo thêm bài viết: Phân tích hiệu năng SQL Server với Wait Statistics ở đây. 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