5

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,

Improve this question

2 Answers 2

Reset to default
4

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

Improve this answer
4
  • Thanks for the links. It would be great if that trace flag could be configured for just one database
    – Stuart Blackler
    Jul 15, 2012 at 19:56
  • @StuartBlackler yes, but if you size your tempdb large enough, you shouldn't need to rely on the autogrow at all - it should only happen in an extremely abnormal and unplanned scenario. After all, for what purpose are you saving the space that tempdb might use? You can't really use it for other things, so why not just pre-size tempdb to the max?
    – Aaron Bertrand
    Jul 15, 2012 at 20:00
  • Typically in the environments I work in, SQL Server is generally placed on a multi-purpose server. So SBS might be on the box as well so I have to keep these things in mind. I am definitely an accidental dba (developer by trade) and your comments are much appreciated. I will have to come up with sensible defaults for each environment. Thanks
    – Stuart Blackler
    Jul 15, 2012 at 20:10
  • @StuartBlackler but still I hope my point hits home - if you're reserving space for SBS and it uses that space, you're screwed when tempdb has to autogrow anyway. So leaving a volume half empty and having a race to see which database uses the space first is a very dangerous gamble.
    – Aaron Bertrand
    Jul 15, 2012 at 20:12
4

Just an additional note, be sure to turn on instant file initialization. That means that if your databases grow, they grow much faster.

Improve this answer
2
  • Got that down on my to-do list. Sys-admins are going to love me on Tuesday when im back in the office :D Thanks for the link.
    – Stuart Blackler
    Jul 15, 2012 at 20:18
  • +1 it's a good point, I'm so used to working in environments where this was configured long ago, it's not the first thing that comes to mind. :-)
    – Aaron Bertrand
    Jul 15, 2012 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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