patternMinor
SQL Server 2012 Local SSD TempDB Slow
Viewed 0 times
local2012ssdtempdbsqlslowserver
Problem
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
Below are the top 5 rows returned
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.
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 DESCBelow 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 1So 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.
Solution
We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO.
We are now in the good place of reads ~ 2ms and Writes at <= 5ms
We are now in the good place of reads ~ 2ms and Writes at <= 5ms
Context
StackExchange Database Administrators Q#68198, answer score: 4
Revisions (0)
No revisions yet.