管理查詢存放區的最佳做法

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

本文概述 SQL Server 查詢存放區與周圍功能的管理。

注意

根據預設,在 SQL Server 2022 (16.x) 中,查詢存放區現在會針對所有新建立的 SQL Server 資料庫啟用,以協助更妥善追蹤效能歷程記錄、針對查詢計劃相關問題進行疑難排解,以及啟用新的查詢處理器功能。

Azure SQL Database 中的查詢存放區預設值

本節描述 Azure SQL Database 中最佳的組態預設值,其設計目的是確保查詢存放區及相依功能可確實運作。 預設組態已針對持續收集資料最佳化,也就是在 OFF/READ_ONLY 狀態花費最少的時間。 如需所有可用查詢存放區選項的詳細資訊,請參閱 LTER DATABASE SET 選項 (Transact-SQL)

組態 描述 預設 註解
MAX_STORAGE_SIZE_MB 指定查詢存放區在客戶資料庫內佔用的資料空間限制 SQL Server 2019 (15.x) 之前為 100
從 SQL Server 2019 (15.x) 開始為 1000
對新資料庫強制執行
INTERVAL_LENGTH_MINUTES 定義彙總和保存查詢計畫所收集到的執行階段統計資料的時段大小。 對於此組態定義的一段時間,每個使用中的查詢計劃最多會有一個資料列 60 對新資料庫強制執行
STALE_QUERY_THRESHOLD_DAYS 以時間為基礎的清理原則,可控制保存執行階段統計資料和非使用中查詢的保留期限 30 對新資料庫和具有先前的預設值 (367) 的資料庫強制執行
SIZE_BASED_CLEANUP_MODE 指定當查詢存放區資料大小接近限制時,是否進行自動資料清理 AUTO 對所有資料庫強制執行
QUERY_CAPTURE_MODE 指定是否會追蹤所有查詢或只有查詢的子集 AUTO 對所有資料庫強制執行
DATA_FLUSH_INTERVAL_SECONDS 指定擷取的執行階段統計資料在排清到磁碟之前,保留在記憶體中的最大期間 900 對新資料庫強制執行

重要

這些預設值會自動套用至 Azure SQL Database 查詢存放區啟用的最終階段中。 在啟用後,Azure SQL Database 便不會變更客戶設定的組態值,除非這些組態值會對主要工作負載或查詢存放區的可靠作業造成負面影響。

注意

無法在 Azure SQL Database 單一資料庫與彈性集區中停用查詢存放區。 執行 ALTER DATABASE [database] SET QUERY_STORE = OFF 將會傳回警告 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

如果您想要繼續使用自訂設定,請使用 ALTER DATABASE 搭配查詢存放區選項 ,以將組態還原到先前的狀態。 請查看使用查詢存放區的最佳做法來了解如何選擇最佳的組態參數。

設定最佳查詢存放區擷取模式

在查詢存放區中保留最相關的資料。 下表描述每個查詢擷取模式的典型案例:

查詢存放區擷取模式 狀況
全部 根據所有查詢圖形、其執行頻率和其他統計資料,徹底分析您的工作負載。

識別您工作負載中的新查詢。

偵測是否使用特定查詢來識別使用者或自動參數化的機會。

請注意:這是 SQL Server 2016 (13.x) 與 SQL Server 2017 (14.x) 的預設擷取模式。
Auto 將注意力放在相關且可採取動作的查詢上。 例如,那些會定期執行或耗用大量資源的查詢。

請注意:在 SQL Server 2019 (15.x) 和和更新版本中,這是預設擷取模式。
None 您已擷取要在執行階段中監視的查詢集,並想排除其他查詢可能會造成的分心。

[無] 適合用於測試和效能評定環境。

「無」也適用於在出貨時已將查詢存放區組態設定為監視其應用程式工作負載的軟體廠商。

