主页 / dba / 问题 / 115671
Accepted
Vaccano
Asked: 2015-09-22 09:26:11 +0800 CST

如何调试缓冲区问题？

  • 772

我的产品“Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)”显示出奇怪的缓冲区和页面预期寿命 (PLE) 症状。

我每分钟都在我的服务器上运行这个（以跟踪这个问题）：

SELECT @ple = CAST([cntr_value] AS VARCHAR(20))
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

SELECT @usedBufferPages = CAST(COUNT(*) /128 AS VARCHAR(20)) 
FROM sys.dm_os_buffer_descriptors

DECLARE @StartDate VARCHAR(8) = Convert(VARCHAR(8), GETDATE(), 14)
RAISERROR ('%s. PLE at %s and Used Buffers at %s at %s ', 0, 
            1,@runCountString ,@ple, @usedBufferPages, @StartDate) WITH NOWAIT

这是一些示例输出：

16. 858 的 PLE 和 7290 的已用缓冲区 09:51:42
17. 918 处的 PLE 和 7342 处的已用缓冲区 09:52:42
18. 978 处的 PLE 和 7408 处的已用缓冲区 09:53:43
19. PLE 为 1039，使用缓冲区为 7547，时间为 09:54:43
20. PLE 为 1100，使用缓冲区为 7697，时间为 09:55:44
21. 1160 处的 PLE 和 7901 处的已用缓冲区 09:56:45
22. 1221 处的 PLE 和 7961 处的已用缓冲区 09:57:46
23. 1282 处的 PLE 和 8012 处的已用缓冲区 09:58:46
24. 11 点的 PLE 和 313 点的已用缓冲区 09:59:46
25. PLE 在 31 和 Used Buffers 在 966 在 10:00:46
26. PLE 为 90，使用缓冲区为 1580，时间为 10:01:47
27. 151 处的 PLE 和 3072 处的已用缓冲区 10:02:47
28. 211 处的 PLE 和 3152 处的已用缓冲区 10:03:47
29. 271 处的 PLE 和 3729 处的已用缓冲区 10:04:47

在第 24 项，SQL Server 报告 PLE 从1,282 变为 11。SQL Server 还报告使用的缓冲区从8,012 变为 313。

首先，我寻找运行不佳的查询，并找到了一些已修复的查询（对问题没有影响）。但是，我没有发现任何与我遇到 PLE/缓冲区问题的时间相关的问题查询。此外，如果它是一个运行不佳的查询，那么我认为缓冲区将充满该查询的数据，而不是空的/丢失的/错误的。

接下来我认为发生这种情况时虚拟机的内存受到限制。但我问过我的系统管理员，他向我保证内存不是动态的，也不是以任何方式共享的。（它一直被分配什么，它就得到什么。）另外，我每 10 分钟运行一次这个脚本，当 PLE 报告少于 50 时：

  SELECT * FROM sys.dm_os_sys_memory

当 PLE/缓冲区高和低时，它会报告相同/相似的值。为了完整起见，下面是上面#24 之前和之后的值示例： 

total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_cache_kb kernel_paged_pool_kb kernel_nonpaged_pool_kb system_high_memory_signal_state system_low_memory_signal_state system_memory_state_desc
20970996 4758672 24378868 7929404 4844160 686076 182752 1 0 可用物理内存高
20970996 4743468 24378868 7892632 4845000 686580 182688 1 0 可用物理内存高

我检查了系统健康会话，但没有显示任何相关内容。（它所具有的只是模拟错误，它们的时间与 PLE/Buffers 显示问题的时间无关。

我已经跟踪了这种情况发生的频率，但我看不到模式或将其与任何工作或计划的活动联系起来。

下图显示了 21 小时内的 PLE 和缓冲区：

PLE 和缓冲器超过 21 小时

所以我很难过。我认为问题的核心是缓冲区而不是 PLE。（我认为 PLE 得到了低的错误报告，因为所有缓冲区都以某种方式消失了。）

但我想不出任何可能发生的方式。或者下一步该做什么。

我很乐意提供有关其他要检查的内容的建议或有关此问题可能是什么的建议。

评论中问题的更新：

那么，给服务器分配多少内存呢？VM 有 20 GB 的内存。
什么是最大服务器内存？ 

名称 值 value_in_use 描述
max server memory (MB) 13000 13000 服务器内存的最大大小 (MB)
min server memory (MB) 0 16 服务器内存的最小大小 (MB)

注意：我刚刚对此做了一些阅读，看来这些设置对我的服务器来说是错误的。

数据库有多大？该服务器上运行着两个事务数据库（我正在让服务器隔离它们。）它们的大小分别为 383 GB 和 378 GB。

该服务器上还运行着哪些其他应用程序和服务？该服务器托管我的应用程序的数据。没有其他东西打它。（我有一个用于报告等的复制操作数据存储。

