asked
TempDB Write Latency
I have been noticing errors such as below when DBCC CheckDB is running each night. SQL Server has encountered 17 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [I:\MSSQL10_50.SQL08\MSSQL\DATA\tempdb3.ndf] in database [tempdb] (2). The OS file handle is 0x00000000000014 I have run a query and notice the WriteLatency for TempDB is very high. Is there anything that can be done to stop this from happening? This really only happens during CheckDB but it is very frustrating to always see this error. ReadLatency WriteLatency DB 4 3598 tempdb 4 3589 tempdb 3 3566 tempdb 4 3552 tempdb
Latency is going to come from four possible locations. 1) You have contention on the resource. Lots of stuff is writing to the disk and it's slowing things down. 2) Something is wrong with the disk. You may need to run checkdsk on it. 3) Something is wrong with your disk sub-system. Fiber channel or switch or something has gone bad. 4) Network latency is an issue and this is a network disk.
The thing I find very strange is the Write Latency for User databases is not higher than 30 but TempDB is 3000.
Excellent answer. When looking at latency, examine where the bottleneck is. I normally find it in contention for the resource with other things working. When that is the case, using faster disks (such as changing to an SSD) can improve the speed of the disk overall and help with the issue. That will not help if the bottleneck is in the network though.
There could be some problem with TempDB. You can restart the server(if possible) to go for new TempDB or choose a drive which is different from User Databases. Specify more space for TempDB files. Also, run below commands to see if there are some issues:
http://blog.sqlauthority.com/2007/11/13/sql-server-dbcc-checkdb-introduction-and-explanation-dbcc-checkdb-errors-solution/ Also, see whether any transactions is being held for a long time and causing write contention. Refer to articles on optimizing TempDB performance:
http://www.confio.com/logicalread/sql-server-tempdb-best-practices-initial-sizing-w01/#.UuNRgCe6biw &
technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx &
http://www.mssqltips.com/sqlservertip/1388/properly-sizing-the-sql-server-tempdb-database/
Hi, Just wondering did you ever resolve this as I am currently seeing a similar issue. I recently added a new Raid 10 T: drive to a server , previously the tempdb was sharing a drive with data files , it had total latency of about 15 ms , write 29 ms ,, I moved the 4 tempdb files(i have 8 CPUs) to the new drive sized at 5gb , they have not grown since there is nothing else on the drive , the read performance is excellent < 2ms however the write performance is 1580ms , i am guessing something is wrong with the disk config but I cant be sure.. anyone any ideas ???
You'll get a lot more responses by posting this as a new question rather than as an answer to an existing question. The only people who will see this are the ones who have answered or commented on this question. Posting a new question will get a bigger audience. It does sound like a disk configuration issue. I'm not an expert in that area, so I won't be able to help.
I'm having a similar issue, has anyone found a cause for this high write latency and low read latency? I have a brand new SSD shelf and we are seeing this on brand new drives.
SSD are known for there faster read operation for being in IC chip set. Can you elaborate more how your TempDB files looks like (i.e. total number etc)
Let's go on this issue step by step: 1. Though RAID 1+0 (10) what type of disk they are? i.e. normal SATA, SCSI, iSCSI, FC etc. 2. What is the disk RPM? i.e speed of the disk 7200 RPM, 10K rpm etc 3. What is the Logical write and physical write of the disk? 4. what is the Average Disk Queue length (You may try executing perf mon counters for step 3 & 4. By doing above, what we are trying to achieve is to ensure that I/O subsystem is perfectly fine or there is a problem there itself? If the above are fine we can also try using SQLIOSIM utility for which you can read more from this [Technet Article][1] [1]:
http://support.microsoft.com/kb/231619
20 People are following this question.
