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.

image

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

image

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

image

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

image

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.
Share this article

Tags: , , , , , , ,

This entry was posted on Thursday, February 7th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


4 Responses to “SQL Server 2012: Filestream Multiple Containers”

  1. Kanthi says:

    Can we do this somehow in SQL SERVER 2008 ? Please respond to my yahoo id.

    Thanks

  2. Aditya says:

    Hi vinod… Once again a great blogpost..Thank you very much… I also learnt that there was a subsequent improvement in fileshare level in 2012 versions of sql server and windows where we can create our databases and on new smb version which can be clustered(great mechanism of forwarding all the file open handles to fail over node once its failed over)…. It would be great to have a blog on this area :)

Leave a Reply



 

Email
Print