‘Working’/problem/Struck/…/…// with ‘tempdb’ in SQL Server
#‘Recommendations’ to reduce allocation contention in SQL Server ‘tempdb ‘database
#Only One’ tempdb’ Data File (Are you sure..??)

Introduction: 

Have you ever been to any summit, lets spare out some useless directions. I ask you again: Have you ever been to technical summit??. I am sure if you were you must have felt stresses with the only way to reach a destination and then being in the queue to reach the seminar hall.

Imagine the same with the SQL server,Temp DB and Queries. Limite number of ways to reach to the same destination (TempDB: .mdf) queries need to be in the queue to get the required data or the required resource

Imagine the same with the SQL server,Temp DB and Queries. Limite number of ways to reach to the same destination (TempDB: .mdf) queries need to be in the queue to get the required data or the required resource.

 So today we are going to make ourselves expert in the existing confusing resourceful world.

  

Summary: 

SQL Server uses database called tempdb to handle a lot of system tasks : a shared workspace (as well as user-created temp tables.) SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. 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.

Detailed Explanation:

 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. 

Bottom Root cause :

•  Non Uniform Growth
• Insufficient disk I/O response 
• The regular visit to the SGAM Page for the allocation.
• The frequent requirement to visit the PFS page to find the page free space.
• Limited number of concurrent GAM, SGAM basically limited number of files.

The Figure below explains the scenario where the TempDb files are not configured to grow equally and this uneven growth enforces more free file to be utilized more slowing the performance. 

Remedy; (Get rid of it..! At Least When you know you can ..Right!!)

• 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.

• Use trace flags:T-1117 & T-1118 -T1117 - When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points. -T1118 - When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations 

  

• Make each data file the same size; this allows for optimal proportional-fill performance.

References: 

• 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 / Microsoft 1 / Microsoft 2