Do I need to enable Trace Flag 1117 for equally sized data files?
What I can't understand is if data files are filling proportionally, won't they auto-grow proportionally either? In that case we can't omit using TF1117.


The proportional fill algorithm states that if you have multiple files in a file group and all have different free space, SQL Server will fill the space in the files such that it would try to fill more in the file which has larger space as compared to one having lower space. Example if you have 2 files in FG with 10 and 20 GB size respectively, with proportional fill for every 1 extent allocated to 10GB file 2 extent will be allocated to 20 GB file.

While TF 1117 says that if I am enabled and autogrowth happens I would force all files to expand equivalent to amount of data entered. For example if 100 MB of data is entered all files in a file group with TF 1117 enables will increase by 100MB this will not happen with proportional fill as described above.

More about Proportional Fill Algorithm

In most cases adding TF 1117 helps in reducing contention in tempdb but please note once enabled it applies to all the databases running on the system.

PS: If you have SQL Server 2016 and above you do not need to turn on TF 1117 and 1118.