Khám phá Kho Truy vấn

Đã hoàn thành

Kho Truy vấn SQL Server là một tính năng cho mỗi cơ sở dữ liệu tự động ghi lại lịch sử truy vấn, kế hoạch và thống kê thời gian chạy, đơn giản hóa việc khắc phục sự cố hiệu suất và tinh chỉnh truy vấn. Nó cũng cung cấp thông tin chuyên sâu về mô hình sử dụng cơ sở dữ liệu và tiêu thụ tài nguyên.

Kho Truy vấn bao gồm ba kho lưu trữ:

• Kho kế hoạch: Lưu trữ thông tin về kế hoạch thực thi ước tính.
• Lưu trữ thống kê thời gian chạy: Lưu trữ thông tin thống kê thực hiện.
• Số liệu thống kê chờ đợi lưu trữ: Vẫn chờ đợi thông tin thống kê.

Bật Kho Truy vấn

Kho Truy vấn được bật theo mặc định trong cơ sở dữ liệu Azure SQL. Nếu bạn muốn sử dụng nó với SQL Server và Azure Synapse Analytics, trước tiên bạn cần phải bật nó. Để bật tính năng Lưu trữ Truy vấn, hãy dùng truy vấn sau đây hợp lệ cho môi trường của bạn:

-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

Cách Lưu trữ Truy vấn thu thập dữ liệu

Kho Truy vấn tích hợp với quy trình xử lý truy vấn ở nhiều giai đoạn. Tại mỗi điểm tích hợp, dữ liệu được thu thập trong bộ nhớ và ghi vào đĩa không đồng bộ để giảm thiểu chi phí I/O. Các điểm tích hợp như sau:

1. Khi một truy vấn thực hiện lần đầu tiên, văn bản truy vấn và kế hoạch thực thi ước tính ban đầu của truy vấn sẽ được gửi đến Kho truy vấn và tiếp tục tồn tại.

2. Kế hoạch cập nhật trong Kho truy vấn khi một truy vấn biên dịch lại. Nếu các recompile kết quả trong một kế hoạch thực hiện mới được tạo ra, nó cũng vẫn tồn tại trong kho truy vấn để augment các kế hoạch trước đó. Ngoài ra, Kho Truy vấn theo dõi số liệu thống kê thực thi cho từng kế hoạch truy vấn cho mục đích so sánh.

3. Trong quá trình biên dịch và kiểm tra các giai đoạn biên dịch lại, Kho truy vấn xác định nếu có kế hoạch bắt buộc cho truy vấn được thực hiện. Truy vấn được biên dịch lại nếu Kho truy vấn cung cấp một kế hoạch bắt buộc khác với kế hoạch trong bộ đệm ẩn thủ tục.

4. Khi một truy vấn thực hiện, thống kê thời gian chạy của truy vấn sẽ tiếp tục tồn tại trong Kho truy vấn. Kho Truy vấn tổng hợp dữ liệu này để đảm bảo thể hiện chính xác mọi kế hoạch truy vấn.

Để tìm hiểu thêm về cách Query Store thu thập dữ liệu, hãy xem Cách Query Store thu thập dữ liệu.

Các kịch bản phổ biến

Kho Truy vấn SQL Server cung cấp thông tin chuyên sâu có giá trị về hiệu suất của các thao tác cơ sở dữ liệu. Các kịch bản phổ biến bao gồm:

• Xác định và sửa chữa hồi quy hiệu năng do lựa chọn kế hoạch thực thi truy vấn kém hơn.
• Xác định và điều chỉnh các truy vấn sử dụng tài nguyên cao nhất.
• Kiểm tra A/B để đánh giá tác động của cơ sở dữ liệu và thay đổi ứng dụng.
• Đảm bảo tính ổn định về hiệu năng sau khi nâng cấp SQL Server.
• Xác định các truy vấn được sử dụng thường xuyên nhất.
• Kiểm tra lịch sử kế hoạch truy vấn cho truy vấn.
• Xác định và cải thiện khối lượng công việc ngoài dự tính.
• Tìm hiểu các thể loại chờ đợi phổ biến của cơ sở dữ liệu và các truy vấn và kế hoạch đóng góp ảnh hưởng đến thời gian chờ.
• Phân tích các mẫu hình sử dụng cơ sở dữ liệu theo thời gian về tiêu thụ tài nguyên (CPU, I/O, Bộ nhớ).

