I am facing problems with tempdb please help and provide your suggestions.

Initially, I have found tempdb contention problem in my server (Sql server) by using Simple talk Script. By using that script results i have found tempdb contention is existed. So i have started adding more tempdb files. I have added 8 files since i have 24 logical cores.

I have used below query to find cpu count.

SELECT cpu_count,hyperthread_ratio FROM [sys].[dm_os_sys_info] 

It returns

Cpu_Count-24 hyperthread_ratio -6

After i have added more tempdb data files it resolves tempdb contention problem. I used to monitor Write/Read latency of each Database regularly.

Now i am seeing more write latency in tempdb it is in multiplies of 1000 for all files.

Before adding files i have never seen tempdb latency like above mentioned in my sql server

I have used Paul Randal script to monitor latency.

So please help me to solve this problem. Why latency is more?. Is anything wrongly i did?

Note:- Data files(All databases) are located in one separate drive.

  • 4
    Perhaps your IO subsystem cannot handle the load you are throwing at it. Check the disk queue lengths in PMON. – Dan Guzman May 30 '17 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.