RSS Feed

Cranky Series: Set your File Growth Increments


August 27, 2012 by Mike Hillwig

One of the things that drives me nuts about working with Oracle DBAs is that they think installing and configuring SQL Server is easy. From their perspective, you mount the installer media, start the setup, and click NEXT a bunch of times. And in a few minutes you have a fully functioning database server. If they only knew just how bad of an idea this really is.

SQL Server is software. And like any software package, your results are directly related to the amount of effort and planning put into deploying the software. Yes, you can take the defaults and put your system databases and TempDB on the C drive. And yes, you can set MAX RAM to the amount on the box. But these will give you terrible results.

Another bad default is file growth increments. Growing data files 1 MB at a time and transaction logs by 10% will very quickly lead you to heavily fragmented disks. And I don’t care what your storage administrator says, disk fragmentation is a real problem, even on your fancy new SAN.

When a data file grows, it’s effectively issuing an ALTER DATABASE statement, which will ultimately cause performance problems.

Here is the challenge with this default. Pick a better setting for it. Trust me. It won’t work. Whatever setting you pick won’t work in my environment. And whatever I pick probably won’t work in yours. So you need to know your environment and what will work for you. Because I’m working with multiple installations of the same application, I know which databases grow a lot over time and which ones grow very little. That means some databases are set to grow 5 GB at a time, others 1 GB at a time, and others at 500 MB at a time.

There is no right solution. However, there is a wrong solution, and that’s using the defaults.