Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Jul 29 '11 at 14:00

This question came from our site for professional system and network administrators.

2 Answers 2

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.

share|improve this answer
    
I have the statistics that you suggested being gathered and they were being gathered at the time of the errors so I can look into that. I'll do some analysis and see if I can fix it. Thank you for your response. –  Aushin Jul 29 '11 at 14:11
    
I can't seem to upvote you here. On ServerFault I could've. –  Aushin Jul 29 '11 at 14:11
    
Because he's not registered here. –  jcolebrand Jul 30 '11 at 4:38
    
Please consider trying the upvote again, as I've just registered here. Thx. –  darin strait Feb 20 '12 at 11:50

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) ;
share|improve this answer

Your Answer

 
discard

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.