使用查詢存放區監視效能Monitoring performance by using the Query Store

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 查詢存放區功能為您提供關於查詢計劃選擇及效能的深入資訊。The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. 其可協助您您快速找出由於查詢計劃變更所導致的效能差異,以簡化效能疑難排解作業。It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. 查詢存放區會自動擷取查詢、計劃和執行階段統計資料的歷程記錄,並將其保留供您檢閱。Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. 其會以時段來區分資料、供您查看資料庫使用模式,並了解何時在伺服器上發生查詢計劃變更。It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. 使用 [ALTER DATABASE SET] 選項可設定查詢存放區。You can configure query store using the ALTER DATABASE SET option.

如需操作 Azure SQL DatabaseSQL Database 中查詢存放區的資訊,請參閱操作 Azure SQL Database 中的查詢存放區For information about operating the Query Store in Azure SQL DatabaseSQL Database, see Operating the Query Store in Azure SQL Database.

重要

若您只針對 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中的 Just-In-Time 負載見解使用查詢存放區,請計畫儘快安裝 KB 4340759 中的效能延展性修正程式。If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

啟用查詢存放區Enabling the Query Store

新的資料庫預設不會啟用查詢存放區。Query Store is not active for new databases by default.

使用 SQL Server Management StudioSQL Server Management Studio 中的 [查詢存放區] 頁面Use the Query Store Page in SQL Server Management StudioSQL Server Management Studio

  1. 在 [物件總管] 中,以滑鼠右鍵按一下資料庫,然後按一下 [屬性] 。In Object Explorer, right-click a database, and then click Properties.

    注意

    至少需要 Management StudioManagement Studio 16 版。Requires at least version 16 of Management StudioManagement Studio.

  2. 在 [資料庫屬性] 對話方塊中,選取 [查詢存放區] 頁面。In the Database Properties dialog box, select the Query Store page.

  3. 在 [作業模式 (要求)] 方塊中,選取 [讀取寫入] 。In the Operation Mode (Requested) box, select Read Write.

使用 Transact-SQL 陳述式Use Transact-SQL Statements

使用 ALTER DATABASE 陳述式可啟用查詢存放區。Use the ALTER DATABASE statement to enable the query store. 例如:For example:

ALTER DATABASE AdventureWorks2012 
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); 

如需和查詢存放區相關的更多語法選項,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL).

注意

您無法為 mastertempdb 資料庫啟用查詢存放區。Query Store cannot be enabled for the master or tempdb databases.

重要

如需有關啟用查詢存放區並讓它根據您的工作負載調整的相關資訊,請參閱使用查詢存放區的最佳作法.For information on enabling Query Store and keeping it adjusted to your workload, refer to Best Practice with the Query Store.

查詢存放區中的資訊Information in the Query Store

SQL ServerSQL Server 中任何特定查詢的執行計劃通常會在一段時間後,因為統計資料的變更、結構描述變更、建立/刪除索引等數種不同原因而有所演變。儲存快取的查詢計劃之程序快取,只會儲存最新的執行計劃。Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. 計劃也會因為記憶體不足的壓力,而從計劃快取中收回。Plans also get evicted from the plan cache due to memory pressure. 因此,因為執行計劃變更所造成的查詢效能低下,可能相形重要,而且可能需要許多時間才可解決。As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

因為查詢存放區會為每項查詢保留多個執行計劃,其可強制套用原則以指示查詢處理器要為查詢使用特定的執行計劃。Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. 這也稱為強制執行計劃。This is referred to as plan forcing. 查詢存放區中的強制執行計劃,透過類似於 USE PLAN 查詢提示的機制加以提供,但它不需要在使用者應用程式中進行任何變更。Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. 強制執行計劃可以解決在非常短的期間內,因計劃變更所導致的查詢效能低下。Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

注意

查詢存放區會收集 DML 陳述式 (例如 SELECT、INSERT、UPDATE、DELETE、MERGE 與 BULK INSERT) 的計畫。Query Store collects plans for DML Statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT.

注意

