Proactively manage the growth of data and log files

Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) read here
We recommend that you proactively manage the growth of data and log files by considering the following  recommendations:

As much as possible, pre-grow all data and log files to their anticipated final size.

We recommend that you enable autogrowth for safety reasons.

Do not rely on the default autogrowth settings. Consider the following guidelines when configuring autogrowth:
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.

Set the autogrowth value for the Search service application Property Store database to 10 percent.

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 MAXSIZE property. Periodically review this setting to make sure it is still an appropriate value based on past growth rates.

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.

What should the auto grow value be set to? What should the max file size be 300mb?

Some wouldn’t usually recommend growing more than 2-4 GB at a  time (unless max file size is 2 GB and
many files are around that size), so your customer is probably looking to grow somewhere between
600MB and 2 GB per auto grow in order to get reasonable performance during autogrow operations.

One guy says 2-3 times as large as the max file upload size as a minimum, and your DBA should be notified every time an autogrow operation happens, so that they can proactively increase the size of their database to account for growth.  Ideally, they  should be notified via maintenance plans whenever their data file gets over a certain percentage full  (say… 75%).  Auto-grow is an expensive transaction – everything goes into wait mode while you auto grow your database.

Different operations can make your database grow faster (about whether we write all 0’s to disk or just overwrite as we need to).  It also depends on your usage profile… how much you actually grow on a given day or week.

But keep in mind that more than 1 GB is not good for performance according to the best practices and
recommendations.

We flag 1 GB in the RAP, but I’m not sure who owns or contributed that portion.  You also have to
consider, small autogrow operations is a great way to get many small extents, and higher fragmentation
There are some recommendations in general (not related to SharePoint) to follow regarding how to
implement autogrow.  Have a look at these resources:
http://support.microsoft.com/kb/315512
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629866.aspx (or anything else by
Paul Randal – who now owns SQLSkills and wrote pretty much all of DBCC)
http://msdn.microsoft.com/en-us/library/ms190969.aspx
http://msdn.microsoft.com/en-us/library/cc966534.aspx

so… 1 GB isn’t necessarily bad for performance depending on how your databases are being used… 1 GB
could be really good for performance in some scenarios.

Paul’s blog actually talks about a very important option called instant file initialization – some
companies can use this, others can’t.  If you use this, SQL does not zero out the data on the hard drive as
it allocates space.  The result is that you can read the data on disk from the re-used file.  At MCM, had
we had enough time, there’s actually a demo where they retrieve somebody’s e-mail from disk by using
SQL after it grabs files from disk.  There are too many factors to consider when deciding what the right
value is.  It’s a process that each company should go through themselves, because it varies so much.

A lot of it is how we actually store and allocate pages.  One part of that is what Daniel mentioned
regarding linear disk allocation.  It definitely makes it much easier to read when your data is close
together on disk. And like Daniel said, SANs are a completely different topic… some SANs allow you to
control things, others don’t.

I don’t have the link right now, but Jonathan Kehayias has a free downloadable e-book available which
should discuss some of this in pretty good detail.

Advertisements