SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

Question

Three separate processes have failed in the last two days with very similar errors:

Time-out occurred while waiting for buffer latch type 4 for page (1:1189832), 
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827), 
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827), 
database ID 2.

Two of them are exactly the same, the other is accessing a slightly different pagenum. Database #2 is tempdb. I tried to run DBCC PAGE(tempdb, 1, 1189827, 1) but the output is just gibberish to me. I'm fairly lost.

Does anyone know what the root cause of these timeouts might be? (I've seen dozens of suggestions on other forums, among which was memory corruption)

Any help would be greatly appreciated. If you would like to read the DBCC PAGE output, ask and I will post it.

Answer 1

By the time that you whip out dbcc page and try to look at the page, it's possibly/probably being used for something else anyway. It is tempdb, after all.

I've been at this since SQL 6.5 and I have only ever seen these sorts of page timeouts caused by slow or misconfigured storage. Every incident I can remember offhand, it's been a SAN. If you have a SAN, you should definitely talk to your SAN guy/gal and find out if they have been seeing unusual load on the system or if they have changed anything lately. I've seen problems with flaky fiber switches and cables, for example, or maybe they moved your data onto slower disks lately.

Beyond that, Have a hard look at your drives, SAN or otherwise. Look at the "Avg. Disk Sec/Read" and "Avg. Disk Sec/Write" numbers for the drive that the tempdb MDF file is stored on while stuff is happening, preferably while you are getting errors. It's helpful to look at the bytes/second numbers as well, to get a feel for what the drives are being asked to do (a drive with 1000 ms latency numbers that is being asked to push only 1 MB/sec is one thing, a drive with 50 ms latency when pushing at 10 MB/s is another.)

If the drive is overwhelmed, you may need to reduce the I/O going to the drive. Tactics would include moving tempdb files to another device, creating additional tempdb data files on other devices, tuning queries (any unnecessary GROUP BY or DISTINCT?), moving any data files that are also on that storage device to somewhere else, etc.

Answer 2

If your running 2005+, run the two scripts below and add the output to your original question.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
                                                   + num_of_writes) AS NUMERIC(10,
                                                              1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf
ON  fs.database_id = mf.database_id
    AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

WITH Waits AS 
(
SELECT
wait_type
, wait_time_ms / 1000. AS wait_time_s
, 100. * wait_time_ms / SUM(wait_time_ms) OVER () AS pct
, ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
FROM
sys.dm_os_wait_stats
WHERE
  wait_type NOT IN (
  'CLR_SEMAPHORE',
  'LAZYWRITER_SLEEP',
  'RESOURCE_QUEUE',
  'SLEEP_TASK',
  'SLEEP_SYSTEMTASK',
  'SQLTRACE_BUFFER_FLUSH',
  'WAITFOR',
  'LOGMGR_QUEUE',
  'CHECKPOINT_QUEUE',
  'REQUEST_FOR_DEADLOCK_SEARCH',
  'XE_TIMER_EVENT',
  'BROKER_TO_FLUSH',
  'BROKER_TASK_STOP',
  'CLR_MANUAL_EVENT',
  'CLR_AUTO_EVENT',
  'DISPATCHER_QUEUE_SEMAPHORE',
  'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'XE_DISPATCHER_WAIT',
  'XE_DISPATCHER_JOIN',
   'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
 )
SELECT
    W1.wait_type
  , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
  , CAST(W1.pct AS DECIMAL(12, 2)) AS pct
  , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM
    Waits AS W1
INNER JOIN 
    Waits AS W2
ON  W2.rn <= W1.rn
GROUP BY
    W1.rn
  , W1.wait_type
  , W1.wait_time_s
  , W1.pct
HAVING
    SUM(W2.pct) - W1.pct < 99
OPTION (RECOMPILE) ;