I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states:
When a file in the filegroup meets the autogrow threshold, all files
in the filegroup grow. This trace flag affects all databases and is
recommended only if every database is safe to be grow all files in a
filegroup by the same amount.
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.
EDIT
Here is the version of SQL Server
Best Answer
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.