BUG: Page Fault Statement in Admin Companion Is Incorrect

• Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

The following statement from the Microsoft SQL Server Administrator Companion contains two inaccuracies:

You can use SQL Performance Monitor to help determine what the threshold is for your system: the Page Faults/sec counter of the Memory Object indicates whether you are generating any page faults. If so, SQL Server is running with too much memory.

• The statement implies that any page faults are bad. Due to the way the Windows NT Server Virtual Memory Manager (VMM) is designed, it is normal for a low rate of page faults to occur, even if the computer has plenty of extra memory. The Windows NT Server VMM will do working set trimming (and probably other operations) to steal pages from the working set of SQL Server, which in turn causes page faults when SQL Server references those pages again.
  
  This is minimized by SQL Server setup, which selects "maximize throughput for network applications", as seen in the Network Control Panel under Server Service. However, a low level of page faults can still occur, and this is normal. What constitutes a "low rate" will vary, but typically it is under 10 per second.
• The recommendation to monitor page faults per second using the Memory object gives a system-wide view of paging. Many other processes on the computer can cause paging besides SQL Server. Instead, the documentation should advise using the page faults per second counter under the process object, and selecting the Sqlservr.exe instance. This restricts the view of paging only to that caused by the SQL Server process.

STATUS

Microsoft has confirmed this to be a problem in the documentation for Microsoft SQL Server versions 6.0 and 6.5.

Additional query words:

Keywords          : kbnokeyword kbSQLServ600 kbSQLServ650 
Version           : winnt:6.0,6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 6, 1999