Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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, 
db_name(a.database_id) AS dbname,, 
db_file_type =  CASE 
                WHEN a.file_id = 2 THEN 'Log' 
                ELSE 'Data' 
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location, 
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.

share|improve this question
Do you have any benchmark to compare to? Is the RAID itself performing fast enough for you? Any IO tools show you different result that you see from the DMV? – DenisT Jun 13 '14 at 13:07
You could use SQLio to test the performance of the SSDs in a pattern similar to SQL Server. – Max Vernon Jun 13 '14 at 18:10
RAID controller or software? If controller, is write cache enabled? If so, disable and test again. Does the controller support TRIM? – Mark Storey-Smith Jun 13 '14 at 19:03
how many processors you have ? what is allocation size on this F drive ? what is showing activity monitor for tempdb related activity ? You have OLAP or OLTP system ? – Kuba Miazek Jun 17 '14 at 18:53
Reviewers please note there are several unanswered questions in comments, hence the suggestion to put on hold until answered. – Mark Storey-Smith Jun 21 '14 at 13:00
up vote 4 down vote accepted

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.