查詢存放區根據預設不會收集原生編譯預存程序的資料。Query Store does not collect data for natively compiled stored procedures by default. 請使用 sys.sp_xtp_control_query_exec_stats 來啟用收集原生編譯預存程序的資料。Use sys.sp_xtp_control_query_exec_stats to enable data collection for natively compiled stored procedures.

等候統計資料是另一種可協助您針對 Database EngineDatabase Engine 效能進行疑難排解的來源資訊。Wait stats are another source of information that helps to troubleshoot performance in the Database EngineDatabase Engine. 等候統計資料長久以來只能在執行個體層級取得,難以回溯至特定查詢。For a long time, wait statistics were available only on instance level, which made it hard to backtrack waits to a specific query. SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL Database 開始,查詢存放區會包含追蹤等候統計資料的維度。下列範例會啟用查詢存放區來收集等候統計資料。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

ALTER DATABASE AdventureWorks2012 
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

使用查詢存放區功能的常見情況包括:Common scenarios for using the Query Store feature are:

  • 強制執行先前的查詢計劃,快速找出並修正計劃效能低下。Quickly find and fix a plan performance regression by forcing the previous query plan. 修正因為執行計劃變更而最近出現的效能低下。Fix queries that have recently regressed in performance due to execution plan changes.
  • 判斷在指定的時段執行查詢的次數、協助 DBA 疑難排解資源的效能問題。Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • 識別過去 x 小時內的前 n 項查詢 (依據執行時間、記憶體耗用量等等)。Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • 稽核指定的查詢之查詢計劃記錄。Audit the history of query plans for a given query.
  • 分析特定資料庫的資源 (CPU、I/O 及記憶體) 使用模式。Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
  • 識別前 n 項等候資源的查詢。Identify top n queries that are waiting on resources.
  • 了解特定查詢或計劃的等候本質。Understand wait nature for a particular query or plan.

查詢存放區包含三個存放區:The Query Store contains three stores:

  • 計劃存放區以保存執行計劃資訊。a plan store for persisting the execution plan information.
  • 執行階段統計資料存放區以保存執行統計資料資訊。a runtime stats store for persisting the execution statistics information.
  • 等候統計資料存放區以保存等候統計資料資訊。a wait stats store for persisting wait statistics information.

計劃存放區中可為查詢儲存的不重複計劃數目,受限於 max_plans_per_query 組態選項。The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. 為了增強效能,資訊會以非同步方式寫入存放區。To enhance performance, the information is written to the stores asynchronously. 若要將空間使用量降至最低,在執行階段統計資料存放區中的執行階段執行統計資料,會以固定的時段彙總。To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 對查詢存放區目錄檢視進行查詢時,會顯示這些存放區中的資訊。The information in these stores is visible by querying the Query Store catalog views.

下列查詢會傳回查詢存放區中查詢與計劃的相關資訊。The following query returns information about queries and plans in the Query Store.

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 ;  

使用迴歸查詢功能Use the Regressed Queries feature

啟用查詢存放區之後,請重新整理 [物件總管] 窗格中的資料庫部分,以新增查詢存放區 區段。After enabling the Query Store, refresh the database portion of the Object Explorer pane to add the Query Store section.

SSMS 物件總管中的 SQL Server 2016 查詢存放區樹狀結構 SSMS 物件總管中的 SQL Server 2017 查詢存放區樹狀結構SQL Server 2016 Query Store tree in SSMS Object Explorer SQL Server 2017 Query Store tree in SSMS Object Explorer

