SQL Server文件流-删除“速度" [英] SQL Server Filestream - Delete "speed"

问题描述

我第一次使用文件流数据类型(SQL Server 2008)，并且在执行一些快速插入/删除操作时遇到了问题.基本上，即使我手动调用垃圾收集器，从文件系统中实际删除文件的速度也比插入/删除速度慢(据我所知，CHECKPOINT应该调用垃圾收集器).

I'm working with the filestream datatype for the first time (SQL Server 2008) and I'm running into issues when I am doing some fast insert/deletes. Basically the speed at which the files are actually removed from the FileSystem is way slower then the insert/delete speed even if I invoke the garbage collector manually (As far as I know the CHECKPOINT is supposed to invoke the garbage collector).

下面的代码说明了问题-执行大约需要30秒，但是您必须等待几分钟，才能从文件系统中删除最后一个文件(当我查找C:\ FSTest \ Files文件夹时)

The code below illustrate the problem - It takes roughly 30 seconds to executes, but you have to wait quite a few minutes for the last file to be deleted from the filesystem (When I look up the C:\FSTest\Files folder)

有什么方法可以加快垃圾收集器的速度吗? (这似乎大约每10秒删除20个文件-这使我相信，如果我每秒存储/删除多于2条记录，最终我将最终填充硬盘驱动器)

Is there any way to speed up the garbage collector? (It seems to roughly deletes 20 files every 10 seconds - which makes me believe that if I store/delete more then 2 records per second I will eventually end up filling the hard drive)

谢谢

CREATE DATABASE FSTest ON PRIMARY
    (NAME = FSTest_data, FILENAME = N'C:\FSTest\FSTest_data.mdf'),
FILEGROUP FSTestFileGroup CONTAINS FILESTREAM
    (NAME = FSTestFiles,FILENAME = N'C:\FSTest\Files')
LOG ON 
    (NAME = 'FSTest_log', FILENAME = N'C:\FSTest\FSTest_log.ldf');
GO

USE FSTest;
GO

CREATE TABLE FSTest (
    Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    Name VARCHAR (25),
    Data VARBINARY(MAX) FILESTREAM);
GO

ALTER DATABASE FSTest SET RECOVERY SIMPLE;
GO

SET NOCOUNT ON
DECLARE @test int
SET @test=0
WHILE @test<1000 BEGIN
    INSERT INTO FSTest(Name,Data) VALUES('test',CAST('abc' AS VARBINARY(MAX)))
    DELETE FROM FSTest WHERE Name='test'
    CHECKPOINT
    SET @test = @test+1
END

更新:

我尝试了更长的时间，插入/删除速度更接近我的需求，并且执行30分钟后，仍然可以观察到相同的情况:文件创建得比文件删除快得多.

Update:

I tried the same for a longer period of time with a insert/delete speed closer to my needs and after 30 minutes of execution the same situation is observable: Files are created way faster then they get deleted.

SET NOCOUNT ON
DECLARE @test int
SET @test=0
WHILE @test<100000 BEGIN
    INSERT INTO FSTest(Name,Data) VALUES('test',CAST('abc' AS VARBINARY(MAX)))
    DELETE FROM FSTest WHERE Name='test'
    WAITFOR DELAY '00:00:00:200'
    CHECKPOINT
    SET @test = @test+1
END

推荐答案

经过更多研究(并感谢Paul Randal的博客-有关文件流和垃圾收集的许多非常详细的信息)，一旦删除了行并设置了检查点，执行后，文件被放入系统表(Tombstone表)中，然后每10秒运行一个进程(Ghost Cleanup)，并从该表中删除一些项(准确地说是20项).因此，基本上，我们每秒只能进行2次删除操作，而且似乎还没有办法更改此行为.

After some more research (and thanks to Paul Randal's blog - lots of very detailed information surrounding filestream and garbage collection), once the rows are deleted and a checkpoint is executed, the files are put in a system table (Tombstone table), then every 10 seconds a process (Ghost Cleanup) runs and remove some items from that table (20 to be exact). So basically we are limited to 2 deletes/seconds and there seems to be no way (yet) to change this behavior.

由于我每秒要进行4次删除操作，因此我需要找到文件流的替代方法.

Since I have a sustained 4 deletes per seconds I will need to find an alternative to filestream.

感谢大家的投入.

这篇关于SQL Server文件流-删除“速度"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！