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?