Khám phá các dạng xem Kho Truy vấn

Khi Kho Truy vấn được bật trên cơ sở dữ liệu, thư mục Kho Truy vấn sẽ hiển thị cho cơ sở dữ liệu trong Object Explorer. Đối với Azure Synapse Analytics, dạng xem Kho Truy vấn được hiển thị bên dưới Dạng xem Hệ thống. Các dạng xem Kho Truy vấn cung cấp thông tin chuyên sâu tổng hợp, nhanh chóng về các khía cạnh hiệu năng của cơ sở dữ liệu SQL Server.

Truy vấn Hồi quy

Truy vấn hồi quy trải nghiệm tình trạng giảm hiệu suất theo thời gian do thay đổi kế hoạch thực thi. Kế hoạch thực hiện ước tính có thể thay đổi do nhiều yếu tố khác nhau, bao gồm thay đổi sơ đồ, thay đổi thống kê và thay đổi chỉ mục. Điều tra bộ đệm ẩn thủ tục có thể là bản năng đầu tiên, nhưng nó chỉ lưu trữ kế hoạch thực thi mới nhất cho truy vấn và các kế hoạch có thể được loại bỏ dựa trên nhu cầu về bộ nhớ của hệ thống. Tuy nhiên, Kho Truy vấn vẫn giữ nguyên nhiều kế hoạch thực thi cho mỗi truy vấn, cho phép linh hoạt khi chọn một kế hoạch cụ thể thông qua kế hoạch buộc phải giải quyết hồi quy hiệu suất truy vấn do thay đổi kế hoạch gây ra.

Chế độ xem Truy vấn hồi quy có thể xác định chính xác các truy vấn có chỉ số thực thi đang thoái lui do thay đổi kế hoạch thực thi trong một khung thời gian cụ thể. Dạng xem này cho phép lọc dựa trên số liệu đã chọn (chẳng hạn như thời lượng, thời gian CPU, số hàng và nhiều mục khác) và thống kê (tổng, trung bình, tối thiểu, tối đa hoặc độ lệch chuẩn). Sau đó, bộ lọc sẽ liệt kê 25 truy vấn được hồi quy hàng đầu dựa trên bộ lọc được cung cấp. Theo mặc định, dạng xem biểu đồ thanh đồ họa của các truy vấn được hiển thị, nhưng bạn có thể tùy chọn xem các truy vấn theo định dạng lưới.

Sau khi chọn một truy vấn từ ngăn truy vấn trên cùng bên trái, ngăn tóm tắt kế hoạch sẽ hiển thị các kế hoạch truy vấn cố định liên kết với truy vấn theo thời gian. Việc chọn kế hoạch truy vấn trong ngăn Tóm tắt Kế hoạch sẽ hiển thị kế hoạch truy vấn đồ họa ở ngăn dưới cùng. Các nút thanh công cụ trong cả ngăn tóm tắt kế hoạch và ngăn kế hoạch truy vấn đồ họa cho phép bạn buộc kế hoạch đã chọn cho truy vấn đã chọn. Cấu trúc và hành vi của ngăn này được sử dụng nhất quán trên tất cả các dạng xem Truy vấn SQL.

Ngoài ra, bạn có thể sử dụng sp_query_store_force_plan thủ tục được lưu trữ để sử dụng tính năng cưỡng chế kế hoạch.

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

Sử dụng Tài nguyên Tổng thể

Chế độ xem Mức tiêu thụ tài nguyên tổng thể cho phép phân tích tổng mức tiêu thụ tài nguyên cho nhiều chỉ số thực thi (chẳng hạn như số lần thực thi, thời lượng, thời gian chờ, v.v.) trong một khung thời gian cụ thể. Các biểu đồ được thể hiện có tính tương tác; khi chọn một số đo từ một trong các biểu đồ, một lần truy sâu qua dạng xem hiển thị các truy vấn liên kết với số đo đã chọn sẽ hiển thị trong tab mới.

