Share via

tempdb file number for many logical processor

sakuraime 2,331 Reputation points
Aug 24, 2020, 4:59 AM

I am not sure what does it means :

for example, currently I have 8 tempdb files and I have 16Cores , when I see contention on tempdb , i need to set 32 (8x4) tempdb files ????

19658-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,063 questions
0 comments No comments
{count} vote

2 answers

Sort by: Most helpful
  1. CathyJi-MSFT 22,316 Reputation points Microsoft Vendor
    Aug 24, 2020, 7:51 AM

    Hi sakuraime,

    No. If your system has more than 8 logical processors, start with 8 data files and monitor your server’s workload to determine if more data files would be beneficial. If you do find that an increase is warranted, add 4 data files at a time, but do not add more than the number of logical processors. Please refer to How Many tempdb Data Files Should My SQL Server Have?

    The current best advice around adding tempdb data files is enshrined in KB article 2154845.

    If the response helped, do "Accept Answer" and upvote it.

    Best regards,
    Cathy

    1 person found this answer helpful.

  2. Tom Phillips 17,741 Reputation points
    Apr 15, 2021, 1:12 PM

    This is only important if you are having tempdb contention.

    Are you having tempdb contention?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.