您應該謹慎使用 [無],以免錯失追蹤重要新查詢並將其最佳化的機會。 除非您有需要「無」的特定案例,否則請避免使用。
Custom SQL Server 2019 (15.x) 在 ALTER DATABASE ... SET QUERY_STORE 命令下引進了自訂擷取模式。 雖然預設為 [自動],但建議使用時,若有可能引進額外負荷查詢存放區的任何疑慮,資料庫管理員可以使用自訂擷取原則來進一步微調查詢存放區擷取行為。 如需詳細資訊與建議,請參閱本文稍後的自訂擷取原則。 如需此語法的詳細資訊,請參閱 ALTER DATABASE SET 選項

注意

當 [查詢存放區擷取模式] 設定為 [全部]、[自動] 或 [自訂] 時,系統一律會擷取資料指標、預存程序中的查詢,以及原生編譯的查詢。 若要擷取原生編譯查詢,請使用 sys.sp_xtp_control_query_exec_stats 來啟用收集每個查詢的統計資料。

在查詢存放區中保留最相關的資料

將查詢存放區設定為僅包含相關資料,以利持續執行、提供良好的疑難排解體驗,並對您的一般工作負載影響最小。

下表提供最佳作法:

最佳做法 設定
限制保留的歷程記錄資料。 設定以時間為基礎的原則,以啟用自動清除。
篩選掉不相關的查詢。 將 [查詢存放區擷取模式] 設定為 [自動]。
當到達大小上限時,刪除比較不相關的查詢。 啟用大小基礎的清除原則。

自訂擷取原則

啟用「自訂」查詢存放區擷取模式之後,即可在新的查詢存放區擷取原則設定下,使用查詢存放區額外組態來微調特定伺服器中的資料收集。

新的自訂設定可定義在內部擷取原則時間臨界值期間會發生什麼情況。 這是評估可設定條件的時間界限;如果任何條件成立的話,即可由查詢存放區來擷取查詢。

查詢存放區擷取模式會指定查詢存放區的查詢擷取原則。

  • 全部:擷取所有的查詢。 此選項是 SQL Server 2016 (13.x) 與 SQL Server 2017 (14.x) 中的預設值。
  • [自動]:會忽略不頻繁的查詢,以及包含無意義編譯和執行期間的查詢。 執行計數、編譯和執行階段持續時間的臨界值由系統內部決定。 從 SQL Server 2019 (15.x) 開始,這是預設選項。
  • :查詢存放區會停止擷取新的查詢。
  • [自訂]:允許額外控制及微調資料收集原則的功能。 新的自訂設定可定義在內部擷取原則時間臨界值期間會發生什麼情況。 這是評估可設定條件的時間界限;如果任何條件成立的話,即可由查詢存放區來擷取查詢。

應考慮調整適合您環境之自訂擷取原則的時機:

  • 資料庫非常大。
  • 資料庫有大量唯一的特定查詢。
  • 資料庫有特定的大小或成長限制。

使用最新版本的 SQL Server Management Studio (SSMS)

若要在 Management Studio 中檢視目前的設定:

  1. 在 SQL Server Management Studio [物件總管] 中,以滑鼠右鍵按一下資料庫。
  2. 選取 [屬性] 。
  3. 選取 [查詢存放區]。 在 [查詢存放區] 頁面上,確認 [Operation Mode (Requested)] 為[讀取寫入]。
  4. 將 [查詢存放區擷取模式] 設定為 [自訂]。
  5. 請注意,[查詢存放區擷取原則] 下的四個擷取原則欄位現在已啟用且可設定。

自訂擷取原則範例

下列範例會將 QUERY_CAPTURE_MODE 設定為 [自動],並設定自訂擷取模式。 下列每一項都會將自訂擷取原則設定為 SQL Server 2022 (16.x) 中的預設值。 請考慮調整這些值以減少擷取的查詢數目,進而減少查詢存放區的磁碟使用量。 建議以微調方式逐漸變更這些值。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

