共用方式為

Query Store 提示最佳做法

適用於： SQL Server 2022 （16.x） 及後版本 Azure SQL Database AzureSQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫

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

查詢儲存庫提示的使用案例

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

Caution

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

當程式碼無法變更時

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

您可以使用查詢存放區提示，例如，在不需要重新部署程式碼的情況下，使擷取、轉換、載入（ETL）工作負載受益。 可透過這部 14 分鐘的影片，瞭解如何使用查詢存放區提示來改善大量載入的效能：

查詢存放區提示是輕量型查詢微調方法，但如果查詢開始出問題，則應該使用更大量的程式碼變更來處理。 如果您經常需要將查詢存放庫提示應用到查詢上，請考慮進行更大幅度的查詢重構。 SQL Server 查詢優化器通常會選取查詢的最佳執行計劃。 我們建議只使用提示作為經驗豐富的開發人員和資料庫管理員的最後手段。

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

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

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

您可以將查詢存放區提示新增和移除至查詢批次，以便在為應對突發工作負載高峰而設定的時間段內調整效能。

作為替代計劃指南的方案

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

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

查詢存放區提示會覆寫硬編碼語句層級提示與現有計劃指導。

考慮較新的相容性層級

當您因廠商規格或較長測試周期而無法使用更新的資料庫相容性層級時，查詢存放區提示可能是一種有價值的方法。 當資料庫可以使用較高的相容性層級時，請考慮升級個別查詢的資料庫相容性層級，以利用 SQL Server 的最新效能優化和功能。

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

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

如需完整的指南，請參閱查詢存放區提示範例。

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

另一個查詢存放區提示可協助的情況是，SQL Server 實例移轉或升級之後無法直接修改查詢。 可使用查詢存放區提示套用過去的相容性層級到查詢，直到查詢可以重寫或以其他方式解決，以便在最新的相容性層級中良好執行。 使用 查詢存放區的回歸查詢報告、在移轉期間使用 查詢調整助手工具，或其他查詢層級的應用程式遙測，識別在較高相容性層級下退化的異常查詢。 如需相容性層級之間差異的詳細資訊，請檢閱相容性層級之間的差異。

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

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

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

Note

ABORT_QUERY_EXECUTION查詢提示僅在 Azure SQL Database、Azure SQL Managed InstanceAUTD 及 SQL Server 2025 （17.x） 中提供。

例如，若要封鎖 39 的未來執行 query_id ，請執行 sys.sp_query_store_set_hints ，如下所示：

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

若要解除封鎖相同的查詢，請執行 sys.sp_query_store_clear_hints：

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

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

應注意下列考量：

  • 當您為查詢指定此提示時，嘗試執行查詢會失敗，並出現錯誤 8778、嚴重性 16、 查詢執行已中止，因為已指定ABORT_QUERY_EXECUTION提示。

  • 若要解除阻塞查詢，您可以將 query_id 值傳遞至 @query_id 參數，以清除 sys.sp_query_store_clear_hints 預存程序中的提示。

    • 此預存程序會清除查詢的所有提示。 如果您想要在解除封鎖查詢時保留現有提示，請使用 sys.sp_query_store_set_hints，移除 ABORT_QUERY_EXECUTION 提示但保留其他提示。

  • 您可以使用系統檢視在封鎖的查詢存放區中尋找查詢，如下列範例查詢所示：

    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 此值，至少必須在查詢存放區中記錄一個查詢執行。 此執行不一定成功。 這表示可以封鎖未來逾時或取消查詢的執行。

  • 如果您需要封鎖或解除封鎖具有特定查詢哈希的所有查詢，請考慮使用自動化腳本。 例如，dbo.sp_query_store_modify_hints_by_query_hash是範例預存程式，它會針對符合查詢哈希的所有sys.sp_query_store_set_hints值，在迴圈中呼叫 sys.sp_query_store_clear_hints 或 query_id 系統預存程式。

  • 如果在您封鎖它時查詢已經在執行，它將繼續執行。 您可以使用 KILL 語句中止查詢。

    • 查詢存放區中不會記錄已終止查詢的執行。 如果查詢尚未在查詢存放區中，您必須讓查詢完成或逾時，以取得您可以封鎖的 query_id。

  • 當查詢被ABORT_QUERY_EXECUTION提示封鎖時，execution_type檢視中的execution_type_desc和欄位會分別設定為 4 和Exception。

  • 如同所有查詢存放區提示，您必須擁有 ALTER 資料庫的許可權，才能設定和清除 ABORT_QUERY_EXECUTION 提示。

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

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

資料分佈變更

計劃指南、透過查詢存放區強制執行的計劃以及查詢存放區參數，都會覆蓋最佳化工具的決策制定。 查詢存放區提示現在可能會有幫助，但未來可能不會。 例如，如果查詢存放區提示可協助先前數據散發中的查詢，則如果大規模 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 已忽略提示。

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

相關內容

  • Last updated on