選取 [迴歸查詢] ,開啟 中的 [迴歸查詢] SQL Server Management StudioSQL Server Management Studio窗格。Select Regressed Queries to open the Regressed Queries pane in SQL Server Management StudioSQL Server Management Studio. [迴歸查詢] 窗格會顯示查詢存放區中的查詢與計劃。The Regressed Queries pane shows you the queries and plans in the query store. 頂端的下拉式清單方塊,可供您依據各種條件篩選查詢:持續時間 (毫秒) (預設)、CPU 時間 (毫秒)、邏輯讀取 (KB)、邏輯寫入 (KB)、實體讀取 (KB)、CLR 時間 (毫秒)、DOP、記憶體耗用量 (KB)、資料列計數、已使用的記錄記憶體 (KB)、已使用的暫存 DB 記憶體 (KB),以及等候時間 (毫秒)。Use the drop down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).
選取計劃即可以圖形方式檢視查詢計劃。Select a plan to see the graphical query plan. 按鈕可用來檢視來源查詢、強制執行及取消強制執行查詢計畫、在格線和圖表格式之間切換、比較所選取的計畫 (如果選取了多個),以及重新整理顯示。Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

SSMS 物件總管中的 SQL Server 2016 迴歸查詢SQL Server 2016 Regressed Queries in SSMS Object Explorer

若要強制執行計劃,請選取查詢與計劃,然後按一下 [強制執行計劃] 。To force a plan, select a query and plan, and then click Force Plan. 您只可以強制執行由查詢計劃功能所儲存且仍保留在查詢計劃快取中的計劃。You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

尋找等候查詢Finding waiting queries

SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL Database 開始,可在查詢存放區中使用每個查詢經過一段時間的等候統計資料。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database, wait statistics per query over time are available in Query Store.

在查詢存放區中,等候類型會合併到等候類別In Query Store, wait types are combined into wait categories. sys.query_store_wait_stats & #40;TRANSACT-SQL & #41; 可將等候類別對應至等候類型。The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

選取 [查詢等候統計資料] ,以在 SQL Server Management StudioSQL Server Management Studio v18 或更新版本中開啟 [查詢等候統計資料] 窗格。Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management StudioSQL Server Management Studio v18 or higher. [查詢等候統計資料] 窗格會在查詢存放區中顯示包含前幾個等候類別的長條圖。The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. 使用頂端的下拉式清單來選取等候時間的彙總準則:平均值、最大值、最小值、標準差及總計 (預設值)。Use the drop down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

SSMS 物件總管中的 SQL Server 2017 查詢等候統計資料SQL Server 2017 Query Wait Statistics in SSMS Object Explorer

按一下長條圖來選取等候類別,隨即顯示有關所選取等候類別的詳細資料檢視。Select a wait category by clicking on the bar and a detail view on the selected wait category displays. 這個新的長條圖包含提供給該等候類別的查詢。This new bar chart contains the queries that contributed to that wait category.

SSMS 物件總管中的 SQL Server 2017 查詢等候統計資料詳細檢視SQL Server 2017 Query Wait Statistics detail view in SSMS Object Explorer

使用頂端的下拉式清單方塊,根據所選取等候類別的各種等候時間準則來篩選查詢:平均值、最大值、最小值、標準差及總計 (預設值)。Use the drop down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). 選取計劃即可以圖形方式檢視查詢計劃。Select a plan to see the graphical query plan. 提供有按鈕可供檢視來源查詢、強制執行或取消強制執行查詢計劃,以及重新整理顯示畫面。Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

等候類別會將不同的等候類型合併到本質類似的貯體中。Wait categories are combining different wait types into buckets similar by nature. 不同的等候類別需要不同的後續操作分析來解決問題,但同類別的等候類型會導致非常類似的疑難排解體驗,而提供受影響的前幾項查詢可能就是順利完成大部分這類調查所缺少的片段。Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

以下範例示範如何在查詢存放區引入等候類別之前及之後深入了解您的工作負載:Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

