在我的工作站和企业版双节点虚拟机群集上运行sql Server 2008 Developer Edition,其中我称之为“alpha群集”.
使用varbinary(max)列删除行所花费的时间与该列中数据的长度直接相关.这听起来可能听起来很直观,但在调查之后,它与我对sql Server实际上如何删除行的理解以及处理这类数据的理解相冲突.
问题源于我们在.NET Web应用程序中看到的删除超时(> 30秒)问题,但为了讨论起见,我对其进行了简化.
删除记录时,sql Server会在事务提交后将其标记为Ghost Cleanup Task清理的ghost(请参阅Paul Randal’s blog).在一个测试中,在varbinary(max)列中分别删除了包含16 KB,4 MB和50 MB数据的三行,我在页面上看到这种情况发生在数据的行内部分以及事务中登录.
对我来说似乎很奇怪的是,在删除期间,X锁被放置在所有LOB数据页上,并且页在PFS中被释放.我在事务日志中看到了这一点,以及sp_lock和dm_db_index_operational_stats DMV(page_lock_count)的结果.
如果这些页面不在缓冲区缓存中,这会在我的工作站和我们的alpha集群上产生I / O瓶颈.实际上,来自相同DMV的page_io_latch_wait_in_ms实际上是删除的整个持续时间,并且page_io_latch_wait_count对应于锁定页面的数量.对于我工作站上的50 MB文件,当使用空缓冲区缓存(checkpoint / dbcc dropcleanbuffers)启动时,这会转换为超过3秒,我毫不怀疑,对于繁重的碎片和负载,它会更长.
我试图确保它不仅仅占用缓存占用时间的空间.在执行删除而不是检查点方法之前,我读取了来自其他行的2 GB数据,这不仅仅是分配给sql Server进程.不确定这是否是一个有效的测试,因为我不知道sql Server如何改变数据.我认为它总是会推销旧的,转而支持新的.
此外,它甚至不修改页面.我可以用dm_os_buffer_descriptors看到这个.删除后页面是干净的,而对于所有三个小,中和大删除,修改页面的数量小于20.我还比较了DBCC PAGE的输出以查找查找页面的样本,并且没有任何更改(仅从PFS中删除了ALLOCATED位).它只是解除了他们的负担.
为了进一步证明页面查找/解除分配导致问题,我尝试使用文件流列而不是vanilla varbinary(max)进行相同的测试.无论LOB大小如何,删除都是恒定时间.
那么,首先是我的学术问题：
>为什么sql Server需要查找所有LOB数据页才能锁定它们？这只是一个关于锁如何在内存中表示的细节(以某种方式存储在页面中)？如果未完全缓存,这会使I / O影响强烈依赖于数据大小.
>为什么X完全锁定,只是为了解除分配？仅仅使用行内部分锁定索引叶片是不够的,因为解除分配不需要修改页面本身？有没有其他方法来获取锁保护的LOB数据？
>为什么要预先释放页面,因为已经有专门用于此类工作的后台任务？
也许更重要的是,我的实际问题：
>有没有办法让删除操作不同？我的目标是不管大小如何都是恒定的时间删除,类似于文件流,事后在后台进行任何清理.这是配置吗？我存储奇怪的东西吗？
以下是如何重现所描述的测试(通过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,4 * 1024 * 1024) AS varbinary(max)))
INSERT INTO [T] VALUES (@LargeID,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
以下是在我的工作站上分析删除的一些结果：
| Column Type | Delete Size | Duration (ms) | Reads | Writes | 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 |
我们不一定只使用文件流,因为：
>我们的数据大小分布不保证.
>实际上,我们在许多块中添加数据,而文件流不支持部分更新.我们需要围绕这个设计.
更新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.
此外,如果将数据写入日志,我希望页面本身是脏的.似乎只记录了deallocations,它与删除后的脏内容相匹配.
更新2
我确实收到了Paul Randal的回复.他肯定了这样一个事实：它必须读取所有页面才能遍历树并找到要解除分配的页面,并表示没有其他方法可以查找哪些页面.这是1& 1的一半答案. 2(虽然没有解释需要锁定行外数据,但这是小土豆).
问题3仍然是开放的：如果已经有后台任务要清除删除,为什么要预先释放页面？
当然,所有重要的问题是：有没有办法直接减轻(即不解决)这种依赖于大小的删除行为？我认为这将是一个更常见的问题,除非我们真的是唯一在sql Server中存储和删除50 MB行的人？是否有其他人通过某种形式的垃圾收集工作解决这个问题？
