SQL Server Performance Blog

Brett Hawton – 3 September 2010

Over the past few months I have visited a number of large production SQL Server installations at various organizations. Whilst great care had been given to the optimal setup of both the SQL Server and attendant databases, little care was given to the initial sizing of TempDB.

One of the most common errors was not correctly estimating the size that TempDB would grow to. Typically TempDB is created with a default size of 8MB and 10% growth increments. This is woefully insufficient for almost any production server and typically a DBA hasn’t done the math to see just how many expansions TempDB will undergo on the way to reaching the size needed on the server.

Let’s do some simple math to show just how many expansions are involved. On a production system TempDB is likely to rapidly expand to a few hundred MB within a short space of time. Using the 8MB initial size default and the 10% growth default, TempDB would have to go through 40 separate expansions just to achieve a relatively mediocre size of 300MB. Forty separate growth expansions is a huge number and likely to guarantee that each separate expansion resides in a different physical area on the disk platters which underlie most LUN allocations on a SAN or RAID array.

In most production instances I have observed, an initial allocation of around 500MB is a good starting point with the growth increment set to 200MB or so. In just 3 expansions TempDB could be at 1GB if needed whereas with the default size and growth this would take 52 expansions to achieve. You can quickly determine the current size that your TempDB (data and logs) have reached on a SQL Server instance by running the following script:

USE tempdb
GO
SET NOCOUNT ON
SELECT name, physical_name, type_desc, size*0.0078 as [Size in MB]
	FROM sys.database_files
GO

So far we have looked at the data file expansion in TempDB but the log file’s initial default sizing at just 1MB is typically even further from the mark. Whilst it is true that the recovery model of TempDB is set to “Simple” this does not mean that the TempDB transaction log will not be used. Anytime that a transaction is begun and TempDB work is performed, the TempDB transaction log will grow until the transaction is committed (or rolled back).

Take a look at the TSQL below. It performs a number of inserts into a temporary table as part of a transaction:

USE tempdb
GO
SET NOCOUNT ON
DECLARE @Counter int
SELECT @Counter = 1
CREATE TABLE #TempTable (col1 varchar(1024))
BEGIN TRANSACTION
WHILE @Counter < 40000
	BEGIN
	SELECT @Counter = @Counter + 1
	INSERT INTO #TempTable values (REPLICATE('A',1024))
	END
SELECT name, physical_name, type_desc, size*0.0078 as [Size in MB]
	FROM sys.database_files
DROP TABLE #TempTable
COMMIT TRANSACTION
GO

Note the SELECT statement near the end (just before the DROP TABLE and COMMIT TRANSACTION), it will show us the size of the TempDB transaction log at that instant.

Note that in this relatively small transaction, that the TempDB transaction log has grown to 16MB. Now using some math we see that with a default of 1MB and 10% increments in order to achieve 16MB the log has been expanded 31 times. Thirty-one expansions and its only 16MB big! One can see how unwise it is to leave the defaults as they are. In the case of the TempDB transaction log on a production system I would initially allocate at least 200MB as its initial size with increments set to 100MB.

One further issue which needs to be mentioned is that so long as the SQL Server service has the privilege “SE_MANAGE_VOLUME_NAME” allocated to it then the allocation of additional TempDB data pages is fast, as these areas do not have to be “zeroed out” on the IO subsystem. However it’s equally important to note that SQL Server always has to zero out any new transaction log allocations made. This “zeroing out” process can indeed take some considerable time and is a synchronous operation (the transactions requiring the extra log space will wait for this to complete before continuing on). This is yet another reason to make sure that these initial allocations are substantial enough to cover all normal usage.

As this post has shown, the default initial data and transaction log allocations for TempDB are woefully inadequate for production servers and will result in tens or even hundreds of additional small allocations needing to be made. Some of these allocations are synchronous in nature which will further slow transaction processing on the server.