none
Number of Tempdb files RRS feed

  • Question

  • Hi,

    I have a quick question...

    My server has two 8 core processor. Should I use 8 or 16 data files for Tempdb?

    Many thanks,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Monday, August 24, 2015 4:57 PM

Answers

  • Microsoft's recommendation is to use 8 data files and then monitor for contention:

    "As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code."

    https://support.microsoft.com/en-us/kb/2154845



    SingleHop: A leader in Managed Azure
    Monday, August 24, 2015 5:20 PM
  • You should start with 8 and then monitor to see if you see any contention.

    Read these

    http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, August 24, 2015 6:53 PM

All replies