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

Trong bài viết này Stanford tiếp tục cùng các bạn học quản trị SQL Server tìm hiểu về bảng Data file I/O trong công cụ Activity Monitor trên SQL Server.

Disk I/O latency là một thông số quan trọng cần theo dõi để thấy hiệu năng của I/O subsystem trong một hệ thống. SQL Server cần đọc dữ liệu từ ổ cứng (disk) lên bộ nhớ (memory) để phục vụ các nhu cầu truy vấn và ngược lại, cần ghi dữ liệu từ memory xuống ổ cứng cho những thay đổi mà người dùng thực hiện. Bảng Data file I/O này giúp quản trị viên theo dõi dung lượng dữ liệu được đọc lên và ghi xuống, cùng với độ trễ của mỗi hành động (I/O request). Sử dụng kết hợp với những lời khuyên từ các chuyên gia về SQL Server hoặc chính từ Microsoft sẽ giúp bạn có nhận định đúng về hiện trạng I/O subsystem của mình và có hành động phù hợp.

Bảng Data file I/O

Data luôn nằm trên memory là tình huống tốt nhất vì khi câu truy vấn cần dùng là có ngay, nhưng nếu phải đọc từ file ta cần biết mất bao lâu để data đó load lên memory, tương tự như vậy cho việc ghi xuống. Bảng Data file I/O cung cấp cho quản trị viên hai thước đo cơ bản nhất để đánh giá tình hình hoạt động của các files này đó là lưu lượng data đọc/ghi trên file và độ trễ (latency) của các lần thực hiện. Hình bên dưới thể hiện những thông tin Activity Monitor cung cấp cho chúng ta theo các tiêu chí này.

Hình 1: Minh họa disk I/O latency trên data file .mdf của database QLNhanVienLuong_170424

Sử dụng SQLQueryStress giả lập 10 kết nối truy vấn dữ liệu. Ta thấy chỉ có mỗi data file .mdf của cơ sở dữ liệu QLNhanVienLuong_170424 là có hoạt động với lưu lượng hơn 10MB/sec và độ trễ 15 ms, những data file khác hầu như không có tương tác gì. Hình trên là kết quả khi sử dụng phần demo bài viết trước (Database I/O), cũng với thiết lập 3GB buffer pool và các câu truy vấn đọc tất cả thông tin trong bảng NhanVien2 của database với dữ liệu gần 1 triệu dòng. Sau đây là ý nghĩa các cột trên bảng này như sau:

Database: Tên database.
File Name: Tên file và đường dẫn đến thư mục chứa file trên host.
MB/sec Read: Giá trị trung bình lượng data đọc từ file này trong 1 giây (theo đơn vị tính MB).
MB/sec Written: Giá trị trung bình lượng data ghi xuống file này trong 1 giây (theo đơn vị MB).
Response Time (ms): Độ trễ trung bình trên 1 giây ( theo đơn vị milliseconds) của cả hai hành động đọc lên và ghi xuống file trong khoảng "Refresh Interval".

Cứ mỗi một khoảng thời gian "Refresh Interval", ví dụ chúng ta chọn là 10 giây. Activity Monitor lại truy vấn các thông tin này từ SQL Server. Thực hiện các thao tác tính toán để lấy ra tổng dung lượng đọc/ghi, số lần request I/O, và độ trễ phát sinh trong 10 giây vừa qua. Từ các giá trị này chúng ta xác định được các thông tin như trên bảng Data file I/O.

SQL Server lấy Disk I/O latency từ đâu ?

Chúng ta cũng sử dụng SQL Server Profiler giống bài viết trước để theo dõi cách Activity Monitor truy vấn dữ liệu như thế nào? Thực hiện các bước tương tự cho Profiler chúng ta thấy lúc khởi tạo Activity Monitor tạo bảng tạm tên #am_dbfileio, sau đó định kì thực thi một đoạn code T-SQL để thu thập và tính toán ra kết quả mong muốn. Các bạn có thể xem đoạn code sau để thấy rõ điều này.

--- bước 1: tạo bảng tạm

IF OBJECT_ID('tempdb..#am_dbfileio', 'U') IS NULL

BEGIN

CREATE TABLE #am_dbfileio (collection_time datetime PRIMARY KEY, total_io_bytes numeric (28, 1));

END;

IF OBJECT_ID ('tempdb..#am_dbfilestats', 'U') IS NULL

BEGIN

CREATE TABLE #am_dbfilestats (

[collection_time] datetime,

[Database] sysname,

[File] nvarchar(1024),

[Total MB Read] numeric (28,1),

[Total MB Written] numeric (28,1),

[Total I/O Count] bigint,

[Total I/O Wait Time (ms)] bigint,

[Size (MB)] bigint

);

CREATE CLUSTERED INDEX cidx ON #am_dbfilestats ([collection_time]);

