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.

T F1118

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