Enable TF1117 & TF1118

TF1117 - When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. This behavior 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

Suggested Action

Enabled TF1117 & TF1118 as startup parameters.

Any suggestion from Aireforge should be thoroughly tested before being implemented into production.

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