Does tempdb recommended count depend on the total processors on the computer or on the processors in a single NUMA node?
-2
votes
1
answer
1110
views
> The number of secondary data files depends on the number of (logical)
> processors on the machine. As a general rule, if the number of logical
> processors is less than or equal to eight, use the same number of data
> files as logical processors. If the number of logical processors is
> greater than eight, use eight data files.
Link: https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#physical-properties-of-tempdb-in-sql-server
In server properties, processors section, I can see there are 2 NUMA nodes with 12 processors each. In the SQL error logs I can see:
So it looks like the computer has got 4 physical processor nodes, with 6 logical processors in each node. In this scenario what is the recommended value of number of tempdb and does it depend on the number of NUMA nodes or number of total logical processors or number of logical processors per NUMA node?
I am asking because the previous DBA has set the number of tempdb files to 6 (this is the number of logical processors in a single NUMA node). Whereas I understand based on the above MSDN link that the best practice is to configure the number of tempdb files as 8 or more in increments of 4 (12, 16, 20, 24).
SQL Server detected 24 sockets with 1 cores per socket and 1 logical processors per socket. 24 total logical processors.
Automatic soft-NUMA was enabled because SQL server has detected hardware NUMA nodes
with greater than 8 physical cores.


Asked by variable
(3590 rep)
May 15, 2022, 12:39 AM
Last activity: May 15, 2022, 01:29 AM
Last activity: May 15, 2022, 01:29 AM