Hành trình đếm sao – 4. Đo lường hiệu suất truy vấn T-SQL

Lưu Hoàng Lộc

• 10 min read
Hành trình đếm sao – 4. Đo lường hiệu suất truy vấn T-SQL

Lưu ý: Bài viết này được đăng lại từ kho lưu trữ cũ nên một số hình ảnh minh họa đã bị mất. Xin lỗi bạn đọc vì sự bất tiện này.

Hiệu suất truy vấn là một chủ đề cực kỳ hấp dẫn nhưng cũng đầy thách thức đối với bất kỳ ai làm việc với T-SQL. Một câu lệnh tối ưu đồng nghĩa với tốc độ phản hồi chớp nhoáng, giảm thiểu thời gian chờ đợi và mang lại trải nghiệm tuyệt vời cho người dùng. Nhưng làm thế nào để chúng ta thực sự "cân đo đong đếm" được hiệu suất này? Làm sao để biết một câu lệnh là tốt hay chưa tốt, hoặc so sánh xem giữa hai cách viết (như ví dụ ở bài trước), cách nào tỏ ra ưu việt hơn?
Bài viết này sẽ hướng dẫn bạn cách đo lường hiệu suất truy vấn thông qua ba chỉ số cốt lõi: CPU time, Elapsed time, và Logical reads. Hiểu rõ ý nghĩa của các thông số này đồng nghĩa với việc bạn nắm bắt được mức độ tiêu hao tài nguyên của hệ thống để đổi lấy kết quả. Đây là nền tảng kỹ năng thiết yếu giúp các phân tích và quyết định của bạn khi thực hiện Query Performance Tuning trở nên chuẩn xác hơn.

Đo lường hiệu suất truy vấn trên session hiện tại

SQL Server cung cấp hai lệnh mạnh mẽ là SET STATISTICS TIME và SET STATISTICS IO để báo cáo chi tiết về thời gian và tài nguyên lưu trữ (I/O) mà câu truy vấn đã sử dụng. Hai lệnh này hoạt động độc lập và chỉ có tác dụng đối với phiên làm việc (session) mà bạn đang kết nối. Theo thói quen, tôi thường bật cả hai cùng lúc để có cái nhìn toàn cảnh.
Hãy thử chạy lại câu truy vấn nhóm dữ liệu kết hợp mệnh đề HAVING từ bài trước:
SQL

SET STATISTICS IO ON
SET STATISTICS TIME ON
 
--- Tìm kiếm sử dụng mệnh đề HAVING
SELECT location, COUNT(*) AS cnt
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 
GROUP BY location
HAVING location LIKE '%vietnam%'
 AND COUNT(*) > 10
ORDER BY cnt
OPTION(MAXDOP 1)
 
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
Lưu ý: Mệnh đề OPTION(MAXDOP 1) được thêm vào nhằm ép SQL Server chạy truy vấn tuần tự (serial) thay vì song song (parallelism), giúp kết quả thống kê ổn định hơn. Việc tắt tính năng thống kê ở cuối khối lệnh cũng là một thói quen tốt để giữ sự gọn gàng cho session.

Sau khi thực thi, kết quả thống kê sẽ xuất hiện ở tab Messages với định dạng tương tự như sau:
Plaintext

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 3, logical reads 192, physical reads 9, read-ahead reads 183, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 7405, physical reads 3, read-ahead reads 7374, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 800856, physical reads 4, read-ahead reads 800161, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 7969 ms,  elapsed time = 18034 ms.

Phân tích STATISTICS TIME

Kết quả cung cấp hai giá trị quan trọng (đơn vị: mili-giây):

  • CPU time: Tổng thời gian vi xử lý thực sự thực hiện việc tính toán để xử lý dữ liệu.
  • Elapsed time (Duration): Tổng thời gian từ khoảnh khắc SQL Server nhận lệnh cho đến khi dòng dữ liệu cuối cùng được trả về cho client.

Mối liên hệ giữa hai chỉ số này rất thú vị và được thể hiện qua công thức:

Elapsed Time = CPU Time + Wait Time

Trong đó, Wait time là tổng thời gian câu truy vấn phải "đứng im" để chờ được cấp phát các tài nguyên như: chờ nạp dữ liệu vào RAM, chờ mở khóa (lock) dữ liệu đang bị chiếm dụng, chờ cấp phát bộ nhớ cấp phát, hoặc chờ độ trễ mạng. Chúng ta sẽ đi sâu vào cơ chế này trong bài viết về Wait and Queue.

Phân tích STATISTICS IO

Kết quả cho thấy danh sách các đối tượng tham gia truy xuất dữ liệu:

  • Table 'Users' và 'Posts': Các bảng gốc được gọi tên trong truy vấn.
  • Worktable/Workfile: Đây là các cấu trúc tạm thời do SQL Server tự động tạo ra trong quá trình xử lý. Chúng thường được dùng để chứa dữ liệu trung gian cho các phép toán phức tạp (như Hash Match hoặc Sort).

