SQL Server Performance

Performance Monitor results skewed

Discussion in 'Performance Tuning for DBAs' started by amy, Nov 4, 2002.

  1. amy New Member

    Hi,<br /><br />Any insight on this would be greated appreciated! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I am benchmarking performance on two servers and here are the results for running a read intensive stored procedure 500 times. Both Servers are SQL2k sp2 on Win2k<br /><br />******Server A******* <br />Quad 700 mhz<br />3.5 gb RAM<br />3 disk Raid 5 on RA4100<br /><br />Average elapsed time for 500 executes: 3 minutes 3 seconds<br /><br />Performance Monitor Counters<br />- Memory: Pages/Sec 0.111<br />- Physical Disk: Avg. Disk Queue Length 0.713<br />- Processor: % Processor Time 12.906<br />- SQL Server Latches: Avg Latch Wait Time 278.851<br />- Latch Waits/Sec: Average 2.909, Maximum: 8.970<br />- Total Latch Wait Time: Maximum 46.989 <br /><br />******Server B******* <br />Quad 700 mhz<br />4 gb RAM<br />3 disk Raid 5 on a SAN<br /><br />Average elapsed time for 500 executes: 1 minutes 52 seconds<br /><br />Performance Monitor Counters<br />- Memory: Pages/Sec 0.000<br />- Physical Disk: Avg. Disk Queue Length 0.105<br />- Processor: % Processor Time 16.786<br />- SQL Server Latches: Avg Latch Wait Time 11304.729<br />- Latch Waits/Sec: Average 3.879, Maximum: 11.000<br />- Total Latch Wait Time: Maximum 15.999<br /><br />I have run this test several times with the results being about the same each time. Does anyone know why the Average Latch Wait Time is so large on Server B but the query runs faster and there is no indication of an i/o bottleneck?<br /><br />Thanks<br /><br />Amy<br />
  2. bradmcgehee New Member

    I don't have an answer, but can only offer some ideas. Pehaps some others have some ideas.

    When you run the tests on the two servers, does the buffer start at the same level? By this, I mean, the buffer should be empty of all stored procedures and data. By doing this, you can better ensure that your tests are identical. To clear out the buffer before running tests, here's a tip from my website:

    When experimenting with the tuning of your SQL Server, you may want to run the DBCC DROPCLEANBUFFERS command to remove all the test data from SQL Server's data cache (buffer) between tests to ensure fair testing. If you want to clear out all of the stored procedure cache, use this command, DBCC FREEPROCCACHE. If you want to only clear out the stored procedure cache for a single database (not the entire server) use DBCC FLUSHPROCINDB. All of these commands are for testing purposes and should not be run on a production SQL Server.

    If this doesn't account for the differences, then my next best guess is that the differences in the disk subsystems are causing the difference. If one of the subsystems is much faster than the other, this can account for some, if not much of the differences. Unfortunately testing to verify this may be difficult.

    It appears that the second system, with the SAN storage, is much faster, and that is the one with higher number of latches. This may be because it is working much faster, and creating latches at a much faster pace, which may in turn force the latch wait time to increase because of the extra load.

    I don't really think the increase in latch time is an issue, as your overall performance is much better.

    I look forward to seeing the results of your testing.

    Brad M. McGehee
  3. amy New Member

    Thanks for the response. Even with the SAN being faster the Avg wait time still seems totally out of whack. I'll keep on testing. Any other ideas from anyone??

Share This Page