My clients are lucky. I have walked in the door and within a week, with no hardware changes, they saw their SharePoint installations running 400 percent faster than before. I know that's a tall claim, but as a consultant with very wide exposure, I can say with some confidence that most SharePoint installations' databases do not get the attention they deserve. Maybe it is a disciplinary thing, DBAs are usually not SharePoint experts, and SharePoint experts are usually not DBAs, so the poor SharePoint SQL Server is left alone to suffer under its default settings.
The issue is that out-of-the-box SharePoint content databases are absolutely terrible for performance. Are you running your production SharePoint installation with the default database settings? If so, you really need to read this article.
In this article, I share my top 10 database optimization techniques. There are many other facets to SharePoint performance that this article doesn't cover, and each of these optimizations needs further discussion and analysis. But this should start you thinking. Keep track of how many of the techniques your production installation qualifies for. Let's roll.
Optimize TempDB
TempDB is the number one bottleneck for your SharePoint environment. All transactions in all databases go through TempDB first. As a result, it's very important for you to measure TempDB stall times, latencies, and disk IO. Ensure that:
- TempDB is split into multiple files. The number of files must be equal to the number of processors on your SQL Server. This has diminishing and even negative returns if the number of processors is too high. I like to not go beyond 16 files on current hardware.
- TempDB's recovery model is set to simple.
- TempDB is on the fastest disks. Stall times should be around 1ms on current day hardware.
- The disks have more than 25% of free space to accommodate for peak usage.
- Each TempDB file is 25% of the largest content database.
- All TempDB files are of equal size.
- Files are split across multiple disks for better performance.
- The TempDB disk is not used for anything other than TempDB.
- TempDB has enough free space.
Size Your Content Databases
SharePoint stores the majority of its content in SQL Server databases. Many of these databases are concerned with the overall configuration of the system or managed services support. The majority of these databases are those that accept uploaded content or collaborative content. These databases need to be sized with various factors in mind, such as:
- The ability to backup/restore the content quickly, allowing for quicker SLAs and isolation in the event of database failure.
- The SharePoint system avoids SQL Server transactions in many instances. It does so to avoid locks, but at the cost of resultant orphaned data or possible data corruption. Larger databases have more orphaned items than smaller ones. Also smaller databases keep the problems isolated.
It's very important for any project to estimate content database size. This is especially important in collaborative document-centric projects. Not doing this upfront planning can lead to significant manageability, performance, and reliability issues.
As a rough rule of thumb, use this formula to size a content database:
Database size = ((D * V) * S) + (10 KB * (L + (V * D)))
Where:
- D = expected number of documents,
- V = expected number of versions
- S = average size of the document
- L = number of list items, and 10KB is the expected metadata on each document.
The above calculation does not account for recycle bin, which I will talk about shortly.
Additionally, I recommend that no content database be more than 100GB in size. If your site collections are quite small, I recommend that you have no more than 50 site collections per database. MSDN's recommendation of no greater than 200GB is, I feel, too optimistic. For very large installations, don't go over 300 content databases per farm.
Capacity Plan for Growth
Through the normal cycle of IO operations, databases grow. When they're created, databases store data files and log files. A DBA has the option of splitting the database into multiple files, but the complexity that offers doesn't render any benefit except in very extreme circumstances. An example of an extreme circumstance is a 400GB content database that you've inherited from the previous architect.
However, the default growth settings of a SharePoint content database are woefully inadequate. It is recommended that you:
- Pre-allocate the databases and use growth as insurance, not as your de facto mechanism. In other words, as the database grows, it should occupy free space that you have already pre-allocated and not require growth. Every time a database runs out of space, it has to grow, which causes database fragmentation, and the perceived performance for the user uploading the document (which causes the growth) is negative.
- Enable growth in size, not percentage. This is important because as a database gets bigger, the percentage growth may result in the increase of many gigabytes of storage allocation. This causes the SQL Server to look for free space and grow the database, while the user waits for an upload or save operation to work.
- Monitor databases for free space, and ensure that you add new databases to accommodate growth ahead of time, and not as a reactionary mechanism after problems occur. Once you have hit the pre-allocated size limits of the database, the database fragments, causing performance degradation.
Modify model database settings to ensure that newly created databases follow size allocation rules. Growth parameters are not copied from the model database when databases are created from the SharePoint central administration UI. I recommend that the addition of newer content databases be a well-defined process done by operations or the DBA team.
SQL Server Disk Cluster Size
SQL Server disks must use a large cluster size. If in doubt, use 64KB, but the exact size may depend on your SAN architecture. The default 4KB allocation size in NTFS is woefully inadequate. Using a larger cluster size results in consumption of more disk space when lots of small files are used.
SQL Server databases are usually very large files, and they can significantly benefit by contiguous disk space allocation. By changing from 4KB allocation size (default) to a 64KB allocation size, you can gain as much as 30% performance improvement for read operations. I highly recommend that you allocate SQL Servers in this manner.
Disk IO Operations
Fast disks and disks with low latencies greatly benefit the performance of SQL Server. I recommend that you leverage industry standard technologies such as PCI Express disks and allocate certain databases on faster disks. Place the following data files on the fastest drives in order of precedence:
- TempDB data files and transaction logs
- Content database transaction log files
- Search databases, except for the Search administration database
- Content database data files
It is important to benchmark the disks at each fresh install, and to continue monitoring the disks' health if you want to ensure consistent performance over the lifetime of the system.
Defragment Indexes
Index fragmentation can cause high CPU usage, which means disk usage, and therefore performance degradation. Any indexes above 50% in fragmentation percentages that affect more than 4000 rows are bad, and the system performance can significantly benefit from defragmenting the indexes. Note that indexes do not defragment themselves; this is something a DBA needs to do.
Update Statistics
SQL Server has the ability to auto-update statistics and auto-create statistics. Those settings in SQL Server are not recommended for use with SharePoint, because SharePoint has its own mechanism for updating statistics. SharePoint uses a timer job and a stored procedure called sp_updatestats. Even though SharePoint uses a timer job, it's not a bad idea to monitor and use this procedure yourself.
It's important to know that because SharePoint runs statistics update operations as timer jobs they shouldn't run while backups are in process, or the auto-update statistics will be cancelled. Over time, this can result in a huge negative impact on performance.
Recycle Bin
Look at your default Windows recycle bin. It's probably set to 5%-10% of your total disk space. Now go to Central Administration and check out the recycle bin for SharePoint. By default, it stores documents in the first stage recycle bin for 30 days, and then in the second stage for 50% of the size of the content database.
FIFTY PERCENT! That effectively doubles your storage needs, a far cry from what your desktop can offer. The funny thing is, servers are usually backed up well too, so in my opinion, the out-of-the-box recycle bin settings are far too aggressive. Feel free to tone them down and reduce your storage costs.
Memory Allocation
SQL Server, by default, is configured to consume as much memory as possible. This can have a negative impact on the performance of everything, even the operating system. Alternatively, in situations where the server is used for other purposes besides the database, when other applications require more memory, SQL Server may have already consumed all possible memory. This is true only if you are sharing the Web front end (WFE) and SQL Server on the same server.
I recommend that you define SQL Server memory usage with a maximum and minimum. Minimum allocations help in warm up times. Maximum allocations help in peak conditions and consistent usage over time.
In planning for your memory allocations, don't forget to account for your clustering configuration.
MAXDOP
By default, the setting for SQL Server's Maximum Degree of Parallelization, or MAXDOP, is 0. This means that it tries to execute as many SQL queries in parallel as it can. The funny thing is, SharePoint performs best if the maximum degree of parallelization is set to 1 (i.e., disabled parallelization). You should therefore tweak your SQL Server's MAXDOP setting and restart SQL Server.
Summary
It would be incredibly remiss to say that this article covered even 20% of the possibilities when it comes to improving your SharePoint server performance. I can think of at least 10 more, and there are a lot of nuances even within the ten I covered.
Content database performance affects everything! Optimizing SharePoint content databases is the first step of many in improving your SharePoint environment's performance. I find it incredibly surprising how few installations use these basic optimizations.
Now, be a good SharePoint consultant and take this article to your boss and implement these optimizations right away. And do let me know if you see any improvement.
I'll be back with more tips, but until then, Happy SharePointing.
