RBS Filestream Provider Small Blob Optimization Settings

The RBS Filestream Provider has a number of configuration options designed to ensure optimal read and write performance across a range of blob sizes. The defaults were chosen after performance testing on various hardware and should be left as-is unless there are specific circumstances that require modification. The term in-band refers to T-SQL access through the SQL Server engine, and out-of-band refers to Win32 streaming access via the Filestream feature provided UNC path and standard streaming APIs.

Inline Storage (max_size_inline_blob)

For very small blobs, the overhead required to store these in Filestream files may be significant. The time required to create a new Filestream file or open a handle to a Filestream file can be substantially more than that required to store small blobs in internal SQL Server data structures in the user’s database. If the incoming blob is at or below the max_size_inline_blob size threshold it will be directly stored in the blob_data_inline varbinary(max) column of the internal RBS data table. During a read request the blob ID is checked and the read request dealt with by returning a T-SQL in-band stream. The max_size_inline_blob configuration value can be changed at any time for new blob inserts, however blobs that have already been stored inline will not be moved or migrated. The default value of the max_size_inline_blob parameter is set to 61140 bytes which was determined as the break-even point where creating a new file or opening a handle to a file outside of the database is more expensive than the slower data access when stored internally.

In-Band Read (max_size_inband_read) and Write (max_size_inband_write)

There is a performance penalty for setting up an out-of-band handle to access a Filestream file. If the blob to be stored or retrieved is small it can be faster to stream using in-band T-SQL access rather than spinning up an out-of-band stream even if they are stored in a Filestream column. This applies for both read and write scenarios. The default value of the max_size_inband_read and max_size_inband_write parameters are set to 1258290 bytes. As this value is used dynamically it can be changed in the blob store configuration and subsequent read and writes will honor the new value.

Out-of-band Read and Write

If the blob size is greater than the configuration setting values a Filestream share path is generated and the data is streamed in or out of the Filestream data file using Win32 streaming APIs. The streaming performance is much greater than going through T-SQL or directly using varbinary(max) however the initial cost to set up the streaming handle can be greater. Performance testing suggests the break-even point for SQL Server 2008 Filestream operations is around 1.2 MB, hence the default upper limits for the in-band read configuration settings.

- mike