locked
Temp DB size RRS feed

  • Question

  • Hi, all,

    We just installed a new program which uses SQL 2008 R2 DB. One of the recommended specs was 40 GB of temp DB size. Can anyone please explain what it meant by that?

    What I have now is quadcore, dual procs server, tempdb has 1 temp primary file and 9 tempdev.ndf files. Each started with 1 GB initial size and unrestriced autogrowth.

    Thank you very much in advance.

    Monday, October 20, 2014 7:55 PM

Answers

  • The multiple files reduces the tempdb storage contention adding multiple files definitely a overhead. 

    Good practice is to create one data file for each CPU on the server 

    I would recommend you to go through the below link.

    http://msdn.microsoft.com/en-us/library/ms175527(SQL.100).aspx

    --Prashanth

    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:18 PM
    Answerer
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:27 PM
  • Hi, all,

    We just installed a new program which uses SQL 2008 R2 DB. One of the recommended specs was 40 GB of temp DB size. Can anyone please explain what it meant by that?

    What I have now is quadcore, dual procs server, tempdb has 1 temp primary file and 9 tempdev.ndf files. Each started with 1 GB initial size and unrestriced autogrowth.

    Thank you very much in advance.

    It means the disk in which tempdb files are stored should have 40 GB space reserved for file growth. It means you should expect it to grow up to 40 GB. This is probably for security reasons.


    • Edited by Samir Abrahao Monday, October 20, 2014 8:42 PM
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:41 PM
  • For high performance tempdb data and log should be on independent drives.

    Disk configuration: http://www.sqlusa.com/bestpractices/configureharddisk/

    >The multiple files reduces the tempdb storage contention adding multiple files definitely a overhead. 

    Any documentation on that? Putting 10 tempdb files on the same (slow) drive will not improve performance to a significant degree.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014







    • Edited by Kalman Toth Monday, October 20, 2014 9:05 PM
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:58 PM

All replies

  • The multiple files reduces the tempdb storage contention adding multiple files definitely a overhead. 

    Good practice is to create one data file for each CPU on the server 

    I would recommend you to go through the below link.

    http://msdn.microsoft.com/en-us/library/ms175527(SQL.100).aspx

    --Prashanth

    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:18 PM
    Answerer
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:27 PM
  • Hi, all,

    We just installed a new program which uses SQL 2008 R2 DB. One of the recommended specs was 40 GB of temp DB size. Can anyone please explain what it meant by that?

    What I have now is quadcore, dual procs server, tempdb has 1 temp primary file and 9 tempdev.ndf files. Each started with 1 GB initial size and unrestriced autogrowth.

    Thank you very much in advance.

    It means the disk in which tempdb files are stored should have 40 GB space reserved for file growth. It means you should expect it to grow up to 40 GB. This is probably for security reasons.


    • Edited by Samir Abrahao Monday, October 20, 2014 8:42 PM
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:41 PM
  • For high performance tempdb data and log should be on independent drives.

    Disk configuration: http://www.sqlusa.com/bestpractices/configureharddisk/

    >The multiple files reduces the tempdb storage contention adding multiple files definitely a overhead. 

    Any documentation on that? Putting 10 tempdb files on the same (slow) drive will not improve performance to a significant degree.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014







    • Edited by Kalman Toth Monday, October 20, 2014 9:05 PM
    • Marked as answer by slho Monday, October 20, 2014 10:53 PM
    Monday, October 20, 2014 8:58 PM