Các chỉ số đi kèm bao gồm:

  • Logical reads (Quan trọng nhất): Số lượng trang dữ liệu (data pages - mỗi trang 8KB) được đọc từ bộ nhớ đệm (Buffer Cache). Số lượng page càng lớn nghĩa là hệ thống phải xử lý lượng dữ liệu khổng lồ, kéo theo CPU hoạt động nhiều hơn và thời gian lâu hơn. Giảm thiểu logical reads chính là mục tiêu tối thượng của việc tối ưu hóa.
  • Physical reads: Số lượng trang dữ liệu được đọc trực tiếp từ ổ cứng (Disk). Khi dữ liệu chưa có sẵn trên RAM, Storage Engine sẽ phát sinh lệnh đọc (I/O request) để kéo trang đó từ ổ cứng lên RAM (tính là 1 physical read), sau đó mới truy cập nó trên RAM (tính tiếp 1 logical read).
  • Scan count: Số lần engine khởi tạo quá trình quét dữ liệu (có thể là Index Seek hoặc Index Scan).
  • Read-ahead reads: Số lượng trang dữ liệu được đọc trước từ ổ cứng dựa trên cơ chế dự đoán của SQL Server, nhằm tối ưu I/O thay vì phải chờ đọc từng trang lẻ tẻ.

Trở lại với thông số của câu lệnh trên: Tổng thời gian chạy (Elapsed time) là 18.034 ms, nhưng thời gian dùng CPU thực tế chỉ là 7.969 ms. Áp dụng công thức, ta thấy câu truy vấn đã phải "chờ" (Wait time) hơn 10 giây. Đồng thời, nó phải quét 7.405 pages của bảng Users và 800.856 pages của bảng Posts.
Để tối ưu, chúng ta cần tìm cách triệt tiêu thời gian chờ này (lý tưởng nhất là bằng 0), đồng thời tối ưu chỉ số Logical Reads để giảm tải cho CPU. Tuy nhiên, do lúc này chúng ta chưa trang bị đủ kiến thức về Execution Plan, ta sẽ tạm dừng ở bước đánh giá chi phí này.

So sánh trực tiếp hiệu suất của hai cách viết

Bây giờ, chúng ta sẽ áp dụng 3 chỉ số vàng (CPU time, Elapsed time, Logical reads) để phán xử xem: đặt điều kiện lọc '%vietnam%' ở WHERE hay ở HAVING sẽ chạy nhanh hơn.

Lưu ý: Để đảm bảo tính công bằng (loại bỏ độ trễ của Physical Reads), hãy chạy đoạn code này 3 lần để toàn bộ dữ liệu cần thiết được tải sẵn lên Buffer Cache.

SQL

SET STATISTICS IO, TIME ON
 
--- Cách 1: Điều kiện tìm kiếm ở mệnh đề HAVING
SELECT location, COUNT(*) AS cnt
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 
GROUP BY location
HAVING location LIKE '%vietnam%'
    AND COUNT(*) > 10
ORDER BY cnt
OPTION (MAXDOP 1)
 
--- Cách 2: Điều kiện tìm kiếm ở mệnh đề WHERE
SELECT location, COUNT(*) AS cnt
FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerUserId
WHERE PostTypeId = 2 AND location LIKE '%vietnam%'
GROUP BY location
HAVING COUNT(*) > 10
ORDER BY cnt
OPTION (MAXDOP 1)
 
SET STATISTICS IO, TIME OFF

Kết quả trả về cho thấy sự khác biệt rõ rệt:
Cách 1 (Dùng HAVING):
Plaintext

Table 'Workfile'. Scan count 3, logical reads 192...
Table 'Users'. Scan count 1, logical reads 7405...
Table 'Posts'. Scan count 1, logical reads 800856...
 SQL Server Execution Times: CPU time = 3750 ms,  elapsed time = 3744 ms.

Cách 2 (Dùng WHERE):
Plaintext

Table 'Workfile'. Scan count 0, logical reads 0...
Table 'Posts'. Scan count 1, logical reads 800856...
Table 'Users'. Scan count 1, logical reads 7405...
 SQL Server Execution Times: CPU time = 2922 ms,  elapsed time = 2940 ms.

Phân tích kết quả:

  1. Về thời gian: Cả hai câu đều có Elapsed Time xấp xỉ CPU Time, chứng tỏ dữ liệu đã được nạp đầy đủ trên RAM và không phát sinh bất kỳ độ trễ I/O nào.
  2. Về tài nguyên RAM: Lượng Logical Reads quét trên 2 bảng chính (Users và Posts) hoàn toàn giống nhau. Tuy nhiên, cách viết số 1 (HAVING) đã phát sinh thêm nhu cầu tạo bảng tạm Workfile (192 logical reads). Việc phải xử lý thêm bộ đệm tạm thời này chính là nguyên nhân gây chậm trễ. (Tôi sẽ giải thích cơ chế nội tại tạo ra sự khác biệt này trong bài Execution Plan).
  3. Về tài nguyên CPU: Cách số 2 (WHERE) chỉ tiêu tốn 2.922 ms so với 3.750 ms của cách số 1.

