Tìm hiểu về gợi ý truy vấn

  • 4 phút

Gợi ý truy SELECTvấn là các tùy chọn hoặc chiến lược có thể được áp dụng để bắt buộc bộ xử lý truy vấn sử dụng một toán tử cụ thể trong kế hoạch thực thi cho , INSERT, UPDATEhoặc DELETE câu lệnh. Gợi ý truy vấn ghi đè lên bất kỳ kế hoạch thực thi nào mà bộ xử lý truy vấn có thể chọn cho một truy vấn nhất định với OPTION mệnh đề.

Trong hầu hết các trường hợp, trình tối ưu hóa truy vấn sẽ chọn một kế hoạch thực thi hiệu quả dựa trên các chỉ mục, thống kê và phân phối dữ liệu. Người quản trị cơ sở dữ liệu hiếm khi cần can thiệp theo cách thủ công.

Bạn có thể thay đổi kế hoạch thực thi của truy vấn bằng cách thêm gợi ý truy vấn vào cuối truy vấn. Ví dụ: nếu OPTION (MAXDOP <integer_value>) bạn thêm vào cuối truy vấn sử dụng một CPU duy nhất, truy vấn có thể sử dụng nhiều CPU (song song) tùy thuộc vào giá trị bạn chọn. Hoặc bạn có thể sử dụng OPTION (RECOMPILE) để đảm bảo rằng truy vấn tạo ra một kế hoạch mới, tạm thời mỗi lần truy vấn được thực thi.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

Mặc dù gợi ý truy vấn có thể cung cấp một giải pháp bản địa hóa cho các vấn đề liên quan đến hiệu năng khác nhau, bạn nên tránh sử dụng chúng trong môi trường sản xuất vì những lý do sau.

  • Việc có gợi ý truy vấn cố định trên truy vấn của bạn có thể dẫn đến các thay đổi cơ sở dữ liệu cấu trúc có lợi cho truy vấn đó không được áp dụng.
  • Bạn không thể hưởng lợi từ các tính năng mới và được cải thiện trong các phiên bản tiếp theo của SQL Server nếu bạn kết ghép truy vấn với một kế hoạch thực thi cụ thể.

Tuy nhiên, có một số gợi ý truy vấn có sẵn trên SQL Server, được sử dụng cho các mục đích khác nhau. Hãy thảo luận về một vài trong số họ dưới đây:

  • FAST <integer_value>—truy xuất số hàng integer_value <> hàng đầu tiên trong khi tiếp tục thực hiện truy vấn. Nó hoạt động tốt hơn với các tập dữ liệu nhỏ và giá trị thấp để gợi ý truy vấn nhanh. Khi số hàng tăng lên, chi phí truy vấn trở nên cao hơn.

  • OPTIMIZE FOR—cung cấp hướng dẫn cho trình tối ưu hóa truy vấn rằng nên sử dụng một giá trị cụ thể cho biến cục bộ khi biên dịch và tối ưu hóa truy vấn.

  • USE PLAN—trình tối ưu hóa truy vấn sử dụng kế hoạch truy vấn được xác định bởi thuộc xml_plan truy vấn.

  • RECOMPILE—tạo một kế hoạch mới, tạm thời cho truy vấn và bỏ nó ngay sau khi thực thi truy vấn.

  • { LOOP | MERGE | HASH } JOIN—chỉ định tất cả các thao tác nối được thực hiện bởi LOOP JOIN, MERGE JOINhoặc HASH JOIN trong toàn bộ truy vấn. Trình tối ưu hóa chọn chiến lược nối tốn kém nhất từ các tùy chọn nếu bạn chỉ định nhiều hơn một gợi ý gia nhập.

  • MAXDOP <integer_value>—ghi đè mức độ tối đa của giá trị song song của sp_configure. Truy vấn xác định tùy chọn này cũng sẽ thay thế Thống đốc Tài nguyên.

Bạn cũng có thể áp dụng nhiều gợi ý truy vấn trong cùng một truy vấn. Ví dụ sau đây sử dụng HASH GROUP gợi FAST <integer_value> ý truy vấn và trong cùng một truy vấn.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO

Để tìm hiểu thêm về gợi ý truy vấn, hãy xem Gợi ý (Transact-SQL).

Gợi ý Kho Truy vấn

Gợi ý Kho Truy vấn cung cấp một phương pháp đơn giản để định hình kế hoạch truy vấn mà không cần sửa đổi mã ứng dụng.

Gợi ý kho truy vấn rất hữu ích khi trình tối ưu hóa truy vấn không tạo ra kế hoạch thực thi hiệu quả và khi nhà phát triển hoặc DBA không thể sửa đổi văn bản truy vấn ban đầu. Trong một số ứng dụng, văn bản truy vấn có thể được mã hóa cứng hoặc được tạo tự động.

Ảnh chụp màn hình về cách hoạt động của gợi ý Lưu trữ Truy vấn.

Để sử dụng gợi ý Kho Truy vấn, bạn cần xác định query_id Kho Truy vấn của câu lệnh truy vấn mà bạn muốn sửa đổi thông qua dạng xem ca-ta-lô Kho Truy vấn, báo cáo Kho Truy vấn dựng sẵn hoặc Thông tin chuyên sâu về Hiệu suất Truy vấn cho Cơ sở dữ liệu Azure SQL. Sau đó, thực sp_query_store_set_hints hiện với query_id gợi ý truy vấn và chuỗi mà bạn muốn áp dụng cho truy vấn.

Ví dụ sau đây cho thấy cách nhận truy query_id vấn cụ thể, rồi sử dụng truy vấn đó để áp RECOMPILE dụng và MAXDOP gợi ý cho truy vấn.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
    INNER JOIN sys.query_store_query q 
        ON qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

Có một số kịch bản trong đó gợi ý Kho Truy vấn có thể giúp giải quyết các vấn đề về hiệu năng ở mức truy vấn.

  • Biên dịch lại một truy vấn trên mỗi thực hiện.
  • Giới hạn mức độ song song tối đa cho hoạt động cập nhật thống kê.
  • Sử dụng kết nối Hàm băm thay vì liên kết Vòng lặp Lồng nhau.
  • Sử dụng mức độ tương thích 110 cho một truy vấn cụ thể trong khi vẫn giữ cho cơ sở dữ liệu ở tính tương thích hiện tại.

Để biết thêm thông tin về gợi ý Kho Truy vấn, hãy xem Gợi ý Lưu trữ Truy vấn.