SQL Server Performance

Page faults reasonable?

Discussion in 'Performance Tuning for Hardware Configurations' started by merrillaldrich, Dec 7, 2005.

  1. merrillaldrich New Member

    Hi -

    We have a rather under-performing third party database which we've been attempting to troubleshoot. Among other activities, I am trying to ascertain whether we have a clear issue with running low on SQL server memory, or if the bottleneck lies somewhere else.

    We have Perfmon running with a variety of counters. Over a 1 hour 15 minute period of high usage:

    Buffer Cache Hit Ratio Average: 98 (good news)
    Average Page Faults per Second / process sqlservr: 100 (bad news?)
    Average Page Faults per Second overall -- slighly higher, but mirrors the SQL server value (this server is dedicated to SQL Server only, so that makes sense)
    Average Memory / Pages per Second (Hard Page Faults) almost 0 (0.004 -- good news)

    To me this indicates in-RAM paging -- ie. the machine is not going to disk, thank goodness, but it's paging data in and out of the SQL portion of RAM. I have read that this can impact CPU load, and our CPU load is actually high, bumping 100% occasionally when busy, and averaging about 75 or 80%.

    Question -- is the Page Faults per Second value of 100 a red flag, or just a yellow one? What value should we really worry about for that counter? Zero would be nice, but at what cost?

    We are on SQL server 2000 SE, new 2 processor server with Win 2003 Server, plenty of RAM on the machine itself, so the limiting factor AFA RAM is the SQL Server Standard Edition limit. Before I even suggest upgrading (major bucks where I work) I need certainty that this RAM situation is a real problem.

    Any advice?

    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
  2. Luis Martin Moderator

    To me is between green and yellow.
    100 is no to much, more when you have very log pages/sec.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    HOw about TEMPDB placement on this server and its usage?


    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. merrillaldrich New Member

    I'll check it out. What are you thinking? Disk activity on temp DB? Something else?

    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.

Share This Page