Kết luận: Đưa điều kiện lọc lên mệnh đề WHERE giúp câu truy vấn nhẹ hơn, không sinh rác tạm thời và tối ưu tốc độ hơn hẳn.

Đo lường hiệu suất truy vấn từ các Session khác

Các lệnh SET ở trên rất tiện lợi cho lập trình viên, nhưng chỉ hoạt động trên session nội bộ. Trong vai trò quản trị (DBA), nếu cần theo dõi hoặc "bắt bệnh" các truy vấn đang chạy từ các ứng dụng hay người dùng khác, bạn cần đến các công cụ chuyên nghiệp hơn.
SQL Server cung cấp hai nền tảng giám sát chính: SQL Server Profiler và Extended Events. Cả hai đều có khả năng thu thập các số liệu CPU, Duration và Reads (lưu ý: đơn vị của Duration trong Profiler có thể là micro-second tùy cấu hình).
Hình ảnh minh họa: (Bạn có thể theo dõi sự kiện 'sql_batch_completed' trên cả hai công cụ).
Giữa hai công cụ này, các chuyên gia hiện đại luôn khuyến nghị sử dụng Extended Events. SQL Server Profiler là công nghệ cũ, gây ảnh hưởng nặng nề đến hiệu năng tổng thể của máy chủ, đặc biệt là khi lượng Request lớn (dễ dàng đẩy %Processor Time lên mức nguy hiểm). Trong khi đó, Extended Events được xây dựng sâu vào trong hệ thống, cực kỳ nhẹ bén và là vũ khí tối thượng để Troubleshooting sự cố trên môi trường Production.

"Bắt mạch" hiệu suất truy vấn trong quá khứ

Công việc thường ngày của một DBA là xử lý những lời phàn nàn "khó đỡ": "Sao tối qua lúc 11h59 hệ thống chậm thế?" hay "Câu query này hôm qua chạy vèo vèo mà nay như rùa bò". Rất may, SQL Server không để chúng ta phải "đoán mò".
Hệ thống lưu giữ lại dấu vết của các câu truy vấn thông qua các Dynamic Management View (DMVs). View sys.dm_exec_query_stats chính là kho báu chứa các thông số tích lũy về hiệu năng của mọi câu lệnh đang nằm trong Plan Cache (dù là lệnh chạy trực tiếp hay nằm trong Stored Procedure).
Lưu ý rằng do được lưu trên RAM, dữ liệu này sẽ bị xóa trắng nếu SQL Server khởi động lại, bị tràn bộ nhớ (memory pressure), hoặc câu lệnh bị biên dịch lại (recompile). Dữ liệu trong view này mang tính cộng dồn.
Ví dụ cách trích xuất số liệu:
SQL

SELECT t.text, creation_time, last_execution_time, execution_count, 
    total_worker_time, total_elapsed_time, total_logical_reads,
    (total_worker_time/execution_count)/1000 AS avg_cpu_time_ms,
    (total_elapsed_time/execution_count)/1000 AS avg_elapsed_time_ms,
    (total_logical_reads/execution_count) AS avg_logical_reads
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t 
WHERE t.text LIKE '%Posts%'
    AND t.text NOT LIKE '%SELECT t.text%'

Bằng cách JOIN với hàm sys.dm_exec_sql_text, chúng ta có thể ánh xạ được cấu trúc lệnh thực tế. Kết quả trả về cho thấy thời điểm truy vấn xuất hiện, lần thực thi cuối, số lần đã chạy, cùng tổng khối lượng tài nguyên (CPU, Duration, Logical Reads) đã tiêu thụ. Việc lấy trung bình (lấy tổng chia cho số lần chạy) cung cấp cái nhìn tổng quan cực kỳ hữu ích để điều tra nguyên nhân.
Để dữ liệu này đạt độ chính xác cao và phục vụ phân tích dài hạn, cách tốt nhất là thiết lập các SQL Agent Job để lưu trữ (snapshot) thông tin này định kỳ vào một bảng vật lý.
Thông qua bài viết này, tôi hy vọng bạn đã nắm vững cách định lượng sức mạnh của một câu lệnh SQL và biết cách "gọi tên" các chỉ số tài nguyên. Đây là hành trang không thể thiếu để chúng ta bước tiếp vào những kỹ thuật tối ưu hóa chuyên sâu hơn. Hẹn gặp lại các bạn trong những chủ đề tiếp theo!