Dạng xem chi tiết cung cấp 25 truy vấn người tiêu dùng tài nguyên hàng đầu đã đóng góp vào số liệu đã được chọn. Dạng xem chi tiết này sử dụng giao diện nhất quán cho phép kiểm tra các truy vấn được liên kết và các chi tiết của truy vấn, đánh giá các kế hoạch truy vấn ước tính đã lưu và tùy chọn sử dụng kế hoạch bắt buộc để cải thiện hiệu suất. Dạng xem này có giá trị khi tài nguyên hệ thống trở thành vấn đề, chẳng hạn như khi mức sử dụng CPU đạt đến công suất.

Truy vấn Tiêu thụ Tài nguyên Hàng đầu

Chế độ xem Truy vấn tiêu thụ tài nguyên hàng đầu tương tự như chi tiết của chế độ xem Tiêu thụ tài nguyên tổng thể. Nó cũng cho phép chọn một số liệu và thống kê như một bộ lọc. Tuy nhiên, các truy vấn hiển thị là 25 truy vấn có ảnh hưởng nhất dựa trên khung thời gian và bộ lọc đã chọn.

Dạng xem Truy vấn Tiêu thụ Tài nguyên Hàng đầu cung cấp chỉ báo đầu tiên về bản chất ngoài dự kiến của khối lượng công việc khi xác định và cải thiện khối lượng công việc ngoài dự kiến. Ví dụ: trong hình ảnh sau đây, số liệu Số lượng Thực thi và Thống kê Tổng được chọn để công bố rằng khoảng 90% truy vấn tiêu tốn tài nguyên hàng đầu chỉ được thực hiện một lần.

Truy vấn với kế hoạch bắt buộc

Chế độ xem Truy vấn có kế hoạch bắt buộc cung cấp cái nhìn nhanh về các truy vấn có kế hoạch truy vấn bắt buộc. Dạng xem này trở nên liên quan nếu kế hoạch bắt buộc không còn hoạt động như mong đợi và cần được đánh giá lại. Dạng xem này cung cấp khả năng xem lại tất cả kế hoạch thực thi ước tính tiếp tục cho truy vấn đã chọn một cách dễ dàng xác định xem một kế hoạch khác hiện đã phù hợp hơn với hiệu suất hay không. Nếu vậy, các nút thanh công cụ sẵn dùng để không thực hiện một kế hoạch theo yêu cầu.

Truy vấn Có Biến thể Cao

Hiệu suất truy vấn có thể khác nhau giữa thực thi. Chế độ xem Truy vấn có biến thể cao chứa phân tích các truy vấn có biến thể hoặc độ lệch chuẩn cao nhất cho một chỉ số đã chọn. Giao diện nhất quán với hầu hết các dạng xem Kho Truy vấn cho phép kiểm tra chi tiết truy vấn, đánh giá kế hoạch thực thi và tùy chọn bắt buộc một kế hoạch cụ thể. Sử dụng dạng xem này để điều chỉnh các truy vấn không thể dự đoán thành một mẫu hình hiệu suất nhất quán hơn.

Thống kê Chờ Truy vấn

Chế độ xem Thống kê Chờ Truy vấn phân tích các danh mục chờ hoạt động tích cực nhất cho cơ sở dữ liệu và hiển thị biểu đồ. Biểu đồ này có tính tương tác; chọn một thể loại chờ truy sâu vào chi tiết của các truy vấn góp phần vào thống kê thời gian chờ.

Giao diện dạng xem chi tiết cũng nhất quán với hầu hết các dạng xem kho truy vấn cho phép kiểm tra chi tiết truy vấn, đánh giá kế hoạch thực thi và tùy chọn bắt buộc một kế hoạch cụ thể. Dạng xem này giúp xác định các truy vấn ảnh hưởng đến trải nghiệm người dùng trên các ứng dụng.

Truy vấn Theo dõi

