Share via

What is allocation bottleneck?

  • Article

Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as follows

· PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores status for 8088 pages.

o Free space available

o If it is allocated or not

o Has ghost records (when a row is deleted, it is marked as ghost)

· GAM (Global Allocation Map): Tracks if a uniform extent is free or not. A bit ‘1’ indicates that extent is available for allocation. Each GAM page tracks 64000 extents or 4GB

· SGAM (Shared Allocation Map): Tracks if an extent is mixed extent or not. A bit ‘1’ indicates that it is a mixed extent and has one or more free pages. Mixed extents are used to allocate first 7 pages of an object. If number of pages in an object is 8 or larges, its pages are allocated from uniform extents. Each SGAM covers 64000 extents or 4 GB

So for example, if you have a database file of size 16GB, it will have 4 GAM pages (each GAM covers 4GB), 4 SGAM pages and 256 PFS pages. It is worth mentioning that each file has its own PFS, GAM and SGAM pages.

Now, let us look at a high level what happens during allocation. A page allocation is done when an object is first created or as part of inserting the row that requires a new page to be allocated. Please note, my intent in the following description is not to describe the allocation algorithm of SQL Server (in fact, the allocation mechanism is much more complex and possibly widely different) but to give you an idea how you can get contention in allocation structures.

· SQL Server looks at the target extent if there are any pages available for allocation by examining the PFS page under SH latch. If a page is found, it is allocated and the PFS information is updated under UPDATE latch. For HEAPs, but not for Btree, the SQL Server will look into other allocated pages with enough free space to store the new row and then updates the new free space information by taking the UPDATE latch on PFS page.

· If no such page is found, the SQL Server looks at GAM page under SH latch to find a free uniform extent (assuming the object already has > 8 pages). If no such extent is found, then it looks at the next GAM page and so on. When a free extent is found, the SQL Server takes an UPDATE latch on the GAM page and updates the information.

· If the object has <= 8 pages, a page from mixed extent needs to be allocated. The SQL Server looks at SGAM page under SH latch and if a mixed extent with one or more free page is found, the page is allocated and the SGAM page is updated by taking the UPDATE latch

Similarly, when page is deallocated or an object is dropped or when a row is deleted (for HEAPs), the allocation structures need to be updated. As you can now imagine, if there is significant allocation/deallocation activity in a SQL Server database, multiple threads may need to wait to acquire X or SH latch in non-conflicting mode which can lead to allocation bottleneck.

Starting with SQL Server 2005, the caching mechanism for objects in TempDB has been improved significantly which will reduce the allocation contention incurred by your workload. you can address SGAM bottleneck by enabling TF-1118. Please refer to Managing TempDB in SQL Server: TempDB Configuration.

 

Thanks

Sunil Agarwal

Comments

  • Anonymous
    January 04, 2009
    In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that

  • Anonymous
    January 10, 2009
    This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot.

  • Anonymous
    January 28, 2009
    Hi Sunil, I've read many times SQL Server uses a deadlock-proof latching algorithm. But by your description is certainly possible for a deadlock to occur. If two processes are scanning a GAM page under SH latch, none of them will be able to take a X latch (latch compatibility rules are the same for locks???). Then if them both want to upgrade to a X latch, won't this be a deadlock?

  • Anonymous
    January 29, 2009
    Actually, the intent of my blog is to describe why latch contention can happen. This is what I had mentioned in the beginning of the BLOG. I checked with the person in the ;know' and he pointed outr that we take an UPDATE latch to flip the bit. I have updaed the blog.This does not block readers. I am sure the algorithm is more complex than I describe here for the reasons you outlined (i.e. avoid deadlock at all costs) thanks for your interest and comments

  • Anonymous
    January 29, 2009
    Hi, I saw in the post your intention wasn't to describe the algorithm. I just have never been able to find something about HOW deadlock is actually avoided. Thanks for the answer.

  • Anonymous
    September 24, 2012
    Hi just an update GAM pages map what extents have been allocated may it be uniform or mixed.

  • Anonymous
    November 23, 2013
    Really Great Sunil. :)