查詢儲存需要很長時間才能載入持續時間

我正在執行 Query Store 以使數據庫性能更好。直到現在它一直執行良好。當我嘗試載入前一天的 Top 持續時間時，我花了 26 分鐘來載入螢幕。

我正在執行的 SQL 版本是：Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64) Oct 14 2021 00:47:52 版權所有 (C) 2017 Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2019 數據中心 10.0（內部版本 17763：）（管理程序）

查詢在QS中彈出：

/*
This query text was retrieved from showplan XML, and may be truncated.
*/

SELECT TOP (@results_row_count)
   p.query_id query_id,
   q.object_id object_id,
   ISNULL(OBJECT_NAME(q.object_id),'') object_name,
   qt.query_sql_text query_sql_text,
   ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
   SUM(rs.count_executions) count_executions,
   COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
   JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
   JOIN sys.query_store_query q ON q.query_id = p.query_id
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC

執行計劃顯示對像上的聚集索引掃描成本為 96% [plan_persist_runtime_stats]。

以下執行計劃來自不同的伺服器：

• 第一個執行計劃 （26 分鐘內完成）
• 第二個執行計劃 （15 分鐘後停止）
• 第三個執行計劃 （16 分鐘後停止）

我沒有通過腳本配置查詢儲存，只是通過屬性。但在我的設置之下：

ALTER DATABASE [<DATABASE>] SET QUERY_STORE (
   OPERATION_MODE              = READ_WRITE,                   
   CLEANUP_POLICY              = (STALE_QUERY_THRESHOLD_DAYS = 15), 
   DATA_FLUSH_INTERVAL_SECONDS = 900, 
   MAX_STORAGE_SIZE_MB         = 500,                             
   INTERVAL_LENGTH_MINUTES     = 60, 
   SIZE_BASED_CLEANUP_MODE     = AUTO,                             
   QUERY_CAPTURE_MODE          = AUTO,                             
   MAX_PLANS_PER_QUERY         = 200
   WAIT_STATS_CAPTURE_MODE     = OFF                                
   )

問題

如果它是 SQL Server 錯誤，是否可以在某個地方找到具有此行為的版本列表？

有誰知道修復是什麼？

核心問題是驅動 SSMS GUI 報告的 T-SQL 程式碼效率不高。不幸的是，微軟長期以來一直在 SSMS 的部分內容中包含低效的 T-SQL。查詢儲存數據模型和構成 DMV 的程式碼使問題更加複雜。許多地方都有不尋常的性能陷阱。例如，對於某些查詢儲存 DMV ，連接消除可能無法按預期工作。

我將通過對您擷取的查詢文本進行最小的努力來支持我的批評。照原樣，對我的一個生產數據庫執行原始程式碼需要 6 秒：

請注意，由於 runtime_stats_interval_id 的硬編碼過濾器值，以下重寫僅適用於我開發它的數據庫：

DECLARE
@results_row_count INT = 100,
@interval_end_time datetimeoffset = '2021-12-08 03:00:00',
@interval_start_time datetimeoffset = '2021-12-07 03:00:00';

SELECT *, qt.query_sql_text query_sql_text
FROM
(
      SELECT TOP (@results_row_count)
             p.query_id query_id,
             q.object_id object_id,
             ISNULL(OBJECT_NAME(q.object_id),'') object_name,
             q.query_text_id,
             ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
             SUM(rs.count_executions) count_executions,
             COUNT(distinct p.plan_id) num_plans
      FROM sys.query_store_runtime_stats rs
             JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
             JOIN sys.query_store_query q ON q.query_id = p.query_id   
      WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
      AND rs.runtime_stats_interval_id BETWEEN 11534 and 11558 -- code omitted to do this mapping but it isn't hard
      GROUP BY p.query_id, q.query_text_id, q.object_id
      --HAVING COUNT(distinct p.plan_id) >= 1
      ORDER BY total_duration DESC
) q1
INNER JOIN sys.query_store_query_text qt ON q1.query_text_id = qt.query_text_id;

新程式碼返回相同的結果，但在大約 600 毫秒內完成：

當然，以上內容對你沒有太大幫助。這只是一個跡象，表明 SSMS 存在缺陷，Microsoft 可以通過最終的程式碼更改來解決該缺陷。我可以考慮以下選項來解決您的問題：

1. 放棄使用某些 SSMS 查詢儲存 GUI 報告並改為執行自定義 T-SQL。社區的一些成員已經編寫了程式碼來做到這一點，並免費提供。Quickie Store就是這樣一個例子。
2. 這不太可能，但您的性能問題可能是由參數嗅探引起的。當您打開查詢儲存 GUI 時，它會立即在一個小時的日期範圍內執行報告。可能是 SQL Server 為該一小時範圍記憶體了一個查詢計劃，該計劃在 24 小時日期範圍內表現不佳。我以前曾在一天中某些時間非常繁忙的系統上看到過這種情況。您可以通過從記憶體中刪除您標識的查詢計劃來測試參數嗅探問題。
3. 這個站點上有一個答案，它通過使用計劃指南來解決 SSMS 查詢儲存 GUI 性能問題。
4. 如果您與 Microsoft 簽訂了支持契約，您可以嘗試向他們開具支持票。您也可以嘗試在社區回饋論壇上留下有關性能緩慢的回饋。

在不相關的說明中，我在您的問題中註意到您沒有啟用等待統計資訊收集。如果可能的話，我鼓勵你啟用它。我發現它是解決查詢超時原因的強大工具。不幸的是，它確實破壞了一些 GUI 報告。

如果您的 Query Store 配置得有點過大，那麼對 Query Store 執行任何查詢都會非常慢。

嘗試減小查詢儲存的大小：

ALTER DATABASE [<DATABASE>] SET QUERY_STORE = ON;
GO
ALTER DATABASE [<DATABASE>] SET QUERY_STORE (
   OPERATION_MODE              = READ_WRITE,                       -- READ_WRITE, READ_ONLY
   CLEANUP_POLICY              = (STALE_QUERY_THRESHOLD_DAYS = 3), -- 30 (def)
   DATA_FLUSH_INTERVAL_SECONDS = 600, 
   MAX_STORAGE_SIZE_MB         = 1024,                             -- Size of the Query Store
   INTERVAL_LENGTH_MINUTES     = 60, 
   SIZE_BASED_CLEANUP_MODE     = AUTO,                             -- AUTO (DEF), OFF
   QUERY_CAPTURE_MODE          = AUTO,                             -- AUTO (DEF 2019), ALL (DEF 2017), NONE, CUSTOM
   MAX_PLANS_PER_QUERY         = 4
   WAIT_STATS_CAPTURE_MODE     = ON                                -- ON, OFF (as of 2017)
   -- QUERY_CAPTURE_POLICY         = STALE_CAPTURE_POLICY_THRESHOLD = 1 DAY, EXECUTION_COUNT = 30, TOTAL_COMPILE_TIME_MS = 8, TOTAL_EXECUTIOIN_TIME_MS = 4 -- (as of 2019)
)

我們在 650 GB 大小的數據庫上遇到了查詢儲存問題，其中 10240 MB 的查詢儲存大小基本上會破壞數據庫。我們無法查詢 QS，它會充滿數千條 SQL 語句。

將 Query Store 的大小減小到 6 GB 以下是（在我們的例子中）無法查詢 Query Store 問題的解決方案。

參考閱讀

• 查詢儲存的最佳實踐(Microsoft | SQL Docs)
• ALTER DATABASE SET 選項 (Transact-SQL) (Microsoft | SQL Docs)

引用自：https://dba.stackexchange.com/questions/303563