Configure tempdb to reduce contention - AWS Prescriptive Guidance

Configure tempdb to reduce contention

We recommend that you configure tempdb with multiple data files of equal size and with equal growth factor.

On a busy database server that uses tempdb a lot, you might notice severe blocking when the server experiences a heavy load. You might notice that tasks are waiting for wait resources that point to pages in tempdb. These pages might be Page Free Space (PFS) and Shared Global Allocation Map (SGM) pages that have the format 2:x:x (for example, 2:1:1 or 2:1:2).

To improve the concurrency of tempdb, you can increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. Here are some guidelines:

  • If the number of logical processors is equal to, or less than, 8: Use the same number of data files and logical processors.

  • If the number of logical processors is higher than 8: Use 8 data files.

If contention persists, increase the number of data files in multiples of 4 until the contention is remediated, up to the number of logical processors on the server. This will help avoid SGAM contention in tempdb. If you’re using SQL Server 2014 or an earlier version, you also need to enable trace flag 1118. This flag forces page allocations on uniform extents instead of mixed extents, which minimizes scans on the SGAM page and reduces contention.

Starting with SQL Server 2016 (13.x), this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES options of ALTER DATABASE. For example: 

alter database <database name> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES

For more information about setting these options, see the Microsoft SQL Server documentation.