Khi DBA không chạy một mình: Tối ưu từ Database tới ứng dụng

Đầu tiên, hãy đặt mình ở hoàn cảnh nơi các DBA, Developer hay DevOps không trực tiếp tạo ra lợi nhuận cho tổ chức, và chỉ là một tổ, một phòng của back office. VD như một công ty FinTech chẳng hạn, bạn đứng ở tuyến sau nhưng phải giữ cho hệ thống hoạt động mượt mà, với hàng triệu giao dịch mỗi ngày.

Sếp của bạn sẽ complain khi nào hệ thống chậm, hay một báo cáo bị chậm, đội front office sẽ hoảng loạn khi một ngày mưa gió, hệ thống treo đơ hay bị sập. Công sức, đóng góp của bạn không được ghi nhận mà chỉ nhận được những lời trách móc. Nhưng bạn vẫn có một niềm tự hào khi chịu trách nhiệm đảm bảo hệ thống - trái tim của tổ chức - không bao giờ "hụt hơi" và hoạt động trơn tru.

Để làm được điều đó, hãy cùng thằng tôi, người đứng sau hậu tường, “lặn sâu” vào MS SQL Server để tìm thủ phạm. Chúng ta sẽ bắt đầu bằng cách tìm ra những "kẻ gây rối" - các truy vấn chạy chậm hoặc chạy nhiều - sau đó tối ưu hóa chúng từ cả phía database và ứng dụng nhé.

Công cụ xác định các truy vấn chạy chậm hoặc chạy nhiều

Để trả lời câu hỏi, chúng ta cần xác định các truy vấn gây chậm trễ hoặc được thực thi thường xuyên. Các công cụ và kỹ thuật sau được sử dụng:

• Query Store: Tính năng từ SQL Server 2016, tự động ghi lại lịch sử thực thi, kế hoạch, và thống kê. Bạn có thể bật qua thuộc tính database và xem báo cáo "Top Resource Consuming Queries", tùy chỉnh theo thời gian, CPU, hoặc số lần đọc logic. Ví dụ, sau 1-2 ngày chạy ở môi trường production, bạn sẽ có dữ liệu thực tế. Tham khảo Microsoft Learn - Query Store.
• Dynamic Management Views (DMVs): Các DMV như sys.dm_exec_query_stats cung cấp thống kê hiệu suất. Truy vấn mẫu đã được cung cấp ở phần trực tiếp, giúp tìm các truy vấn chậm nhất dựa trên thời gian thực thi trung bình. Lưu ý, DMVs bị xóa khi SQL Server khởi động lại, nên cần xem xét thời gian uptime của server.
• SQL Server Profiler và Extended Events: Profiler ghi lại các sự kiện như batch SQL, stored procedure, và RPC, nhưng đã bị deprecated. Extended Events, dựa trên Event Tracing (ETW), là lựa chọn hiện đại, dễ bật và phát triển giải pháp tùy chỉnh. Tham khảo tài liệu Stackify - SQL Performance Tuning.
• Activity Monitor: Công cụ trong SSMS, giúp xem hoạt động hiện tại, bao gồm các quy trình, chờ tài nguyên, và truy vấn tốn tài nguyên. Bạn có thể sắp xếp theo thời gian thực thi, CPU, hoặc số lần đọc logic.

Hãy thường xuyên giám sát để bắt kịp vấn đề sớm, như câu nói: "Một mũi khâu đúng lúc cứu được chín mũi khâu" - hoặc ở đây, một truy vấn được tối ưu hóa đúng lúc cứu được cả hệ thống.

Tối ưu hóa từ phía database

Sau khi xác định các truy vấn, chúng ta cần tối ưu hóa từ phía database. Các kỹ thuật bao gồm:

• Thêm chỉ mục (Indexes): Chỉ mục giúp tăng tốc tìm kiếm, đặc biệt cho cột thường được tìm kiếm hoặc join. Tuy nhiên, cũng cần tránh quá nhiều index, kẻo query của bạn dùng trực tiếp bảng thay vì index, các bạn có thể đọc thêm ở bài viết trước của tôi. Cách audit, nhận diện over-index, và xây dựng chiến lược index + partition chuẩn bài
• Cập nhật thống kê (Statistics): SQL Server dùng thống kê để ước lượng số hàng trả về. Hãy cập nhật thường xuyên với câu lệnh UPDATE STATISTICS Orders WITH FULLSCAN; để đảm bảo kế hoạch thực thi câu query (Execution plans) luôn chính xác.
• Yêu cầu viết lại toàn bộ truy vấn: Tránh SELECT *, thay bằng liệt kê cột cần thiết, tránh thảm họa dữ liệu. Đồng thời, có thể thay subquery bằng join khi có thể, vì join thường hiệu quả hơn.
• Sử dụng Stored Procedures: Đối với truy vấn chạy nhiều, stored procedures giúp precompile kế hoạch, tăng tốc.
• Phân tích kế hoạch thực thi (Execution Plans): Xem kế hoạch trong SSMS (Ctrl + M), tìm cảnh báo như key lookups hoặc chỉ mục thiếu. Công cụ như ApexSQL Plan giúp phân tích trực quan hơn, các bạn có thể tham khảo các bài viết về phân tích Execution Plans ở đây.

