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]
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.