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

Rajesh Kumar Yadav 20 Reputation points
2024-06-23T09:07:08.07+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,268 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2024-06-23T09:42:55.6833333+00:00

    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.


  2. MikeyQiaoMSFT-0444 1,750 Reputation points
    2024-06-24T09:47:45.9133333+00:00

    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.