主页 / dba / 问题 / 315842
Accepted
lifeisajourney
Asked: 2022-08-20 11:36:05 +0800 CST

测试时使用缓存数据执行存储过程不佳

  • 772

我有一个存储过程，第一次运行大约需要 15 秒，后续运行需要 1 到 2 秒。如果我等待一个小时并再次运行它，那么它又需要 15 秒。

我猜它在随后的运行中使用缓冲池中的缓存数据，而第一次它必须将数据从磁盘加载到缓冲池。我正在尝试调整此存储过程，但在第一次运行后我无法测试我的更改，因为它只需要 1 到 2 秒。

我知道我可以使用该DBCC DROPCLEANBUFFERS命令来释放缓存并运行我的存储过程，但是我不允许在工作中清除缓存。我也试过WITH RECOMPILE了，但这只会创建一个新计划，但仍然使用缓存的数据。是否有另一种强制存储过程不使用缓存数据的方法？

sql-server optimization

2 个回答

  1. Best Answer

    否。保存 CPU 并等待实际执行计划中的统计信息，您会看到 15 秒的持续时间。然后尽量减少 CPU 和读取。如果您看到 X 读取 = 15 秒的 PAGEIOLATCH 等待，那么您可以合理估计减少读取的影响。

    重要的是发现并修复导致缓冲池流失的查询。您的查询可能至少部分归咎于您，但您需要找出为什么此查询的数据没有保留在缓存中。可能是其他查询，可能需要更多内存，或者更好的压缩，或者避免表扫描等。

    • 8

  2. 四个评论。首先，如果您必须这样做，请安排将数据库的备份恢复到可以使用 DBCC DROPCLEANBUFFERS 的测试系统上。

    其次，使用逻辑读取而不是物理读取。在大多数情况下，我不喜欢依赖物理读取和查询优化的持续时间。如果您专注于逻辑读取并减少它们，那么物理读取通常会紧随其后。在某些情况下，您确实必须读取大量数据，并且减少逻辑读取不是一种选择。有时肠道检查会有所帮助。如果您在 1000 页表上执行 10000 次逻辑页读取以获取 1 条记录，那是非常错误的。（见过这样的坏东西。）如果你有一份关于所有数据的报告，那么在一个 1000 页表上进行 1000 次逻辑读取是很棒的。

    第三，衡量绩效。您可以在用于测试查询的连接上使用以下 SET 语句。它将给出每个表使用的 CPU 时间和 IO 使用情况。这些 SET 只需要执行一次。它们保持活动状态，直到连接关闭或设置为 OFF。这对于 1 个或几个查询很好，但对于某些代码来说会非常嘈杂。

    SET STATICS IO ON
SET STATISTICS TIME ON

    它看起来像下面这样。

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 22693, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 51, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalnames'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 913 ms.

    如果您需要的只是总体性能的摘要，那么像流动这样的东西可能就足够了。需要 SQL Server 2014 或更高版本；否则，需要用有效的东西（例如，datediff）替换经过的时间。顺便说一句，request_id 包含在 MARS 启用的情况下。除非您想要一个很好的噪音示例，否则不要为此启用上述 SET 语句。（这里和上面的结果是针对不同的查询，不会匹配。）

    DECLARE @time int, @cpu int, @logical bigint, @reads bigint, @writes bigint

SELECT @time = -total_elapsed_time, @cpu = -cpu_time, @logical = -logical_reads, @reads = -reads, @writes = -writes
FROM sys.dm_exec_requests WITH (NOLOCK) WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()
    
    WAITFOR DELAY '00:00:01.234';-- code to measure - wait
    DECLARE @t1 int=0, @t2 float=0; while @t1<1000000 begin set @t2=@t2+.5*power(.5,@t1) set @t1+=1 end;-- code to measure - cpu
    SELECT TOP 1000000 t1.*, t2.name as [x] INTO temp_xxxx FROM [sys].[all_objects] t1 CROSS JOIN [sys].[all_columns] t2; SELECT COUNT(*) FROM temp_xxxx; DROP TABLE temp_xxxx;-- code to measure - logical reads and writes

SELECT @time += total_elapsed_time, @cpu += cpu_time, @logical += logical_reads, @reads += reads, @writes += writes
FROM sys.dm_exec_requests WITH (NOLOCK) WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()

RAISERROR('time %d ms, cpu %d ms, logical %I64d pages, reads %I64d pages, writes %I64d pages', 10, 1, @time, @cpu, @logical, @reads, @writes) WITH NOWAIT

    (1000000 rows affected)
(1 row affected)
time 3228 ms, cpu 1964 ms, logical 71094 pages, reads 56 pages, writes 18303 pages

    第四，如果代码已经优化，可能还有其他问题。如果你有 100GB 的活动数据和只有 8GB 的​​ RAM ......（过去 20 年，我记得有一个案例是硬件。查询也很糟糕，但是 vCPU 问题把它推到了边缘。一个性能问题可能是您的查询的优化问题。但是，这将是一个单独的主题。）

    • 2

相关问题