删除SQL Server中的LOB数据的性能

这个问题涉及到这个论坛主题 。

在我的工作站上运行SQL Server 2008 Developer Edition，并在Enterprise Edition双节点虚拟机群集中引用“alpha群集”。

使用varbinary（max）列删除行的时间与该列中数据的长度直接相关。 这听起来可能听起来很直观，但经过调查后，它与我对SQL Server如何实际删除一般行并处理这种数据的理解相冲突。

问题源于我们在.NET Web应用程序中看到的删除超时（> 30秒）问题，但是为了讨论的缘故，我简化了它。

• 如何更改SQL Server数据库的sorting规则？
• 改变MySQL中所有表中所有列的字符集和整理
• 如何validationSQLite db3文件是否有效/一致
• 有没有办法确定用于创buildMDF或BAK文件的SQL Server的版本？
• 我如何让SQL Server释放内存？

当logging被删除时，SQL Server将其标记为ghost，在事务提交之后的一段时间内被Ghost Cleanup Task清理掉（参见Paul Randal的博客 ）。 在testing中，分别在varbinary（max）列中删除三行，分别为16 KB，4 MB和50 MB数据，我在页面上看到这种情况发生在数据的行内部分以及事务login。

我觉得奇怪的是，在删除过程中，X锁被放置在所有的LOB数据页上，并且页在PFS中被释放。 我在事务日志以及sp_lock和dm_db_index_operational_stats DMV（ page_lock_count ）的结果中看到了这一点。

这会在我的工作站和我们的alpha群集上创build一个I / O瓶颈，如果这些页面不在缓冲区caching中的话。 实际上，来自同一个DMV的page_io_latch_wait_in_ms实际上是整个删除的持续时间， page_io_latch_wait_count与locking页面的数量相对应。 对于我的工作站上的50 MB文件，当空缓冲区caching（ checkpoint / dbcc dropcleanbuffers ）开始时，这将转换为3秒以上，并且我毫不怀疑，对于繁重的碎片和负载，时间会更长。

我试图确定，这不仅仅是在caching中分配占用空间的空间。 在执行删除操作之前，我从其他行读取了2 GB的数据，而不是分配给SQL Server进程的checkpoint方法。 不知道这是否是一个有效的testing，因为我不知道SQL Server如何洗牌数据。 我认为它会一直推出新的赞成新的。

此外，它甚至不修改页面。 这我可以看到与dm_os_buffer_descriptors 。 删除后的页面是干净的，而对于所有三个小的，中等的和大的删除，修改的页面的数量都小于20。 我还比较了DBCC PAGE输出以查找查找页面的样本，并且没有任何更改（仅从PFS中删除了ALLOCATED位）。 它只是释放他们。

为了进一步certificate页面查找/解除分配导致的问题，我尝试使用文件stream列而不是香草varbinary（最大）相同的testing。 无论LOB大小如何，删除都是恒定的。

所以，首先我的学术问题：

1. 为什么SQL Server需要查找所有的LOB数据页面以便Xlocking它们？ 这只是关于如何在内存中表示锁的细节（以某种方式与页面一起存储）？ 如果没有完全caching，这会使I / O影响严重依赖于数据大小。
2. 为什么Xlocking，只是为了释放它们？ 仅用行内部分locking索引叶是不够的，因为释放不需要自己修改页面？ 有没有其他的方式来获得锁的防止LOB数据？
3. 为什么要先分配这些页面呢，因为已经有了一个专门处理这类工作的背景任务？

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

• 有什么办法使删除操作不同？ 我的目标是无论大小如何，都会不断的删除文件，类似于文件stream，在这之后的任何清理都会在后台进行。 这是一个configuration的东西？ 我奇怪地存储的东西？

以下是如何重现所描述的testing（通过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 

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

 | 列types| 删除大小| 持续时间（毫秒）| 阅读| 写入|  CPU |
 -------------------------------------------------- ------------------
 |  VarBinary |  16 KB |  40 |  13 |  2 |  0 |
 |  VarBinary |  4 MB |  952 |  2318 |  2 |  0 |
 |  VarBinary |  50 MB |  2976 |  28594 |  1 |  62 |
 -------------------------------------------------- ------------------
 |  FileStream |  16 KB |  1 |  12 |  1 |  0 |
 |  FileStream |  4 MB |  0 |  9 |  0 |  0 |
 |  FileStream |  50 MB |  1 |  9 |  0 |  0 |

我们不一定只是使用文件stream，因为：

1. 我们的数据大小分布不保证。
2. 实际上，我们在很多块中添加数据，文件stream不支持部分更新。 我们需要围绕这个devise。

更新1

testing了一个理论，即将数据写入事务日志作为删除的一部分，这似乎并不是这种情况。 我testing了这个不正确吗？ 见下文。

 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 1651 | 171860 。

此外，如果将数据写入日志，我希望这些页面本身很脏。 只有解除分配似乎被logging，这与删除后的脏东西相匹配。

更新2

我得到了Paul Randal的回复。 他肯定了这样一个事实：它必须阅读所有的页面才能遍历树，并find哪些页面要释放，并且指出没有其他方法来查找哪些页面。 这是1和2的一半答案（虽然没有解释需要locking行外数据，但这是小土豆）。

问题3仍然是开放的：如果已经有一个后台任务来执行删除清理，为什么要先分配页面呢？

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