☰

SQL Server 中删除 LOB 数据的性能

tag-icon • tag-icon sql-server
SQL Server 中删除 LOB 数据的性能

这个问题与此论坛主题。

在我的工作站上运行 SQL Server 2008 开发版，并在企业版双节点虚拟机群集上运行，我将其称为“alpha 群集”。

删除带有 varbinary(max) 列的行所需的时间与该列中数据的长度直接相关。乍一看，这可能听起来很直观，但经过调查，我发现这与我对 SQL Server 实际删除行和处理此类数据的理解相冲突。

该问题源于我们在 .NET Web 应用程序中看到的删除超时（> 30 秒）问题，但为了讨论起见，我对其进行了简化。

当一条记录被删除时，SQL Server 会将其标记为幽灵，以便在事务提交后稍后由幽灵清理任务进行清理（请参阅Paul Randal 的博客)。在测试删除 varbinary(max) 列中分别包含 16 KB、4 MB 和 50 MB 数据的三行时，我发现这种情况发生在包含行内数据的页面上，以及事务日志中。

我觉得奇怪的是，删除期间所有 LOB 数据页上都放置了 X 锁，而这些页在 PFS 中被释放。我在事务日志以及DMVsp_lock的结果中看到了这一点dm_db_index_operational_stats( page_lock_count)。

如果这些页面尚未位于缓冲区缓存中，这将在我的工作站和我们的 alpha 集群上造成 I/O 瓶颈。事实上，page_io_latch_wait_in_ms来自同一 DMV 的 几乎是删除的整个持续时间，并且page_io_latch_wait_count对应于锁定页面的数量。对于我工作站上的 50 MB 文件，当从空缓冲区缓存 ( checkpoint/ dbcc dropcleanbuffers) 启动时，这相当于超过 3 秒，而且我毫不怀疑，如果碎片严重且负载较低，时间会更长。

我试图确保它不只是在缓存中分配空间，从而占用了那段时间。在执行删除而不是方法之前，我从其他行读取了 2 GB 的数据checkpoint，这比分配给 SQL Server 进程的数据要多。不确定这是否是一个有效的测试，因为我不知道 SQL Server 如何对数据进行重新排列。我假设它总是会淘汰旧的，而采用新的。

此外，它甚至不会修改页面。我可以用 看到这一点dm_os_buffer_descriptors。删除后页面干净，而所有三个小型、中型和大型删除的修改页面数量均少于 20。我还比较了 的输出，DBCC PAGE以对查找的页面进行抽样，没有任何变化（只有位ALLOCATED从 PFS 中删除）。它只是释放它们。

为了进一步证明页面查找/释放是导致问题的原因，我尝试使用文件流列而不是 vanilla varbinary(max) 进行相同的测试。无论 LOB 大小如何，删除都是恒定时间。

首先，我的学术问题是：

  1. 为什么 SQL Server 需要查找所有 LOB 数据页才能对它们进行 X 锁定？这只是锁在内存中表示方式的一个细节（以某种方式与页面一起存储）吗？如果没有完全缓存，这会使 I/O 影响在很大程度上取决于数据大小。
  2. 为什么要使用 X 锁，只是为了释放它们？既然释放不需要修改页面本身，那么只锁定索引叶和行内部分还不够吗？还有其他方法可以获取锁所保护的 LOB 数据吗？
  3. 既然已经有一个专门用于此类工作的后台任务，为什么还要预先释放这些页面呢？

也许更重要的是我的实际问题是：

  • 有什么方法可以让删除操作有所不同吗？我的目标是无论大小如何，都以恒定时间进行删除，类似于文件流，任何清理都在事后在后台进行。这是配置问题吗？我存储东西的方式奇怪吗？

以下是如何重现所描述的测试（通过 SSMS 查询窗口执行）：

