使用查詢存放區監視效能

適用于:SQL Server 2016 (13.x) 及更新版本 Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics (專用 SQL 集區)

查詢存放區功能可讓您深入瞭解SQL Server、Azure SQL資料庫、Azure SQL 受控執行個體和 Azure Synapse Analytics 的查詢計劃選擇和效能。 查詢存放區可協助您快速找出查詢計劃變更所造成的效能差異,藉此簡化效能疑難排解。 查詢存放區會自動擷取查詢、計劃和執行階段統計資料的歷程記錄,並將其保留供您檢閱。 其會以時段來區分資料、供您查看資料庫使用模式,並了解何時在伺服器上發生查詢計劃變更。 使用 [ALTER DATABASE SET] 選項可設定查詢存放區。

重要

如果您在 SQL Server 2016 (13.x) 中使用 查詢存放區,請儘快在KB 4340759中規劃安裝效能延展性修正程式。

啟用查詢存放區

  • 預設會針對新的Azure SQL資料庫和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 中的 [查詢存放區] 頁面

  1. 在物件總管中,以滑鼠右鍵按一下資料庫,然後選取 [屬性]。

    注意

    至少需要 16 版的 Management Studio。

  2. 在 [資料庫屬性] 對話方塊中,選取 [查詢存放區] 頁面。

  3. 在 [作業模式 (要求)] 方塊中,選取 [讀取寫入] 。

使用 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)

注意

無法為 mastertempdb 資料庫啟用查詢存放區。

重要

如需有關啟用查詢存放區並讓它根據您的工作負載調整的相關資訊,請參閱使用查詢存放區的最佳作法.

查詢存放區中的資訊

SQL Server中任何特定查詢的執行計畫通常會隨著時間演進,原因有一些不同的原因,例如統計資料變更、架構變更、建立/刪除索引等。 (儲存快取查詢計劃的程式快取) 只會儲存最新的執行計畫。 計劃也會因為記憶體不足的壓力,而從計劃快取中收回。 因此,因為執行計劃變更所造成的查詢效能低下,可能相形重要,而且可能需要許多時間才可解決。

因為查詢存放區會為每項查詢保留多個執行計劃,其可強制套用原則以指示查詢處理器要為查詢使用特定的執行計劃。 這也稱為強制執行計劃。 查詢存放區中的強制執行計劃,透過類似於 USE PLAN 查詢提示的機制加以提供,但它不需要在使用者應用程式中進行任何變更。 強制執行計劃可以解決在非常短的期間內,因計劃變更所導致的查詢效能低下。

注意

查詢存放區會收集 DML 陳述式 (例如 SELECT、INSERT、UPDATE、DELETE、MERGE 與 BULK INSERT) 的計畫。

查詢存放區根據預設不會收集原生編譯預存程序的資料。 請使用 sys.sp_xtp_control_query_exec_stats 來啟用收集原生編譯預存程序的資料。

