This article can also be found in the Premium Editorial Download "SharePoint Insider: Open source add-ons enhance Sharepoint 2010 collaboration."
Download it now to read this article plus other related content.
When things slow down in a SharePoint system, the initial fix is often to add more Web servers to better handle the load. But those front-end servers rely on the same back-end database server, and the back end is where SharePoint usually encounters the most performance problems. That’s because it essentially relies on SQL Server as both a database and a file system. It’s not that SQL Server isn’t a speed demon; it can be. But SharePoint’s processing demands tend to highlight SQL Server’s most common bottleneck: disk I/O.
It’s important to keep that in mind when planning for long-term SharePoint performance management. So what can you do to mitigate, or even avoid, the logjams that can snarl SQL Server throughput?
Start by getting SharePoint’s binary large objects, or blobs -- typically file attachments if you’re a SharePoint person -- out of the SQL Server database. Both SharePoint and SQL Server support Microsoft’s Remote BLOB Storage (RBS) technology, which enables SQL Server to put all of those Word, Excel and other documents back on the NTFS file system where they belong.
More about Microsoft SharePoint enterprise social collaboration
Learn how a well-planted SharePoint 2010 backup strategy could help save the farm
Discover how to avoid performance anxiety in your SharePoint 2010 installation
Find out how dispersed organizations test mettle of SharePoint 2010 infrastructure
Read the news story about the Microsoft acquisition of Yammer collaboration
NTFS, it turns out, reads and writes files quickly, whereas SQL Server has to manage them across its 8 KB database pages, which can eat up a lot of disk space and server resources. To avoid that, RBS puts files in NTFS and provides pointers to them in SQL Server. That means you can write those file attachments to entirely different disks than the one containing the main database, helping to reduce disk contention and keep SQL Server speedy.
Premium storage and regular maintenance
One of the best SharePoint performance management investments is buying fast storage -- and lots of it. Organizations should focus first on speed in planning their purchases because it’s often one of the most expensive aspects of storage. If you can afford them, ultra-fast storage area networks fronted by solid-state drive caches can provide high-end storage performance, which SharePoint often needs. Of course, it is also wise to ensure that there’s enough storage capacity available for all the data being created in a SharePoint installation.
Regular maintenance is also crucial to ongoing SQL Server efficiency. The SharePoint collaboration platform can generate a lot of data, especially in heavily used document stores where versioning occurs to track changes in documents and maintains multiple versions of them for reference purposes. And masses of new data can leave SQL Server confused about the best way to execute queries. Proper database maintenance, including rebuilding or reorganizing indexes and updating SQL Server’s statistics, helps SQL Server remain trim and fit for the best performance. Also, it makes sense to defragment database files when the fragmentation level begins to exceed 7% or 8%.
Break it up
Although SharePoint itself scales without major issues because of the Web’s inherent scalability, SQL Server doesn’t do so quite as easily. That’s why managers of many large SharePoint environments build multiple server farms, each with a dedicated back-end system. For example, an organization can have one set of SharePoint servers and an associated SQL Server database for the company-wide document library, another for users’ blogs, another for special projects and so forth. That’s likely to be more fruitful than trying to dump everything into a single SQL Server database.
SharePoint administrators should also plan to acquire a few extra tools -- SharePoint’s native backup routines, for example, aren’t the speediest in the world. In addition, administrators should visit Microsoft’s TechNet website, which details potential resolutions to specific performance problems as well as best practices designed to help keep SharePoint environments from slowing to a crawl.
In the end, though, many SharePoint performance problems end up being traced back to SQL Server disk throughput issues. By focusing on building a robust, scalable and manageable storage back end from the outset, organizations should be able to benefit from a longer-lasting SharePoint investment that offers greater efficiency and fewer performance bottlenecks.
Performance Metrics Need to be Defined, Tracked
It’s also important that you develop some reasonable, and measurable, performance expectations for your SharePoint environment. The key is to try and base them on the end-user experience. In other words, acceptable performance might be defined by the maximum amount of time a user can expect to wait when checking out a document, accessing a blog or accomplishing another SharePoint task. Such metrics can then be broken down into many potential back-end performance metrics so you and your users can assess how well SQL Server is holding up.
Once user-experience metrics are defined, they need to be measured regularly, with the results graphed so that trends can be identified. That will make it easy to detect if a SharePoint system starts failing to meet the organization’s expectations.
ABOUT THE AUTHOR:
Don Jones is a senior partner and principal technologist at strategic IT consulting firm Concentrated Technology LLC.
This was first published in July 2012