Enable TF1117 & TF1118

TF1117 - When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. This behaviour will be enabled for all databases, not just tempdb.

TF1118 - Reduces contention on the SGAM page as new by allocating all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page.

Enabling TF1118 in SQL Server Configuration

đź’ˇ TF1117 & TF1118 are ignored in SQL Server 2016 and later.

Suggested Action

Enable TF1117 & TF1118 on startup using startup parameters.

Further Reading

​Recommendations to reduce allocation contention in SQL Server tempdb database | Microsoft Docs

​SCM Services - Configure Server Startup Options | Microsoft Docs

​DBCC TRACEON - Trace Flags (Transact-SQL) | Microsoft Docs