什么是VM技术VM Ware。
此 VM 是否在仅托管具有类似资源分配的 VM 的主机上运行？我们公司有很多虚拟机。都是大小不一的。这是最大的之一。

您能否确认您的系统管理员告诉您的有关内存分配的内容，而不必相信他？ 我不能。我无权使用这些工具。

（根据我的经验，系统管理员会说很多话来推卸责任并责怪应用程序或其他任何人，如果这意味着他们不必做任何事情的话。） 我完全理解这种情绪。

我同意，这种模式看起来确实像是严重的内存压力。我希望找到一些东西来证明 SQL 感受到了内存压力。所以我可以将它发回给系统管理员进行更多研究。

等待时间统计

WaitType Wait_S Resource_S Signal_S WaitCount 百分比 AvgWait_S AvgRes_S AvgSig_S
---------------------- ---------- ---------- ------ --- ------------ ------------ ------------ -------- ------ ---
PAGEIOLATCH_SH 16250.10 16219.14 30.96 2171649 29.59 0.0075 0.0075 0.0000   
CXPACKET 14214.03 13238.56 975.47 1187935 25.88 0.0120 0.0111 0.0008   
PAGEIOLATCH_EX 6814.59 6806.21 8.38 638725 12.41 0.0107 0.0107 0.0000   
写日志 5157.42 4873.44 283.98 3588476 9.39 0.0014 0.0014 0.0001   
备份 2569.51 2538.12 31.39 1704119 4.68 0.0015 0.0015 0.0000   
LCK_M_IX 2477.15 2477.10 0.05 113 4.51 21.9217 21.9213 0.0004   
ASYNC_IO_COMPLETION 2079.99 2079.66 0.33 836 3.79 2.4880 2.4876 0.0004   
备份缓冲区 1807.75 1759.11 48.64 380189 3.29 0.0048 0.0046 0.0001   
IO_COMPLETION 986.23 985.84 0.39 116112 1.80 0.0085 0.0085 0.0000
sql-server sql-server-2012

4 个回答

  1. 您的缓冲池只有 13GB，您的数据库有 383 GB 和 378 GB，您将其归类为 OLTP - 小事务运行过于频繁。

    上述情况，如果我必须想象如下：

    在此处输入图像描述 （来源：谷歌相册）

    您必须了解 SQL Server 如何存储信息：

    SQL Server 在内存中将信息存储在称为内存缓存的结构中。缓存中的信息可以是数据、索引条目、编译过程计划和各种其他类型的 SQL Server 信息。为了避免重新创建信息，它会尽可能长时间地保留在内存缓存中，并且当它太旧而无法使用时，或者当新信息需要内存空间时，通常会从缓存中删除。删除旧信息的过程称为内存扫描。内存扫描是一项频繁的活动，但不是连续的。

    由于数据库大小庞大和缓冲池不足，您肯定会遇到内存不足的情况。参考 -例如如何确定理想内存？

    收集等待统计信息并检查因缓冲池内存浪费而引起的性能问题

    推荐：

    向服务器实例添加更多内存，并在具有足够内存的不同 VM 上分隔两个数据库。

    • 9

  2. 这里几乎没有什么可调试的——您需要添加内存，将您的数据库逻辑地拆分到多个虚拟机上，或者了解您必须对有限内存进行的改组将导致性能问题和不稳定的 PLE。试图将 800 GB 的数据放入 13 GB 的内存中就像试图将其存放在背包中一样。

    • 7
  3. Best Answer

    如This SE thread所讨论并由 OP 确认。

    该问题是由于 SQl Server 2012 中的错误引起的。此错误已在SQL Server 2012 SP1 CU4中修复。或者为了安全起见，我建议您应用SQL Server 2012 SP2而不是 CU4。

    根据 Microsoft Bug 修复详细信息

    您可能会在 SQL Server 2012 中遇到性能下降的情况。当您检查 SQL Server 性能监视器工具时，您会看到以下内容：

    • SQLServer:Buffer Manager\Page 预期寿命性能计数器值快速下降。发生此问题时，计数器接近 0。

    • 2

  4. 仔细查看正在执行的查询。仅在数据库上使用内存通常是一个过于粗略的指标，无法改进。假设您不能影响查询（黑盒应用程序），仍然值得了解是什么影响了内存使用。例如，一个批处理过程可能会通过查询一个大表上的所有数据来一次性使用所有的缓冲区空间。

    特别是寻找任何导致全表扫描的缺失索引——因为它们可以有效地刷新服务器上的缓存。

    SQL Server 有一套出色的分析工具，可以实时监控它，我怀疑一旦深入研究，您会发现一些突出的问题。

    并不是说我建议更改数据库模式，但要注意的一件事是过大的 varchar 字段——它们确实会占用大型数据库的缓存空间。

    • 0

相关问题