Điều chỉnh hiệu suất trong SQL Server: tìm những truy vấn chậm

Điều chỉnh hiệu suất SQL là một trận chiến không bao giờ kết thúc. Bài viết này sẽ cung cấp một số lời khuyên để bạn có thể tìm ra các truy vấn SQL chậm và thực hiện điều chỉnh hiệu suất trong SQL Server.

1. Tìm truy vấn chậm với DMV của SQL

Một trong những tính năng tuyệt vời của SQL Server là chế độ quản lý năng động (Dynamic Management Views - DMVs) được tích hợp sẵn. Các chế độ quản lý năng động này cung cấp nhiều thông tin về các vấn đề khác nhau.

Có một số DMV cung cấp dữ liệu về thống kê truy vấn, kế hoạch thực thi (execution plan), truy vấn gần đây và nhiều thông tin khác nữa. Một số DMV có thể được sử dụng cùng nhau để cung cấp một số thông tin chi tiết quan trọng.

Ví dụ, truy vấn dưới đây được sử dụng để tìm các truy vấn có nhiều lần đọc, viết nhất, thời gian hoạt động (CPU), v.v…

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Kết quả của truy vấn sẽ giống như hình ảnh bên dưới. Bạn có thể thấy rằng truy vấn ở trên cùng chiếm tất cả các tài nguyên.

Từ kết quả này, chúng ta có thể sao chép truy vấn SQL đó và xem xét các biện pháp cải thiện hiệu suất như thêm một chỉ mục v.v…

Ưu điểm: Luôn đưa ra số liệu thống kê tổng hợp.

Nhược điểm: Không cho người dùng biết cái gì đang gọi truy vấn và không thể hình dung khi các truy vấn được gọi theo thời gian.

2. Báo cáo truy vấn qua các giải pháp APM

Một trong những tính năng tuyệt vời của công cụ quản trị hiệu suất ứng dụng (APM) là khả năng theo dõi các truy vấn SQL. Ví dụ, Retrace theo dõi truy vấn SQL qua nhiều nhà cung cấp cơ sở dữ liệu, trong đó có SQL Server.

Retrace có thể cho người dùng biết có bao nhiêu truy vấn đã được thực hiện, thời gian thực hiện truy vấn trung bình và những giao dịch đang gọi truy vấn. Đây thực sự là những thông tin có giá trị để điều chỉnh hiệu suất SQL.

Các giải pháp APM thu thập dữ liệu bằng cách tạo hiệu suất thấp đối với mã ứng dụng khi chạy. Dưới đây là ảnh chụp màn hình từ bảng điều khiển ứng dụng của Retrace, cho thấy một ứng dụng cụ thể có thời gian truy vấn SQL nhiều nhất.

Retrace thu thập các số liệu thống kê hiệu suất về từng truy vấn SQL được thực hiện. Bạn có thể tìm kiếm các truy vấn cụ thể để tìm ra các vấn đề tiềm ẩn.

Bằng cách chọn một truy vấn cụ thể, bạn có thể thấy tần suất truy vấn đó được gọi theo thời gian và thời gian thực hiện truy vấn. Ngoài ra, bạn cũng có thể xem các trang web sử dụng truy vấn SQL và những yếu tốt ảnh hưởng tới hiệu suất.  

Vì Retrace là công cụ ghi chép mã và theo dõi các đối tượng request ASP.NET, nó thậm chí có thể cung cấp toàn bộ thông tin về hoạt động của mã.

Hình ảnh dưới đây hiển thị tất cả các truy vấn SQL và các thông tin chi tiết khác về hoạt động của mã. Retrace thậm chí có thể hiển thị thông báo đăng nhập trong cùng một chế độ xem. Ngoài ra, nó còn cho thấy địa chỉ máy chủ, tên cơ sở dữ liệu truy vấn đang được thực hiện và xem có bao nhiêu hồ sơ được trả lại.

Retrace cung cấp thông tin SQL toàn diện và nhiều tính năng theo dõi, cảnh báo về các truy vấn SQL.

Ưu điểm: Cung cấp báo cáo chi tiết trên từng ứng dụng và từng truy vấn. Có thể hiển thị các giao dịch chi tiết cách truy vấn được sử dụng chỉ với 10 đô la một tháng.

Nhược điểm: Không cung cấp số lần đọc hoặc viết cho mỗi truy vấn.

3. SQL Server Profiler 

SQL Server Profiler là công cụ ghi chép hữu ích nếu muốn xem truy vấn SQL được thực hiện trong cơ sở dữ liệu thời gian thực. SQL Profiler ghi lại các sự kiện chi tiết về tương tác với SQL Server.

• Kết nối đăng nhập, ngắt kết nối đăng nhập và không thể kết nối
• Câu lệnh SELECT, INSERT, UPDATE, DELETE
• Các cuộc gọi trạng thái gói lệnh thủ tục RPC
• Mở đầu và kết thúc Stored Procedure
• Mở đầu và kết thúc các câu lệnh trong Stored Procedure.
• Mở đầu và kết thúc một gói lệnh SQL
• Lỗi được ghi vào nhật ký lỗi của SQL Server
• Một khóa được mua hoặc phát hành trên một đối tượng cơ sở dữ liệu
• Kiểu dữ liệu con trỏ mở
• Kiểm tra cho phép bảo mật

Bạn có thể đọc bài viết Giám sát cơ sở dữ liệu với SQL Profiler để biết cách sử dụng SQL Server Profiler nhé. 

Ưu điểm: cung cấp thông tin dữ liệu chi tiết.

Nhược điểm: Người dùng phải kích hoạt công cụ này thủ công. 

4. Extended Events của SQL Server

SQL Profiler đã được thay thế bằng SQL Server Extended Events, điều này đã khiến nhiều người dùng bực bội. Extended Events hoạt động thông qua Event Tracing (ETW), đây là cách phổ biến mà Microsoft thực hiện cho tất cả các công nghệ của mình để đưa ra dữ liệu chẩn đoán.

ETW cung cấp các tính năng linh hoạt hơn nhiều. Là một nhà phát triển, bạn có thể dễ dàng chạm vào các sự kiện ETW từ SQL Server để thu thập dữ liệu cho mục đích tùy chỉnh.

Ưu điểm: Dễ dàng để kích hoạt và chạy.

Nhược điểm: Vì công cụ này còn khá mới, nên chưa nhiều người biết đến nó. 

5. Query Performance Insight cho cơ sở dữ liệu SQL Azure

Trong Azure Portal, bạn có thể truy cập vào một loạt các báo cáo hiệu suất và các mẹo tối ưu hóa rất hữu ích.

Lưu ý: Các báo cáo này chỉ có sẵn cho các cơ sở dữ liệu được lưu trữ trên SQL Azure.

Trong ảnh chụp màn hình bên dưới, bạn có thể thấy SQL Azure dễ dàng sử dụng các truy vấn dùng hầu hết các CPU, Data IO và Log IO. Ngoài ra nó còn có một số báo cáo cơ bản tuyệt vời được tích hợp sẵn.

Bạn cũng có thể chọn một truy vấn cụ thể để có được thông tin chi tiết giúp điều chỉnh hiệu suất SQL.

Ưu điểm: Cung cấp thông tin cơ bản chi tiết.

Nhược điểm: Chỉ hoạt động trên Azure, không báo cáo trên nhiều cơ sở dữ liệu.