使用查詢存放區監視效能
適用於：
SQL Server 2016 （13.x） 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics （僅限專用 SQL 集區）MICROSOFT Fabric 中的 SQL 資料庫
查詢存放區 功能可讓您深入瞭解 SQL Server、Azure SQL 資料庫、Fabric SQL 資料庫、Azure SQL 受控執行個體 和 Azure Synapse Analytics 的查詢計劃選擇和效能。 查詢存放區可協助您快速找出由於查詢計劃變更所導致的效能差異，以簡化效能疑難排解作業。 查詢存放區會自動擷取查詢、計劃和執行階段統計資料的歷程記錄，並將其保留供您檢閱。 其會以時段來區分資料、供您查看資料庫使用模式，並了解何時在伺服器上發生查詢計劃變更。
您可以使用 ALTER DATABASE SET 選項來設定 查詢存放區。
- 如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱 操作 Azure SQL Database 中的查詢存放區。
- 如需使用查詢存放區探索可採取動作的資訊和調整效能的更多資訊，請參閱使用查詢存放區調整效能。
- 如需在不更改應用程式碼的情況下制定查詢計劃的詳細資訊，請參閱查詢存放區提示。
重要
若您只針對 SQL Server 2016 (13.x) 中的 Just-In-Time 工作負載見解使用查詢存放區，請計畫儘快安裝 KB 4340759 中的效能可擴縮性修正程式。
啟用查詢存放區
- 根據預設，系統會為新的 Azure SQL Database 和 Azure SQL 受控執行個體資料庫啟用查詢存放區。
- 針對 SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)，系統預設不會啟用查詢存放區。 使用 SQL Server 2022 (16.x) 啟動新的資料庫時，系統預設會使用
READ_WRITE模式啟用。 若要在 SQL Server 2022 (16.x) 中啟用功能以進一步追蹤效能歷程記錄、為與問題有關的查詢計劃疑難排解以及啟用新功能，我們建議在所有資料庫上啟用查詢存放區。 - 根據預設，不會針對新的 Azure Synapse Analytics 資料庫啟用查詢存放區。
使用 SQL Server Management Studio 中的查詢存放區分頁
在 [物件總管] 中，以滑鼠右鍵按一下資料庫，然後點選 [屬性]。
注意
至少需要 Management Studio 16 版。
在 [資料庫屬性] 對話方塊中，選取 [查詢存放區] 頁面。
在 [作業模式 (要求)] 方塊中，選取 [讀取寫入] 。
使用 Transact-SQL 陳述式
ALTER DATABASE使用語句來啟用指定資料庫的 查詢存放區。 例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
目前限制ALTER DATABASE在 Fabric SQL 資料庫中設定 查詢存放區 的選項。
在 Azure Synapse Analytics 中，在不使用其他選項的情況下啟用查詢存放區，例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON;
如需和查詢存放區相關的更多語法選項，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。
注意
您無法為 master 或 tempdb 資料庫啟用查詢存放區。
重要
如需有關啟用查詢存放區並讓它根據您的工作負載調整的相關資訊，請參閱使用查詢存放區的最佳作法.
查詢存放區中的資訊
SQL Server 中任何特定查詢的執行計畫通常會在一段時間後，因為統計資料的變更、結構描述變更、建立/刪除索引等數種不同原因而有所演變。儲存快取的查詢計劃之程序快取，只會儲存最新的執行計劃。 計劃也會因為記憶體不足的壓力，而從計劃快取中收回。 因此，因為執行計劃變更所造成的查詢效能低下，可能相形重要，而且可能需要許多時間才可解決。
因為查詢存放區會為每項查詢保留多個執行計劃，其可強制套用原則以指示查詢處理器要為查詢使用特定的執行計劃。 這也稱為強制執行計劃。 查詢存放區中的強制執行計劃，透過類似於 USE PLAN 查詢提示的機制加以提供，但它不需要在使用者應用程式中進行任何變更。 強制執行計劃可以解決在非常短的期間內，因計劃變更所導致的查詢效能低下。
注意
查詢存放區會收集 DML 陳述式，例如 SELECT、INSERT、UPDATE、DELETE、MERGE 和 BULK INSERT。
根據設計，查詢存放區不會收集 DDL 陳述式 (例如 CREATE INDEX 等) 的計畫。查詢存放區藉由收集基礎 DML 陳述式的計畫來擷取累積資源耗用量。 例如，查詢存放區可能會顯示內部執行的 SELECT 和 INSERT 陳述式，以填入新的索引。
查詢存放區根據預設不會收集原生編譯預存程序的資料。 請使用 sys.sp_xtp_control_query_exec_stats 來啟用收集原生編譯預存程序的資料。
等候統計資料是另一種可協助您針對資料庫引擎效能進行疑難排解的來源資訊。 等候統計資料長久以來只能在執行個體層級取得，難以回溯至特定查詢。 從 SQL Server 2017 (14.x) 和 Azure SQL Database 開始，查詢存放區會包含追蹤等候統計資料的維度。下列範例會啟用查詢存放區來收集等候統計資料。
ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
使用查詢存放區功能的常見情況包括：
- 強制執行先前的查詢計劃，快速找出並修正計劃效能低下。 修正因為執行計劃變更而最近出現的效能低下。
- 判斷在指定的時段執行查詢的次數、協助 DBA 疑難排解資源的效能問題。
- 識別過去 x 小時內的前 n 項查詢 (依據執行時間、記憶體耗用量等等)。
- 稽核指定的查詢之查詢計劃記錄。
- 分析特定資料庫的資源 (CPU、I/O 及記憶體) 使用模式。
- 識別前 n 項等候資源的查詢。
- 了解特定查詢或計劃的等候本質。
查詢存放區包含三個存放區：
- 計劃存放區以保存執行計劃資訊。
- 執行階段統計資料存放區以保存執行統計資料資訊。
- 等候統計資料存放區以保存等候統計資料資訊。
計劃存放區中可為查詢儲存的不重複計劃數目，受限於 max_plans_per_query 組態選項。 為了增強效能，資訊會以非同步方式寫入存放區。 若要將空間使用量降至最低，在執行階段統計資料存放區中的執行階段執行統計資料，會以固定的時段彙總。 對查詢存放區目錄檢視進行查詢時，會顯示這些存放區中的資訊。
下列查詢會從查詢存放區傳回關於查詢、其方案、編譯時間和執行階段統計資料的資訊。
SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;
次要複本上的查詢存放區
適用於：SQL Server (從 SQL Server 2022 (16.x) 開始)
次要複本功能上的查詢存放區可對次要複本工作負載執行主要複本可用的相同查詢存放區功能。 啟用次要複本上的查詢存放區時，複本會傳送查詢執行資訊，這些資訊通常會儲存回主要複本的查詢存放區內。 之後主要複本會將資料保存至自己查詢存放區的磁碟內。 基本上，主要和所有次要複本會共用同一個查詢存放區。 查詢存放區存在於主要複本中，並將所有複本的資料儲存在一起。
如需次要複本查詢存放區的完整資訊，請參閱 Always On 可用性群組次要複本的查詢存放區。
使用迴歸查詢功能
啟用查詢存放區之後，請重新整理 [物件總管] 窗格中的資料庫部分，以新增查詢存放區 區段。
注意
針對 Azure Synapse Analytics，查詢存放區檢視可在 [物件總管] 窗格資料庫部分的 [系統檢視] 下取得。
選取 [迴歸查詢]，開啟 SQL Server Management Studio 中的 [迴歸查詢] 窗格。 [回歸查詢] 窗格會顯示 查詢存放區 中的查詢和計劃。 頂端的下拉式清單方塊，可供依據各種準則來篩選查詢：持續時間 (毫秒) (預設)、CPU 時間 (毫秒)、邏輯讀取 (KB)、邏輯寫入 (KB)、實體讀取 (KB)、CLR 時間 (毫秒)、DOP、記憶體耗用量 (KB)、資料列計數、已使用的記錄記憶體 (KB)、已使用的暫存 DB 記憶體 (KB)，以及等候時間 (毫秒)。
選取計劃即可以圖形方式檢視查詢計劃。 按鈕可用來檢視來源查詢、強制執行及取消強制執行查詢計畫、在格線和圖表格式之間切換、比較所選取的計畫 (如果選取了多個)，以及重新整理顯示。
若要強制執行方案，請選取查詢與方案，然後選取 [強制執行方案]。 您只可以強制執行由查詢計劃功能所儲存且仍保留在查詢計劃快取中的計劃。
尋找等候查詢
自 SQL Server 2017 (14.x) 以及 Azure SQL Database 開始，一段時間後每個查詢的等待統計資料皆可在查詢存放區使用。
在查詢存放區中，等候類型會合併到等候類別。 sys.query_store_wait_stats (Transact-SQL) 可將等候型別對應至等候類型。
選取 [查詢等候統計資料]，以在 SQL Server Management Studio 18.0 或更新版本中開啟 [查詢等候統計資料] 窗格。 [查詢等候統計資料] 窗格會在查詢存放區中顯示包含前幾個等候類別的長條圖。 使用頂端的下拉式清單來選取等候時間彙總準則：平均值、最大值、最小值、標準差及總計 (預設)。
選取長條圖來選取等候類別，隨即顯示有關所選取等候類別的詳細資料檢視。 這個新的長條圖包含提供給該等候類別的查詢。
使用頂端的下拉式清單方塊，根據所選取等候類別的各種等候時間準則來篩選查詢：平均值、最大值、最小值、標準差及總計 (預設)。 選取計劃即可以圖形方式檢視查詢計劃。 提供有按鈕可供檢視來源查詢、強制執行或取消強制執行查詢計劃，以及重新整理顯示畫面。
等候類別會將不同的等候類型合併到本質類似的貯體中。 不同的等候類別需要不同的後續操作分析來解析問題，但同類別的等候型別會導致非常類似的疑難排解體驗，而提供受影響的前幾項查詢可能就是順利完成多數這類調查所缺少的片段。
以下範例示範如何在查詢存放區引入等候類別之前及之後深入了解您的工作負載：
| 過去的體驗 | 新的體驗 | 動作 |
|---|---|---|
| 每個資料庫的高 RESOURCE_SEMAPHORE 等候 | 查詢存放區特定查詢的高記憶體等候 | 找出查詢存放區中記憶體耗用量名列前茅的查詢。 這些查詢可能會進一步延遲受影響查詢的進度。 請考慮對這些查詢或受影響的查詢使用 MAX_GRANT_PERCENT 查詢提示。 |
| 每個資料庫的高 LCK_M_X 等候 | 查詢存放區特定查詢的高鎖定等候 | 查看受影響查詢的查詢文字，並找出目標實體。 在查詢存放區中尋找修改相同項目的其他查詢，這些查詢經常執行且/或持續時間很長。 找出這些查詢之後，請考慮變更應用程式邏輯，改善並行存取，或使用限制較少的隔離等級。 |
| 每個資料庫的高 PAGEIOLATCH_SH 等候 | 查詢存放區特定查詢的高緩衝區 IO 等候 | 在查詢存放區中尋找實體讀取次數高的查詢。 如果它們符合高 IO 等候的查詢，請考慮引入基礎實體索引搜尋，以執行搜尋而不是掃描，進而將查詢的 IO 負擔降至最低。 |
| 每個資料庫的高 SOS_SCHEDULER_YIELD 等候 | 查詢存放區特定查詢的高 CPU 等候 | 尋找查詢存放區中前幾項最耗 CPU 的查詢。 在它們中間找出高 CPU 趨勢與受影響查詢之高 CPU 等候相互關聯的查詢。 專注於將那些查詢最佳化，可能存在計畫迴歸或缺少索引。 |
設定選項
如需設定查詢存放區參數的可用選項，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。
查詢 sys.database_query_store_options 檢視可判斷查詢存放區目前的選項。 如需值的詳細資訊，請參閱 sys.database_query_store_options。
如需使用 Transact-SQL 陳述式設定組態選項的範例，請參閱選項管理。
注意
針對 Azure Synapse Analytics，查詢存放區可在其他平台上啟用，但不支援額外設定選項。
相關檢視、函數與程序
透過 Management Studio 或使用下列檢視與程序來檢視及管理查詢存放區。
查詢存放區函數
此函式可協助執行查詢存放區作業。
查詢存放區目錄檢視
目錄檢視會提供查詢存放區的相關資訊。
查詢存放區預存程序
預存程序可設定查詢存放區。
sp_query_store_consistency_check (Transact-SQL)1
1 在極端的案例中，查詢存放區可能會因為內部錯誤而進入錯誤狀態。 從 SQL Server 2017 (14.x) 開始，您可以透過在受影響的資料庫中執行 sp_query_store_consistency_check 預存程序來復原查詢存放區。 如需 actual_state_desc 資料行描述中所述的詳細資訊，請參閱 sys.database_query_store_options。
查詢存放區維護
本文已深入探討查詢存放區維護與管理的最佳做法與相關建議：管理查詢存放區的最佳做法。
效能稽核及疑難排解
如需使用查詢存放區深入進行效能調整的詳細資訊，請參閱用查詢存放區調整效能。
其他效能主題：