END;

--- bước 2: truy vấn data định kì mỗi "Refresh Interval"

DECLARE @current_collection_time datetime;

SET @current_collection_time = GETDATE();

-- Grab a snapshot

INSERT INTO #am_dbfilestats

SELECT

@current_collection_time AS collection_time,

d.name AS [Database],

f.physical_name AS [File],

(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],

(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],

(fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],

fs.io_stall AS [Total I/O Wait Time (ms)],

fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]

FROM sys.dm_io_virtual_file_stats(default, default) AS fs

INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id

INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time

DECLARE @previous_collection_time datetime;

SELECT TOP 1 @previous_collection_time = collection_time

FROM #am_dbfilestats

WHERE collection_time < @current_collection_time

ORDER BY collection_time DESC;

DECLARE @interval_ms int;

SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last

SELECT

cur.[Database],

cur.[File] AS [File Name],

CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],

CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],

-- protect from div-by-zero

CASE

WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0

ELSE

(cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])

/ (cur.[Total I/O Count] - prev.[Total I/O Count])

END AS [Response Time (ms)]

FROM #am_dbfilestats AS cur

INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]

WHERE cur.collection_time = @current_collection_time

AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot

DELETE FROM #am_dbfilestats

WHERE collection_time != @current_collection_time;

Activity Monitor sử dụng DMV sys.master_files để lấy ra tất cả các data files cần theo dõi trên SQL Server và DMF sys.dm_io_virtual_file_stats cung cấp thông tin hoạt động của từng file. Mỗi khi bước 2 được thực thi Activity Monitor sẽ lấy giá trị mới lưu vào bảng tạm, tiếp đó so sánh giá trị mới này với giá trị đã lưu lần trước và tính ra kết quả chênh lệch, đây chính là thông số phát sinh trong khoảng thời gian "Refresh Interval". Function sys.dm_io_virtual_file_stats này nhận hai tham số đầu vào là database_id và file_id, nếu bạn để mặc định nó sẽ trả ra tất cả các files của các database.

select f.name, f.physical_name,f.type_desc,f.state_desc, f.size/128 as inMB,

sample_ms, num_of_reads, num_of_bytes_read, Io_stall_read_ms, num_of_writes, num_of_bytes_written,

io_stall_write_ms, io_stall, size_on_disk_bytes

FROM sys.dm_io_virtual_file_stats(default, default) AS fs

INNER JOIN sys.master_files f ON fs.database_id = f.database_id

AND fs.file_id = f.file_id;

Hình 2: Các column chính của sys.dm_io_virtual_file_stats

Sau đây là ý nghĩa các cột trong câu truy vấn SQL trên như sau:

database_id: Mỗi database trong instance có một giá trị id và duy nhất.
file_id: Mỗi database sẽ có danh sách các files đánh id từ 1. file_id là duy nhất trong một database.
sample_ms: Thời gian tính bằng đơn vị millisecond kể từ khi SQL Server start.
num_of_reads: Số lần đọc data trên file này, tính theo giá trị sample_ms (từ lúc SQL Server start). Con số này tính theo physical read, chứ không phải logical read.
num_of_bytes_read: Dung lượng bytes đã đọc từ file này kể từ khi SQL Server start, lưu ý rằng kích thước bytes mỗi lần đọc có thể khác nhau.
Io_stall_read_ms: Tổng thời gian các processes đã chờ I/O request.
num_of_writes: Số lần ghi data trên file này kể tức lúc start.
num_of_bytes_written: Dung lượng bytes đã ghi lên file này.
io_stall_write_ms: Tổng thời gian chờ hành động ghi của các processes.
io_stall: Tổng thời gian chờ của cả request đọc + ghi
size_on_disk_bytes: Kích thước file trên đĩa, theo đơn vị byte.

Hiểu được cách tổ chức lưu trữ và hoạt động của DMF này giúp cho việc đọc và phân tích kết quả từ bảng Data file I/O hiệu quả hơn. Bạn nhận ra được những file nào đọc ghi thường xuyên và khối lượng là bao nhiêu, chúng có phù hợp với mong đợi của hệ thống không hay là do hiệu suất câu truy vấn không tốt gây nên. Giá trị disk I/O latency (tương tự response time hoặc io_stall) như thế nào là ổn? Dưới đây là giá trị tham khảo từ những chuyên gia hàng đầu về SQL Server trên trang SQLskills.com như sau:

Excellent: < 1ms
Very good: < 5ms
Good: 5 – 10ms
Poor: 10 – 20ms
Bad: 20 – 100ms
Shockingly bad: 100 – 500ms
WOW!: > 500ms

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ề Disk I/O latency trên hệ thống thông qua công cụ Activity Monitor trên SQL Server. Từ đó giúp bạn hiểu rõ hơn về hệ thống của mình.

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