I have a production deployment using local SSDs for tempDB. I have 2 SSDs in a RAID1 configuration. I am seeing average reads of 1-2ms but the average writes are showing as 1377ms on all four of my tempdb data files.
Each tempdb data file is 2GB with a 1GB growth setting (They haven't grown since deployment 5 months ago)
The tempdb log is showing average read 67ms and average write 215ms.
The SSDs are Samsung 840 pros.
The following code is what I use to get my stats
SELECT a.database_Id,
a.file_id,
db_name(a.database_id) AS dbname,
b.name,
db_file_type = CASE
WHEN a.file_id = 2 THEN 'Log'
ELSE 'Data'
END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location,
a.io_stall,
a.io_stall_read_ms / Case When a.num_of_reads = 0 Then 1 Else a.num_of_reads end AvgRead,
a.io_stall_write_ms / Case When a.num_of_writes = 0 Then 1 Else a.num_of_writes end AvgWrite,
Cast(Round(((( a.size_on_disk_bytes / 1024 ) / 1024.0 ) / 1024), 2) as float) AS size_on_disk_gb
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id AND a.database_id = b.database_id
ORDER BY a.io_stall DESC
Below are the top 5 rows returned
database_Id file_id dbname name db_file_type disk_location io_stall AvgRead AvgWrite size_on_disk_gb
2 1 tempdb tempdev Data F: 19782846713 2 1377 2
2 3 tempdb tempdev2 Data F: 19782655021 2 1377 2
2 5 tempdb tempdev4 Data F: 19782364070 2 1377 2
2 4 tempdb tempdev3 Data F: 19782151571 2 1377 2
2 2 tempdb templog Log F: 378829065 67 215 1
So my tmepdb files on SSDs are the slowest drives I have. Anything I should be looking at from a configuration/infrastructure point of view? I am currently studying the applications usage of tempdb and any memory spills but I'm not seeing anything terrible.
