tempdb file number for many logical processor

sakuraime 2,316 Reputation points
2020-08-24T04:59:30.197+00:00

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.
12,607 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,071 Reputation points Microsoft Vendor
    2020-08-24T07:51:50.217+00:00

    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,716 Reputation points
    2021-04-15T13:12:21.387+00:00

    This is only important if you are having tempdb contention.

    Are you having tempdb contention?

    0 comments