2

I'm experiencing large write latency on our tempdb database, but on no other database. (from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms.

For comparison: Write latency on all databases except tempdb are below 5ms, tempdb was about 500ms when using 8 datafiles. I removed 4 datafiles 2 days ago just to check if something changes and it looks like the latency doubled after doing this.

A few notes on our environment:

  • SQL Server 2008 R2 SP3 VM running on Hyper-V
  • one large RAID10 array attached to our Hyper-V hosts via fibre channel where everything is stored

I have no explanation for this and I don't know where to look for answers. I could of course assume that doubling the data files will half the WriteLatency but I don't know if this is the way to go.

Any ideas on where to start?

Improve this question
4
  • Can you see with sp_whoisactive the process using the tempdb?
    – Ronaldo
    Apr 22, 2020 at 12:08
  • 1
    In order to better observe the behavior of tempdb specifically, I'd suggest putting Extended Events to work. Great post on monitoring tempdb from William Durkin: williamdurkin.com/2015/09/who-blew-up-my-tempdb
    – Grant Fritchey
    Apr 22, 2020 at 12:25
  • Also, look at Jes Borlands script on object creation in tempdb: github.com/grrlgeek/extended-events
    – Grant Fritchey
    Apr 22, 2020 at 12:26
  • @GrantFritchey Thanks for the useful links. The first one only reacts on database growth I believe which I disabled for tempdb-data-files and presized them. I will have a look a those scripts and see if they will also work with 2008 R2, the README says they are for SQL Server 2012+. Thanks so far, I will report back if I find something.
    – Steffen
    Apr 23, 2020 at 11:25

1 Answer 1

Reset to default
3

I seem to have solved it or at least know where it is coming from.

To find out I setup perfmon to monitor avg. ms per write operation and it looks like this. which leads me to the conclusion that those extreme spikes only happen during nightly index rebuilding (I'm using Ola Hallengrens IndexMaintenance-solution for this), normal operation is fine.

My learning: don't rely on cumulated stats in sys.dm_io_virtual_file_stats only, but they are a good indicator to look further for the origin.

Improve this answer
1
  • Great solution. Well done.
    – Grant Fritchey
    Apr 24, 2020 at 10:48

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.