1

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:

  1. Is this a limitation with activity monitor being unable to discern the difference between the files or are they all actually being written to?
  2. Does this indicate the disk I/O rate is actually the limiting factor, or is there something else going on?
  3. Does whatever this issue is occur in newer versions of sql server?

Estimated execution plan of the query I believe to be causing the issue

Actual execution plan including the query I think is the main one causing experiencing the issue

Improve this question
7
  • "the issues seem to coincide with all four of the tempdb files being written to at the same high rate" - What rate (roughly) are they being written to? Is it the same single or few queries that are writing to tempdb and can you add those queries with their execution plans to your post please? What are the specs for the disk(s) your tempdb, database data file, and database log file live on?
    – J.D.
    Commented Oct 11, 2022 at 12:00
  • It's 10-20MB/s each. I'm not quite sure how to find the queries writing to each file, but the queries being written to tempdb are ** Restricted Text **. I'll anonymise an execution plan and add it.
    – Richard Harris
    Commented Oct 11, 2022 at 12:25
  • "I'm not quite sure how to find the queries writing to each file" - Then how did you determine "the issues seem to coincide with all four of the tempdb files being written to at the same high rate"?
    – J.D.
    Commented Oct 11, 2022 at 12:26
  • I can see when the waiting queries start piling up and I was watching the activity monitor at the time. I also cannot adequately anonymise the execution plan so I don't think I can post it.
    – Richard Harris
    Commented Oct 11, 2022 at 12:41
  • "I can see when the waiting queries start piling up" - How?...surely whichever tool you're using will tell you what they're waiting on, which should lead to the queries writing to tempdb if that's the issue. sp_WhoIsActive will give you better details on this. Also, you can use SentryOne Plan Explorer to automatically anonymize your execution plans.
    – J.D.
    Commented Oct 11, 2022 at 12:47

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.