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.

 

Configuration Item	Config Type	Extended Description	Default
max_size_inband_write	Extended	Maximum size in bytes for a blob to be written using in-band access only.	1258290 bytes
max_size_inband_read	Core	Maximum size in bytes for a blob to be returned using in-band access only rather than utilizing Filestream Win32 streaming access.	1258290 bytes
max_size_inline_blob	Extended	Maximum size in bytes for a blob to be stored directly in the varbinary(max) column rather than stored in the Filestream column. Typically this should be set to a very small value. While this value can be changed at any time, any existing blobs that have been stored inline will not be moved.	61140 bytes

- mike

Comments

• Anonymous
  January 28, 2012
  Thanks Mike. This information really helps :)