Wednesday, July 15, 2009

Multiple files for TempDB on multi CORE/CPU Servers

This recommendation is relevant to SQL Servers with multi
COREs/CPUs. A dual-core CPU in this case is considered
to be two CPUs.
The TempDB can have only one data file group and one file
group for logs, but you can create many data files in the group.

In heavy loaded SQL Servers where large number of small Temp
tables created and dropped, allocation bottleneck may occur.

Briefly, when a temp object created/deleted or there is
insertion/deletion of a row(s) in a temp table, a new page(s)/extent(s)
need to be allocated. For this the PFS,GAM or SGAM pages
will be checked and updated.
(See this allocation bottleneck article for more information)
On parallel CPU operations the creation and destruction of temporary
objects may cause blocking.
So Microsoft recommends to divide the TempDB into multiple data
files of equal size. The general recommendation is to have one file
per CPU because only one thread is active per CPU at one time.
For example: if you have a 10 GB TempDB and you have 2 cores box
then you should create 2 data files of 5 GB each file.

Why the files should be equal in size? Because SQL Server allocates
pages/extents using proportional fill. Proportional fill means that,
when there are multiple data files in TempDB, each file is filled in
proportion to the free space that is available in the file so that all
of the files fill up at about the same time.
There are some improvements to the proportional fill algorithm in
SQL Server 2005,so the allocation contention in SQL Server 2005
will be reduced, but still can happen.

If dividing TempDB into multi files doesn`t help and you still have
allocation bottleneck of the SGAM pages, there is TF-1118
trace flag. Under this trace flag SQL Server allocates full extents
to each TempDB object, thereby eliminating the contention
on SGAM page. This TF impacts the user databases as well.
This flag is relevant only to SQL Server 2000.
See this Paul Randal`s article.

For more information:
Working with tempdb in SQL Server 2005
Managing TempDB in SQL Server: TempDB Configuration

No comments:

Post a Comment