When setting the autogrow properties for a SQL Server data file, is it better to use a fixed size or a percentage growth?
It's hard to say always or never in the performance-tuning world. It's usually better to define the reasons why certain decisions are better in particular situations. Whether you use a fixed size or a percentage for autogrow settings, you'll still have to tweak the values as your file grows. Typically, I'll set the file size to a reasonable starting point, then estimate a 10 percent growth. That's simple. However, using percentage growth is dangerous because the file size can become surprisingly large if your data files grow more than you anticipate. Big data files aren't bad, but a growing file can degrade performance. Maybe you've done tests using small autogrow settings and haven't seen performance degradation. But what if your files grow exponentially larger than you anticipate? First, file growth should never be that surprising because you should always be monitoring your database. Second, in general, you should never autogrow your files because you should proactively add space during periods of non-peak usage. Using the percentage growth setting can lead to situations in which the time it takes to autogrow is unpredictable because it gets longer and longer. In some cases, especially when the server is busy, this can lead to timeouts for queries. SQL Server 2005 will address this problem by supporting instant file initialization.
