查詢存放區提示最佳做法

  • 發行項

適用於：SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL 受控執行個體

本文會詳細說明使用查詢存放區提示的最佳做法。 透過查詢存放區提示，不需修改應用程式程式碼，也能調整查詢計劃的型態。

查詢存放區提示的使用案例

請考慮下列使用案例作為查詢存放區提示的理想使用案例。 如需詳細資訊，請參閱使用查詢存放區提示的時機。

警告

由於 SQL Server 查詢最佳化工具通常會選擇最好的查詢執行計畫，因此，建議資深開發人員與資料庫管理員只在必要情況使用提示。 如需詳細資訊，請參閱查詢提示。

無法變更程式碼

使用查詢存放區提示可讓您影響查詢的執行計畫，而不需要變更應用程式程式碼或資料庫物件。 其他功能都無法讓您如此快速且輕鬆地套用查詢提示。

例如可以運用查詢存放區提示，在不重新部署程式碼的狀況下協助 ETL。 可透過這部 14 分鐘的影片，瞭解如何使用查詢存放區提示來改善大量載入的效能：

查詢存放區提示是輕量型查詢微調方法，但如果查詢開始出問題，則應該使用更大量的程式碼變更來處理。 如果經常發現需要將查詢存放區提示套用至查詢，請考慮採取更大規模的查詢重寫。 SQL Server 查詢最佳化工具通常會選擇最好的查詢執行計劃；因此建議資深開發人員與資料庫管理員只在必要情況下使用提示。

如需可套用哪些查詢提示的資訊，請參閱支援的查詢提示。

在高交易負載下，或使用任務關鍵性程式碼

如果因為高執行時間需求或交易式載入而無法進行程式碼變更，查詢存放區提示可以快速地將查詢提示套用至現有的查詢工作負載。 新增和移除查詢存放區提示很簡單。

您可以將查詢存放區提示新增並移除至查詢批次，以調整例外工作負載高載時段的效能。

作為計劃指南的替代專案

在查詢存放區提示之前，開發人員必須依賴計劃指南來完成類似的工作，這可能會很複雜。 查詢存放區提示會與 SQL Server Management Studio (SSMS) 的查詢存放區功能整合，以便以視覺化的方式探索查詢。

使用計劃指南時，必須使用查詢程式碼片段搜尋所有計劃。 查詢存放區提示功能不需要查詢完全相符，也能影響產生的查詢計劃。 查詢存放區提示可以套用至查詢存放區資料集中的 query_id。

查詢資料存放區提示會覆寫硬式編碼陳述式層級提示與現有計劃指南。

考慮較新的相容性層級

當較新的資料庫相容性層級因為廠商規格或較大型的測試延遲而無法使用時，查詢存放區提示可能是寶貴的方法。 當資料庫可以使用較高的相容性層級時，請考慮升級個別查詢的資料庫相容性層級，以利用 SQL Server 的最新效能優化和功能。

例如，如果具有 SQL Server 2022 (16.x) 執行個體與相容性層級 140 的資料庫，您仍然可以使用查詢存放區提示，執行相容性層級 160 的個別查詢。 可使用下列提示：

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

如需完整的教學課程，請參閱查詢存放區提示範例。

升級後，請考慮較舊的相容性層級

另一種查詢存放區提示可派上用場的情況，是 SQL Server 執行個體在移轉或升級之後無法直接修改查詢。 可使用查詢存放區提示對查詢套用先前的相容性層級，直到可以重寫或以其他方式解決，而能在最新的相容性層級中良好執行。 使用查詢存放區的回歸查詢報表、在移轉期間使用 Query Tuning Advisor 工具，或其他查詢層級的應用程式遙測，可識別在較高相容性層級回歸的極端查詢。 如需相容性層級之間差異的詳細資訊，請檢閱相容性層級之間的差異。

以這種方式針對新的相容性層級進行效能測試與部署查詢存放區提示之後，您可以升級整個資料庫的相容性層級，同時保留先前相容性層級的重點問題查詢，而不需要變更任何程式碼。

查詢存放區提示的考量

部署查詢存放區提示時，請考量下列案例。

資料散發變更

計劃指南、透過查詢存放區強制執行的計劃，以及查詢存放區提示，都會覆寫最佳化工具的決策制定。 查詢存放區提示現在可能相當有用，但未來並非如此。 例如，如果查詢存放區提示會協助先前資料散發中的某個查詢，而後出現大規模的 DML 作業導致資料變更，則可能會造成作業效率下降。 新的資料散發可能會令最佳化工具做出比提示更好的決策。 此案例是強制計劃行為最常見的結果。

定期重新評估您的查詢存放區提示策略

在下列情況下，請重新評估現有的查詢存放區提示策略：

  • 已知大型資料散發變更之後。
  • 當 Azure SQL 資料庫、受控執行個體或虛擬機器的服務等級目標 (SLO) 已變更時。
  • 計劃修正已長時間存在。 查詢存放區提示最適合用於短期修正。
  • 非預期的效能迴歸。

潛在影響廣泛

不論參數集、來源應用程式、使用者或結果集為何，查詢存放區提示都會影響查詢的所有執行。 在意外的效能迴歸情況下，可以使用 sys.sp_query_store_clear_hints 輕鬆地移除以 sys.sp_query_store_set_hints 建立的查詢存放區提示。

在生產環境中套用查詢存放區提示之前，請仔細載入任務關鍵性或敏感性系統的負載測試變更。

不支援強制參數化和 RECOMPILE 提示

當資料庫選項 PARAMETERIZATION 設為 FORCED 時，不支援套用使用查詢存放區提示的 RECOMPILE 查詢提示。 如需詳細資訊，請參閱使用強制參數化的指導方針。

RECOMPILE 提示與資料庫層級設定的強制參數化不相容。 如果資料庫已設定強制參數化，而 RECOMPILE 提示是查詢存放區中針對查詢設定之提示字串的一部分，則 Database Engine 會忽略 RECOMPILE 提示，並會套用任何其他適用的提示。 此外，從 2022 年 7 月開始，Azure SQL Database 應該會發出警告 (錯誤碼 12461)，其中指出已忽略 RECOMPILE 提示。

如需可套用哪些查詢提示的資訊，請參閱支援的查詢提示。

另請參閱

下一步