Tối ưu hóa từ phía ứng dụng

Từ phía ứng dụng, developer cần viết truy vấn hiệu quả. Các best practice bao gồm:

• Tránh SELECT *: Chỉ lấy cột cần thiết, giảm dữ liệu truyền và xử lý - nhắc lại một lần nữa, để tránh thảm họa dữ liệu.
• Sử dụng tham số: Thay vì nối chuỗi, dùng parameterized queries. Điều này tránh SQL injection và giúp tái sử dụng kế hoạch thực thi.

• Cẩn thận với ORM: Công cụ như Entity Framework dễ gây N+1 select problem. Sử dụng eager loading hoặc join rõ ràng để tránh.
• Chọn loại join hiệu quả: Dùng INNER JOIN cho mối quan hệ 1-1, LEFT JOIN khi cần tất cả bản ghi từ bảng bên trái.
• Luôn giới hạn số hàng: Dùng TOP hoặc LIMIT để giảm số hàng trả về, đặc biệt với dữ liệu lớn.
• Caching dữ liệu ở phía ứng dụng "ngôi sao" của phần này. Nếu một truy vấn được gọi nhiều lần với cùng dữ liệu (ví dụ, danh sách danh mục sản phẩm, cấu hình hệ thống, hoặc thông tin người dùng), việc truy vấn database liên tục chẳng khác gì bạn đi siêu thị mua cùng một món hàng mỗi ngày thay vì trữ sẵn trong tủ lạnh. Caching giúp lưu trữ dữ liệu tạm thời ở phía ứng dụng, giảm số lần truy vấn database. Đọc thêm bài implement cache của tôi để hiểu hơn nhé.
• Hợp tác với DBA: Developer và DBA cần làm việc chặt chẽ, như beer và lạc vậy.

Ưu nhược điểm các phương án và bài học kinh nghiệm

Các phương án xử lý tổng hợp ở đây bao gồm:

• Thêm chỉ mục: Ưu điểm: Tăng tốc đọc, dễ triển khai. Nhược điểm: Làm chậm write, tốn không gian lưu trữ, có thể gây over-index nếu không phân tích kỹ càng.
• Cập nhật thống kê: Ưu điểm: Đảm bảo kế hoạch thực thi chính xác. Nhược điểm: Có thể tốn thời gian, cần chạy định kỳ.
• Viết lại truy vấn: Ưu điểm: Hiệu quả cao, không tốn tài nguyên thêm. Nhược điểm: Cần kỹ năng, có thể ảnh hưởng ứng dụng hiện tại.
• Sử dụng stored procedures: Ưu điểm: Tăng tốc, tái sử dụng kế hoạch. Nhược điểm: Khó bảo trì nếu phức tạp.

Best practice - bài học kinh nghiệm:

• Giám sát thường xuyên để phát hiện sớm vấn đề.
• Đừng lạm dụng chỉ mục, cần đo lường tác động.
• Viết truy vấn hiệu quả ngay từ đầu, tránh sửa chữa sau.
• Hợp tác giữa developer và DBA là chìa khóa, như câu nói: "Một mình thì nhanh, cùng nhau thì bền."
• Sử dụng Query Store và DMVs để giám sát.
• Cập nhật thống kê định kỳ, thêm chỉ mục dựa trên phân tích kế hoạch thực thi.
• Developers cần hiểu schema database, tránh ORM gây vấn đề N+1. DBA cần hiểu các phương án có thể xử lý ở phía developers
• Kiểm tra hiệu suất trên dữ liệu thực tế, không chỉ trên môi trường test.

Kết luận

Tối ưu hóa database và ứng dụng là một hành trình, không phải đích đến. Hãy tưởng tượng như việc chỉnh dây đàn guitar – làm đúng, bạn sẽ có bản nhạc hay; làm sai, bạn sẽ có tiếng ồn. Và tối ưu một hệ thống không phải chỉ của Developer, DBA rồi cả hạ tầng, cả DevOps, ... và cả sếp của bạn nữa :D

Với các công cụ và kỹ thuật trên, hy vọng bạn sẽ làm cho hệ thống chạy mượt mà, người dùng hài lòng, và cả team vui vẻ. Chúc bạn thành công, và đừng quên, nếu mọi thứ vẫn chậm, hãy pha một ấm trà trước nhé!

Happy coding!

/Son Do - I share real-world lessons, team building & developer growth.

#SQLServer #PerformanceTuning #DevLife #dotnetcore #DatabaseOptimization #1percentbetter #wecommit100xshare #BackendEngineering #DevTips #CachingMatters #FromDevToProd