下列範例查詢會改變現有的查詢存放區,以使用自訂擷取原則來覆寫 EXECUTION_COUNTTOTAL_COMPILE_CPU_TIME_MS 的預設設定。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

查詢存放區大小上限

查詢存放區的預設大小上限為 1000 MB,從 SQL Server 2019 (15.x) 開始。 在舊版本中,預設值為 100 MB。 增加查詢存放區的大小上限適用於處理許多唯一查詢計畫的忙碌資料庫。 調整擷取原則 (請參閱上一節) 是限制查詢存放區磁碟大小,以及防止查詢存放區進入 READ_ONLY 模式的更重要考量。 由於查詢存放區會收集查詢、執行計劃和統計資料,因此它在資料庫中的大小會逐漸增加,直到達到此限制為止。 發生此情況時,查詢存放區會自動將作業模式變更為 READ_ONLY,並停止收集新的資料,這表示您的效能分析已不再正確。

  • 在 SQL Server 和 Azure SQL 受控執行個體中,系統不會嚴格強制執行 MAX_STORAGE_SIZE_MB 限制。
  • 在 Azure SQL 資料庫中,允許的 MAX_STORAGE_SIZE_MB 最大值為 10,240 MB。

只有當查詢存放區將資料寫入磁碟時,系統才會檢查儲存體大小。 這個間隔是由 DATA_FLUSH_INTERVAL_SECONDS 選項或 [Management Studio 查詢存放區] 對話方塊選項 [資料排清間隔] 所設定。

  • 間隔預設值為 900 秒 (15 分鐘)。
  • 如果查詢存放區違反儲存大小檢查之間的 MAX_STORAGE_SIZE_MB 限制,即會轉換為唯讀模式。
  • 如果已啟用 SIZE_BASED_CLEANUP_MODE,也會觸發強制執行 MAX_STORAGE_SIZE_MB 限制的清除機制。
    • 清出足夠空間之後,查詢存放區模式就會自動切換回 READ_WRITE 模式。

如需詳細資訊,請參閱 ALTER DATABASE SET 選項 MAX_STORAGE_SIZE_MB

資料排清間隔 (分鐘)

資料排清間隔會定義將收集的執行階段統計資料保存至磁碟之前的頻率。 在 SQL Server Management Studio 中,該值是以分鐘為單位,但在 Transact-SQL 中,則會以秒為單位來表示。 預設值是 15 分鐘 (900 秒)。

  • 增加資料排清間隔會降低整體查詢存放區儲存體 I/O 影響,但會導致儲存體 I/O 工作負載變得更「尖峰」,對磁碟使用率的影響較少但較重。 如果您的工作負載不會產生大量不同的查詢與計劃,或您可以在資料庫關閉之前承受較長的資料保存時間,請考慮使用較高的值。
  • 減少資料排清間隔會減少在關機、電源中斷或容錯移轉時遺失的查詢存放區資料量。 其也會以較少的資料更頻繁地寫入磁碟,讓儲存體 I/O 對查詢存放區的影響更無感。

注意

您可以使用追蹤旗標 7745,防止查詢存放區在發生容錯移轉或關機命令時將資料寫入磁碟。 如需詳細資訊,請參閱在任務關鍵性伺服器中使用查詢存放區

修改查詢存放區預設值

根據您的工作負載和效能疑難排解需求設定查詢存放區。 預設參數雖然是一個好的起點,但是您應該監視查詢存放區在一段時間內的運作狀況,並據以調整其設定。

檢視查詢存放區目前的設定

在 SQL Server Management Studio (SSMS) 或 T-SQL 中檢視目前的查詢存放區設定。

使用最新版本的 SQL Server Management Studio (SSMS)

若要在 Management Studio 中檢視目前的設定:

  1. 在 SQL Server Management Studio [物件總管] 中,以滑鼠右鍵按一下資料庫。
  2. 選取 [屬性] 。
  3. 選取 [查詢存放區]。

下列指令碼會設定新的 [大小上限 (MB)] 值:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

