Phân tích hiệu năng SQL Server với Wait Statistics - Phần 3

Trong bài viết này chúng ta sẽ cùng tìm hiểu những loại wait type liên quan đến tài nguyên CPU. Đây là những thông số quan trọng để giúp các bạn học quản trị SQL biết rõ điểm nghẽn của SQL Server.

Nếu thấy giá trị Processor Time là 100% thông qua công cụ performance monitor đi kèm trên windows. Ngoài ra, khi CPU quá tải nó còn thể hiện thông qua những thông số không kém phần quan trọng khác như các wait type CXPACKET, SOS_SCHEDULER_YIELD và THREADPOOL.

Nếu bạn còn thắc mắc làm thế nào để biết server mình đang quản lý có những wait type nào hãy xem lại bài viết Tìm hiểu về Phân tích hiệu năng SQL Server với Wait Statistics - Phần 2 tại đây. Bây giờ chúng ta hãy cùng tìm hiểu các wait type liên quan đến tài nguyên CPU.

CXPACKET wait

CXPANếu task mới được tạo ra nhưng không có worker thread để thực thi, SQL Server sẽ cho task đó vào hàng đợi và gán wait type là THREADPOOL. Khi tình huống này xảy ra bạn sẽ cảm thấy SQL Server đứng cứng ngắt, ngay cả việc login vào SSMS để tìm hiểu lý do gì cũng không được, vì không có đủ worker thread cho request login của bạn nên SSMS sẽ loading thật lâu.

Chúng ta sẽ đi qua phần demo dưới đây để thấy số lượng worker thread trong SQL Server tăng tới giới hạn, những request sau đó sẽ có trạng thái THREADPOOL wait và tiếp đó là chúng ta không thể login bằng SSMS như thường lệ.

Số lượng worker thread hiện tại trên SQL Server có thể xác định thông qua DMV sys.dm_os_workersCKET là kết quả của việc thực thi truy vấn song song. Bất cứ khi nào SQL Server sử dụng nhiều threads để thực thi một câu truy vấn đều xuất hiện wait type này. Vậy nên thoạt nhìn sẽ không có vấn đề gì nhưng nếu thời gian chờ của CXPACKET này đủ lớn thì đó là chuyện khác.

CXPACKET xảy ra khi một thread đã thực hiện xong một phần công việc của mình nhưng phải chờ thread khác kết thúc trước khi nó thực hiện công việc tiếp theo. Trong xử lý truy vấn song song luôn có một thread đứng ra đóng vai trò điều phối (có execution context id bằng 0) có trách nhiệm tập hợp kết quả của các thread khác và thread này đóng góp thời gian chờ nhiều nhất cho CXPACKET.

Có một vài lý do khác làm cho thời gian chờ của CXPACKET tăng mạnh hơn, nếu các thread thực hiện việc truy cập và xử lý data nhưng bản thân những thread này phải chờ tài nguyên liên quan mới có thể chạy được gây ảnh hưởng dây chuyền đến thời gian chờ của CXPACKET.

Một tình huống khác cũng góp phần làm tăng thời gian chờ của CXPACKET đó là khi sự phân bổ công việc giữa các thread. Ví dụ bạn chạy câu lệnh đếm tổng số dòng của một bảng A, nếu SQL Server thực thi câu truy vấn này kiểu song song với 5 threads thì sẽ có thread điều phối làm nhiệm vụ tổng hợp data, 4 threads còn lại sẽ có nhiệm vụ truy cập và đếm số dòng của bảng.

Điều gì xảy ra nếu bảng đó có 4 triệu dòng và một thread trong đó được giao đếm 3,4 triệu dòng, còn 3 threads kia mỗi thread chỉ đếm 200K dòng? Đây là tính huống phân bổ data không đồng đều, threads ít công việc hơn sẽ xong sớm hơn và phải chờ thread nhiều việc hoàn thành mới xem là xong việc. Tình huống này sẽ tăng thời gian chờ của CXPACKET.

Lý do để SQL Server quyết định sử dụng truy vấn song song là khi chi phí dự tính của truy vấn vượt quá ngưỡng qui định, giá trị này được thiết lập ở thuộc tính Cost Threshold For Parallelism (CTFP), giá trị mặc định sau khi cài dặt SQL Server là 5. Nếu chi phí câu truy vấn lớn hơn 5 thì SQL Server sẽ xem xét sử dụng xử lý song song.

