Tìm hiểu Wait Statistics để phân tích hiệu năng trong SQL Server Trong bài viết này bạn sẽ nắm rõ một số kiến thức về wait statistics giúp quản trị viên cơ sở dữ liệu dễ dàng hơn trong việc theo dõi và phân tích hiệu năng SQL Server. Mỗi khi thực thi một yêu cầu (request) thực hiện công việc bất kì, SQL Server đều ghi lại các dấu hiệu thể hiện những tình huống đã xảy ra như không có worker thread để bắt đầu, thiếu memory để chạy câu truy vấn, dữ liệu cần truy cập chưa có sẵn trong buffer pool phải load từ disk lên, xung đột khóa trên dòng dữ liệu với process khác, việc ghi xuống file log khá chậm,… Có được những thông tin này quản trị viên có thể nhận ra tài nguyên nào đang là điểm nghẽn và giới hạn phạm vi đào sâu phân tích, nhanh chóng tìm ra lý do là gì, phương án quyết thế nào cho hiệu quả. Wait statistics trong SQL Server là gì ? Sau khi gửi một yêu cầu truy vấn đến SQL Server, thời gian chờ phản hồi của người dùng được tính từ lúc request được gửi đi cho đến khi nhận được dòng dữ liệu cuối cùng (tạm thời không xét thời gian mở đóng kết nối). Khi nhận được yêu cầu, SQL Server sẽ thực thi các công việc cần thiết như truy cập dữ liệu, cắt xén, cộng gộp, sắp xếp…và trả kết quả về cho người dùng. Khoảng thời gian này phần lớn dành cho CPU thực hiện công việc, hay còn gọi là CPU time. Tuy nhiên, với những hệ thống cơ sở dữ liệu có hàng nhìn hoặc chục nhìn yêu cầu thực hiện trong 1 giây, tài nguyên của server sẽ phải chia sẻ giữa các yêu cầu nên xảy ra tranh chấp là điều không thể tránh khỏi. Những khi không có đủ tài nguyên để thực hiện (ví dụ như thiếu memory, không giành được lock) SQL Server sẽ dừng xử lý các yêu cầu này và đưa chúng vào hàng đợi, chờ cho đến khi tài nguyên chúng cần sẵn sàng mới có thể tiếp tục thực hiện. Mỗi khi một yêu cầu buộc phải vào hàng đợi (rời khỏi CPU), SQL Server sẽ ghi lại thời gian request này chờ (wait time) là bao lâu và lý do chờ là gì (wait type – thường ám chỉ tài nguyên request đó chờ). Đây được gọi là wait statistics và các dữ kiện này có thể truy vấn thông qua hai DMVs sau: sys.dm_os_waiting_tasks lưu giữ wait statistics cho những yêu cầu đang chờ tài nguyên ngay lúc này. sys.dm_os_wait_stats lưu giữ tổng thời gian chờ trên các loại tài nguyên kể từ lúc SQL Server khởi động, hoặc lần cuối bị clear wait statistics. Chúng ta sẽ tìm hiểu kĩ hơn về hai DMVs này, cũng như ý nghĩa các loại wait type ở phần sau. Hiện tại, để hiểu rõ hơn cách SQL Server đo đạt wait time như thế nào chúng ta hãy tìm hiểu mô hình xử lý công việc trong SQL Server (execution model). Mô hình xử lý công việc trong SQL Server SQLOS sẽ là thành phần chịu trách nhiệm điều phối, lập lịch và thực thi các yêu cầu trong SQL Server. Bạn có thể đọc thêm về các đối tượng tham gia quản lý luồng ở bài viết này, hoặc tìm hiểu thêm về kiến trúc task và thread trên Microsoft document. Khi một ứng dụng gửi yêu cầu kết nối, SQL Server sẽ xác thực người dùng và tạo cho connection đó một session. Thông qua session này, người dùng có thể gửi các request truy vấn data đến server. Ngay khi nhận được request, SQLOS scheduler sẽ tạo task cho request này và giao đối tượng task được tạo cho worker thread. Worker threads chính là thành phần tham gia việc lên lịch (scheduling) và cũng chỉ có worker thread mới được tiếp cận CPU để thực thi công việc. Có thể hiểu scheduler là đối tượng đại diện cho CPU. Khi khởi động SQL Server sẽ tạo ra một bộ các schedulers bằng với số lượng logical CPU mà SQL Server có. Ví dụ server bạn có 16 physical cores và enable hyper-threading, bạn sẽ có 32 logical cores (hay 32 logical CPUs), vậy bạn sẽ có 32 schedulers tương ứng (cộng thêm 1 scheduler cho DAC sẽ là 33). Mỗi scheduler này quản lý một tập các worker threads và tại một thời điểm chỉ cho một worker thread sử dụng CPU. Một worker thread khi mang trên mình một task nó chỉ có thể ở một trong ba trạng thái RUNNING – RUNNABLE – SUSPENDED và sự luân chuyển giữa các trạng thái thể hiện như hình bên dưới. SQLOS scheduler có ba thành phần tương ứng để lưu giữ các threads với ba trạng thái trên là processor (running), runnable queue (runnable), waiter list (suspended). RUNNING – Đang được thực thi trên CPU SUSPENDED – Bất cứ khi nào thread cần một tài nguyên mà không sẵn có, nó sẽ phải nhường CPU cho request khác, nhảy vào hàng đợi và đổi trạng thái thành suspended. Đây là hàng đợi không thứ tự, tài nguyên của request nào sẵn sàng thì request đó sẽ được lấy ra khỏi hàng đợi này. RUNNABLE – Những threads đang chờ sử dụng CPU sẽ phải chờ ở hàng đợi này, có thứ tự, hoạt động theo nguyên tắc FIFO (first in, first out). Các threads sẽ chuyển đổi qua lại giữa ba trang thái này cho đến khi công việc hoàn thành. Khi mỗi thread nhảy vào CPU để thực hiện công việc, trạng thái của nó sẽ chuyển thành RUNNING. Nó sẽ được SQLOS cấp cho một thời lượng 4 millisecond (gọi là quantum). Trong quá trình thực thi, nếu thread cần truy xuất data page trong buffer cache nhưng không thấy, nó không thể tiếp tục công việc nên sẽ gửi một I/O request để đọc pageID đó từ disk và rời khỏi CPU, đồng thời chuyển trạng thái của mình từ RUNNING thành SUSPENDED. Những threads có trạng thái suspended sẽ nằm trong hàng đợi (còn được gọi là waiter list) chờ tài nguyên của mình cho đến khi chúng sẵn sàng, và tài nguyên chờ được thể hiện bằng một wait type. Trong tình huống trên, khi thread chờ data page được tải từ disk lên sẽ có dạng wait type là PAGEIOLATCH_**. Nếu thread truy xuất data page để đọc (read) sẽ có wait type là PAGEIOLATCH_SH, nếu truy xuất data page để thay đổi (write) sẽ có wait type là PAGEIOLATCH_EX. Ngược lại, trong quá trình thực thi nếu tất cả tài nguyên thread cần đều sẵn sàng (không phải chờ), nó sẽ chạy hết thời lượng được cấp 4 millisecond, sau đó rời khỏi CPU vào cuối hàng đợi runnable và chuyển trạng thái từ RUNNING sang RUNNABLE. Các threads trong waiter list sẽ chờ cho đến khi được báo rằng tài nguyên cần dùng đã sẵn sàng, và được chuyển qua cuối hàng đợi RUNNABLE. Trong tình huống của thread ví dụ ở trên, khi data page đã được load lên memory, thread sẽ được báo rằng tài nguyên đã sẵn sàng và nó được chuyển vào cuối hàng đợi RUNNABLE đồng thời trạng thái của thread được đổi từ SUSPENDED sang RUNNABLE. Khi đó ta nói thread này được đánh thức (being signaled). Scheduler chỉ cho phép tại một thời điểm chỉ có một thread sử dụng CPU (trạng thái RUNNING). Khi một thread rời khỏi CPU bởi vì phải chờ tài nguyên hoặc đã sử dụng hết quantum (4 ms), thread đứng đầu hàng đợi RUNNABLE sẽ được chọn làm đối tượng sử dụng CPU tiếp theo, nó được chuyển vào CPU và trạng thái của thread được đổi từ RUNNABLE sang RUNNING. Như vậy, thread ví dụ ở trên sau một khoảng thời gian chờ trong hàng đợi runnable sẽ tới lúc được chọn để thực thi trên CPU. Thông số Resource wait và signal wait Thông qua mô hình xử lý công việc ở trên đề cập, chúng ta thấy thời gian thực thi một yêu cầu sẽ là tổng thời gian nó dùng CPU với thời gian chờ được xử lý, được thể hiện qua công thức Total execution time = CPU time + Wait time. Chúng ta có hai loại wait time ở đây, ngay khi thread nhảy vào waiter list để đợi tài nguyên SQL Server sẽ ghi nhận nó là resource wait time. Khi thread chờ trong runnable queue để đến lượt thực thi trên CPU sẽ được SQL Server ghi nhận là signal wait time. Đây chính là wait statistics trong SQL Server (đơn vị tính của các giá trị này trong các DMVs là millisecond). Total execution time (elapsed time) = CPU time + signal wait time + resource wait time Phân tích hiệu năng của một câu truy vấn bằng cách phân tích wait statistics của lần execution đó giúp chúng ta thấy được thời gian dành cho phần nào nhiều nhất, giảm thời gian chờ không cần thiết sẽ giúp tăng hiệu năng của câu truy vấn đó nói riêng, và có thể tăng hiệu năng của SQL Server nói chung. Nếu ta thấy signal wait time nhiều có khả năng server đang bị quá tải tài nguyên CPU. Nếu resource wait time cao thì khả năng tài nguyên đó đang là điểm nghẽn. Chúng ta sẽ khám phá hai DMVs lưu giữ thông tin wait statistics và chi tiết các loại resource wait ở bài sau. Hy vọng qua bài viết này Stanford đã giúp các bạn học quản trị cơ sở dữ liệu hiểu về wait statistics trong hệ quản trị SQL Server để vận dụng vào công việc của mình hiệu quả. 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: wait statistics, sql server