共用方式為

Query Store 提示最佳做法

  • 發行項

適用於：Microsoft Fabric 中的 SQL Server 2022 （16.x） Azure SQL 資料庫 Azure SQL 受控執行個體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 工具，或其他查詢層級的應用程式遙測，識別以較高相容性層級回歸的極端查詢。 如需相容性層級之間差異的詳細資訊，請檢閱相容性層級之間的差異。

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

封鎖未來執行有問題的查詢

您可以使用 ABORT_QUERY_EXECUTION 查詢提示來封鎖未來執行已知有問題的查詢，例如不重要的查詢造成高資源耗用量，並影響重要的應用程式工作負載。

備註

目前， ABORT_QUERY_EXECUTION （預覽） 查詢提示僅適用於 Azure SQL Database。

例如，若要封鎖 query_id 39 的未來執行，請執行下列語句：

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

如需詳細資訊，請參閱查詢存放區提示 範例。

應注意下列考量：

  • 當您為查詢指定此提示時，嘗試執行查詢會失敗，並出現錯誤 8778、嚴重性 16、 查詢執行已中止，因為已指定ABORT_QUERY_EXECUTION提示。
  • 若要解除封鎖查詢，您可以將值傳遞 query_id 至 sys.sp_query_store_clear_hints 預存程式，以清除提示。
  • 您可以使用下列範例查詢，從 sys.query_store_query_hints （Transact-SQL） 系統檢視開始，在查詢存放區中尋找系統檢視中封鎖的所有查詢： 
    SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
INNER JOIN sys.query_store_query AS q
ON qsh.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS qt
ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%'
  • 若要取得 query_id 此值，至少必須在查詢存放區中記錄一個查詢執行。 此執行不一定成功。 這表示可以封鎖未來逾時或取消查詢的執行。
  • 如果在您封鎖它時查詢已經在執行，它將繼續執行。 您可以使用 KILL 語句中止查詢。
    • 查詢存放區中不會記錄已終止查詢的執行。 如果查詢尚未在查詢存放區中，您必須讓查詢完成或逾時，以取得您可以封鎖的 query_id。
  • 當查詢被ABORT_QUERY_EXECUTION提示封鎖時，sys.query_store_runtime_stats檢視中的execution_type和execution_type_desc欄位會分別設定為 4 和Exception。
  • 如同所有查詢存放區提示，您必須擁有 ALTER 資料庫的許可權，才能設定和清除 ABORT_QUERY_EXECUTION 提示。

查詢存放區提示的注意事項

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

資料分佈變更

計劃指南、透過查詢存放區強制執行的計劃以及查詢存放區參數，都會覆蓋最佳化工具的決策制定。 查詢存放區提示現在可能會有用，但未來可能不會如此。 例如，如果 Query Store 提示在先前的資料分佈中有助於某個查詢，但大規模 DML 操作改變了資料，這可能會適得其反。 新的資料分佈可能會令優化器做出比提示更佳的決策。 此案例是強制計劃行為最常見的結果。

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

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

  • 在已知大型資料分佈變更後。
  • 當資料庫可用的資源變更時。 例如，當 Azure SQL Database、SQL 受控實例或 SQL Server 虛擬機的計算大小變更時。
  • 計劃修正已變得持久。 查詢存放區提示最適合用於短期修正。
  • 非預期的效能迴歸。

潛在影響廣泛

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

在將查詢存放區提示應用於生產環境之前，請仔細對任務關鍵或敏感性系統進行負載測試變更。

不支援強制參數化以及 RECOMPILE 提示

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

提示 RECOMPILE 與資料庫層級的強制參數化設定不相容。 如果資料庫使用強制參數化，而且 RECOMPILE 提示是查詢存放區中設定的提示字串的一部分，Database Engine 會忽略 RECOMPILE 提示，並且在指定情況下套用其他提示。 此外，從 2022 年 7 月開始，Azure SQL Database 會發出警告（錯誤碼 12461），指出 RECOMPILE 已忽略提示。

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

相關內容