2

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 enter image description here

0
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.