Một nhân tố cũng góp phần ảnh hưởng thời gian chờ của CXPACKET là số lượng threads tham gia xử lý song song, số lượng threads càng nhiều thì chi phí điều phối và đồng bộ càng nhiều. Về mặt logic, số lượng thread tham gia vào một câu truy vấn sẽ không vượt quá số lượng logical CPUs trên instance đó. Con số này có thể điều khiển được thông qua thuộc tính Max Degree of Parallelism (MAXDOP). Giá trị mặc định của nó là 0, tức là sử dụng tất cả threads trong instance (bằng với số lượng logical CPUs).

Để hạn chế thời gian chờ của CXPACKET, tùy vào dạng workload của instance mà bạn xem xét nên chọn CTFP và MAXDOP phù hợp. Với instance chứa OLTP database thường là nơi nhận các request, transaction nhỏ. Nhưng số lượng lớn thì việc xử lý song song không hiệu quả, có thể gây giảm throughput và hiệu năng. Do đó, bạn có thể tăng CTFP lên giá trị cao hơn ví dụ như 50, bên cạnh đó bạn cũng có thể giảm số lượng threads tham gia vào một xử lý song song bằng cách hạ MAXDOP xuống một giá trị nào đó phù hợp hơn.

Với instance chứa data warehouse hoặc chạy các loại reports với lượng data lớn thì rất phù hợp với việc xử lý song song, nhưng bạn cũng cần tham khảo những khuyến nghị từ Microsoft để chọn các thông số cấu hình phù hợp.

Với những tình huống ảnh hưởng dây chuyền và sự phân bổ data không đồng đều như đề cập ở trên, bạn cần giải quyết việc threads chờ đợi tài nguyên khác khi xử lý tác vụ con (sub-tasks), cũng như tìm cách phân bổ data cho đồng đều hơn. Điều này có thể được giải quyết thông qua việc điều chỉnh truy vấn (query tuning), điều chỉnh index (index tuning) và điều chỉnh cấu hình server (server tuning).

SOS_SCHEDULER_YIELD wait

Trong bài viết mô hình thực thi trong SQL Server có đề cập tình huống tuy các tài nguyên một thread cần đều sẵn sàng nhưng nó vẫn phải rời khỏi CPU sau khi đã dùng hết 4 ms quantum. Lúc này nó không phải vào hàng đợi waiter list vì nó không cần phải chờ tài nguyên nào, mà nó nhảy vào runnable queue.
Thời gian thead ở trong runnable queue này sẽ được tính cho giá trị signal wait, nhưng vẫn cần phải gán cho một loại wait type cụ thể mới có thể lưu giữ wait statistics này trong các DMV. SQL Server sử dụng SOS_SCHEDULER_YIELD để theo dõi tình huống như vậy. Và vì loại wait type này không phải là loại resource wait nên bạn sẽ không thấy nó xuất hiện trong DMV sys.dm_os_waiting_tasks.

Những request nào cần tài nguyên CPU hơn 4ms đều phải trải qua loại wait type này. Do vậy luôn xuất hiện wait type SOS_SCHEDULER_YIELD trên instance là điều có thể hiểu được. Tuy nhiên tổng thời gian chờ của wait type này nếu đủ lớn chứng tỏ bạn có nhiều câu truy vấn cần dùng nhiều CPU, lúc này hoặc là bạn cần thực hiện tối ưu hóa truy vấn hoặc đến lúc bạn cần thêm CPU cho server.

Nếu là tình huống do hiệu năng câu truy vấn kém dẫn đến sử dụng tài nguyên CPU quá tải, bạn cần tìm hiểu nguyên các nguyên nhân có thể có như thiếu index, hoặc có index nhưng sử dụng không đúng cách (non-sargable predicates), các tình huống liên quan parameter sniffing, hoặc implicit conversions. Sử dụng kĩ năng điều chỉnh truy vấn (query tuning) để giải quyết.

THREADPOOL wait

Request, Task, Worker Thread, Scheduler là những đối tượng của SQLOS tham gia vào quá trình thực thi một request. Khi SQL Server nhận được request từ client, SOS Scheduler sẽ tạo ra task (hoặc nhiều tasks nếu sử dụng xử lý song song) tương ứng.

Task này sẽ được gắn vào worker thread rồi mới có thể làm việc với Scheduler để được điều phối thực thi trên CPU. Sau khi hoàn thành công việc, data đã trả về cho người dùng, kết thúc request, worker thread này sẽ trở về pool chờ thực thi task khác. Tại một thời điểm, một worker thread chỉ có thể nhận một task.

