Tìm hiểu kỹ thuật đo lường hiệu suất truy vấn T-SQL trong SQL Server

Hiệu suất truy vấn là đề tài cực kỳ thú vị nhưng cũng rất thách thức khi làm việc với các câu lệnh T-SQL. Trong bài viết này sẽ giúp bạn hiểu một số kỹ thuật để đánh giá hiệu suất trong SQL Server.

Một câu truy vấn có hiệu suất cao đồng nghĩa với việc kết quả trả về cực nhanh, thời gian chờ của user ngắn sẽ mang lại trải nghiệm người dùng tốt hơn. Vậy làm thế nào chúng ta đánh giá hiệu suất của một câu truy vấn trong SQL Server ? Hiệu suất như thế nào là tốt và như thế nào là chưa tốt? Nếu có hai câu truy vấn cần so sánh nhau làm sao xác định câu truy vấn nào hiệu quả hơn.

Bài viết này sẽ chia sẻ cách đo lường hiệu suất truy vấn thông qua ba thông số phổ biến là CPU time, elapsed time và logical reads. Ý nghĩa của từng loại thông số sẽ được giải thích ở phần dưới bài viết, hiểu rõ những giá trị này tức là hiểu được mức độ đánh đổi tài nguyên để thực thi một câu truy vấn và điều này sẽ giúp kĩ thuật phân tích ,đánh giá của bạn chính xác hơn khi tối ưu hiệu suất truy vấn (query performance tuning).

Đo hiệu suất truy vấn trên session của mình

SQL Server cung cấp 2 câu lệnh SET STATISTICS TIME và SET STATISTICS IO để xuất ra thời gian và tài nguyên mà câu truy vấn đã dùng khi thực thi. STATISTICS TIME sẽ thống kê thông tin về cpu time và elapsed time, còn STATISTICS IO sẽ thống kê các thao tác về nhập xuất (I/O) liên quan. Đây là hai câu lệnh riêng biệt nên các bạn có thể chạy riêng lẻ và chúng chỉ ảnh hưởng đến mỗi session bạn đang chạy. Chúng ta sẽ sử dụng những câu lệnh trên kết hợp với câu lệnh truy vấn dữ liệu sau để xem các thông số thực hiện như sau:

set statistics io on

set statistics time on

Select l.EventName, count(*) SoLuong from stanfLogSystem l inner join stanfUsers u on l.UserId = u.UserId

where lower(l.EventName) LIKE N'%thêm mới%'

group by l.EventName

order by count(*) desc

OPTION(MAXDOP 1);

set statistics io off

set statistics time off;

Trong câu lệnh SELECT trên mình có thêm mệnh đề OPTION (MAXDOP 1) để đảm bảo SQL Server không sử dụng truy vấn song song (parallelism) mà là tuần tự (serial). Các bạn để ý cú pháp mình sử dụng ở đầu và cuối câu truy vấn là để bật và tắt tương ứng tính năng thống kê này, đây cũng là thói quen tốt trong lập trình nhằm kiểm soát mọi hành vi thừa thãi không cần thiết.

Kết quả thống kê nằm bên tab Messages và có định dạng như sau:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'stanfLogSystem'. Scan count 1, logical reads 24650, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'stanfUsers'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1125 ms, elapsed time = 1154 ms.

Đối với kết quả của SET STATISTICS TIME chúng ta có hai giá trị:

CPU time: thời gian CPU đã sử dụng để cho ra kết quả dữ liệu, đơn vi là millisecond

elapsed time: thời gian tính từ lúc SQL Server nhận được câu truy vấn cho đến khi trả xong dòng dữ liệu cuối cùng cho máy khách đang thực hiện.

Trong đó các bạn cần chú ý Tổng thời gian thực thi một câu truy vấn duration (chính là giá trị elapsed time) bằng thời gian câu truy vấn đó sử dụng CPU cộng với thời gian chờ tài nguyên được cấp phát. Có thể hình dung ý trên bằng biểu thức sau:
elapsed time (duration) = CPU Time + Wait time
Wait time là tổng thời gian chờ nói chung cho tất cả các tài nguyên như chờ data được load lên buffer cache, chờ đặt khóa trên một resource nào đó, chờ cấp phát memory, chờ network,… chúng ta sẽ quay lại biểu thức này khi đến bài viết về wait and queue trong SQL Server .

