Tip: Avoid Auto-Growing Tempdb When SQL Server Starts

Most DBAs know that growing a file dynamically is an expensive operation that degrades performance because write and read activity to the file must temporarily be blocked while the file is growing. However, when managing space for the tempdb database, many DBAs forget the performance impact of auto-growing files. SQL Server rebuilds tempdb each time the database system is stopped and restarted. Tempdb files might have auto-grown, which is the default configuration, since SQL Server was last started. SQL Server doesn't remember the size of the tempdb files when it rebuilds tempdb during a restart. Instead, SQL Server resets the files to the size they were manually configured to be.

Consider the following situation. Tempdb in SQL Server 2000 defaults to having one data file that has the logical name tempdev. The initial size of this file is 8MB. Assume that you manually increase the file size to 500MB by using the ALTER DATABASE command. The file subsequently grows to 550MB through auto-grow operations. SQL Server will reset the tempdev file to 500MB when it rebuilds tempdb. Now, assume that no one manually increased the file size from 8MB to 500MB. Instead, SQL Server auto-grew tempdb to 550MB. In this case, SQL Server resets tempdev back to 8MB when it's stopped and restarted.

The operations that grow a database file can diminish performance because reads and writes are blocked each time the file is grown. In addition, auto-growing the file might contribute to fragmentation on the physical disks. Also, the default growth increment for tempdb is 10 percent. So, for example, SQL Server would need to invoke many auto-grow operations to grow tempdev from 8MB to 550MB. That's bad. However, it's worse if the same cycle happens whenever SQL Server is restarted, assuming that 550MB is the average high-water mark that tempdb reaches during typical operations. Even worse, you might have a hard time troubleshooting why performance is sometimes slower immediately after a server is rebooted.

You can use the query that Listing 1 shows to find tempdb files that have auto-grown since SQL Server was last stopped and started. In general, manually setting tempdb files to a reasonable size will help you avoid significant auto-growth operations. Most importantly, if tempdb files do auto-grow, you need to determine whether the new file sizes are reasonable high-water marks. If they are, consider manually setting the file size to compensate. For example, if a file auto-grew from 8MB to 550MB, you could manually set the file size to 551MB. The file would then be rebuilt to a size of 551MB when SQL Server was stopped and restarted the next time.