By Denny Lee, Ed Campbell

Introducing RBS

So what is RBS and why would I want to use it in order to help my PowerPivot for SharePoint farm?  Well, RBS in this particular case is Remote Blob Store which was introduced as part of one of the SQL Server 2008 Feature Packs.  As of this post, the current version can be found in the SQL Server 2008 R2 November CTP Feature Pack (search SQL Server 2008 R2 Remote Blob Store).  Simply put, remote blob store allows you to put blobs outside of the SQL Server database so you can reduce the performance and manageability impact of storing blobs directly within the database.  For more information about RBS, please refer to the documentation linked below

• Managing Unstructured Data with SQL Server 2008
• Remote BLOB Store Provider Library Implementation Specification 
• Install and configure Remote BLOB Storage (SharePoint Server 2010)

 

Why is RBS good for PowerPivot for SharePoint?

Specific to SharePoint, RBS can potentially improve upload performance (and potentially download too) because the documents you are storing in SharePoint are no longer physically stored as a blog in a varbinary field within a SQL Server database.  With RBS installed, instead of being stored in the database, the file is redirected to a RBS provider.  In the case of a RBS provider like SQL Server FILESTREAM, the file gets stored on to the file system directly.  Why this may be particularly beneficial for PowerPivot is because of the typically larger files that are uploaded to SharePoint.

So how good is it?  The results are preliminary as this is just a single user uploading files of various sizes (92MB to 1.6GB) to a SharePoint content database (in red is default dB storage, in green is using RBS).  As you can see from the figure below, there was significant performance gain which becomes even more apparent as files become larger (x-axis) when using RBS.  While the results are preliminary, the results were exciting enough that I had wanted to get a blog out to encourage others to test this out for themselves.  More information on the test scenario can be found below. 

Figure 1: Comparing Varbinary and RBS upload performance speeds

Test Scenario

To create a fair test between varbinary and RBS, we had setup a PowerPivot Single Server with a dedicated SQL Server similar to the the figure below.  To ensure reproducibility, we had setup two different Hyper-Vs – one for the SharePoint single server farm and one for the SharePoint databases.  Each Hyper-V had their own disk pass-through so that there were separate LUNs (with separate physical disks) to ensure there was not competing I/O.

Figure 2: PowerPivot for SharePoint Single Server Farm with Dedicated SQL Server

We ran the uploads multiple times and the above graph (Figure 1) comparing varbinary and RBS is an average of the different runs (the times were pretty consistent as well).  As well, we had reverted to the previous snapshots so its the exact same HVs and disks when doing a comparison between the two.

Discussion

Note, we are planning to run multi-user query performance tests in order to provide prescriptive guidance which will be in the form of a technical note or whitepaper on sqlcat.com.  But as noted above, the results were so eye-catching that I had wanted to get this out to everyone and encourage you to test this. 

Also, a quick tip – if you want to setup RBS, please ensure that you setup the timer jobs (as part of the install configuration).  The timer jobs are the one responsible for syncing SharePoint and the blob store (e.g. when you delete a file in SharePoint, it does not immediately delete the file from the blob store – the timer jobs perform this task).

Enjoy!