Duty Free!!

SQL Write-Ahead Overhead…

Concurrency enhancements for the tempdb database

Here is a great link that describes Concurrency enhancements for the tempdb database. This also helps understand the way an object gets a page in the memory. 

“During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data. 
As part of allocating a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to find out which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods. 

When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This results in intense contention on the SGAM page when several mixed page allocations are underway, which can cause the problems documented in the “Symptoms” section of this article. ”

A single GAM page for 4GB of Tempdb file means there is heavy contention for that page. This is the same with the PFS as well where the contention is doubled with allocation and deallocation of pages.  This is main reason to have multiple Tempdb files and with the same size. IF any single Tempdb file is  bigger again it can be frequently contended for during the round-robin algorithm for allocating a page.

Also, “The auto-grow of tempdb data files can also interfere with the proportional fill algorithm. Therefore, it may be a good idea to turn off the auto-grow feature for the tempdb data files. If the auto-grow option is turned off, you must make sure to create the data files so that they are large enough to prevent the server from experiencing a lack of disk space with tempdb.”


January 20, 2009 - Posted by | SQL Server

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: