sql-server - 包含持续增长的 TEXT 列的表

Question

我们在生产系统中有一个表(由于遗留原因)正在运行 SQL 2005 (9.0.5266)，并包含一个 TEXT 列(以及其他一些不同数据类型的列)。

突然之间(从一周前开始)，我们注意到这个表的大小每天以 10-15GB 的速度线性增加(而之前它始终保持恒定大小)。该表是消息传递系统的队列，因此其中的数据每隔几秒就会完全刷新一次。任何时候都可能有 0 到大约 1000 行，但随着消息的插入和发送(此时它们被删除)，它会快速波动。

我们找不到增长开始当天发生的任何变化 - 因此现阶段没有发现明显的潜在原因。

一个“明显”的罪魁祸首是 TEXT 列，因此我们检查了现在是否存储了任何大量值，但是(使用 DATALENGTH)我们发现上面没有大约 32k 的单行。我们运行了 CHECKDB，更新了空间使用情况，重建了所有索引等 - 没有任何措施减少大小(并且 CHECKDB 没有显示任何错误)。

我们查询了 sys.allocation_units，大小的增加肯定是 LOB_DATA(这显示total_pages 和used_pa​​ges 以恒定速率一起增加)。

为了减少数据库大小，昨晚我们简单地在相关表旁边创建了一个新表(幸运的是应用程序通过 View 引用了该表)，删除了旧表，并重命名了新表。我们昨晚离开了，因为我们已经缓解了空间问题，并且我们有一个可疑 table 的备份以便今天进一步调查，这让我们感到安慰。然而，今天早上表大小已经达到 14GB(并且还在不断增长)，而表中通常只有约 500 行，并且 MAX(DATALENGTH(text_column)) 仅显示大约 35k。

对于可能导致这种“失控”增长的原因有什么想法，或者我们可以尝试或查询其他任何内容来获取有关到底是什么在使用该空间的更多信息？

干杯，戴夫

Answer 1

这是 dealing with queues 中的普遍问题。链接的文章讨论了 Service Broker 队列，但对于用作队列的普通表来说，问题是相同的。如果您有一个拥有大量资源(CPU、内存、磁盘 IO)的繁忙系统，并且您将该系统上的队列推至高吞吐量，那么这些资源的很大一部分将用于处理两个操作:入队(即 INSERT )和出列(即删除)。然而，记录的完整生命周期需要三个操作:INSERT、DELETE和ghost purge。它们在 CPU/内存/磁盘 IO 需求方面的成本大致相同，因此，如果您将该队列用于 90% 的系统资源，那么您应该为每个队列分配 30% 的资源。但只有前两个是在您的控制之下(即在用户 session 中运行的显式语句)。第三个是ghost purge，是一个由SQL Server控制的后台进程，不可能允许ghost清理进程消耗30%的资源。这是一个基本问题，如果您将踏板踩到金属上足够长的时间，您的*就会踩到它。一旦幽灵记录累积并超过系统/工作负载特定阈值，性能将迅速下降，症状将急剧恶化，导致性能恶化(形成负反馈循环)。

幸运的是，由于您不使用 Service Broker 队列，而是使用实际表作为队列，因此您可以使用一些更好的工具，例如 ALTER TABLE REORGANIZE 和 ALTER TABLE REBUILD 。到目前为止，最好的解决方案是在线索引/表重建。 SQL Server 2012 支持online operations on tables containing BLOBs你可以利用这一点。当然，您必须摆脱已弃用的过时的 TEXT 类型并使用 VARCHAR(MAX)，但这是不言而喻的。

附注:

If you have pages with nothing but ghost records on them, then you will not read those pages again and they won't get marked for cleanup

这是不正确的。只有重影的页面将被扫描检测到并清除。正如我所说，问题不在于检测，而在于资源。如果你足够插入你的系统，你就会跑在幽灵清理之前，而他永远不会追上。