We recently added some secondary tempdb files to reduce allocation contention, this seems to have worked; however we are now getting lots of slowdowns related to I/O (WRITELOG and SQLTRACE_FILE_BUFFER waits specifically).
Having had a look around the issues seem to coincide with all four of the tempdb files being written to at the same high rate. I'd expected the reads and writes to the various tempdbs to be independant, so my questions are:
- Is this a limitation with activity monitor being unable to discern the difference between the files or are they all actually being written to?
- Does this indicate the disk I/O rate is actually the limiting factor, or is there something else going on?
- Does whatever this issue is occur in newer versions of sql server?