SQL Server Performance Forum – Threads Archive

Latch contention – avg latch wait > 2000 ms

I’m trying to sort out a SQL 2000 database. 70Gb size. Running slowly and no improvement moving from standard SCSI RAID to Dell/EMC fibre SAN, even though average disk io queue under peak load is around 30. The average latch wait is over 2000 ms and it’s also running over 60 page faults a second. Regular lock timeouts and SQL command timeouts. Obviously a problem. Where can I find out more about latch contention and the cause of such high average latch wait time? Running on a quad xeon Dell 6650 with 6GB RAM. Richard Clarke

Review the following points: Identify disk bottlenecks by collecting following PERFMON counters :
PhysicalDisk counters:
-Disk Sec/read
-Disk Sec/write
-Disk Queues Buffer Manager counters:
-Page Life Expectancy
-Checkpoint Pages/sec
-Lazy Writes/sec SQL Server Access Methods counters for correct indexing:
-Full Scans/sec
-Index Searches/sec Memory counter:
-Page Faults/sec Use the system table-valued function fn_virtualfilestats to check IoStallMS value. IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. To display IoStallMS, execute the query: SELECT * FROM ::fn_virtualfilestats(dbid,file#) SQL Profiler can help identify which Transact-SQL statements do scan. And lastly, Check Showplan for bad query plans.
Any of the following will reduce these waits:
– Adding additional I/O bandwidth.
– Balancing I/O across other drives.
– Reducing I/O with proper indexing.
– Check for bad query plans. And ensure similar levels of service packs and MDAC versions are installed on Client & Server machines. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>