lots of page faults - whats that mean?

[WiccaChic]

Recently started monitoring my SQL server with perfmon and I am seeing page faults running at 100% almost all the time. Can someone explaing to me what that indicates performance wise?

 

[hmckillop]

A page fault occurs every time SQL accesses a page in memory that is outside it's "working set". Due to the way that the SQL performance monitor counters work in SQL 6.5 and earlier, this area of memory is held outside of the SQL working set, so access/updates to these counters actually CAUSE page faults.

However most page faults are still to areas that are still held in memory and so do not result in expensive/slow reads/writes to the pagefile on disk. There is some CPU overhead caused by the NT Virtual Memory Manager (VMM) becoming involved and context switches, but these don't affect performance unless you are getting hundreds/thousands of page faults/sec.

The counter you should monitor is pages/sec which is the actual i/o caused to the pagefile, and is what you should be worried about.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[timscronin]

Probably want to monitor your sql cache hit ratio also. This should be at almost 100% (this shows that sql is processing through memory, not disk), if this is low sql will start utilizing disk for this work.

 

[SonOfEmidec1100]

Ideally Pages/Sec should be zero with the occasional non zero value, and the cache hit ratio close to 100%

Adverse values would affect performance and probably indicate insufficient memory or incorrectly set memory management values.