使用 查詢存放區 監視效能
適用于：
SQL Server 2016 (13.x) Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics (專用SQL集區)
查詢存放區功能可讓您深入瞭解SQL Server、Azure SQL Database、Azure SQL 受控執行個體和 Azure Synapse Analytics 的查詢計劃選擇和效能。 查詢存放區可協助您快速找出查詢計劃變更所造成的效能差異，藉此簡化效能疑難排解。 查詢存放區會自動擷取查詢、計劃和執行階段統計資料的歷程記錄，並將其保留供您檢閱。 其會以時段來區分資料、供您查看資料庫使用模式，並了解何時在伺服器上發生查詢計劃變更。 使用 [ALTER DATABASE SET] 選項可設定查詢存放區。
- 如需操作 Azure SQL Database 中查詢存放區的相關資訊，請參閱 操作 Azure SQL Database 中的查詢存放區。
- 如需探索可採取動作的資訊，並使用查詢存放區微調效能，請參閱使用 查詢存放區微調效能。
- 如需在不變更應用程式程式碼的情況下塑造查詢計劃的相關資訊，請參閱查詢存放區提示。
重要
如果您在 SQL Server 2016 (13.x) 中使用查詢存放區，請儘快在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) Preview 啟用。 若要啟用功能以更妥善地追蹤效能歷程記錄、針對查詢計劃相關問題進行疑難排解，以及啟用 SQL Server 2022 (16.x) Preview 中的新功能，建議您在新的和現有的資料庫上啟用查詢存放區。
- 根據預設，不會針對新的 Azure Synapse Analytics 資料庫啟用查詢存放區。
使用 SQL Server Management Studio 中的 [查詢存放區] 頁面
在物件總管中，以滑鼠右鍵按一下資料庫，然後選取 [屬性]。
注意
至少需要第 16 版Management Studio。
在 [資料庫屬性] 對話方塊中，選取 [查詢存放區] 頁面。
在 [作業模式 (要求)] 方塊中，選取 [讀取寫入] 。
使用 Transact-SQL 陳述式
使用 ALTER DATABASE 陳述式可啟用指定資料庫的查詢存放區。 例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
在 Azure Synapse Analytics 中，啟用查詢存放區而不使用其他選項，例如：
ALTER DATABASE <database_name>
SET QUERY_STORE = ON;
如需與查詢存放區相關的更多語法選項，請參閱ALTER DATABASE SET Options (Transact-SQL) 。
注意
無法為 master 或 tempdb 資料庫啟用查詢存放區。
重要
如需有關啟用查詢存放區並讓它根據您的工作負載調整的相關資訊，請參閱使用查詢存放區的最佳作法.
查詢存放區中的資訊
SQL Server中任何特定查詢的執行計畫通常會隨著時間演進，原因有許多不同的原因，例如統計資料變更、架構變更、建立/刪除索引等。預存查詢計劃的程式快取 () 只會儲存最新的執行計畫。 計劃也會因為記憶體不足的壓力，而從計劃快取中收回。 因此，因為執行計劃變更所造成的查詢效能低下，可能相形重要，而且可能需要許多時間才可解決。
因為查詢存放區會為每項查詢保留多個執行計劃，其可強制套用原則以指示查詢處理器要為查詢使用特定的執行計劃。 這也稱為強制執行計劃。 查詢存放區中的強制執行計劃，透過類似於 USE PLAN 查詢提示的機制加以提供，但它不需要在使用者應用程式中進行任何變更。 強制執行計劃可以解決在非常短的期間內，因計劃變更所導致的查詢效能低下。
注意
查詢存放區會收集 DML 陳述式 (例如 SELECT、INSERT、UPDATE、DELETE、MERGE 與 BULK 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, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id;
次要複本的查詢存放區
適用于：從 SQL Server 2022 (16.x) Preview) 開始SQL Server (
次要複本功能的查詢存放區可在主要複本可用的次要複本工作負載上啟用相同的查詢存放區功能。 當啟用次要複本的查詢存放區時，複本會將通常儲存在查詢存放區中的查詢執行資訊傳送回主要複本。 然後，主要複本會將資料保存在自己的查詢存放區內。 基本上，主要複本與所有次要複本之間有一個查詢存放區共用。 查詢存放區存在於主要複本上，並將所有複本的資料儲存在一起。
注意
複本集 或 複本群組：複本集定義為所有未命名的複本，這些複本會共用角色 (主要、次要、異地次要、異地主要複本) 或個別具名複本。
儲存有關查詢的資料可以分析為以複本集為基礎的工作負載。 複本查詢存放區可讓您監視和調整可能針對次要複本執行的任何唯一隻讀工作負載的效能。
為次要複本啟用查詢存放區
在針對次要複本使用查詢存放區之前，您必須設定並設定Always On可用性群組。
重要
適用于：SQL Server 2022 (16.x) CTP 2.x
您必須先啟用下列一組追蹤旗標，才能啟用次要複本的查詢存放區：12606、12606、12607、12608、12610、T12624。 若要啟用這些追蹤旗標：
- 從 [ 執行 ] 功能表) 開啟 services 管理主控台 (services.msc。
- 以滑鼠右鍵按一下 SQL Server 2022 CTP 2 SQL Server服務，然後選取 [屬性]。
- 如果服務狀態為 [ 執行中]，請選取 [ 停止]。 這會停止已安裝的實例。
- 在 [ 開始參數] 方塊中，新增值：
-T12606 -T12607 -T12608 -T12610 -T12624 - 選取 [啟動] 以啟動服務。
- 選取 [確定]。
使用ALTER DATABASE SET 選項為次要複本啟用查詢存放區， (Transact-SQL) 。 下列範例會在主資料庫上啟用查詢存放區，然後在次要複本上啟用。 若要執行此程式碼，請連接到主要複本上的資料庫。
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO
ALTER DATABASE CURRENT
FOR SECONDARY SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE
);
GO
您可以連線到次要複本上的資料庫並執行下列 Transact-SQL，以驗證次要複本上是否已啟用查詢存放區：
SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO
下列查詢sys.database_query_store_options範例結果表示查詢存放區處於次要複本的讀取/寫入狀態。 readonly_reason8 的 表示查詢是針對次要複本執行。 這些結果表示次要複本上已成功啟用查詢存放區。
| desired_state | desired_state_desc | actual_state | actual_state_desc | readonly_reason |
|---|---|---|---|---|
| 2 | READ_WRITE | 2 | READ_WRITE | 8 |
次要複本查詢存放區的效能考慮
次要複本用來將查詢資訊傳回主要複本的通道，是用來讓次要複本保持最新狀態的相同通道。 資料會儲存在主要複本上查詢存放區用於主要複本上執行之查詢的相同資料表中，這會導致查詢存放區的大小成長。
因此，當系統負載過大時，您可能會注意到因為通道多載而變慢。 此外，目前針對查詢存放區存在的相同臨機操作查詢擷取問題，將會繼續在次要複本上執行的工作負載。 深入瞭解如何在查詢存放區中保留最相關的資料。
停用次要複本的查詢存放區
若要停用次要複本的查詢存放區，請連線到主要複本上的資料庫，然後執行下列程式碼：
ALTER DATABASE CURRENT
FOR SECONDARY SET QUERY_STORE = OFF;
GO
使用迴歸查詢功能
啟用查詢存放區之後，請重新整理 [物件總管] 窗格中的資料庫部分，以新增查詢存放區 區段。
注意
針對 Azure Synapse Analytics，查詢存放區檢視可在 [物件總管] 窗格的資料庫部分的 [系統檢視] 下取得。
選取[回歸查詢] 以開啟 [回歸查詢] 窗格SQL Server Management Studio。 [迴歸查詢] 窗格會顯示查詢存放區中的查詢與計劃。 頂端的下拉式方塊，可供依據各種準則來篩選查詢：持續時間 (毫秒) (預設)、CPU 時間 (毫秒)、邏輯讀取 (KB)、邏輯寫入 (KB)、實體讀取 (KB)、CLR 時間 (毫秒)、DOP、記憶體耗用量 (KB)、資料列計數、已使用的記錄記憶體 (KB)、已使用的暫存 DB 記憶體 (KB)，以及等候時間 (毫秒)。
選取計劃即可以圖形方式檢視查詢計劃。 按鈕可用來檢視來源查詢、強制執行及取消強制執行查詢計畫、在格線和圖表格式之間切換、比較所選取的計畫 (如果選取了多個)，以及重新整理顯示。
若要強制執行計畫，請選取查詢和計畫，然後選取 [強制計畫]。 您只可以強制執行由查詢計劃功能所儲存且仍保留在查詢計劃快取中的計劃。
尋找等候查詢
從 2017 SQL Server 2017 (14.x) 和 Azure SQL Database 開始，查詢存放區會提供每個查詢的等候統計資料。
在查詢存放區中，等候類型會合併到等候類別。 sys.query_store_wait_stats (Transact-SQL) 中提供等候類別與等候類型的對應。
選取[查詢等候統計資料] 以在 SQL Server Management Studio v18 或更高版本中開啟 [查詢等候統計資料] 窗格。 [查詢等候統計資料] 窗格會在查詢存放區中顯示包含前幾個等候類別的長條圖。 使用頂端的下拉式清單來選取等候時間彙總準則：平均值、最大值、最小值、標準差及總計 (預設)。
按一下長條圖來選取等候類別，隨即顯示有關所選取等候類別的詳細資料檢視。 這個新的長條圖包含提供給該等候類別的查詢。
使用頂端的下拉式清單方塊，根據所選取等候類別的各種等候時間準則來篩選查詢：平均值、最大值、最小值、標準差及總計 (預設)。 選取計劃即可以圖形方式檢視查詢計劃。 提供有按鈕可供檢視來源查詢、強制執行或取消強制執行查詢計劃，以及重新整理顯示畫面。
等候類別會將不同的等候類型合併到本質類似的貯體中。 不同的等候類別需要不同的後續分析來解決問題，但相同類別中的等候類型會導致非常類似的疑難排解體驗，並在等候頂端提供受影響的查詢，將會是成功完成大部分這類調查的遺漏部分。
以下範例示範如何在查詢存放區引入等候類別之前及之後深入了解您的工作負載：
| 過去的體驗 | 新的體驗 | 動作 |
|---|---|---|
| 每個資料庫的高 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。
主要使用方式情節
選項管理
本節提供管理查詢存放區功能本身的一些指導方針。
查詢存放區狀態
查詢存放區會將其資料儲存在使用者資料庫中，這也就是為什麼其有大小限制 (以 MAX_STORAGE_SIZE_MB 設定)。 若查詢存放區中的資料達到上限，查詢存放區會自動從讀寫狀態變更為唯讀，並會停止收集新的資料。
查詢 sys.database_query_store_options 可判斷查詢存放區目前是否在作用中，以及其目前是否正在收集執行階段統計資料。
SELECT actual_state, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
查詢存放區狀態是由資料 actual_state 行決定。 若與想要的狀態不同，readonly_reason 資料行可以提供更多的資訊。 當查詢存放區大小超過配額時，此功能會切換至read_only模式並提供原因。 如需原因的詳細資訊，請參閱sys.database_query_store_options (Transact-SQL) 。
取得查詢存放區選項
若要找出查詢存放區狀態的詳細資訊，請於使用者資料庫中執行下列作業。
SELECT * FROM sys.database_query_store_options;
設定查詢存放區間隔
您可以覆寫彙總查詢執行階段統計資料的間隔 (預設為 60 分鐘)。 透過 sys.database_query_store_options 檢視，即可看到新的間隔值。
ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);
INTERVAL_LENGTH_MINUTES 不允許任意值。 您可以使用下列其中一項：1、5、10、15、30、 60 或 1440 分鐘。
注意
對於 Azure Synapse Analytics，不支援自訂查詢存放區組態選項，如本節所示。
查詢存放區空間使用量
若要檢查目前的查詢存放區的大小和限制，請在使用者資料庫中執行下列陳述式。
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
如果查詢存放區的儲存體已滿，請使用下列陳述式來擴充該儲存體。
ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);
設定查詢存放區選項
使用單一 ALTER DATABASE 陳述式即可一次設定多個查詢存放區選項。
ALTER DATABASE <database name>
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 3000,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 15,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 1000,
WAIT_STATS_CAPTURE_MODE = ON
);
如需組態選項的完整清單，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。
清除空間
查詢存放區內部資料表於建立資料庫時建立在 PRIMARY 檔案群組中，且該組態之後無法變更。 如果您快要用完了空間，可能會想要使用下列陳述式，來清除舊的查詢存放區資料。
ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;
或者，您也可能只想要清除特定查詢資料，因為它對於查詢最佳化和計劃分析來說較不相關，但也佔用一樣的空間。
在 Azure Synapse Analytics 中，無法清除查詢存放區。 過去 30 天內會自動保留資料。
刪除臨機操作查詢
這會從查詢存放區清除臨機和內部查詢，讓查詢存放區不會用盡空間，並移除我們真正需要追蹤的查詢。
SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track
DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
SELECT q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE q.is_internal_query = 1 -- is it an internal query then we dont care to keep track of it
OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
GROUP BY q.query_id
HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE()) -- if it has been more than 5 minutes since the adhoc query ran
ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
EXEC sp_query_store_remove_query @id;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;
您可以用不同的邏輯來定義自己的程序，以清除不再需要的資料。
以上範例使用 sp_query_store_remove_query 擴充預存程序，來移除不必要的資料。 您也可以：
- 使用
sp_query_store_reset_exec_stats來清除指定計劃的執行時間統計資料。 - 使用
sp_query_store_remove_plan移除單一方案。
效能稽核和疑難排解
如需使用 查詢存放區 深入瞭解效能微調的詳細資訊，請參閱使用 查詢存放區 微調效能。
其他效能主題：
另請參閱
- 查詢存放區預存程式 (Transact-SQL)
- 查詢存放區目錄檢視 (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- 即時查詢統計資料
- 活動監視器
- 查詢存放區如何收集資料
- 效能的監視與微調
- 效能監視及微調工具
- 使用含有記憶體內部 OLTP 的查詢存放區