等候統計資料 是另一種資訊來源,可協助針對 Database Engine 中的效能進行疑難排解。 等候統計資料長久以來只能在執行個體層級取得,難以回溯至特定查詢。 從 2017 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) ) 開始SQL Server (

次要複本功能的查詢存放區可在主要複本可用的次要複本工作負載上啟用相同的查詢存放區功能。 啟用次要複本的查詢存放區時,複本會將通常儲存在查詢存放區中的查詢執行資訊傳回主要複本。 然後,主要複本會將資料保存在其本身查詢存放區內的磁片。 基本上,主要複本與所有次要複本之間有一個查詢存放區共用。 查詢存放區存在於主要複本上,並將所有資料複本的資料儲存在一起。

如需次要複本查詢存放區的完整資訊,請參閱Always On可用性群組次要複本的查詢存放區

使用迴歸查詢功能

啟用查詢存放區之後,請重新整理 [物件總管] 窗格中的資料庫部分,以新增查詢存放區 區段。

SSMS 物件總管中查詢存放區報告樹狀結構的螢幕擷取畫面。

注意

對於 Azure Synapse Analytics,查詢存放區檢視可在 [物件總管] 窗格的資料庫部分的 [系統檢視] 下取得。

選取[回歸查詢],以在SQL Server Management Studio中開啟 [回歸查詢] 窗格。 [迴歸查詢] 窗格會顯示查詢存放區中的查詢與計劃。 頂端的下拉式方塊,可供依據各種準則來篩選查詢:持續時間 (毫秒) (預設)、CPU 時間 (毫秒)、邏輯讀取 (KB)、邏輯寫入 (KB)、實體讀取 (KB)、CLR 時間 (毫秒)、DOP、記憶體耗用量 (KB)、資料列計數、已使用的記錄記憶體 (KB)、已使用的暫存 DB 記憶體 (KB),以及等候時間 (毫秒)。

選取計劃即可以圖形方式檢視查詢計劃。 按鈕可用來檢視來源查詢、強制執行及取消強制執行查詢計畫、在格線和圖表格式之間切換、比較所選取的計畫 (如果選取了多個),以及重新整理顯示。

SSMS 物件總管中SQL Server回歸查詢報表的螢幕擷取畫面。

若要強制計畫,請選取查詢和計畫,然後選取 [ 強制計畫]。 您只可以強制執行由查詢計劃功能所儲存且仍保留在查詢計劃快取中的計劃。

尋找等候查詢

從 2017 SQL Server 2017 (14.x) 和 Azure SQL Database 開始,查詢存放區會提供每個查詢的等候統計資料。

在查詢存放區中,等候類型會合併到等候類別。 sys.query_store_wait_stats (Transact-SQL) 中提供等候類別與等候類型的對應。

選取[查詢等候統計資料] 以在 SQL Server Management Studio v18 或更高版本中開啟 [查詢等候統計資料] 窗格。 [查詢等候統計資料] 窗格會在查詢存放區中顯示包含前幾個等候類別的長條圖。 使用頂端的下拉式清單來選取等候時間彙總準則:平均值、最大值、最小值、標準差及總計 (預設)。

SSMS 物件總管中SQL Server查詢等候統計資料包表的螢幕擷取畫面。

選取列上的等候類別,並在選取的等候類別上顯示詳細資料檢視來選取等候類別。 這個新的長條圖包含提供給該等候類別的查詢。

SSMS 物件總管中SQL Server查詢等候統計資料詳細資料檢視的螢幕擷取畫面。

使用頂端的下拉式清單方塊,根據所選取等候類別的各種等候時間準則來篩選查詢:平均值、最大值、最小值、標準差及總計 (預設)。 選取計劃即可以圖形方式檢視查詢計劃。 提供有按鈕可供檢視來源查詢、強制執行或取消強制執行查詢計劃,以及重新整理顯示畫面。

等候類別會將不同的等候類型合併到本質類似的貯體中。 不同的等候類別需要不同的後續追蹤分析來解決問題,但相同類別的等候類型會導致非常類似的疑難排解體驗,並在等候頂端提供受影響的查詢,將會是成功完成大部分這類調查的遺漏部分。

以下範例示範如何在查詢存放區引入等候類別之前及之後深入了解您的工作負載:

過去的體驗 新的體驗 動作
每個資料庫的高 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 在極端的案例中,查詢存放區可能會因為內部錯誤而進入錯誤狀態。 從 2017 SQL Server 2017 (14.x) 開始,如果發生這種情況,查詢存放區可以在受影響的資料庫中執行 sp_query_store_consistency_check 預存程式來復原。 如需資料行描述中所述 actual_state_desc 的詳細資料,請參閱sys.database_query_store_options

查詢存放區維護

本文已擴充維護與管理查詢存放區的最佳做法和建議:管理查詢存放區的最佳做法

效能稽核和疑難排解

如需使用 查詢存放區 深入瞭解效能微調的詳細資訊,請參閱使用 查詢存放區 微調效能

其他效能主題:

另請參閱

下一步