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.
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 ???
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] :