Thành thạo SQL Server Query Store: Capture, Phân tích, và Giải quyết hiệu suất Query

Microsoft® SQL Server® Query Store, như tên gợi ý, giống như một kho lưu trữ capture lịch sử database của các query đã thực thi, query runtime execution statistics, và execution plans. Vì dữ liệu được lưu trên đĩa, bạn có thể truy xuất dữ liệu Query Store bất kỳ lúc nào cho mục đích troubleshooting, và SQL Server restart không ảnh hưởng đến dữ liệu. Dùng Query Store, được giới thiệu trong SQL Server 2016 và có sẵn trong tất cả các bản phát hành sau, để troubleshoot vấn đề hiệu suất do query plan changes gây ra.

Giới thiệu

Bạn có thể thấy baseline data analysis hữu ích cho performance troubleshooting, nhưng cho đến khi Query Store được giới thiệu, thông tin đó không có sẵn native trong SQL server. Nếu Query Store được bật cho một database, nó giữ thông tin về query đã thực thi cùng với execution plans và runtime statistics. Nó được bật ở cấp database và có thể bật cho tất cả user databases và database hệ thống MSDB. Thông tin và metadata liên quan Query Store được lưu trong internal tables trong chính database đó. Không cần quản lý backup riêng cho Query Store vì standard database backup có tất cả thông tin cần thiết. Để xem dữ liệu Query Store, bạn cần có quyền View Database State, và bạn cần có quyền DB_Owner để force và unforce plans. Bạn có thể xem dữ liệu này qua management studio và qua T-SQL.

Cấu hình Query Store

Thực hiện các bước sau để bật Query Store cho một database:

1. Chuột phải Database -> Go to Properties.
2. Dưới Select a Page, chọn Select Query Store.
3. Trong phần General, thay đổi Operation Mode (Requested) từ Off thành Read-Write.
4. Để các trường khác trong drop-down box Operation Mode (Requested) ở giá trị mặc định pre-populated.
5. Click Ok trong hộp database properties để bật Query Store cho database đã chọn.

Bạn có thể bật Query Store với T-SQL bằng mã sau:

ALTER DATABASE [DB_Name] SET QUERY_STORE = ON; 

Các tùy chọn cấu hình khác nhau của Query Store

Danh sách sau chứa mô tả một số tùy chọn Query Store:

• Operation Mode (Requested) có ba giá trị: Off, Read Only, và Read Write. Khi Query Store có mode là Read, không có execution plan mới hoặc query runtime statistics nào được thu thập. Mode này dành cho read-only operation liên quan đến Query Store. Thay đổi mode thành Read Write cho phép Query Store thu thập query được thực thi, execution plan dùng cho query đó, và runtime statistics cho query chạy trong database đã chọn.

• Data Flush Interval (Minutes) cho phép bạn đặt tần suất collected execution plans và query runtime statistics được flush từ memory ra disk. Mặc định, nó được đặt là 15 phút.

• Statistics Collection Interval định nghĩa aggregation interval của query runtime statistics nên được dùng bên trong Query Store. Mặc định, nó được đặt là 60 phút.

• Max Size (MB) dùng để cấu hình kích thước tối đa của Query Store. Mặc định, nó được đặt là 100 MB. Dữ liệu trong Query Store được lưu trong database nơi SQL Server Query Store được bật. Tùy thuộc vào kích thước cấu hình ở đây, sau khi Query Store đạt kích thước tối đa, operation mode tự động chuyển sang mode Read Only.

• Capture Mode giúp bạn chọn loại query nào capture trong Query Store. Tùy chọn mặc định là All, lưu mọi query được thực thi. Khi tùy chọn này được đặt là auto, Query Store cố gắng triage query nào capture, theo priority, và cố gắng bỏ qua query executed không thường xuyên cũng như ad hoc query khác.

• Stale Query Threshold (Days) dùng để định nghĩa dữ liệu ở trong Query Store bao lâu. Giá trị mặc định cho mode này là 30 ngày.

Query Store Reports

Query Store bao gồm các báo cáo sau:

• Regressed Queries: Xác định query mà execution metrics gần đây đã regressed hoặc thay đổi tệ hơn.
• Overall Resource Consumptions: Phân tích tổng resource consumption cho database cho bất kỳ execution metrics nào.
• Top Resource Consuming Queries: Query có tác động lớn nhất đến resource consumption của database.
• Queries with Forced Plans: Báo cáo built-in, hiển thị tất cả query với forced execution plans.
• Queries with High Variation: Báo cáo này hiển thị query với parameterization problems thường xuyên nhất.
• Tracked Queries: Theo dõi thực thi các query quan trọng nhất trong thời gian thực.

