Trace flag 1117 will allow us to take care of tempdb contention. It permits us to grow our data files at the same time.
We also use 1118 to prevent tempdb contention. Trace flag 1118 deals with how we allocate extents whether it be mixed extents or uniform extents
These trace flags are enabled by default in SQL Server 2016. For prior versions, you have to manually turn-on these trace flags.
Below are the ways to enable Trace Flags:
To Enable Trace Flags at Startup
Go to Configuration Manager > Select your instance > Choose Properties > Go to Startup Parameters tab.
Enable Trace Flags with DBCC TRACEON/TRACEOFF
Add the -1 to enable it globally on the instance
DBCC TRACEON( 1117, -1) DBCC TRACEON( 1118, -1)
To see which trace are enabled:
DBCC TRACESTATUS
