Symptoms
- 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.
Cause
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:
- 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
- This trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see the "Monitoring contention caused by DML operations" subtopic under Working with tempdb in SQL Server 2005 page on Technet.
- Starting in SQL Server 2016, some of these configuration changes are automatic and do not need user intervention. For more information, see the following CSS SQL Server Engineers Blog articles:
- 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:
- Microsoft Knowledge Base:
4099472 PFS page round robin algorithm improvement in SQL Server 2014, 2016 and 2017
- MSSQL Tiger Team Blog:
Files and trace flags and updates in SQL Server tempdb
- Microsoft Knowledge Base:
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
- 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
- -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.
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
- 328551 Concurrency enhancements for the tempdb database
- TempDB Monitoring and Troubleshooting: Allocation Bottleneck
- Managing TempDB in SQL Server: TempDB Configuration
- SQL Server TempDB – Number of Files – The Raw Truth
- SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
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.