How To Check TempDB Speed in SQL Server ?

---

Below script provides you details about TempDB data files and shows how fast TempDB responding to write and read requests in SQL Server.

---

---

SELECT files.physical_name,
files.name,stats.num_of_writes,
(1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write__ms,
stats.num_of_reads,(1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_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'

 

---

---

If read and writes finishing in 20ms or less then TempDB performance is ok.
if it’s not then it is a problem and we have to do some performance tuning on SQL Server.

---