TempDB Configuration

TempDB in SQL Server

The TempDB in sql server is usually THE most used database within the instance. This database is a system database and is shared amongst all the other databases within the instance. So what is tempdb used for. Here is a list of the most common uses:

• temporary tables
• table variables
• cursors
• internal operations such as sorts and spools
• Used in ‘Snapshot Isolation’  and Read-Comitted Snapshot Isolation for row versioning
• Multiple Active Result Sets or MARS

You can easily see therefore if you have lots of databases all creating temporary tables, using cursors, and using tempdb for its internal operations such as performing a sort operation, or HASHING then this database can quite often become a bottleneck.

Clues that TempDB May be the Bottleneck

One of the first clues that TempDB may be the bottleneck is the presence of PAGELATCH_UP or PAGELATCH_EX waits as one of the top waits on your server – not to be confused with PAGEIOLATCH waits as these are a different thing.

TempDB has ‘special’ pages  that is uses to keep track of objects created within it called GAM pages (Global Allocation Map) and ‘PFS’ pages (Pages Free Space). It is these pages that can become the ‘hotspot’ within TempDB whenever it is getting over worked.

By adding more TempDB files we effectively create more of these pages which should help in getting rid of any TempDB contention you may be experiencing.

How Many Files to Add ?

If you only have 1 TempDB data file on a machine then you should consider the possibility of adding more datafiles. Try adding 1 datafile per logical processor upto a maximum of 8 for 8 logical processors – this is the Microsoft recommendation but there are others who say half the amount of data files compared to logical processors.

I would say if you have 8 logical processors then start with 4 TempDB files and monitor your wait stats afterwards and see if the PAGELATCH wait stats drop. If they don’t then add 4 more TempDB data files and monitor again.

Adding more datafile than required can adversely effect the performance of sql server, so this is something you should be aware of.

Sizing your data files

If your tempdb file has grown to 10Gb then you should consider sizing your datafiles to equal this amount as a total. So for example if your are adding 4 data files, make them 2.5Gb in size. It is vital that you make the files the same size in order to accommodate sql servers ’round robin’ approach to filling the data files up.

Creating your Tempdb files and sizing them, should be done as an offline task. Trying to resize and add data files to sql server whilst people are using the server can lead to data corruption so seek to do this task out of hours or in an appropriate maintenance window.