查詢儲存的總體資源消耗報告是告訴我我的數據庫做得很糟糕還是只是數字被破壞了？

最近，我們將其中一台生產伺服器從 SQL Server 2016 升級到了 SQL Server 2019 (CU 15)。這對我來說是在我們的主應用程序數據庫上啟用查詢儲存的絕佳機會。它已經執行了幾天，這就是總體資源消耗報告顯示的內容：

在螢幕截圖中，我挑選了一些看起來很瘋狂的數字（在啟用查詢儲存的第一天）並將它們標準化為更容易討論的度量單位。一天之內，邏輯讀取消耗約 183 TB 的數據，或記憶體消耗****約 5 TB的數據，在此伺服器上似乎幾乎是不可能的。

This database is the John Smith of databases, at only 100 GB big for the Data file and 200 GB for the Log file. At most, maybe 100 different users connect to it throughout the day, and there’s not a ton of transactions being created in a single day. The server itself only has 32 GB of Memory provisioned for it. For 5 TB of Memory to be consumed, the allocated Memory would need to be filled up over 150 times throughout the day.

我能想到的唯一其他可能相關的資訊是在升級之後，我們立即將此數據庫的“兼容性級別”設置為 150（SQL Server 2019）並關閉“舊基數估計”設置。我知道這並不理想，最好在收集基線指標時讓塵埃落定，但升級的部分原因是為了解決一些緊急的性能問題，這些設置組合實際上在我們的測試中最有效（而且似乎仍然工作得很好）。

我們之前遇到的一些性能問題是由於瘋狂的基數估計，如果查詢儲存使用估計的數據點，那麼我實際上可以看到這個報告的數字是相關的，但我不得不想像報告是使用實際數據點？儘管如果這是另一個跡象表明我的生產伺服器/數據庫在我持續征服以解決基數估計問題時配置方式存在根本性錯誤，那將會很有趣。

我讀錯了這些數字，是查詢儲存出錯了，還是我的伺服器吐司？

我的工作站可以做大約 100K-150K LIO/CPU 秒。邏輯 IO (LIO) 正在從頁面記憶體中讀取單個 8KB 頁面。即，當使用簡單的查詢計劃對記憶體表進行大並行掃描時，我會得到如下 IO 統計資訊：

表“frs_big”。掃描計數 9，邏輯讀取519631，物理讀取 0，頁面伺服器讀取 0，預讀讀取 0，頁面伺服器預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 頁面伺服器讀取 0，lob 讀取-預讀為 0，lob 頁面伺服器預讀為 0。

（受影響的 1 行）

SQL Server 執行時間：CPU 時間 = 5109毫秒，經過時間 = 655 毫秒。

使用 MAXDOP 1，同樣的掃描只需要 3625 CPU 毫秒。它執行 Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz。

在此期間，您有 22,902,891,616KB 的邏輯 IO，其中每個 LIO 為 8KB，因此22902891616/8LIO 和 20,393,171 毫秒的 CPU 時間。所以

with q as
(
 select 22902891616/8 lio, 20393171 cpu 
)
select 1000*lio/cpu lio_per_cpu_sec, lio/(24*60*60) lio_per_sec
from q 

或者

lio_per_cpu_sec                         lio_per_sec
--------------------------------------- ---------------------------------------
140,383                                 33,134

140,383 LIO/CPU 秒。因此，邏輯 IO 的數量大致對應於總 CPU 使用率，並且您在 24 小時內平均為 33,000 LIO/秒。

鑑於您使用所有更新的優化器行為升級到 2019，您可能有一些導致過度掃描的錯誤計劃，但伺服器的速度和適中的大小使您的記憶體命中率保持非常高，並保持感知性能可接受。

我不確定那個記憶體指標是什麼意思。這是報告背後的查詢：

exec sp_executesql N'WITH DateGenerator AS
(
SELECT CAST(@interval_start_time AS DATETIME) DatePlaceHolder
UNION ALL
SELECT  DATEADD(d, 1, DatePlaceHolder)
FROM    DateGenerator
WHERE   DATEADD(d, 1, DatePlaceHolder) < @interval_end_time
), WaitStats AS
(
SELECT
   ROUND(CONVERT(float, SUM(ws.total_query_wait_time_ms))*1,2) total_query_wait_time
FROM sys.query_store_wait_stats ws
   JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE NOT (itvl.start_time > @interval_end_time OR itvl.end_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, itvl.end_time)
),
UnionAll AS
(
SELECT
   CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
   ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
   ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
   ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
   ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
   ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
   ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
   ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
   ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) as total_log_bytes_used,
   ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) as total_tempdb_space_used,
   DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
   DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
)
SELECT 
   total_count_executions,
   total_duration,
   total_cpu_time,
   total_logical_io_reads,
   total_logical_io_writes,
   total_physical_io_reads,
   total_clr_time,
   total_dop,
   total_query_max_used_memory,
   total_rowcount,
   total_log_bytes_used,
   total_tempdb_space_used,
   total_query_wait_time,
   SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) , SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time))
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll , WaitStats
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@interval_start_time='2022-01-03 15:57:04.2571919 -06:00',@interval_end_time='2022-02-03 15:57:04.2571919 -06:00'

和

ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,

在我看來，這不是一個非常有用的指標。

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