We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see
tempdb around avg 32 ms wait on write. Also over the week
tempdb usage I mean available free space has come down under 50 GB from 300 GB.
I am suspecting this spike could be from
tempdb. The environment is a mix of OLTP and warehouse people querying data for large select when looking for real time data in case not available from LS standby.
My question is how can I check what in
tempdb if it is would have caused this spike?
Also can those select queries also contribute to
tempdb write latency? These select queries are run on tables over TBs of data and user can have part of queries like order by and group by from what I have seen. But I am not sure how to prove they are contributing to sudden spike.