SharePoint 2010 SQL DB autogrowth – leave it on!

I was on the MSDN forums recently discussing SharePoint database index fragmentation and spotted a question around SharePoint database autogrowth that I thought was interesting. Although this has been raised several time in the past (Alex Dean blogged about it back in 2009) , I thought it worth clarifying this once more now that SharePoint 2010 is RTM.

From what I have read, some seem to think that DB autogrowth settings should be turned off and pre-growth should be used exclusively to ensure a high level of performance. I strongly disagree with this – autogrowth should be left ON for safety, but the default settings should be changed to pre-grow the SQL DB and ensure that if a database ever reaches its pre-defined limit it grows by a sensible amount rather than in 1MB chunks.
SQL 2008 Default Autogrowth Settings
Default SQL Server 2008 autogrowth settings
This article provides a little more about autogrowth. In essence, the time taken to complete a transaction will include the time taken to (auto)grow the database by the configured amount. In the context of SharePoint: if you upload a 50MB file and you are using default autogrowth settings, your content database needs to grow by an amount equal to 50 times your growth increment to accommodate your file. No wonder I hear so many complaints about SharePoint timing out during large file uploads! Another implication of growth in small increments includes physical fragmentation which can degrade performance of your SQL server over time.

 

=============================
  1. As much as possible, pre-grow all data and log files to their anticipated final size. 
  2. We recommend that you enable autogrowth for safety reasons. Do not rely on the default autogrowth settings. Consider the following guidelines when configuring autogrowth:
    1. When you plan content databases that exceed the recommended size (200 GB), set the database autogrowth value to a fixed number of megabytes instead of to a percentage. This will reduce the frequency with which SQL Server increases the size of a file. Increasing file size is a blocking operation that involves filling the new space with empty pages.
    2. Set the autogrowth value for the Search service application Property Store database to 10 percent.
    3. If the calculated size of the content database is not expected to reach the recommended maximum size of 200 GB within the next year, set it to the maximum size the database is predicted to reach within a year — with 20 percent additional margin for error — by using the ALTER DATABASE MAXSIZEproperty. Periodically review this setting to make sure it is still an appropriate value based on past growth rates.
  3. Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.
=========================
As regards to specific autogrowth recommendations, the article above recommends configuring growth increments of approximately one-eighth the size of your file. Using an example of a 50GB content database, you would configure an increment of just over 6GB which sounds sensible to me. Obviously the figure varies depending on the environment and usage figures: a heavy collaboration enterprise environment might benefit from a slightly larger increment, for example.
As an aside: taking point 1.) and 3.) into account, you will probably have realised that you require a lot of space for your SharePoint 2010 SQL databases. Assuming you have 10 Web applications, each with one content database that is estimated to reach 50GB within the next year, you require 600GB of pre-grown storage space with a 20% error margin – and SQL storage is not cheap storage. Bear in mind this is only your content databases – you also need to factor in your central admin content, configuration database and service application databases. Add the recommended 25% available disk space and you require close to a terabyte of SQL storage space. No wonder support for RBS is a welcome addition to SharePoint 2010!
In summary:
·         Autogrowth is good. Keep it on for safety (it should be considered a contingency only).
·         Pre-growth is good. Ensure your databases are pre-grown to approximately their estimated size according to Microsoft’s recommendations.
·         The default autogrowth settings are bad. Increase the growth increment in order to improve performance and reduce fragmentation.
I hope that was useful to you. Let me know if you have any feedback or queries.

One thought on “SharePoint 2010 SQL DB autogrowth – leave it on!

  1. oscar

    When I am unable to add a service application database, is that a tell tell sign my sharepoint databases are running out of space??

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *