The following script adopted from Brent Ozar’s blog on his TempDB Performance and Configuration topic. I strongly encourage you to pay a visit to his site.
This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:
SELECT files.physical_name, files.name, stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms, stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms FROM sys.dm_io_virtual_file_stats(2, NULL) as stats INNER JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id WHERE files.type_desc = 'ROWS'
Brent Ozar suggested to look for two things from the result generate from the script:
- Are writes being evenly distributed between data files?
- Are writes finishing in 20ms or less?
If either of those question is no, then we have some performance tuning work need to kick in.