Configuring tempdb for SQL Server 2005

Configuring the tempdb System Database for SQL Server 2005

The tempdb system database in SQL Server 2005 plays a much more important role than in earlier versions of SQL Server. Consequently it is important to ensure that it has been correctly sized and configured.

The tempdb system database is still used to store intermediate results for queries, temporary tables, table variables, table-valued functions and cursors. However, there have been a number of changes in SQL Server 2005.

The tempdb system database is explicitly used by a number of SQL Server 2005’s features, including:

• Database Mail
• Event Notifications
• Multiple Active Result Sets (MARS)
• Online Index Rebuild
• Query Notifications
• Read Committed Snapshot Isolation (RCSI)
• Row-Versioning
• Service Broker
• Snapshot Isolation

However, unbeknownst to a lot of DBAs, a number of SQL Server 2005 features now use the tempdb system database implicitly, including:

• Index Creation
• Triggers
• DBCC CHECK commands

This was not necessarily the case in SQL Server 2000. As you can see, you will mostly likely be utilizing the tempdb system database more heavily after performing an upgrade to SQL Server 2005. Potentially your tempdb system database will become more of a bottleneck if it is not correctly sized and configured.

Compounding this potential bottleneck is the fact that SQL Server 2005 has to modify more system tables whenever it creates an object in the tempdb system database (sysallocunits, syshobts, syshobtcolumns, sysrowsets, sysrowsetcolumns).

Another factor is that whenever an object needs to be created SQL Server 2005 has to modify the internal allocation pages (the Page Free Space (PFS) page, the Global Allocation Map (GAM) page and in particular the Shared Global Allocation Map (SGAM) page) to allocate the mixed and/or uniform extents. Thus there is potentially contention on the SGAM page.

General Best Practices

First and foremost you should determine how heavily your SQL Server 2005 instance will be utilizing the tempdb system database. There is no substitute for determining and consequently knowing how the tempdb system database is being utilized. This can be achieved by talking to your database developers and/or software vendor.

It can be as simple as going through the checklist of features that use the tempdb system database that I described above. Don’t hesitate to ask what your software vendor recommends. (Although in my experience software vendors usually have no idea about the database engine that their product is using.) Otherwise it is a simple matter of monitoring the tempdb system database through the SQL Server 2005 tools. I will discuss what to monitor at the end of this article.

Otherwise there are a number of tenets that you can apply to maximize the performance of your tempdb system database:

• Place the tempdb system database on a disk drive separate from your user database files.
• Place the tempdb system database on a dedicated disk drive.
• Place the tempdb system database on a faster disk drive. (Say a 15,000rpm disk drive instead of a 10,000rpm disk drive.)
• Separate the log file from the data file for your tempdb system database.
• Consider using a solid state drive (SSD) for the tempdb system database. A quick search on the web indicated that a number of commodity hardware vendors have, or will have in the near future, a number of SSDs out in the market.
• Consider placing the tempdb system database on the local drives instead of on the SAN.
• Avoid placing the tempdb system database on a RAID 5 partition due to the write I/O penalty.
• Ensure that you have configured Instant File Initialization under Windows 2003. This will improve the performance of the automatic growth of databases, including the tempdb system database.

But again, remember, that you have to determine that your SQL Server 2005 instance is heavily utilizing the tempdb system database to realize the maximum performance benefits.

Optimal Configuration of the tempdb system database

The default configuration of the tempdb system database for SQL Server 2005 (8MB in size) is sub-optimal. I suppose Microsoft’s main consideration for its main configuration was to ensure that it is consistent across all the SKUs that exist, from Express Edition to Enterprise Edition. They would expect DBAs to configure it correctly in the enterprise space.

There are a number of simple tenets that you can follow for the optimal configuration of the tempdb system database:

• Pre-allocate an appropriate size to the data and log files. (Ignore Microsoft’s lame recommendation in SQL Server 2005 BOL!) The goal is to avoid the SQL Server 2005 database engine having to invoke automatic growth. (Note that you can still leave automatic growth to ensure that your tempdb system database does not run out of space.)
• Create as many data files as you have physical cores on your SQL Server 2005 instance. (In other words do not include hyper-threaded (HTT) processors.) This will improve performance by allowing SQL Server 2005 to take advantage of the proportional fill feature of the database engine, and reduce the contention created through concurrency.
• Ensure that all data files are of equal size. This will ensure that SQL Server 2005’s proportional fill feature of the database engine will function optimally.
• Consider using trace flag -T1118 to reduce the contention on your SGAM pages. SQL Server 2005 will no longer use mixed extents for the allocation of database objects.

The real question is how large your initial tempdb system database size should be. Generally, disk space is not a capacity, so remember that you can afford to be generous. A lot of times I allocate a generous amount of space, beyond what the SQL Server 2005 instance really needs, as I know that there is plenty of unutilized space on that partition in any case.  So then it is a matter of monitoring your SQL Server 2005 instance to determine whether the space that you have allocated has been sufficient, and adjust accordingly.

Monitoring the tempdb System Database

You should monitor your tempdb system database to help determine if the sizes that you have allocated to the data and log file has been sufficient. Again the rationale is to avoid automatic growth as that will degrade performance.

After monitoring the following performance object counters you should be able to adjust the initial size of your tempdb system database data and log files:

• SQLServer:Transactions : Free Space in tempdb (KB)
• SQL Server:Databases : Log File(s) Used Size (KB)
• SQL Server:Databases : Data File(s) Size (KB)
• SQL Server:Databases : Log File(s) Size (KB)

You can also query the sys.dm_db_file_space_usage DMV, which was added by Microsoft to specifically monitor the space utilization of the various objects in the tempdb system database files.