Beware the BLOB taking over your SQL - the 1st Post of the Top 5 Performance Killers in #SharePoint Storage! #qSharePoint

Welcome to my first post in a series of about the Top 5 Performance Killers in SharePoint Storage. With SharePoint exploding across organizations, many of these same organizations are going to suddenly feel the hit all that new user content stored in SharePoint has on SharePoint performance.

 

Exuberant use will increase the data stored in SharePoint, including large files and multiple versions of files. This isn’t a bad thing; in fact, it usually indicates good adoption and effective collaboration. But this explosion of the content database can cause an outcry from two quarters: end users complain about SharePoint’s slow performance, and SQL DBAs object that SharePoint is taking up too much expensive server space and processing power.

 

SharePoint Storage Performance Killer #1:

BLOBs, BLOBs and more BLOBs: How Unstructured Data is Taking Over SQL

 

Consider the core document types you store in SharePoint: PDFs, Word and PowerPoint files, large Excel spreadsheets. A document in any of these common file types is usually well over a megabyte, which means it is saved in SQL Server as a BLOB (Binary Large OBject). Having many BLOBs in SQL Server causes several issues.

 

First, BLOBs take up storage – a lot of storage. Whenever a user creates a new file in SharePoint, a new BLOB is created, increasing the size of the content database. If versioning is turned on in the SharePoint environment, then each new version of a file requires a new BLOB, so a 10 MB PowerPoint presentation that has been altered 10 times takes up 100 MB of space even though it is just one file! In fact, up to 90-95% of the storage overhead of a SharePoint content database in SQL Server is comprised of BLOBs (https://blogs.msdn.microsoft.com/mikewat/2008/08/19/improving-sharepoint-with-sql-server-2008/.)

 

Moreover, those BLOBs aren’t just consuming space; they are also using server resources. A BLOB is unstructured data. Therefore, any time a user accesses any file in SharePoint, the BLOB has to be reassembled before it can be delivered back to the user – taking SQL processing power and time. And when SQL Server is reading data from the database files, it has to skip over BLOBs in the data files that aren't essential to processing a query, which also consumes processing power and further slows SharePoint performance.

 

The Solution

The solution is to move BLOBs out of SQL Server and into a secondary storage location – specifically, a higher density storage array that is reasonably fast, like a file share or network attached storage (NAS).

 

This approach addresses both the storage and server resource issues that BLOBs cause. In fact, a recent Microsoft whitepaper reports that externalizing BLOBS in SharePoint reduces the size of the content database in SQL to just 2-5% of its original size. Moreover, externalized data was found to be 25% faster in end user recall times and 30.8% faster in user upload times (see this post here for more details). This performance improvement is due, in part, to increased BLOB transfer speeds between SQL Server and the client, since some of the file transfer can be handed off to the Windows Server operation system rather than the BLOB having to be processed and reassembled by the SQL Server process.

 

Microsoft offers two options for offloading BLOBs to less expensive, more efficient storage: external BLOB storage (EBS) and remote BLOB storage (RBS). EBS is Microsoft’s original BLOB management API, and Microsoft has established RBS as its successor technology. They are fully supported parts of the SQL Server ecosystem, but because they are fairly new, administrators are wary of using them. One concern is that offloaded BLOBs could be easily lost. Although this fear is unfounded, other concerns are legitimate. In particular, both EBS and RBS have limited management and offer no flexibility with rules for externalizing data. When used natively, these options only add to the complexity of managing SharePoint storage. For example, ordinary actions like deleting files or creating new versions leave behind orphaned BLOBs in the BLOB store, requiring manual cleanup. This restriction, the lack of administrative tools (it’s all done by T-SQL queries), and the relative inflexibility of the FILESTREAM RBS provider have led many architects to advise against using the native provider in a production environment without the help of third-party tools such as Quest Storage Maximizer.

 

Quest Storage Maximizer for SharePoint uses both EBS and RBS to make externalizing content easy. With Storage Maximizer for SharePoint, you can easily move large, old and unused SharePoint data from SQL storage to more efficient storage, like file shares and network attached storage devices, all while keeping the data accessible to the end user via SharePoint.

 

Storage Maximizer for SharePoint integrates into SharePoint’s Central Administration and offers an intuitive user interface where you can set the scope for externalizing documents from a farm down to a document library, specify the target for the external data, and define the rules for which data to externalize:

   

Figure 1. Storage Maximizer for SharePoint makes it easy to move large or old SharePoint data from SQL Server to more efficient storage, improving SharePoint performance.

    

Figure 2. Storage Maximizer enables you to select the scope of a storage definition job, from the farm down to the list or library.

By externalizing the content that slows your system, Storage Maximizer for SharePoint improves SharePoint performance for searching, uploading and accessing data, enhancing user productivity and satisfaction.

 

Stay tuned to this blog for the rest in this series of The Top 5 Performance Killers in SharePoint Storage!

Anonymous