SQL Server Performance Forum – Threads Archive

Page faults reasonable?

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.

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.

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.

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.

]]>