過去的體驗Previous experience 新的體驗New experience 動作Action
每個資料庫的高 RESOURCE_SEMAPHORE 等候High RESOURCE_SEMAPHORE waits per database 查詢存放區特定查詢的高記憶體等候High Memory waits in Query Store for specific queries 找出查詢存放區中記憶體耗用量名列前茅的查詢。Find the top memory consuming queries in Query Store. 這些查詢可能會進一步延遲受影響查詢的進度。These queries are probably delaying further progress of the affected queries. 請考慮對這些查詢或受影響的查詢使用 MAX_GRANT_PERCENT 查詢提示。Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
每個資料庫的高 LCK_M_X 等候High LCK_M_X waits per database 查詢存放區特定查詢的高鎖定等候High Lock waits in Query Store for specific queries 查看受影響查詢的查詢文字,並找出目標實體。Check the query texts for the affected queries and identify the target entities. 在查詢存放區中尋找修改相同項目的其他查詢,這些查詢經常執行且/或持續時間很長。Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. 找出這些查詢之後,請考慮變更應用程式邏輯,改善並行存取,或使用限制較少的隔離等級。After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
每個資料庫的高 PAGEIOLATCH_SH 等候High PAGEIOLATCH_SH waits per database 查詢存放區特定查詢的高緩衝區 IO 等候High Buffer IO waits in Query Store for specific queries 在查詢存放區中尋找實體讀取次數高的查詢。Find the queries with a high number of physical reads in Query Store. 如果它們符合高 IO 等候的查詢,請考慮引入基礎實體索引搜尋,以執行搜尋而不是掃描,進而將查詢的 IO 負擔降至最低。If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
每個資料庫的高 SOS_SCHEDULER_YIELD 等候High SOS_SCHEDULER_YIELD waits per database 查詢存放區特定查詢的高 CPU 等候High CPU waits in Query Store for specific queries 尋找查詢存放區中前幾項最耗 CPU 的查詢。Find the top CPU consuming queries in Query Store. 在它們中間找出高 CPU 趨勢與受影響查詢之高 CPU 等候相互關聯的查詢。Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. 專注於將那些查詢最佳化,可能存在計畫迴歸或缺少索引。Focus on optimizing those queries - there could be a plan regression, or perhaps a missing index.

組態選項Configuration Options

設定查詢存放區參數可使用下列選項。The following options are available to configure Query Store parameters.

OPERATION_MODEOPERATION_MODE
可以是 READ_WRITE (預設) 或 READ_ONLY。Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
設定 STALE_QUERY_THRESHOLD_DAYS 引數可指定在查詢存放區中保留資料的天數。Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the Query Store. 預設值是 30。The default value is 30. SQL DatabaseSQL Database Basic 版的預設值為 7 天。For SQL DatabaseSQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
決定將寫入查詢存放區之資料保存到磁碟的頻率。Determines the frequency at which data written to the Query Store is persisted to disk. 為了獲得最佳效能,查詢存放區所收集的資料會以非同步方式寫入磁碟。To optimize for performance, data collected by the query store is asynchronously written to the disk. 此非同步傳輸發生的頻率是透過 DATA_FLUSH_INTERVAL_SECONDS 所設定。The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. 預設值為 900 (15 分鐘)。The default value is 900 (15 min).

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
設定查詢存放區的大小上限。Configures the maximum size of the Query Store. 若查詢存放區中的資料達到 MAX_STORAGE_SIZE_MB 限制,查詢存放區會自動從讀寫狀態變更為唯讀狀態,並停止收集新的資料。If the data in the Query Store hits the MAX_STORAGE_SIZE_MB limit, the Query Store automatically changes the state from read-write to read-only and stops collecting new data. SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)) 的預設值為 100 MBThe default value is 100 MB for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)). SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始,預設值為 1 GBStarting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. SQL DatabaseSQL Database Premium 版的預設值為 1 GB,而 SQL DatabaseSQL Database Basic 版的預設值為 10 MBFor SQL DatabaseSQL Database Premium edition, default is 1 GB and for SQL DatabaseSQL Database Basic edition, default is 10 MB.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
決定執行階段執行統計資料彙總至查詢存放區的時間間隔。Determines the time interval at which runtime execution statistics data is aggregated into the Query Store. 若要最佳化空間的使用量,在執行階段統計資料存放區中的執行階段執行統計資料,會以固定的時段彙總。To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. 這個固定時段是透過 INTERVAL_LENGTH_MINUTES 所設定。This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 預設值是 60秒。The default value is 60.

SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE
控制當總資料量接近大小上限時,是否要自動啟用清除。Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. 可以是 AUTO (預設) 或 OFF。Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE
指定讓查詢存放區根據執行計數和資源耗用來擷取所有查詢或相關查詢,或是停止新增查詢而僅追蹤目前的查詢。Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. 可以是 ALL (擷取所有查詢)、AUTO (忽略不頻繁及具有無意義編譯和執行期間的查詢)、CUSTOM (使用者定義擷取原則) 或 NONE (停止擷取新查詢)。Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration), CUSTOM (user defined capture policy), or NONE (stop capturing new queries). SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)) 的預設值為 ALLThe default value is ALL for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)). SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始,預設值為 AUTOStarting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is AUTO. Azure SQL DatabaseAzure SQL Database 的預設值是 AUTOThe default value for Azure SQL DatabaseAzure SQL Database is AUTO.

MAX_PLANS_PER_QUERYMAX_PLANS_PER_QUERY
表示維護每個查詢計劃的大數目的整數。An integer representing the maximum number of plans maintained for each query. 預設值為 200The default value is 200.

WAIT_STATS_CAPTURE_MODEWAIT_STATS_CAPTURE_MODE
控制查詢存放區是否擷取等候統計資料資訊。Controls if Query Store captures wait statistics information. 可以是 OFF 或 ON (預設)。Can be OFF or ON (default).

查詢 sys.database_query_store_options 檢視以判斷查詢存放區的目前選項。Query the sys.database_query_store_options view to determine the current options of the Query Store. 如需值的詳細資訊,請參閱 sys.database_query_store_optionsFor more information about the values, see sys.database_query_store_options.

如需使用 Transact-SQLTransact-SQL 陳述式設定選項的詳細資訊,請參閱 選項管理For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

透過 Management StudioManagement Studio 或使用下列檢視與程序來檢視及管理查詢存放區。View and manage Query Store through Management StudioManagement Studio or by using the following views and procedures.

查詢存放區函式Query Store Functions

此函式可協助執行查詢存放區作業。Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

查詢存放區目錄檢視Query Store Catalog Views

目錄檢視會提供查詢存放區的相關資訊。Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings (Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query (Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval (Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

查詢存放區預存程序Query Store Stored Procedures

預存程序可設定查詢存放區。Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats (Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan (Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query (Transact-SQL)sp_query_store_remove_query (Transact-SQL)
sp_query_store_consistency_check (Transct-SQL)sp_query_store_consistency_check (Transct-SQL)

主要使用方式案例Key Usage Scenarios

選項管理Option Management

本節提供管理查詢存放區功能本身的一些指導方針。This section provides some guidelines on managing Query Store feature itself.

查詢存放區目前為作用中?Is Query Store currently active?

查詢存放區會將其資料儲存在使用者資料庫中,這也就是為什麼它有大小的限制 (以 MAX_STORAGE_SIZE_MB 設定)。Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). 若查詢存放區中的資料達到上限,查詢存放區會自動從讀寫狀態變更為唯讀,並會停止收集新的資料。If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

查詢 sys.database_query_store_options 可判斷查詢存放區目前是否在作用中,以及其目前是否正在收集執行階段統計資料。Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

SELECT actual_state, actual_state_desc, readonly_reason,   
    current_storage_size_mb, max_storage_size_mb  
FROM sys.database_query_store_options;  

查詢存放區的狀態取決於 actual_state 資料行。Query Store status is determined by actual_state column. 若與想要的狀態不同,readonly_reason 資料行可以提供更多的資訊。If it's different than the desired status, the readonly_reason column can give you more information.
當查詢存放區的大小超過配額時,此功能會切換為 readon_only 模式。When Query Store size exceeds the quota, the feature will switch to readon_only mode.

取得查詢存放區選項Get Query Store options

若要找出查詢存放區狀態的詳細資訊,請於使用者資料庫中執行下列作業。To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

設定查詢存放區間隔Setting Query Store interval

您可以覆寫彙總查詢執行階段統計資料的間隔 (預設為 60 分鐘)。You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>   
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);  

注意

INTERVAL_LENGTH_MINUTES 不允許任意值。Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. 您可以使用下列其中一項:1、5、10、15、30、 60 或 1440 分鐘。Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

新的間隔值是透過 sys.database_query_store_options 檢視而公開。New value for interval is exposed through sys.database_query_store_options view.

查詢存放區空間使用量Query Store space usage

若要檢查目前的查詢存放區的大小和限制,請在使用者資料庫中執行下列陳述式。To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb   
FROM sys.database_query_store_options;  

如果查詢存放區的儲存體已滿,請使用下列陳述式來擴充該儲存體。If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>   
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);  

設定查詢存放區選項Set Query Store options

使用單一 ALTER DATABASE 陳述式即可一次設定多個查詢存放區選項。You can set multiple Query Store options at once with a single ALTER DATABASE statement.

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)For the full list of configuration options, see ALTER DATABASE SET Options (Transact-SQL).

清理空間Cleaning up the space

查詢存放區內部資料表於建立資料庫時建立在 PRIMARY 檔案群組中,且該組態之後無法變更。Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. 如果您快要用完了空間,可能會想要使用下列陳述式,來清除舊的查詢存放區資料。If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;  

或者,您也可能只想要清除特定查詢資料,因為它對於查詢最佳化和計劃分析來說較不相關,但也佔用一樣的空間。Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

刪除臨機操作查詢Delete ad-hoc queries

這會刪除只執行一次且超過 24 小時的查詢。This deletes the queries that were only executed only once and that are more than 24 hours old.

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  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  
  
OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @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;  

您可以用不同的邏輯來定義自己的程序,以清除不再需要的資料。You can define your own procedure with different logic for clearing up data you no longer want.

以上範例使用 sp_query_store_remove_query 擴充預存程序,以移除不必要的資料。The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. 您也可以使用:You can also use:

  • sp_query_store_reset_exec_stats 為指定的計畫清除執行階段統計資料。sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
  • sp_query_store_remove_plan 以移除單一計畫。sp_query_store_remove_plan to remove a single plan.

效能稽核及疑難排解Performance Auditing and Troubleshooting

查詢存放區會保留整個查詢執行過程當中的編譯和執行階段度量歷程記錄,以讓您詢問關於工作負載的問題。Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

n 個在資料庫上執行的查詢?Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,   
    qt.query_text_id, p.plan_id, rs.last_execution_time  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
ORDER BY rs.last_execution_time DESC;  

每項查詢的執行次數?Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

前一個小時內,平均執行時間最長的查詢數目?The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    rs.last_execution_time   
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

過去 24 小時內,有相對應的平均資料列計數與執行計數,且具有最大平均實體 I/O 讀取的查詢數目?The number of queries that had the biggest average physical I/O reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id  
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

具有多項計畫的查詢?Queries with multiple plans? 這些查詢特別有趣的原因是,它們都是因為計劃選擇變更而導致低下的對象。These queries are especially interesting because they are candidates for regressions due to plan choice change. 下列查詢能找出這些查詢以及所有計劃:The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, q.query_id   
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id  
JOIN sys.query_store_plan AS p  
    ON p.query_id = q.query_id  
GROUP BY q.query_id  
HAVING COUNT(distinct plan_id) > 1  
)  
  
SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans AS qm  
JOIN sys.query_store_query AS q  
    ON qm.query_id = q.query_id  
JOIN sys.query_store_plan AS p  
    ON q.query_id = p.query_id  
JOIN sys.query_store_query_text qt   
    ON qt.query_text_id = q.query_text_id  
ORDER BY query_id, plan_id;  

最近效能低下的查詢 (與時間中的不同點相較)?Queries that recently regressed in performance (comparing different point in time)? 下列查詢範例會傳回所有過去 48 小時內,因為計劃選擇變更而導致執行時間為兩倍的查詢。The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. 查詢會並列比較所有執行階段。Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

