SharePoint is a database driven product and makes frequent database trips for almost all of its work. Most of what is shown to the user is either a list or a document (also called a BLOB) and both come from the database.
As a result, SharePoint performance is often not very good (slow response times). Slow response times can frustrate users and in case of customer facing applications, you can even lose customers and as a result direct revenue.
The SharePoint performance problems occur primarily due to the two reasons:
- BLOBs overwhelm SQL Server: SQL Server is a relational database that was designed to store structured relational data and not large binary data. Therefore, when SharePoint stores its documents (BLOBs) in SQL Server, it overwhelms the database and this slows down SharePoint performance considerably. More than 90% of the data stored in SQL Server for SharePoint is BLOB data.
- Too many database trips for lists: Lists are the central characteristic of SharePoint and everything is displayed as lists to the user. And, all these lists are stored in SQL Server and SharePoint makes too many trips to the database which also overwhelms it and this slows down SharePoint performance.
Figure 1: SharePoint deployment with performance bottlenecks
As you can see in Figure 1, the Web Front End (WFE) servers are frequently going to the SQL Server cluster for meta data, list content, and BLOBs. On top of this, Index Server and Query Servers are also going to the same database. The entire database is overloaded and slows down quite a bit. That is the reason why in typical deployments SharePoint performance cannot scale beyond 6-7 WFE servers in a load-balanced web farm.
So what’s the solution?……………Externalize BLOBs and Use Caching
What you need is better SharePoint performance with faster response times even as your increase the number of users or number of WFE servers in the web farm. Better SharePoint performance results in satisfied users and an increase in revenue or website loyalty if your application is customer facing.
You can dramatically improve SharePoint performance if you use a few of intelligent techniques. They are:
- Externalize Documents (BLOBs): By moving all your BLOBs out of SQL Server and into a file system or SAN/NAS storage, you will relieve a lot of pressure from the database. This will allow SQL Server to focus more on what it does best, namely to process relational data transactions. And, a faster SQL Server means better SharePoint performance with more scalability.
- Cache Lists and BLOBs: By using caching on lists, you would reduce the database traffic even further since most structured data in SharePoint is displayed as lists. Caching list should reduce lists traffic by 50-70% since most database operations for lists are read-operations. Additionally, by caching BLOBs in an extremely fast in-memory cache, you’re even reducing trips to the file server of SAN/NAS storage and improving your BLOB read performance greatly which improves SharePoint performance.
Figure 2: Better SharePoint Performance with externalized BLOBs & Caching
Figure 2 shows how by using a combination of external BLOB storage (EBS) and in-memory caching, you can dramatically improve not only SharePoint performance but keep this good performance even when you increase user/transaction load on your SharePoint deployment.
The database trips are now made only to read lists once and then they’re cached for a configurable period of time so you don’t have to make these expensive database trips for lists again. Additionally, BLOBs are moved out of SQL Server and therefore all BLOB operations no longer go to the database. And, on top of all this, in-memory caching is used on both BLOBs and Lists which improves access time from WFE server to the cache and results in better SharePoint performance.