SQL Server Performance

System Monitor Counter values

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by apat, Oct 18, 2007.

  1. apat New Member

    During performance monitoring for Load Test on few servers. I have configured following counters in system performance monitor. Can anyone tell me the ideal values or percentage level for this counters, from a good performance point of view.
    1. Memory
    a. Available Kbytes:
    b. Available Mbytes:
    c. Pages/sec:
    1. Physical Disk
    a. %Disk Read Time
    b. %Disk Write Time:
    c. Avg. Disk Queue Length:
    1. Processor
    a. %Processor Time:
    1. SQL Server: Access Methods
    a. Page Splits/sec:
    1. SQL Server: Buffer Manager
    a. Buffer Cache Hit Ratio:
    b. Page Life Expectancy:
    1. SQL Server: Memory Manager
    a. Connections Memory(KB):
    b. Target Server Memory(KB):
    c. Total Server Memory(KB):
    1. SQL Server: SQL Statistics
    a. SQL Compilations/sec:
    b. SQL Re-Compilations/sec:
    - apat
  2. thomas New Member

    There are seldom hard and fast rules about this. Generally it's "as low as possible" or "as high as possible".. but anyway

    Memory - pages/sec shouldn't be high (200/sec + is a bit of a concern)
    Available MBytes should be plenty (100+)

    Physical disk - Ave queue - depends on your array or SAN. As low as possible.
    % Disk Time values are generally useless for modern disk arrays, you end up with values like 250% which is meaningless. Concentrate on queueing and disk throughput - counters like Disk Bytes/Transfer, Disk Transfers/sec, Disk Bytes/sec, to calculate your throughput. This should, of course, be as high and as fast as possible

    Processor % time - as low as possible. Anything sustained over 50% is a concern

    Page Splits/sec - low as poss

    Buffer Cache Hit Ratio - this should ideally be 99% or above nearly all the time. If it frequently drops below 95% you may need more RAM

    Page Life Expectancy - high as poss

    SQL Server memory - monitor Process/sqlservr/Private Bytes and ensure SQL Server has the correct amount of RAM. Target Server Memory should be nearly the same as Total Server Memory if sql server has sufficient RAM.

    Compilations/Recompilations - shouldn't be too high, but will vary considerably depending on whether your app uses SPs or ad-hoc sql, etc etc etc etc

    This may be vague and none of the above is hard and fast - other users will almost certainly disagree with some of my judgements - which will go to prove that this is an art, not a science, generally, and many judgements are subjective.

    A lot depends on your hardware, your app, and you particular server's config.
  3. thomas New Member

    Also add System/Processor Queue Length - if this is non-zero it will show you have processor queueing. some queuing every now and then is fine, but sustained non-zero values can be a cause for concern.
  4. apat New Member


Share This Page