Content Database Capacity
Issue description
This check determines whether the content databases are configured in such a way to ensure the best performance of the system.
Explanation
SharePoint Server uses SQL Server to store configuration and user data. Therefore it is extremely important that SQL Server be as fast and optimized as possible. The size and the configuration of a Content Database can have an impact to the performance of the system.
As a general “rule of thumb”, you should not allow content databases to grow beyond 200 GB. If you are using Remote BLOB Storage (RBS), the total volume of remote BLOB storage and metadata in the content database should not exceed this limit. To support Content Databases of up to 4 TB, disk sub-system performance of 0.25 IOPS per GB is required. 2 IOPS per GB is recommended for optimal performance. You also have to develop plans for high availability, disaster recovery, future capacity and performance testing.
Content databases with no explicit size limit are also supported, but under strict requirements. Site templates used to create site collections in a content database bigger than 4 TB must be either Document Center or Records Center. Less than 5% of the content in the content database is accessed each month on average, and less than 1% of content is modified or written each month on average. Alerts, workflows or item-level security should also not be used.
In addition, you should proactively monitor your databases and tweak some settings when required. It is advisable to pre-size content database files, if possible, to the expected size. This allows you to avoid the overhead of auto-growth, which can require some resources and have negative performance impacts. There is no universal recipe for the values you can use for the initial size and auto-growth settings, as this will depend heavily on your environment and usage.
Important factor to consider when scaling the environment and setting the limits are defined Service Level Agreements (SLAs), especially Recovery Point Objective (RPO) and Recovery Time Objective (RTO). Having large content databases means longer backup and restore times, which directly affects RPO and RTO.
Solution
These are general guidelines on setting the content database’s initial size. The content database’s initial size should be set to a value larger than the default. These values should be set in accordance with your environment and the expected amount of data. If your SharePoint farm will host a small amount of data, you could go with 500 MB or 1 GB as the initial size. This is merely an example, and the exact value depends heavily on the expected amount of data, available disk space, (expected) number of content databases, and other factors. Monitor your content database on a regular basis and change the settings if required.
To set the database initial size and auto-growth size, start SQL Server Management Studio and connect to the SQL Server instance which hosts your content databases. In the Object Explorer tree, select a content database, right-click on it and click Properties. In the Database Properties window, select Files page.
On this page you can set initial size and auto-growth size for both MDF (data) and LDF (transaction log) files. For auto-growth, avoid setting it to a percentage number.
For example, if you set auto-growth to 10% on a content database of 100 GB size, when auto-growth operation occurs, SQL Server will extend the file by allocating an additional 10 GB, which may take a bit of time and slow down the system during the operation. If you set it to 1% and you have a content database of 1 GB, SQL Server will perform the auto-growth operation every 10 MB. If you upload a 50 MB file to the SharePoint, auto-growth will occur 4-5 times during a single upload, which is also sub-optimal. Therefore, set auto-growth to a fixed number of megabytes instead of a percentage.
Additional information
Additional information can be found in the following article:
Last updated