使用 SQL Server Management Studio 或 Transact-SQL 來設定資料排清間隔的不同值:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

[統計資料收集間隔]:定義所收集執行階段統計資料的資料粒度層級 (以分鐘表示)。 預設值是 60 分鐘。 如果您需要更精細的資料粒度或使用較短時間來偵測與解決問題,請考慮使用較低的值。 但請注意,它會直接影響查詢存放區資料的大小。 使用 SQL Server Management Studio 或 Transact-SQL 來設定統計資料收集間隔的不同值:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

[過時查詢臨界值 (天數)]:以時間為基礎的清除原則,可控制所保存執行階段統計資料和非作用中查詢的保留期限 (以天表示)。 根據預設,查詢存放區設定為保留資料 30 天,但對您的案例來說,可能並不需要這麼久。

請避免保留您不打算使用的歷史資料。 這個做法可降低變更為唯讀狀態的機率。 您也可以更輕鬆預測出查詢存放區資料大小及偵測/解決問題的時間。 使用 Management Studio 或下列指令碼來設定以時間為基礎的清除原則:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

[以大小為基準的清除模式]:可指定當查詢存放區的資料大小到達限制時是否自動清除資料。 請啟用 [以大小為基準的清除模式],以確保查詢存放區一律以讀寫模式執行並收集最新的資料。 在繁重的工作負載下,無法保證查詢存放區清除可持續將資料大小維持在限制內。 自動資料清除的進度可能會落後,並 (暫時) 切換為唯讀模式。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

[查詢存放區擷取模式]:可指定查詢存放區的查詢擷取原則。

  • 全部:擷取所有的查詢。 此選項是 SQL Server 2016 (13.x) 與 SQL Server 2017 (14.x) 中的預設值。
  • [自動]:會忽略不頻繁的查詢,以及包含無意義編譯和執行期間的查詢。 執行計數、編譯和執行階段持續時間的臨界值由系統內部決定。 從 SQL Server 2019 (15.x) 開始,這是預設選項。
  • :查詢存放區會停止擷取新的查詢。
  • [自訂]:允許額外控制及微調資料收集原則的功能。 新的自訂設定可定義在內部擷取原則時間臨界值期間會發生什麼情況。 這是評估可設定條件的時間界限;如果任何條件成立的話,即可由查詢存放區來擷取查詢。

重要

當 [查詢存放區擷取模式] 設定為 [全部]、[自動] 或 [自訂] 時,系統一律會擷取資料指標、預存程序中的查詢,以及原生編譯的查詢。 若要擷取原生編譯查詢,請使用 sys.sp_xtp_control_query_exec_stats 來啟用收集每個查詢的統計資料。

下列指令碼會將 QUERY_CAPTURE_MODE 設定為 AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

範例

下列範例會將 QUERY_CAPTURE_MODE 設定為 [自動],並在 SQL Server 2016 (13.x) 中設定其他建議選項:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

下列範例會將 QUERY_CAPTURE_MODE 設定為 [自動],並在 SQL Server 2017 (14.x) 中設定其他建議選項以包含等候統計資料:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

下列範例會將「自訂」擷取原則設定為 SQL Server 2019 (15.x) 預設值,而不是新的預設「自動」擷取模式。 如需自訂擷取原則選項與預設值的詳細資訊,請參閱 <query_capture_policy_option_list>

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

查詢存放區維護

本節提供管理查詢存放區功能本身的一些指導方針。

查詢存放區狀態

查詢存放區會將其資料儲存在使用者資料庫中,這也就是為什麼其有大小限制 (以 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

取得查詢存放區選項

若要找出查詢存放區狀態的詳細資訊,請於使用者資料庫中執行下列作業。

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 中,無法清除查詢存放區。 自動保留過去七天的資料。

刪除特定查詢

這會清除查詢存放區中的特定查詢與內部查詢,讓查詢存放區不會因空間不足而移除真正需要追蹤的查詢。

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 移除單一計劃。