Recommendations to reduce allocation contention in SQL Server tempdb database

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

Symptoms

On a server that is running Microsoft SQL Server, you notice severe blocking when the server is experiencing a heavy load. Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks] indicates that these requests or tasks are waiting for tempdb resources. Additionally, the wait type is PAGELATCH_UP, and the wait resource points to pages in tempdb. These pages might be of the format 2:1:1, 2:1:3, and so on (PFS and SGAM pages in tempdb).
Note If a page is evenly divisible by 8088, it is a PFS page. For example, page 2:3:905856 is a PFS in file_id=3 in tempdb.
The following operations use tempdb extensively:
  • Repetative create-and-drop operation of temporary tables (local or global).
  • Table variables that use tempdb for storage.
  • Work tables that are associated with CURSORS.
  • Work tables that are associated with an ORDER BY clause.
  • Work tables that are associated with an GROUP BY clause.
  • Work files that are associated with HASH PLANS.
These activities may cause contention problems.

Cause

When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be briefly unresponsive.
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.

To allocate a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to determine 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 causes intense contention on the SGAM page when several mixed-page allocations are underway. This can cause the problems that are documented in the "Symptoms" section.

Note De-allocation activities must also modify the pages. This can contribute to the increased contention.

To learn more about the different allocation mechanisms that are used by SQL Server (SGAM, GAM, PFS, IAM), see the "References" section.

Resolution

To improve the concurrency of tempdb, try the following methods:

  • Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code.
  • Consider implementing the best practice recommendations in the following TechNet topic:
     
    Working with tempdb in SQL Server 2005
  • If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages.

    Notes
     
  • Apply the relevant CU for SQL Server 2016 and 2017 to take advantage of recent update. An improvement has been made that further reduces contention in SQL Server 2016 and SQL Server 2017. In addition to the round-robin allocation across all tempdb data files, the fix improves PFS page allocation by performing round-robin allocations across several PFS pages in the same data file. For more information, see the following content:
     

More information

Increase the number of tempdb data files that have equal sizing

As an example, if the single data file size of tempdb is 8 GB, and the Log file size is 2 GB, the recommendation is to increase the number of data files to eight (8) (each of 1 GB to maintain equal sizing), and leave the log file as is. Having the different data files on separate disks would be provide additional performance benefit. However, this is not required. The files can co-exist on the same disk volume.

The optimal number of tempdb data files depends on the degree of contention seen in tempdb. As a starting point, you can configure tempdb to be at least equal to the number of logical processors that are assigned for SQL Server. For higher-end systems, the starting number could be eight (8). If the contention is not reduced, you may have to increase the number of data files.

We recommend that you use equal sizing of data files. SQL Server 2000 Service Pack 4 (SP4) introduced a fix that uses a round robin algorithm for mixed page allocations. Because of this improvement, the starting file is different for each consecutive mixed page allocation (if more than one file exists). The new allocation algorithm for SGAM is pure round robin, and does not honor the proportional fill to maintain speed. We recommend that you create all tempdb data files at the same size.
 


How increasing the number of tempdb data files reduces contention

The following list explains how increasing the number of tempdb data files that have equal sizing reduces contention:
  • If you have one data file for the tempdb, you only have one GAM page, and one SGAM page for each 4 GB of space.
  • Increasing the number of data files that have the same sizes for tempdb effectively creates one or more GAM and SGAM pages for each data file.
  • The allocation algorithm for GAM allocates one extent at a time (eight contiguous pages) from the number of files in a round robin fashion while honoring the proportional fill. Therefore, if you have 10 equally sized files, the first allocation is from File1, the second from File2, the third from File3, and so on.
  • The resource contention of the PFS page is reduced because eight pages at a time are marked as FULL because GAM is allocating the pages.
 

How implementing trace flag -T1118 reduces contention

The following list explains how using trace flag -T1118 reduces contention:
  • -T1118 is a server-wide setting.
  • Include the -T1118 trace flag in the Startup parameters for SQL Server so that the trace flag remains in effect even after SQL Server is recycled.
  • -T1118 removes almost all single page allocations on the server.
  • By disabling most of the single page allocations, you reduce the contention on the SGAM page.
  • If -T1118 is turned ON, almost all new allocations are made from a GAM page (for example, 2:1:2) that allocates eight (8) pages (1 extent) at a time to a object as opposed to a single page from an extent for the first eight (8) pages of an object, without the trace flag.
  • The IAM pages still use the single page allocations from the SGAM page, even if -T1118 is turned ON. However, when it is combined with hotfix 8.00.0702 and increased tempdb data files, the net effect is a reduction in contention on the SGAM page. For space concerns, see the next section.
Disadvantages


The disadvantage of using -T1118 is that you may see increases in database size if the following conditions are true:

  • New objects are created in a user database.
  • Each of the new objects occupy less than 64 KB of storage.

If these conditions are true, you may allocate 64 KB (8 pages * 8 KB = 64 KB) for an object that only requires 8 KB of space, thus wasting 56 KB of storage. However, if the new object uses more than 64 KB (8 pages) in its lifetime, there is no disadvantage to the trace flag. Therefore, in a worst case scenario, SQL Server may allocate seven (7) additional pages during the first allocation only for new objects that never grow beyond one (1) page.

References

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, refer to the following table.

Rule software Rule title Rule description Product versions against which the rule is evaluated
System Center Advisor SQL Server database configuration: Number of tempdb data files might cause blocking System Center Advisor checks the number of data files that are configured for the tempdb database. If there is only one, and the server that is running SQL Server uses more than one processor, this alert if generated. Review the information in this article, and add more data files to the tempdb database. SQL Server 2008
SQL Server 2008 R2
SQL Server 2012