Tìm hiểu công cụ theo dõi hiệu năng Activity Monitor trong SQL Server

Trong bài viết này Stanford sẽ giúp các bạn học quản trị SQL Server tìm hiểu công cụ Activity Monitor tích hợp trên SSMS để theo dõi những công việc xảy ra và quản lý hiệu năng trên SQL Server.

Để biết được SQL Server đang làm gì sẽ là một quan trọng và không hề đơn giản, đặc biệt nếu bạn không phải là quản trị viên cơ sở dữ liệu. Bên cạnh việc xác định những câu truy vấn nào đang thực thi, chúng ta còn có thể biết được mức độ ảnh hưởng của chúng lên hiệu năng hiện tại của server. SQL Server Activity Monitor là một chức năng trong SQL Server Management Studio, nó giúp theo dõi những tiến trình đang diễn ra như thế nào và tài nguyên hệ thống được sử dụng ra sao.

Bảng điều khiển SQL Server Activity Monitor

Có hai cách để mở bản điều khiển này, hoặc là bạn click vào icon trên tool bar như hình 1. Hoặc bạn click chuột phải vào instance name và chọn Activity Monitor trong cửa sổ Objects Explorer như trong hình 2.

Hình 1: Mở Activity Monitor từ icon trên tool bar

Hình 2: Mở Activity Monitor từ cửa sổ Object Explorer

Sau khi chọn một trong hai cách trên, bạn sẽ thấy bảng điều khiển chính của Activity Monitor hiện lên và bắt đầu tải các dữ liệu liên quan. Có 6 bảng thông tin lần lượt là Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, và Active Expensive Queries. Các bảng này có thể mở rộng hoặc thu gọn cho tiện việc theo dõi và chỉ những bảng nào được chọn (mở rộng) SQL Server Activity Monitor mới tải data. Mặc định là bảng điều khiển con Overview được mở như hình dưới.

Hình 3: Giao diện chính của Activity Monitor

Activity Monitor trong SQL Server thu thập thông tin từ nhiều nguồn nhưng chủ yếu là truy vấn trực tiếp trên instance mà bạn đang theo dõi. Bạn có thể click chuột phải vào vùng trống trong bảng con Overview để thiết lập giá trị "Refresh interval" từ 1 giây đến 1 giờ cho cả bảng điều khiển chính. Mình chọn 1 giây để dễ nhận thấy sự thay đổi khi demo, Activity Monitor sẽ cập nhật thông tin trên dashboard sau mỗi giây.

Quyền hạn cần thiết để sử dụng SQL Server Activity Monitor

Vì Activity Monitor truy vấn các data tổng quan ở cấp độ instance nên login cần có quyền VIEW SERVER STATE. Bên cạnh đó, các bảng điều khiển còn lại thu thập các thông tin về database I/O, nội dung các câu truy vấn nên sẽ cần thêm quyền CREATE DATABASE, ALTER ANY DATABASE, và VIEW ANY DEFINITION. Vì một số bảng hỗ trợ thêm các chức năng như trace process cụ thể bằng profiler, kill process nào đó nên sẽ cần có thêm các quyền tương ứng như ALTER TRACE và sysadmin role để kill process.

Để việc sử dụng dashboard này dễ dàng và hiệu quả, mình sẽ giải thích ý nghĩa từng thông số cùng với ví dụ minh họa để các bạn thấy được những tình huống, hành động nào tác động làm tăng hoặc giảm các thông số tương ứng để sau này chỉ nhìn vào Activity Monitor các bạn có thể mường tượng được SQL Server bạn theo dõi đang chịu tải như thế nào.

Bảng Overview

Bảng này gồm các đồ thị thể hiện những thông số tổng quan nhưng cũng cực kỳ quan trọng giúp chúng ta hình dung được trạng thái của SQL Server hiện tại thế nào và đang hoạt động ra sao ?

Hình 4: Bảng Overview

% Processor Time – Phần trăm thời gian CPUs đang được dùng để thực thi các tiến trình. Số này càng cao hệ thống càng bận rộn.
Waiting Tasks – Số lượng các tasks (có thể xem là các worker threads) đang phải chờ tài nguyên như locks, memory, I/O hoặc chờ CPU. Đối với bản thân mỗi worker thread, thời gian chờ giống như thời gian chết. Nếu có nhiều waiting tasks và kéo dài chứng tỏ hệ thống hoạt động chưa hiệu quả.
Database I/O – Lượng data được transfer qua lại mỗi giây (tính theo đơn vị MB) giữa memory và disk hoặc giữa các disk với nhau.
Batch Requests/sec – Số lượng requests SQL Server nhận được mỗi giây. Đây là thông số quan trọng để đo mức độ hoạt động của một instance. Con số này càng lớn đòi hỏi quản trị viên càng chú ý theo dõi, vận hành instance đó kĩ hơn.

% Processor Time

% Processor Time là tỷ lệ phần trăm CPUs được sử dụng để thực thi các tác vụ trong server, giá trị này là của cả OS chứ không phải riêng SQL Server. Các request đến SQL Server phần lớn bị giới hạn bởi thời gian xử lý I/O ( I/O inbound) hơn là CPU. Tuy nhiên trong các hệ thống OLTP với lượng memory đủ lớn để cache data và số lượng requests đến SQL Server cũng đủ lớn thì tài nguyên CPUs có thể trở thành điểm nghẽn (CPU bottleneck).

Cơ sở dữ liệu mình dùng demo là tempdb với bảng Test có kích thước gần 2GB, máy tính sử dụng có cấu hình 8 cores (16 threads), 32GB memory và ổ cứng SSD. SQL Server maximum server memory được thiết lập 4GB, đủ để chứa cả database trong bộ nhớ cache. Câu truy vấn được dùng cho demo như sau:

CREATE TABLE Test(

id INT identity(1,1),

data1 CHAR(8000),

createdDate DATETIME

)

go

DECLARE @i INT = 1;

BEGIN TRAN

WHILE @i <= 250000

BEGIN

INSERT Test

SELECT CAST(@i AS CHAR(10)), GETDATE()

SET @i += 1

END

COMMIT;

Sau đó sử dụng công cụ SQLQueryStress giải lập 5 thread với mỗi giây lúc đó thông số % Processor Time là nhảy vọt lên cao khoản 20%.

Hình 5: % Processor Time

Waiting Tasks
Waiting là việc một process chờ một tài nguyên nào đó để có thể tiếp tục thực thi, và tất nhiên trong khi chờ tài nguyên process này sẽ có trạng thái là suspended (không dùng CPU time). Ở trên mình có đề cập các tài nguyên như CPU, I/O, memory và locks. Đây chỉ là những tài nguyên nói chung, thực chất SQL Server chia nhỏ các tài nguyên trên thành rất nhiều thành phần nhỏ hơn mà bạn có thể tìm hiểu ở phần wait statistics. Phần demo này mình sẽ cho các bạn thấy một số process phải chờ khóa (lock) và chúng sẽ xuất hiện ở danh sách các waiting tasks.

Mình sẽ sử dụng công cụ SQLQueryStress để giả lập nhiều request truy vấn data bảng Test, trong khi đó một process ở SSMS đang update một record của bảng và chưa kết thúc transaction của nó thành ra các process trên phải chờ. Bạn thực thi đoạn code sau trên SSMS:

use tempdb;

go

Begin tran

Update Test set data1 = 'Activity Monitor' where id = 10;

--Rollback

Hình 6: SQLQueryStress với 5 threads giả lập

Và kết quả là có 5 processes đang chờ tài nguyên lock trên bảng Test. Lần này chỉ có thông số waiting tasks tăng cao, các thông số khác đều thấp sát đáy.

Hình 7: Kết quả của những processes đang chờ locks

Database I/O
Vì SQL Server buffer pool (data cache) trên máy tính của mình đang được cấu hình 2GB, đủ để chứa nguyên database tempdb (2GB) nên chúng ta sẽ không thấy data transfer giữa disk và memory. Tiếp theo chúng ta sẽ giải lập với 10 threads để xem thông tin database i/o ra sao nhé.

Thực hiện truy vấn 250k dòng bảng Test với dung lượng hơn 1.9 GB và dung lượng data cache chỉ gần 2GB (vì phải dùng cho một số thành phần khác như plan cache) nên SQL Server buộc phải clean data đã đọc và load data mới lên cache liên tục để phục vụ các câu truy vấn.

Hình 8: Database I/O thể hiện việc transfer data giữa memory và disk, hơn 2GB/sec

Batch Requests/sec

Các bạn thấy với sự hỗ trợ của SQLQueryStress chúng ta đã giả lập được hơn 200 requests gửi đến SQL Server trong mỗi giây để thấy số lượng request trên mỗi giây với thông tin như hình sau:

Hình 9: Batch Requests/sec tăng cao

Với những hệ thống lớn thực tế, lượng request này có thể lên đến vài chục ngàn.

Như vậy trong bài viết này Stanford đã giúp các bạn học quản trị SQL Server làm việc với Activity Monitor với các khái niệm, ý nghĩa và những tình huống cụ thể tác động đến một vài thông số quan trọng trong việc xác định tải của SQL Server. Hiểu được khi nào những giá trị này cao, khi nào chúng thấp sẽ giúp bạn giới hạn phạm vi tìm hiểu trong các tình huống xử lý sự cố về hiệu năng SQL Server.

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 khóa học sql cùng chuyên gia giàu kinh nghiệm Stanford tại đây: http://bit.ly/2SLPYFF và nhận ưu đãi hấp dẫn của Stanford trong thời gian này. Bạn có thể gọi theo hotline: 0963 723 236 - 0866 586 366 để được gọi lại tư vấn trực tiếp nhé.

=============================
☎ 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

Lớp học	Thời gian
Lịch khai giảng chính thức khóa học C/C ++ for base - CC032501CB	19h00 ngày 26/03/2025
Lịch khai giảng chính thức khóa học SQL Base - SQL032501CB	18h00 ngày 06/03/2025
Lịch khai giảng chính thức khóa học C# for base - CS012501CB	18h00 ngày 06/01/2025
Lịch khai giảng chính thức khóa học Android Developer - AD122401DEV	8h30 ngày 14/12/2024
Xem thêm

Thông tin chi tiết