Monitoring Performance By Using the Query Store
查詢存放區功能為 DBA 提供查詢計劃選擇及效能的深入了解。 它能讓您快速找出因為查詢計劃中的變更所導致的效能差異,以簡化效能疑難排解。 該功能會自動擷取查詢、計劃及執行階段統計資料的記錄,並會保留這些記錄供您檢閱。 其會以時段來區分資料、供您查看資料庫使用模式,並了解何時在伺服器上發生查詢計劃變更。 使用 ALTER DATABASE SET 選項,可設定查詢存放區。
適用于:SQL Database V12 (取得) 。 |
重要
目前是預覽功能。 若要使用查詢存放區,您必須同意並了解運作查詢存放區受到授權合約預覽條款 (例如,Enterprise 合約、Microsoft Azure 合約或 Microsoft 線上訂用帳戶合約) 的限制,以及任何適用的 Microsoft Azure Preview 增補使用條款。
啟用查詢存放區
新的資料庫預設不會啟用查詢存放區。
使用 Management Studio 中的查詢存放區頁面
在 [物件總管] 中,以滑鼠右鍵按一下資料庫,然後按一下 [屬性] 。 (需要 SQL Server 2016 版 Management Studio.)
在 [資料庫屬性] 對話方塊中,選取 [查詢存放區] 頁面。
在 [啟用] 方塊中,選取 [True] 。
使用 Transact-SQL 陳述式
使用
ALTER DATABASE
陳述式可啟用查詢存放區。 例如:ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
如需與查詢存放區相關的更多語法選項,請參閱 ALTER DATABASE SET Options (Transact-SQL) 。
注意
您無法為 master 資料庫啟用查詢存放區。
查詢存放區中的資訊
SQL Server 中任何特定查詢的執行計畫通常會在一段時間後,因為統計資料的變更、結構描述變更、建立/刪除索引等數種不同原因而有所演變。儲存快取的查詢計劃之程序快取,只會儲存最新的執行計劃。 計劃也會因為記憶體不足的壓力,而從計劃快取中收回。 因此,因為執行計劃變更所造成的查詢效能低下,可能相形重要,而且可能需要許多時間才可解決。
因為查詢存放區會為每項查詢保留多個執行計劃,其可強制套用原則以指示查詢處理器要為查詢使用特定的執行計劃。 這也稱為強制執行計劃。 查詢存放區中的強制執行計劃,透過類似於 USE PLAN 查詢提示的機制加以提供,但它不需要在使用者應用程式中進行任何變更。 強制執行計劃可以解決在非常短的期間內,因計劃變更所導致的查詢效能低下。
使用查詢存放區功能的常見情況包括:
強制執行先前的查詢計劃,快速找出並修正計劃效能低下。 修正因為執行計劃變更而最近出現的效能低下。
判斷在指定的時段執行查詢的次數、協助 DBA 疑難排解資源的效能問題。
識別過去 x 小時內的前 n 項查詢 (依據執行時間、記憶體耗用量等等)。
稽核指定的查詢之查詢計劃記錄。
分析特定資料庫的資源 (CPU、I/O 及記憶體) 使用模式。
查詢存放區包含兩個存放區。 計劃存放區 可保存執行計劃資訊,而 執行階段統計資料存放區 則會保存執行統計資料資訊。 在計劃存放區中可為查詢儲存的不重複之計劃的數目,受限於 max_plans_per_query
組態選項。 若要增強效能,資訊會以非同步方式寫入兩個存放區。 若要將空間使用量降至最低,在執行階段統計資料存放區中的執行階段執行統計資料,會以固定的時段彙總。 對查詢存放區目錄檢視進行查詢時,會顯示這些存放區中的資訊。
下列查詢會傳回查詢存放區中查詢與計劃的相關資訊。
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id ;
使用迴歸查詢功能
啟用查詢存放區之後,請重新整理 [物件總管] 窗格的資料庫部分,以新增[查詢存放區] 區段。
選取 [回歸查詢],開啟 Management Studio 中的 [ 回歸查詢 ] 窗格。 [迴歸查詢] 窗格會顯示查詢存放區中的查詢與計劃。 頂端的下拉式清單方塊,可供您依據各種條件選取查詢。 選取計劃即可以圖形方式檢視查詢計劃。 提供有按鈕可供檢視來源查詢、強制執行或取消強制執行查詢計劃,以及重新整理顯示畫面。
若要強制執行計畫,請選取查詢和計畫,然後按一下 [ 強制計畫]。 您只能強制查詢計劃功能儲存的計畫,而且仍會保留在查詢計劃快取中。
設定選項
OPERATION_MODE可以READ_WRITE或READ_ONLY。
CLEANUP_POLICY 設定 STALE_QUERY_THRESHOLD_DAYS 引數,以指定要在查詢存放區中保留資料的天數。
DATA_FLUSH_INTERVAL_SECONDS 決定將寫入查詢存放區之資料保存到磁碟的頻率。 為了獲得最佳效能,查詢存放區所收集的資料會以非同步方式寫入磁碟。 此非同步傳輸發生的頻率,以 DATA_FLUSH_INTERVAL_SECONDS 設定。
MAX_SIZE_MB 設定查詢存放區的大小上限。 若查詢存放區中的資料到達了 MAX_SIZE_MB 限制,查詢存放區會自動從讀寫狀態變更為唯讀,並會停止收集新的資料。
INTERVAL_LENGTH_MINUTES 決定執行階段執行統計資料彙總至查詢存放區的時間間隔。 若要最佳化空間的使用量,在執行階段統計資料存放區中的執行階段執行統計資料,會以固定的時段彙總。 這個固定時段由 INTERVAL_LENGTH_MINUTES 所設定。
查詢 sys.database_query_store_options
檢視可判斷查詢存放區目前的選項。
如需使用 Transact-SQL 語句設定選項的詳細資訊,請參閱 選項管理。
相關檢視、函數與程序
您可以透過 Management Studio 或使用下列檢視和程式來檢視和管理查詢存放區。
查詢存放區目錄檢視
共有七個目錄檢視可提供有關查詢存放區的資訊。
查詢存放區預存程序
共有六個預存程序可設定查詢存放區。
金鑰使用方式案例
選項管理
本節提供管理查詢存放區功能本身的一些指導方針。
查詢存放區目前為作用中?
查詢存放區會將其資料儲存在使用者資料庫中,這也就是為什麼其有大小限制 (以 MAX_STORAGE_SIZE_MB
設定)。 若查詢存放區中的資料達到上限,查詢存放區會自動從讀寫狀態變更為唯讀,並會停止收集新的資料。
執行下列指令碼可判斷查詢存放區目前是否在作用中,以及其目前是否正在收集執行階段統計資料。
DECLARE @x nvarchar(100) = CAST(newid() AS nvarchar(100));
DECLARE @query nvarchar(max) =
N'IF EXISTS
(
SELECT *
FROM sys.query_store_query_text
WHERE query_sql_text LIKE ''%' + @x + N'%''
)
SELECT ''Query Store is active'' ;
ELSE SELECT ''Query Store is NOT active''' ;
EXEC sp_executesql @query;
取得查詢存放區選項
若要找出查詢存放區狀態的詳細資訊,請於使用者資料庫中執行下列作業。
SELECT * FROM sys.database_query_store_options;
設定查詢存放區間隔
您可以覆寫彙總查詢執行階段統計資料的間隔 (預設為 60 分鐘)。
USE master;
GO
ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);
請注意,不得使用任意值,您應使用下列其中之一:1、5、10、15、30 和 60。
透過 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
);
清理空間
查詢存放區內部資料表於建立資料庫時建立在 PRIMARY 檔案群組中,且該組態之後無法變更。 如果您快要用完了空間,可能會想要使用下列陳述式,來清除舊的查詢存放區資料。
ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;
或者,您也可能只想要清除特定查詢資料,因為它對於查詢最佳化和計劃分析來說較不相關,但也佔用一樣的空間。
刪除臨機操作查詢 如此做會刪除只執行一次且超過 24 小時的查詢。
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;
您可以用不同的邏輯來定義自己的程序,以清除不再重要的資料。
以上範例使用 sp_query_store_remove_query
擴充預存程序,來移除不必要的資料。 您也可以使用其他兩項程序。
sp_query_store_reset_exec_stats
- 清除指定計劃的執行時間統計資料。sp_query_store_remove_plan
- 移除單一方案。
效能稽核及疑難排解
因為查詢存放區會透過執行查詢來保留編譯記錄與執行階段計量,所以可以很輕易回答許多有關您工作負載的不同問題。
上次 在 資料庫上執行的 n 個查詢。
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;
每個查詢的執行次數。
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;
過去一小時內具有最長平均執行時間的查詢數目。
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 小時內具有最大平均實體 IO 讀取的查詢數目,以及對應的平均資料列計數和執行計數。
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;
具有多項計劃的查詢。 這些查詢特別有趣的原因是,它們都是因為計劃選擇變更而導致低下的對象。 下列查詢能找出這些查詢以及所有計劃:
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;
最近效能低下的查詢 (與時間中的不同點相較)。 下列查詢範例會傳回所有過去 48 小時內,因為計劃選擇變更而導致執行時間為兩倍的查詢。 查詢會並列比較所有執行階段。
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
即可。
最近效能低下的查詢 (比較最近的執行與記錄的執行)。 下一個查詢會依據執行時段,比較查詢的執行。 在此特別的範例中,查詢會比較最近期間內 (1 小時) 與記錄期間 (前一天) 的執行,並找出因 additional_duration_workload 所引發的項目。 此度量會計算最近的平均執行與記錄的平均執行之間的差,乘以最近執行的數目。 它實際上代表與記錄相較,最近的執行引發了多少額外的時間:
--- "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);
維護查詢效能穩定性
對於多次執行的查詢,您可能會注意到SQL Server使用不同的計畫,導致不同的資源使用率和持續時間。 您可利用查詢存放區,輕鬆偵測查詢效能何時低下,以及判斷在意的時段中的最佳計劃。 然後可以對未來的查詢強制執行該最佳計劃。
也可以為具有參數 (自動設定參數或手動設定參數) 的查詢,找出不一致的查詢效能。 您可以在不同的計劃間,找出適合所有或大部分參數值的良好且快速之計劃,並強制執行該計劃,為更多使用者案例留下可預測的效能。
為查詢強制執行計劃 (套用強制原則)。 若要為特定的查詢強制執行一項計劃,則每次查詢執行時,就會強制以該計劃執行。
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
使用 sp_query_store_force_plan
時,只能強制執行查詢存放區所記錄的計劃,做為該查詢的計劃。 換句話說,可用於查詢的計劃,是已經用於執行 Q1 的計劃 (查詢存放區當時在作用中)。
移除為查詢強制執行計劃。 若要再次依賴 SQL Server 查詢最佳化工具來計算最佳的查詢計劃,請使用 sp_query_store_unforce_plan
以取消為該查詢所選取的強制計劃。
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;