Đối với kết quả của SET STATISTICS IO:

Worktable/Workfile: Là những đối tượng tạm thời mà SQL Server đã tạo ra và sử dụng trong quá trình xử lý câu truy vấn. Bên cạnh việc truy xuất dữ liệu của những bảng dữ liệu trong SQL Server cần dùng tới những đối tượng này để lưu trữ dữ liệu tạm thời hoặc là do cơ chế hoạt động của một số thao tác (ví dụ như Hash Match, cái này thuộc phần execution plan sẽ đề cập ở bài viết khác) cần dùng tới.

Table 'stanfLogSystem' và Table stanfUsers': những bảng tham gia vào câu truy vấn đều xuất hiện trong kết quả thống kê.

Logical reads: Số lượng data pages (8KB page) được đọc từ bộ nhớ (buffer cache) để lấy các dòng dữ liệu cần thiết. Đây là giá trị chúng ta cần quan tâm vì số lượng pages càng lớn câu truy vấn của bạn càng dùng nhiều CPU hơn và thời gian dài hơn. Để dễ hình dung có thể liên tưởng giá trị này là số lượng công việc phải làm.

Physical reads: số lượng data pages (8KB page) được đọc lên từ disk để phục vụ câu truy vấn. Khi xử lý câu truy vấn, nếu page cần đọc đã ở trên buffer cache thì giá trị logical read sẽ được count, nếu chưa có thì storage engine sẽ gửi request I/O để đọc page đó từ disk và physical read đươc count, sau đó đến logical read được count.

Scan count: số lần đọc thêm (có thể là seek hoặc scan) để đảm bảo ko bỏ sót dòng dữ liệu nào từ các page liền kề. Thông số này hơi khó hình dung nhưng một khi bạn hiểu rõ cấu tạo index trong SQL Server và cách index được truy cập sẽ mường tượng ra cách nó đếm giá trị này.

Read-ahead read: số lượng data pages được đọc lên từ disk theo kiểu dự đoán. Nhằm tối ưu hiệu suất đọc data từ disk SQL Server có cơ chế dự đoán các pages sắp cần dùng để tự thực hiện các I/O request hiệu quả hơn thay vì chờ request page nào đọc page nấy. Các bạn có thể xem thêm thông tin liên quan ở đây.

Ngoài ra còn có các thống kê về LOB data với các thông số tương tự như 8KB pages. LOB data ám chỉ những data được khai báo với kiểu dữ liệu text, ntext, image, varchar(max), nvarchar(max) and varbinary(max) (gọi là large objects). Hai bảng chúng ta tham chiếu trong câu truy vấn không có các data type này nên giá trị chúng đều bằng 0.

Như mình nói ở đầu bài chúng ta chỉ cần chú ý tập trung ba giá trị CPU time, elapsed time và logical reads vì chúng là các thông số đầy đủ để đo lường hiệu suất của một câu truy vấn. Chúng ta thấy SQL Server mất 1154 ms để thực hiện xong câu truy vấn trên, và thời gian sử dụng CPU là 1125 ms. Theo như biểu thức mình chia sẻ thì thời gian chờ resources mất khoảng 29 ms. Câu truy vấn đã duyệt 24,650 pages của bảng stanfLogSystem và 5 pages của bảng stanfUsers để lấy các dòng dữ liệu ra xử lý. Muốn thực hiện tối ưu truy vấn để chạy nhanh hơn chúng ta cần giảm thời gian chờ xuống mức tối thiểu ( thời gian chờ bằng không là tốt nhất), còn nếu muốn giảm CPU chúng ta cần tìm cách giảm logical reads từ hai bảng trên. Hiện tại chúng ta đang tìm cách đo đạt chi phí câu truy vấn và chúng ta cũng chưa đủ kiến thức, kĩ thuật, cũng như công cụ hỗ trợ cho việc tối ưu truy vấn nên chúng ta chỉ dừng lại ở mức phân tích và nhận xét như vậy.

Cách đo hiệu suất truy vấn trên session người khác

Bạn có thể sử dụng công cụ SQL profiler do Microsoft tích hợp theo công cụ Microsoft SQL Server Management Studio để theo dõi và đo hiệu suất truy vấn như hình minh họa dưới:

