Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Filestream - Delete "speed"

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).

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)

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)

Thank you

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

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
like image 962
Benoittr Avatar asked Jan 28 '10 23:01

Benoittr


People also ask

What are advantages of Filestream?

FILESTREAM, in SQL Server, allows storing these large documents, images or files onto the file system itself. In FILESTREAM, we do not have a limit of storage up to 2 GB, unlike the BLOB data type. We can store large size documents as per the underlying file system limitation.

What is the difference between Filestream and FileTable?

FileStream and FileTable are features of SQL Server for storing unstructured data in SQL Server alongside other data. The FileStream feature stores unstructured data in the file system and keeps a pointer of the data in the database, whereas FileTable extends this feature even further allowing non-transactional access.

How does Filestream work in SQL Server?

FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.


1 Answers

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.

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

Thanks everyone for your inputs.

like image 148
Benoittr Avatar answered Sep 22 '22 18:09

Benoittr