Tempdb autogrowth settings Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files?

I ask because I would like to let SQL Server use the round-robin algorithm to spread the workload out across each of the files. I understand that if the files are not of equal size, then SQL Server will use the file with the most space, thus increasing the chances on causing latch contention. Is my understanding here correct?

Cheers,
You could run this against each file, replacing each n with the size and growth amounts:

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev2,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

...


Now to ensure that they all grow at the same time (instead of only one of them growing, e.g. in response to a large spill or other operation), then you can enable trace flag 1117, but keep in mind that this flag applies to all databases, so if tempdb isn't your only database with more than one data file, you'll want to test this behavior. More info:

http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Tempdb-configuration-survey-results.aspx