Microsoft KB Archive/168778
Article ID: 168778
Article Last Modified on 3/1/2005
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q168778
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 two inaccuracies are:
- 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.
Microsoft has confirmed that this is a problem in the documentation for Microsoft SQL Server versions 6.0 and 6.5.
Keywords: kbbug KB168778