CREATE TABLE [T] (
    [ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [Data] [varbinary](max) NULL
)

DECLARE @SmallID uniqueidentifier
DECLARE @MediumID uniqueidentifier
DECLARE @LargeID uniqueidentifier

SELECT @SmallID = NEWID(), @MediumID = NEWID(), @LargeID = NEWID()
-- May want to keep these IDs somewhere so you can use them in the deletes without var declaration

INSERT INTO [T] VALUES (@SmallID, CAST(REPLICATE(CAST('a' AS varchar(max)), 16 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@MediumID, CAST(REPLICATE(CAST('a' AS varchar(max)), 4 * 1024 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@LargeID, CAST(REPLICATE(CAST('a' AS varchar(max)), 50 * 1024 * 1024) AS varbinary(max)))

-- Do this before test
CHECKPOINT
DBCC DROPCLEANBUFFERS
BEGIN TRAN

-- Do one of these deletes to measure results or profile
DELETE FROM [T] WHERE ID = @SmallID
DELETE FROM [T] WHERE ID = @MediumID
DELETE FROM [T] WHERE ID = @LargeID

-- Do this after test
ROLLBACK

以下是在我的工作站上分析删除操作的一些结果：

| 列类型 | 删除大小 | 持续时间（毫秒） | 读取 | 写入 | CPU |
--------------------------------------------------------------------------------
| 变量二进制 | 16 KB | 40 | 13 | 2 | 0 |
| 变量二进制 | 4 MB | 952 | 2318 | 2 | 0 |
| VarBinary | 50 MB | 2976 | 28594 | 1 | 62 |
--------------------------------------------------------------------------------
| 文件流 | 16 KB | 1 | 12 | 1 | 0 |
| 文件流 | 4 MB | 0 | 9 | 0 | 0 |
| 文件流 | 50 MB | 1 | 9 | 0 | 0 |

我们不能只使用 filestream，因为：

  1. 我们的数据大小分布不能保证这一点。
  2. 实际上，我们会分多块添加数据，而 filestream 不支持部分更新。我们需要围绕这个问题进行设计。

更新 1

测试了一种理论，即数据作为删除的一部分被写入事务日志，但事实似乎并非如此。我测试错了吗？见下文。

SELECT MAX([Current LSN]) FROM fn_dblog(NULL, NULL)
--0000002f:000001d9:0001

BEGIN TRAN
DELETE FROM [T] WHERE ID = @ID

SELECT
    SUM(
        DATALENGTH([RowLog Contents 0]) +
        DATALENGTH([RowLog Contents 1]) +
        DATALENGTH([RowLog Contents 3]) +
        DATALENGTH([RowLog Contents 4])
    ) [RowLog Contents Total],
    SUM(
        DATALENGTH([Log Record])
    ) [Log Record Total]
FROM fn_dblog(NULL, NULL)
WHERE [Current LSN] > '0000002f:000001d9:0001'

对于大小超过 5 MB 的文件，将返回1651 | 171860。

此外，如果将数据写入日志，我预计页面本身会很脏。似乎只有释放被记录下来，这与删除后的脏内容相符。

更新 2

我确实收到了 Paul Randal 的回复。他确认了必须读取所有页面才能遍历树并找到要释放的页面的事实，并表示没有其他方法可以查找哪些页面。这是对 1 和 2 的一半回答（虽然没有解释对行外数据进行锁定的必要性，但那只是小事一桩）。

问题 3 仍然悬而未决：如果已经有一个后台任务来清理删除的内容，为什么要预先释放页面？

当然，最重要的问题是：有没有办法直接缓解（即不解决）这种依赖于大小的删除行为？我认为这将是一个更常见的问题，除非我们真的是唯一在 SQL Server 中存储和删除 50 MB 行的人？其他人是否都使用某种形式的垃圾收集工作来解决这个问题？

答案1

我无法确切说明为什么删除 VARBINARY(MAX) 比删除文件流效率低得多，但如果您只是想避免 Web 应用程序在删除这些 LOBS 时超时，那么您可以考虑一个想法。您可以将 VARBINARY(MAX) 值存储在原始表（我们称之为 tblParent）引用的单独表（我们称之为 tblLOB）中。

从这里开始，当您删除一条记录时，您可以直接将其从父记录中删除，然后偶尔进行垃圾收集过程，清理 LOB 表中的记录。在此垃圾收集过程中可能会有额外的硬盘活动，但它至少与前端 Web 分开，并且可以在非高峰时间执行。

相关内容