Nếu task mới được tạo ra nhưng không có worker thread nào sẵn sàng (vì đang bận thực thi những request trước đó), SQL Server sẽ tạo thêm worker threads để phục vụ những task này. Nhưng số lượng worker thread trong một instance là có hạn, khi đã đạt giá trị ngưỡng này SQL Server không thể tạo thêm.

SQL Server sẽ tính toán giá trị maximum có thể có của worker thread khi khởi động, bạn có thể tham khảo công thức ở bảng dưới đây và tính toán cho instance của mình như bảng dưới đây:

Đây là số lượng tối đa có thể có trên instance, tuy nhiên bạn có thể giới hạn số lượng này thông qua việc thiết lập giá trị cho thuộc tính Maximum worker threads như hình dưới đây:

Điều chỉnh số lượng thread tối đa cho SQL instance

Mặc định giá trị Maximum worker threads này bằng 0, tức là bằng với giá trị tính toán dựa trên CPUs như ở bảng phía trên. Ngoài ra bạn còn có thể sử dụng T-SQL để cấu hình giá trị này.

Nếu task mới được tạo ra nhưng không có worker thread để thực thi, SQL Server sẽ cho task đó vào hàng đợi và gán wait type là THREADPOOL. Khi tình huống này xảy ra bạn sẽ cảm thấy SQL Server đứng cứng ngắt, ngay cả việc login vào SSMS để tìm hiểu lý do gì cũng không được, vì không có đủ worker thread cho request login của bạn nên SSMS sẽ loading thật lâu.

Chúng ta sẽ đi qua phần demo dưới đây để thấy số lượng worker thread trong SQL Server tăng tới giới hạn, những request sau đó sẽ có trạng thái THREADPOOL wait và tiếp đó là chúng ta không thể login bằng SSMS như thường lệ.

Số lượng worker thread hiện tại trên SQL Server có thể xác định thông qua DMV sys.dm_os_workers:

Số lượng worker threads SQL Server đã tạo ra

Tiếp đến chúng ta sẽ tạo một bảng bất kì trên tempdb và insert vài dòng. Sau đó chúng ta update dòng đầu tiên nhưng không commit transaction này. Kế đó chúng ta sẽ sử dụng SQLStress để giả lập nhiều threads select dòng data đang được update. Lúc này blocking sẽ xảy ra nên các thread select này sẽ phải chờ lock từ transaction trên. Các thread này chờ đồng nghĩa số lượng woker threads available cũng ít lại cho đến khi không đủ dùng.

Sử dụng SQLQueryStress để tạo 600 requests đến server

Vì maximum worker threads trên máy mình chỉ có 576 nên hiện tại với 600 requests đến hẳn là không đủ worker threads để phục vụ. Mình đã thử login vào sesion khác nhưng SSMS cũng bị loading một hồi rồi báo lỗi không thể connect.

Nếu rơi vào tình huống này thì phải làm sao? Đây là lúc Dedicated Admin connection (DAC) thể hiện giá trị của nó. SQL Server chừa đường cho admin vào xử lý bằng cách tạo scheduler và worker threads riêng cho DAC. Kết quả khi sử dụng DAC và truy vấn DMV sys.dm_os_waiting_tasks như hình bên dưới.

Sử dụng DAC khi không đủ worker threads

Những requests đến sau khi không đủ worker threads sẽ không thể login vào SQL Server nên connection không được tạo và hiển nhiên giá trị session trong result set cũng không có.

Chúng ta sử dụng tình huống locking để giả lập tình huống không đủ worker threads phục vụ các request mới nên nó chiếm thời gian chờ nhiều nhất trong các loại wait type. Nếu server gặp sự cố về performance làm các request chạy lâu hơn bình thường thì việc giải phóng worker threads sẽ chậm hơn và dần dần sẽ không đủ worker threads phục vụ cho workload của server, lúc này ta cũng có thể gặp tình huống tương tự.

Như vậy ở bài này chúng ta đã duyệt qua ba wait type phổ biến liên quan đến CPUs, đó là CXPACKET khi chạy truy vấn song song, SOS_SCHEDULER_YIELD khi có những câu truy vấn lớn, sử dụng nhiều tài nguyên CPU và THREADPOOL khi số lượng threads không đủ phục vụ request. Những vấn đề này đều có thể bởi vì index không hiệu quả hoặc câu truy vấn chưa tối ưu hoặc là server config chưa phù hợp.

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, phân tích hiệu năng