Hi,

I have set up Performance Monitor to track the number of Page Faults on a remote Server(NT 4 SP6) running SQL Server 2000. There are no other applications running on this server.

These are the setting for SQL Server..

1:SQL server is configured to use memory dynamically with a min memory setting of 0 and a max of 1792. Total memory avaliable is 2047MB so I thought best to not go beyond 1792MB so as not to starve the OS.

2:Reserve Physical Memory for SQL Server is checked on

3:Min query memory is 1024KB, the default setting

4:Boost SQL Server priority on Windows is checked on

The average page faults is around 600 per sec which I think is high. Pages/sec (i.e. Hard Page faults) are low, about 3 per sec. If I am correct in saying Page Faults/sec minus Page Input/sec equals Soft Page Fauls/sec then that gives me about 600 soft page faults/sec on average as Page Input/sec is tiny (0.01 average).

Can anyone explain why the soft page faults/sec is so high? Or is this normal? What would you expect this figure to be?? I can't find much documentation on the Net about this.

Thanks
Niall

The

I realize this is not answering your question, but for:
4:Boost SQL Server priority on Windows is checked on

You should not bother. As per chat transcript that can be found at:
http://www.microsoft.com/technet/tre...QL/sql1022.asp

Read the following response:
This option is a holdover from SQL Server 6.5, running on Windows NT 3.x, where there could be some benefit from using it if you were careful. There is really no reason for it, but it is hard to remove something that has been around for a while. Don’t look for this option being available in any future versions