主页 / dba / 问题 / 306979
Accepted
J.D.
Asked: 2022-02-04 11:15:04 +0800 CST

查询存储的总体资源消耗报告是告诉我我的数据库做得很糟糕还是只是数字被破坏了？

  • 772

最近，我们将其中一台生产服务器从 SQL Server 2016 升级到了 SQL Server 2019 (CU 15)。这对我来说是在我们的主应用程序数据库上启用查询存储的绝佳机会。它已经运行了几天，这就是总体资源消耗报告显示的内容：

整体资源消耗报告

在屏幕截图中，我挑选了一些看起来很疯狂的数字（在启用查询存储的第一天）并将它们标准化为更容易讨论的度量单位。一天之内，逻辑读取消耗约 183 TB 的数据，或内存消耗约 5 TB的数据，在此服务器上似乎几乎是不可能的。

这个数据库是数据库中的 John Smith，数据文件只有100 GB ，日志文件只有200 GB。最多可能有 100 个不同的用户全天连接到它，而且一天内不会创建大量交易。服务器本身只为其配置了32 GB的内存。要消耗5 TB的内存，分配的内存需要在一天内被填满150 次以上。

我能想到的唯一其他可能相关的信息是在升级之后，我们立即将此数据库的“兼容性级别”设置为 150（SQL Server 2019）并关闭“旧基数估计”设置。我知道这并不理想，最好在收集基线指标时让尘埃落定，但升级的部分原因是为了解决一些紧急的性能问题，这些设置组合实际上在我们的测试中最有效（而且似乎仍然工作得很好）。

我们之前遇到的一些性能问题是由于疯狂的基数估计，如果查询存储使用估计的数据点，那么我实际上可以看到这个报告的数字是相关的，但我不得不想象报告是使用实际数据点？虽然如果这是另一个迹象表明我的生产服务器/数据库在我持续征服以解决基数估计问题时配置方式存在根本性错误，那将会很有趣。

我读错了这些数字，是查询存储出错了，还是我的服务器吐司？

sql-server performance

2 个回答

  1. Best Answer

    我的工作站可以做大约 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,

    在我看来，这不是一个非常有用的指标。

    • 6

  2. 作为一般评论，我认为这里的任何人都不能100％自信地回答您的问题。我们没有基线。不过，这里有一些关于您可以做什么的想法：

    您是否在两台服务器上收集了任何指标？你能比较它们吗？

    对于所有查询，弹出窗口中的数字似乎是整个数据库的累积值。如果我的数学是正确的，平均查询持续时间是 400 毫秒。这可以和旧服务器相提并论吗？

    比较 CPU 使用率。一天是 5,5h 在新服务器上。假设 1 个逻辑 CPU，它的 CPU 平均使用率约为 25%。假设有 2 个逻辑 CPU，它是 12.5% 等。我们没有任何一个服务器的数字，但是知道两者的平均 CPU 使用率和逻辑处理器数量，您应该能够比较这两个服务器的 CPU 时间。假设有 2 个逻辑 CPU，我会说使用率很低。等待统计数据也相对较低（我假设屏幕截图中的等待时间表示等待）。因此，除非您有毒药等待，否则我认为您很好。

    一般来说，我会比较新旧服务器之间的等待统计信息。如果新服务器等待的时间更少，那么很可能你没问题。

    最后：用户是否抱怨应用程序的某些部分现在变慢了？这将强烈暗示某些查询已倒退。

    • 2

相关问题