Đ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.
Chúng tôi chuyên cung cấp những khoá học để trở thành chuyên gia Phân tích dữ liệu, đăng ký ngay để nhận được tư vấn MIỄN PHÍ về chi tiết lộ trình dành riêng cho bạn nhé!
SQL Level 2: Advanced SQL (for Data Engineer) – Lập trình dữ liệu nâng cao
