May 18, 2015 at 7:52 am
I have a 2008r2 box that houses the databases for WSUS and SCOM. It originally had only 1 tempdb file and the avg_write_stall_ms was around 400ms. I was asked to add 3 more tempdb files which I did with no problem. Now, all 4 of the tempdb's have an avg_write_stall_ms of over 700ms!! Why would it go up?
May 18, 2015 at 9:40 am
The write stall has more to do with how your storage is performing than with the number of tempdb files.
Adding tempdb files is typically something done to relieve latch contention on the PFS, GAM, and SGAM pages, although if you add a file on a drive that is backed by a different set of disks than the other files, you could potentially get some relief from spreading out the IO.
Assuming the multiple tempdb files in your case are backed by the same set of disks, it's possible that adding the tempdb files reduced other sorts of contention (like the latch contention mentioned previously), which allowed the instance to push more writes to disk. That could lead to a jump in latency.
Without more information, though, this is all just speculation.
To really understand what's happening would require having stats on the IO against tempdb before and after the change, wait stats before and after the change, and some information about the hardware backing the tempdb files.
I hope this helps!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply