TRACE FLAG 1118 – Proportional Filling and the tempdb

Often when conducting trainings I ask my participants not to take my word for it but try things out for themselves. I was asked a question about the TRACE FLAG 1118 during a recent training for SQL 2012 and wanted to bring attention to the discussion here. The participants had read in a blog that the use of TRACE FLAG 1118 would ensure that all the files in the file group grew simultaneously when using this flag even when the Auto growth settings were different. I argued that the approach to ensure that this could be done was make sure the Autogrowth settings were the same for each file therefore eliminating the need for the trace flag and leveraging proportional filling in fact I went further to suggest that tempdb file sizes should be pre-allocated to reduce the impact of growth. Here is the proof.

This test was run on SQL 2012.

BEFORE THE TEST

WITH TRACE FLAG OFF


RUNNING THE QUERY AFTER SHRINKING THE DATABASE BUT WITH TRACE FLAG ON THIS TIME.


So my advice is to try an avoid trace flags when there are better options available. Mainly because every trace flag enabled is just one more deviation from a standard configuration. Besides using this flag is meant for the benefit it gives in another aspect for page allocations more than the need to grow files together. Here is a very good blog from Paul Randall discussing the internals.

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/