Data files: To Autogrow Or Not To Autogrow?

  • Article

This is a question that's come up several times at TechEd already and is a nice second part to the post I made late last night about shrink. The answer is usually a most emphatic 'no, its far better to manage the growth of your data files manually'.

Why is this? Well, in SQL Server 2000, when a file grows, the file has to be zeroed out before the new space is available to use. This means that the thread that made the allocation request that triggered the file growth has to wait for the zeroing to complete. The reall kicker is that you have no control over when this happens - any random insert or update could cause an allocation and take the perf hit.

So its better to have some knowledge of the data growth pattern of your workload, anticipate the need and then grow the files pro-actively. If this isn't possible (the anticipation part) then at least set the file growth to be some small size, with an alert that will let you know that file growth has taken place - so then you can go in and manually grow the file during a period of low activity. This way you avoid the 'random perf hit' problem.

In SQL Server 2005, the default behavior is the same, but we have a very cool new feature - instant file initialization. This means that when the file grows, the disk space is not zeroed out as before and the file grows instantly, instead the old contents are overwritten as the space is used. The instant file initialization works during the following operations:

  • data file growth
  • data file creation (for a file addition or database creation)
  • filegroup or database restore

and is available when running on Windows XP and Windows Server 2003 and later operating systems.

Here's the spiel from Books Online about how to turn it on:

To use instant file initialization, you must run the SQL Server (MSSQLSERVER) service account under a Windows account and assign the Windows SE_MANAGE_VOLUME_NAME special permission to that Windows account. This permission is assigned to the Windows Administrator's group by default. If you have system administrator rights, you can assign this permission by adding the Windows account to the Perform Volume Maintenance Tasks security policy. For more information about assigning user rights, see the Windows documentation.

Now, this isn't an excuse for not controlling the file sizes yourself but it does alleviate the perf hit if there's something preventing you from taking control manually.

Another cool feature of SQL Server 2005...