question

David 2 1 avatar image
David 2 1 asked

High TempDB io_stall_write_ms And Write Latency?

Hi there, Running the below query I have noticed a high level of io_stall_write_ms and write latency. What would be considered a normal statistic for this? The server was reboot only 11 days ago, which I assume would reset these stats, so is this write latency not disproportionately high? ![alt text][1] TIA [1]: /storage/temp/4641-writelatency.jpg
tempdbsql-server 2014latency
writelatency.jpg (42.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
That depends on the storage under the hood. However, is everything on the C: drive along with tempdb? If so, that could be causing part of the problem as everything is in contention for I/O. Those values are high in my opinion and I would look towards 1) any missing indexes in other databases that could help reduce the I/O and 2) the storage and see what can be tweaked. Hope that helps!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM, many thanks again. I do believe that the problem is IO contention as all the databases were set up on the same volume with RAID10. However I am trying to investigate that this contention issue is definitely causing the latency and not some other underlying issue. I will post the query I used in Tim's answer below.

0 Likes 0 ·
Tim avatar image
Tim answered

so is that a cumulative query? I'd be more interested in what your latency is during normal business hours. Lots of things can trash tempdb and you don't really care because they happen off hours. Index maintenance, ETL processes, etc. Take a look at Paul's script that collects latencies over time, change the waitfordelay to something that works for you. https://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/ But yes, over 1000ms for writes is very bad in most situations.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim, many thanks. The query I used is cumulative from the last restart I believe. I will ty your recommendation. The query I used was based on:

SELECT [io_stall_write_ms],
[num_of_writes],
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]

ORDER BY [io_stall_write_ms] DESC

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.