SQL Server Performance

Latch contention - avg latch wait > 2000 ms

Discussion in 'Performance Tuning for SQL Server Replication' started by RichardClarke, Apr 1, 2004.

  1. RichardClarke New Member

    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
  2. satya Moderator

    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.

Share This Page