• Ignoring memory for now...

    Run performance monitor for an hour or so while the problems are occuring. Add the following counters:

    Physical Disk:Avg sec/read

    Physical Disk:Avg sec/write

    SQL Server Buffer manager:Buffer cache hit ratio

    SQL Server Buffer manager:checkpoint pages/sec

    SQL Server Buffer manager:lazy writes/sec

    SQL Server Latches: Latch waits\sec

    SQL Server Latches: average latch wait time

    SQL Server Locks: Lock waits\sec

    SQL Server Locks: average lock wait time

    SQL Server Access methods:Full scans\sec

    What kind of average values are you seeing over the hour?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass