Archive for February 7th, 2013

SQL Server 2012: Filestream Multiple Containers February 7th, 2013

Vinod Kumar

Filestreams feature was introduced in SQL Server 2008. SQL Server 2012 introduces new improvement where we can now add more than one container in same filegroup (containing filestream). Now that we can have more than one directory from operating system be part of same filegroup for filestream use, it allows us to improve storage (physical database) design for a database while not having to partition a table creatively. This can improve IO, which can be spread across multiple directories / volumes / physical devices.

First let us create our database with Filestream. Below we are creating a single Filestream filegroup at location c:\data\FS1.


Here is the great enhancement. Let us add a second location to the same filegroup.


We will next create a table with filestream data. This is enabled via Varchar(Max) datatype.


Final step is to add rows into the table. We will insert some 10k rows using a while loop.


Filestream file data distribution algorithm is mainly round robin but with a proportional fill element in an effort to try to parallelize the IO activity. What it means is when a new empty container is added, it may be favored more for INSERTs (while still avoiding all INSERTs to be targeted to same). Once new container catches up with the old ones in terms of size, INSERTs would again be fair across containers.

Fineprints with Filestream

  1. You can now run DBCC SHRINKFILE with EMPTYFILE option against a file indicating a container to migrate all the data from that file to other files (containers) within same filegroup.
  2. sp_filestream_force_garbage_collection: in SQL Server 2008 and SQL Server 2008 R2 you could not force the garbage collection of filestream. A new system stored procedure called "sp_filestream_force_garbage_collection" has been added in SQL Server 2012, which can now force the garbage collection to run from a user connection.
  3. This would be especially useful in scenarios where SHRINKFILE was done on a filestream container but not all files have been garbage collected so you cannot remove it. In such cases, you could run GC for filestream manually to clean it up and then remove the logical file representing this directory from database.
  4. sys.database_files catalog view has a column named size which in case of FILESTREAM files indicates the size of the folder.

Continue reading...