Chế độ xem Truy vấn theo dõi cho phép phân tích một truy vấn cụ thể dựa trên giá trị ID truy vấn đã nhập. Sau khi chạy, dạng xem sẽ cung cấp lịch sử thực thi đầy đủ của truy vấn. Dấu kiểm trên thực thi cho biết kế hoạch bắt buộc đã được sử dụng. Dạng xem này có thể cung cấp thông tin chuyên sâu về truy vấn, chẳng hạn như những truy vấn có kế hoạch bắt buộc để xác minh rằng hiệu suất truy vấn vẫn ổn định.

Sử dụng Kho Truy vấn để tìm các chờ truy vấn

Khi hiệu suất của hệ thống bắt đầu xuống cấp, bạn nên tham khảo số liệu thống kê chờ truy vấn để có thể xác định nguyên nhân. Ngoài việc xác định các truy vấn cần điều chỉnh, nó cũng có thể làm sáng lên các nâng cấp cơ sở hạ tầng tiềm năng có lợi.

Kho truy vấn SQL cung cấp dạng xem Thống kê Chờ Truy vấn để cung cấp thông tin chi tiết về các danh mục chờ hàng đầu cho cơ sở dữ liệu. Hiện tại, có 23 hạng mục chờ.

Biểu đồ thanh hiển thị các thể loại chờ có ảnh hưởng nhất đối với cơ sở dữ liệu khi bạn mở dạng xem Thống kê Chờ Truy vấn. Ngoài ra, bộ lọc nằm trong thanh công cụ của ngăn thể loại chờ cho phép thống kê chờ được tính toán dựa trên tổng thời gian chờ (mặc định), thời gian chờ trung bình, thời gian chờ tối thiểu, thời gian chờ tối đa hoặc thời gian chờ độ lệch chuẩn.

Việc chọn một thể loại chờ sẽ truy sâu qua chi tiết của các truy vấn đóng góp vào thể loại chờ đó. Từ dạng xem này, bạn có thể điều tra các truy vấn riêng lẻ có ảnh hưởng nhất. Bạn có thể truy nhập các kế hoạch thực thi ước tính cố định được hiển thị trong ngăn tóm tắt Kế hoạch bằng cách chọn một truy vấn trong ngăn truy vấn. Việc chọn kế hoạch truy vấn từ ngăn tóm tắt Kế hoạch sẽ hiển thị kế hoạch truy vấn đồ họa ở ngăn dưới cùng. Từ dạng xem này, bạn có thể buộc hoặc không thực hiện kế hoạch truy vấn cho truy vấn để cải thiện hiệu suất.

Sửa kế hoạch tự động

SQL Server 2017 và Cơ sở dữ liệu Azure SQL giới thiệu khái niệm sửa kế hoạch tự động bằng cách phân tích dữ liệu trong Kho truy vấn. Khi bạn bật Kho Truy vấn với cơ sở dữ liệu trong SQL Server 2017 (trở lên) và trong Cơ sở dữ liệu Azure SQL, công cụ SQL Server sẽ tìm kiếm hồi quy kế hoạch truy vấn và cung cấp đề xuất. Bạn có thể xem các đề xuất này trong dạng sys.dm_db_tuning_recommendations dạng xem quản lý động (DMV). Những đề xuất này bao gồm các câu lệnh T-SQL để buộc một kế hoạch truy vấn theo cách thủ công khi hiệu suất ở trạng thái tốt.

Nếu bạn có được sự tự tin trong các đề xuất này, bạn có thể cho phép SQL Server để buộc kế hoạch tự động khi hồi quy gặp phải. Cho phép sửa kế hoạch tự động bằng cách ALTER DATABASE và đối AUTOMATIC_TUNING kế hoạch.

Đối với Cơ sở dữ liệu Azure SQL, bạn cũng có thể bật sửa kế hoạch tự động thông qua các tùy chọn điều chỉnh tự động trong cổng thông tin Azure hoặc API REST. Các đề xuất sửa kế hoạch tự động luôn được bật cho bất kỳ cơ sở dữ liệu nào có bật Kho Truy vấn (đây là tùy chọn mặc định cho Cơ sở dữ liệu Azure SQL và Phiên bản được Quản lý của Azure SQL). Đối với cơ sở dữ liệu mới, tính năng sửa kế hoạch tựFORCE_PLAN) được bật theo mặc định cho Cơ sở dữ liệu Azure SQL.