SQL Server Performance

Memory: faults/sec

Discussion in 'Performance Tuning for Hardware Configurations' started by NewDBA, May 17, 2007.

  1. NewDBA New Member

    Hi

    I have sql server 2000 : 2 processors, 2 G of ram, configured for dynamic memory
    allocation

    transaction log shipping configured for 15 min interval to ship trn to other server.


    Now I was monitoring this server and see that all my counters are fine
    available mb = avg 100
    memory pages/sec = avg 0
    sql bufer cache hit ratio - always >99

    But...
    memory faults/sec = avg 8 ( vary from 5 to 30)

    Is that means that my OS is not getting enought memory because SQL taking over everything?
    Or this is my transaction log shipping ( every 15 min ) can cause that counter to be so high?

    Where can I read more about that counter?
    thank you
  2. satya Moderator

    Not really, do you see any performance issues lately during these spikes or at any time?

    In general backup of database or transaction log would see that spikes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. NewDBA New Member

    actually it is not spikes.

    memory faults/sec never goes smaler than 5.

    I don't see any performance issue - but need to understand what that counter
    tells me - not sure

    what value is normal?
    where can I read about that? - can't find any article with numbers
  4. NewDBA New Member

    available mb = avg 100

    It's look ok - right ?
    That means that I do have memory left after all process allocated to themself?
  5. satya Moderator

    Available bytes - this counter is seems reasonably high, then your server is working fine, but check the counter when the server is undergoing the normal load that it is expected to handle.

    You can also look into counters such as:
    SQL Server: Buffer Manager: Buffer cache hit ratio
    SQL Server: Buffer Manager: Page Life Expectancy
    Process: Working Set

    What is the max memory set on the SQL Server?

    http://www.sql-server-performance.com/performance_monitor_counters_memory.asp - on counter tips.


    http://blogs.msdn.com/slavao/archiv...server-always-respond-to-memory-pressure.aspx

    http://sqlserver-qa.net/blogs/perft...t-memory-counters-what-you-need-to-check.aspx - using the correct counters.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. NewDBA New Member

    Memory set on server:
    dynamic from min=0 to max =2048


  7. satya Moderator

    I don't see any problem in this case, as a benchmarking you might monitor that counter on a periodical basis by taking into the account of transactions running on this server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page