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, minimising the need to scan the SGAM page.

💡 TF1117 & TF1118 are ignored in SQL Server 2016 and later.

Suggested Action

Enable TF1117 & TF1118 on startup using startup parameters: in SQL Server Configuration Manager, open the instance's properties and add -T1117 and -T1118 on the Startup Parameters tab.

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