如果您想要查看所有低下的效能 (不只因方案選擇變更的相關項目),只要從上一個查詢移除條件 AND p1.plan_id <> p2.plan_id 即可。If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

等候最久的查詢?Queries that are waiting the most? 此查詢會傳回等候最久的前 10 項查詢。This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

最近效能低下的查詢 (比較最近的執行與記錄的執行)?Queries that recently regressed in performance (comparing recent vs. history execution)? 下一個查詢會依據執行時段,比較查詢的執行。The next query compares query execution based periods of execution. 在此特別的範例中,查詢會比較最近期間內 (1 小時) 與歷程記錄期間 (前一天) 的執行,並找出因 additional_duration_workload所引發的項目。In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. 此度量會計算最近的平均執行與記錄的平均執行之間的差,乘以最近執行的數目。This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. 它實際上代表與記錄相較,最近的執行引發了多少額外的時間:It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  
  
--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  
  
WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time <= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time <= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time <= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time <= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

維護查詢效能穩定性Maintaining query performance stability

若是執行多次的查詢,您可會注意到 SQL ServerSQL Server 使用不同的計劃,而產生了不同的資源使用率與持續時間。For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. 您可利用查詢存放區,輕鬆偵測查詢效能何時低下,以及判斷在意時段中的最佳計劃。With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. 然後可以對未來的查詢強制執行該最佳計劃。You can then force that optimal plan for future query execution.

您也可以為具有參數 (自動設定參數或手動設定參數) 的查詢,找出不一致的查詢效能。You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). 您可以在不同的計劃間,找出適合所有或大部分參數值的良好且快速之計劃,並強制執行該計劃,為更多使用者案例留下可預測的效能。Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

為查詢強制執行計畫 (套用強制原則)Force a plan for a query (apply forcing policy)

針對特定查詢強制執行計畫時,SQL ServerSQL Server 會嘗試在最佳化工具中強制執行該計畫。When a plan is forced for a certain query, SQL ServerSQL Server tries to force the plan in the optimizer. 如果計劃強制失敗,會引發 XEvent,系統會指示最佳化工具以一般方式最佳化。If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

使用 sp_query_store_force_plan 時,只能強制執行查詢存放區所記錄的計劃,作為該查詢的計劃。When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. 換句話說,可用於查詢的計劃,是已經用於執行該查詢的計劃 (查詢存放區當時在作用中)。In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

強制支援向前快轉及靜態資料指標 Plan forcing support for fast forward and static cursors

SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3 開始,查詢存放區支援強制查詢執行計劃,以進行向前快轉及提供靜態 Transact-SQLTransact-SQL 和 API 資料指標。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3, the Query Store supports the ability to force query execution plans for fast forward and static Transact-SQLTransact-SQL and API cursors. 強制執行會透過 sp_query_store_force_planSQL Server Management StudioSQL Server Management Studio 查詢存放區報告支援。Forcing is supported via sp_query_store_force_plan or through SQL Server Management StudioSQL Server Management Studio Query Store reports.

針對查詢移除強制執行計畫Remove plan forcing for a query

若要再次依賴 SQL ServerSQL Server 查詢最佳化工具來計算最佳的查詢計劃,請使用 sp_query_store_unforce_plan 以取消為該查詢所選取的強制計劃。To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

另請參閱See Also

查詢存放區的最佳作法 Best Practice with the Query Store
使用含有記憶體內部 OLTP 的查詢存放區 Using the Query Store with In-Memory OLTP
查詢存放區使用案例 Query Store Usage Scenarios
查詢存放區如何收集資料 How Query Store Collects Data
查詢存放區預存程序 (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
查詢存放區目錄檢視 (Transact-SQL) Query Store Catalog Views (Transact-SQL)
效能的監視與微調 Monitor and Tune for Performance
效能監視及微調工具 Performance Monitoring and Tuning Tools
開啟活動監視器 (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
即時查詢統計資料 Live Query Statistics
活動監視器 Activity Monitor
sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL)
操作 Azure SQL Database 中的查詢存放區Operating the Query Store in Azure SQL Database