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.


 

Leave a Reply

Your email address will not be published. Required fields are marked *