Trong đó cột TextData là nội dung câu lệnh được thực hiện cùng các thông số thời gian xử lý của cpu và tổng thời gian thực hiện câu lệnh duration trong SQL Server.

Đo hiệu suất những câu truy vấn đã chạy

Hầu hết các quản trị viên cơ sở dữ liệu (DBA) đều sẽ nghe những phàn nàn kiểu như "hôm qua lúc 23:59 server chạy rất chậm" hoặc "câu truy vấn này hôm nay sao chạy chậm thế, mọi khi nó chạy nhanh lắm cơ mà". Đó là khi chúng ta cần các thống kê hiệu suất truy vấn trong quá khứ để làm công tác tối ưu. Thật may là bản thân SQL Server có hỗ trợ chúng ta trong chuyện này. Mỗi khi SQL Server thực thi xong một câu truy vấn nó sẽ ghi lại một vài thông tin quan trọng liên quan đến hiệu năng nhằm giúp cho người quản trị có thể truy xuất và phân tích khi cần. Dynamic Management View (thường viết tắt là DMVs) là những view hệ thống mà SQL Server ghi lại trong quá trình làm việc nhằm giúp DBA dễ dàng hơn trong việc troubleshoot và có những tác động phù hợp, sys.dm_exec_query_stats sẽ là view chúng ta cần khảo sát.

Những thông tin trong này được lưu trữ dựa theo plan cache của các statements, có thể là những câu lệnh SELECT riêng lẻ (ad-hoc query) hoặc là các statements bên trong stored procedure. Chúng được lưu trữ trên bộ nhớ chính (memory) nên sẽ bị mất mỗi khi SQL Server restart, hoặc có thể mất khi server bị quá tải bộ nhớ (memory pressure) hoặc DBA chạy những câu lệnh xóa bộ nhớ (cache) chứa những thông số này và đặc biệt khi các statement bị recompile hoặc stored procedure bị alter. Một điều đáng chú ý đó là cách lưu trữ của view này là cộng dồn tất cả các lần chạy của câu lệnh đó. Hãy cùng xem ví dụ sau:

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 '%LogSystem%'

AND t.text NOT LIKE '%SELECT t.text%'

Trong câu truy vấn trên mình có join thêm với DMF (dynamic management function) sys.dm_exec_sql_text để lấy chính xác hai câu lệnh SELECT của chúng ta bằng cách filter những câu truy vấn có text chứa từ LogSystem. Kết quả trả về là nội dung hai câu truy vấn đó cùng với thông tin như tạo ra lúc nào, lần cuối thực thi là khi nào, tổng số lần thực thi cho tới hiện tại, tổng CPU time (worker time), tổng elapsed time, và tổng logical reads. Ba cột cuối trong result set là giá trị trung bình của các thông số bằng cách lấy tổng chia cho số lần execution, ngoài ra bản thân DMV này còn chứa rất nhiều thông tin khác các bạn có thể tự khám phá thêm.

Với cách lưu trữ như vậy chúng ta chỉ biết tương đối hiệu năng truy vấn của chúng trong những ngày trước nhưng cũng rất hữu ích để tham khảo khi troubleshoot những vấn đề về hiệu năng. Nếu các bạn muốn những giá trị này chính xác hơn hãy tạo một SQL Agent job để capture chúng theo thời gian, với từng khoảng thời gian nhỏ giá trị trung bình càng thể hiện chính xác những gì chúng đã thực thi.

Như vậy qua bài viết này đã giúp các bạn học quản trị sql server hiểu thêm những thông số quan trọng thường được sử dụng để xác định hiệu năng của một câu truy vấn và ý nghĩa của từng thông số cũng như cách thu thập chúng.

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 chinh thức khóa học J2EE for Developer- JE082401DEV	19h00 Thứ 6 ngày 16/08/2024
Lịch khai giảng chính thức khóa học ASP.net for Developer - A082401DEV	08h30 thứ 7 ngày 03/08/2024
Lịch khai giảng chính thức lớp Java Developer - JE062401DEV-HV	8h30 thứ 2 ngày 10/06/2024
Xem thêm

Thông tin chi tiết