An Easy Way To Optimize Page Allocation in TempDB

Old Skool Page Lookups

The other day (prompted by a tweet by @buckwoody) I ran the Microsoft Baseline Analyzer on one of my SQL DBs. One of the recommendations it made was that I create multiple data files for tempDB as allocations were slow. I vaguely remember reading about doing this but was still hazy on the details.

So rather than just make the change blindly I wanted to do some research behind the scenes and understand why.

The Mechanics

When you create an entry in tempDB it needs to allocate space just like any other table. There are three types of pages utilized in the allocation process in the tempdb data file: Page Free Space (PFS), Shared Global Allocation Map (SGAM), and Global Allocation Map (GAM).

• The PFS (specified as a 1) holds 1 byte of data that contains how much free space is in each page.
• The SGAM (specified as a 3) holds 1 bit per extent which SQL server uses to find whether or not an extent is a mixed extent with some free space or a full extent (there are some optimizations SQL 2008 has where you allocate a uniform extent, but for the sake of this post I will leave that out).
• The GAM (specified as a 2) holds 1 bit per extent which SQL Server uses to to see which extents are empty to allocate to the new object.

The tempdb page allocation goes something like this:

1. When a temp table is created SQL Server reads the SGAM page to find some free space to allocate for the the table.
2. SQL Server latches the SGAM page and then reads the PFS page to find free pages within the extent.
3. Once free space is found SQL Server creates an exclusive lock on the PFS page and allocates the space.

However, if tempdb is overloaded with allocation requests this can become burdensome with PAGELATCH waits. This can be seen by querying the following:

SELECT session_id, wait_duration_ms,   resource_description
from    sys.dm_os_waiting_tasks
where   wait_type like ‘PAGE%LATCH_%’ and
resource_description like ‘2:%’

This query is getting all the PAGE LATCH wait types where the database id is 2 (tempdb). The order for this is dbid:page:type (in the resource_description). If this type is GAM (ex- 2:1:2), SGAM (ex- 2:1:3), or PFS (ex- 2:1:1) it can represent a bottleneck.

One solution to this issue (and the easiest) is to configure multiple tempdb data files.
Microsoft recommends starting with 1 tempdb data file per CPU and adjusting up from there.

There are a couple issues though. One problem is if you create too many data files you may actually get a performance reduction. Another issue is that tempdb will try and fill the file with the most free space first. So you won’t get any benefit unless you equalize all the data files. So a sample configuration for a quad-core system might look like this:

ALTER DATABASE tempdb
MODIFY FILE (name=tempdev1, size=100MB);
GO
ALTER DATABASE tempdb ADD FILE (name=tempdev2, size=100MB,filename=’Z:\mydata\tempdev2.ndf’);
GO
ALTER DATABASE tempdb
ADD FILE (name=tempdev3, size=100MB,filename=’Z:\mydata\tempdev3.ndf’);
GO
ALTER DATABASE tempdb
ADD FILE (name=tempdev4, size=100MB,filename=’Z:\mydata\tempdev4.ndf’);
GO

Now, in theory, you should have uniform page allocation to tempdb. I highly recommend that you utilize the sys.dm_os_wait_stats DMV before and after to see what kind of effect this has on your PAGELATCH’ing and Duration. Your mileage may vary of course.