Script to Check TempDB Speed

On 14 Jan, 2016

MS SQL DBA

By : Charith Silva

No Comments

Below script shows 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'

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'

We’re looking for two things:

Are writes being evenly distributed between data files?
Are writes finishing in 20ms or less?
If the answer is no to either of those questions, we’ve got some performance tuning work to do.

Dynamic management function (DMF) sys.dm_io_virtual_file_stats returns I/O statistics for data and log files.

Source : http://www.brentozar.com/sql/tempdb-performance-and-configuration/

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva