SQL Server Disk Latency – Find the Culprit for Avg Write Latency Spikes

sql serversql-server-2012t-sqltempdb

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.

Best Answer

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.
Related Question