Force Plans với Query Store

Do plan regression, một query, chạy tốt hôm qua, có thể mất quá nhiều thời gian thực thi, tiêu thụ quá nhiều resource hoặc timeout hôm nay. Mặc định, SQL Server chỉ giữ latest execution plan cho query. Bất kỳ thay đổi nào trong schema, statistics, hoặc indexes có thể thay đổi query execution plan được dùng bởi Query Optimizer. Plan cũng có thể bị drop do pressure trong plan cache memory.

Query Store lưu query plans và statistical information aggregated theo thời gian trong từng database được giám sát. Khác với plan cache, Query Store có thể giữ nhiều plan cho mỗi query và duy trì lịch sử thay đổi query plan cùng với stats liên quan cho mỗi plan. Bằng cách chọn giữa các execution plan khác nhau, bạn có thể tiến hành force bất kỳ execution plan nào. Query Optimizer sau đó dùng forced execution plan này chỉ cho các execution query tới.

Báo cáo Query Store -> Open Top Resource Consuming Queries liệt kê query tốn nhiều resource. Hãy chọn một query để xem xét:

Di chuột qua Plan để xem thống kê liên quan.

Hãy so sánh các plan khác nhau.

Plan 216 chi tiết:

Plan 195 chi tiết:

Average duration cho Plan 216 thấp hơn, nên nó có thể dùng để force plan này trong các execution tiếp theo. Click vào Force Plan để thấy màn hình xác nhận với thông báo “Do you want to force plan 216 for query 42?”

Click Yes. Sau khi plan được force, nó được highlight với checkmark như screenshot sau. Trong tương lai, plan này sẽ được query optimizer dùng cho execution.

Best Practices với Query Store

• Dùng SQL Server Management Studio mới nhất để thấy latest features và enhancements.

• Xác minh và giám sát Query Store data collection.

• Đặt Optimal Query capture mode và xem lại tùy chọn cấu hình Query Store khi cần để điều chỉnh.

• Tránh dùng non-parameterized queries.

• Kiểm tra trạng thái forced plans thường xuyên.

Kết luận

Query Store là tính năng hữu ích được giới thiệu trong SQL Server 2016. Vì tuning performance enhancements là một trong những kỹ năng then chốt yêu cầu cho mọi database administrator (DBA), bạn nên học cách cấu hình và dùng Query Store. Bạn cũng có thể dùng Query Store để theo dõi thay đổi hiệu suất và troubleshoot bất kỳ sự suy giảm nào trong query performance bằng cách so sánh execution plans. Bạn có thể force query plan cho bất kỳ query nào. Điều này ghi đè plan được lưu trong plan cache, và do đó cung cấp lợi ích hiệu suất. Query Store không có tác động lớn đến SQL Server performance vì nó được dùng để capture và lưu query execution statistics và plans để xem sau.

Sử dụng tab Feedback để đưa ra bình luận hoặc đặt câu hỏi.

Tối ưu môi trường của bạn với quản trị, quản lý và cấu hình chuyên nghiệp

Các chuyên gia Dịch vụ ứng dụng của Rackspace (RAS) cung cấp các dịch vụ chuyên nghiệp và được quản lý sau đây trên danh mục ứng dụng rộng:

• Nền tảng eCommerce và Trải nghiệm số
• Quản trị nguồn lực doanh nghiệp (ERP)
• Business Intelligence
• Salesforce Quản trị quan hệ khách hàng (CRM)
• Databases
• Lưu trữ Email và Năng suất

Chúng tôi cung cấp:

• Chuyên môn khách quan: Chúng tôi đơn giản hóa và hướng dẫn hành trình hiện đại hóa của bạn, tập trung vào các khả năng mang lại giá trị ngay lập tức.
• Fanatical Experience™: Chúng tôi kết hợp phương pháp Process first. Technology second.® với hỗ trợ kỹ thuật chuyên dụng để cung cấp giải pháp toàn diện.
• Danh mục không đối thủ: Chúng tôi áp dụng kinh nghiệm đám mây rộng lớn để giúp bạn chọn và triển khai công nghệ phù hợp trên đám mây phù hợp.
• Giao hàng Agile: Chúng tôi gặp gỡ bạn ở bất kỳ đâu trong hành trình của bạn và điều chỉnh thành công của chúng tôi với của bạn.

Chat ngay để bắt đầu.