should i make as many datafiles for temdb as many cpus i have.

Question

hi,

q1 As people said if u ghave GAM conention then u can go for adding as many datafiles of equal size in temdb as many cpus u have is it correct in sqlserver 2019 vesion also.

q2) right click on sqlserver instance then select reprots->perfomance dashboard report then click io statistics . i have attched mine , i have read that it should be less than 10 15 ms per transact(avrage of read and wirte). pls correct me.

yours sincerley

Answer 1

Q1) What is the correct number depends on your workload, but having one file per CPU if you have 256 cores is unlikely to be a good idea. I've seen some formulas that I don't recall exactly. But when you install SQL Server, you can configure up front how many tempdb files you want. There is a default which is computed from what hardware you have. If still in doubt, go for eight files, but not more, unless you are actually seeing GAM contention.

Q2) Your attachment did not make it, so it is not clear what values you think should be less than 10-15 ms. But, yes, if the average time for a read or write operation exceeds 15 ms, this suggests that your I/O operation is not up to speed. This value of 15 ms comes from the access time you can expect on a spinning disk. These days we mainly have SSDs, and the access time in this case is often below the millisecond. But on the other end, we don't have a lot of local disks in our servers these days, but they are in a SAN, and the SAN and the network route to the SAN can be overtaxed, leading to access times way above 15 ms. Something from the perspective of a DBA is nothing acceptable.

Answer 2

Rajesh Kumar Yadav

q1：

The optimal number of tempdb data files depends on the degree of contention seen in tempdb.

q2：

Ideal I/O Latency Time

General guidelines:

1. Read Latency: Ideally, the latency time for each read operation should be less than 10 milliseconds. For high-performance systems, it's even desirable to achieve latency within 5 milliseconds.
2. Write Latency: Write operations are generally slightly slower than read operations, but ideally should also be within 10 milliseconds.

• Avg. Disk sec/Transfer The average time taken for each disk read or write operation.
• Avg. Disk sec/Read The average time taken for each disk read operation.
• Avg. Disk sec/Write The average time taken for each disk write operation.

Since these three values can effectively reflect the I/O speed of the disk, they are often used to measure disk speed.

The reference values are as follows:

• Excellent: < 10 ms
• Average: 10 ~ 20 ms
• A bit slow: 20 ~ 50 ms
• Very slow: > 50 ms

Best Regards,

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.