Avg write latency spikes for disk more than usual. Need help finding the culprit 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.
My question is how can I check what in TempDB if it is would have
caused this spike?

To analyze what impact those queries have on tempdb you could use sp_whoisactive since one of the info it outputs is the use of tempdb:


The most confusing of these columns are those related to tempdb. Each    of the columns reports a number of 8 KB pages. The
[tempdb_allocations] column is collected directly from the
tempdb-related DMVs, and indicates how many pages were allocated in
tempdb due to temporary tables, LOB types, spools, or other
consumers. The [tempdb_current] column is computed by subtracting the
deallocated pages information reported by the tempdb DMVs from the
number of allocations. Seeing a high number of allocations with a
small amount of current pages means that your query may be slamming
tempdb, but is not causing it to grow. Seeing a large number of
current pages means that your query may be responsible for all of
those auto-grows you keep noticing.


Another way to show that you have a tempdb related problem is having sp_BlitzCache™ Result: TempDB Spills. As that page says:

To find the queries that are causing spills, you have several options
– you can capture and examine actual execution plans, or you can
collect exchange spill events through extended events. Paul White
covers your options in his reply to What frequency of hash/sort
spills into tempdb is
concerning?



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.

Certainly yes. If you check the tempdb database doc, you'll see it stores the following objects:


Internal objects that the database engine creates. They include:
Work tables to store intermediate results for spools, cursors, sorts,    and temporary large object (LOB) storage. Work files for
hash join or    hash aggregate operations. Intermediate sort
results for operations    such as creating or rebuilding indexes (if
SORT_IN_TEMPDB is    specified), or certain GROUP BY, ORDER BY, or
UNION queries.