"Phil" <sherrod@newsgroups.nospam > wrote in message
news:uz9KLieAIHA.1212@TK2MSFTNGP05.phx.gbl...
> We are having excessively high page fault rates by msftesql. Our database
> has about 50 million records, and we are performing full text searches
> about
> once every other second.
>
> We are running on a dual CPU server with 8 GB of RAM and a 4-disk RAID 0
> array. The OS is Windows 2003 Server Enterprise Edition, and we are using
> SQL 2005 Enterprise Edition with all updates.
>
> The page fault rate for msftesql is about 6,400 faults per second. The
> Windows task monitor shows it using only 34 MB of memory with a VM size of
> about 13 MB. We haven't been able to figure out how to increase the
> memory
> allocation for it.
>
> The sqlsvr program is using about 1.6 GB of RAM and has a page fault rate
> of
> about 6 faults per second. No other jobs are using more than about 25 MB
> of
> memory, so there should be plenty of memory available for msftesql.
>
> We have used the procedure to set sp_fulltext_service 'resource_usage' to
> 5.
>
> We have not turned on AWE memory usage for the SQL server, but our
> understanding is that AWE only affects the SQL server and not full text
> searching. We have set the minimum and maximum memory settings for the SQL
> server to 1000 MB and 4000 MB.
>
> Is there some other setting that controls how much memory is used by
> msftesql? What can we do to reduce the paging rate?
>
> On the My Computer/Performance screen, we have set Windows to optimize
> background services, and we have set it to favor system cache rather than
> programs for memory usage. We found a document recommending that setting
> to
> improve full text search performance.
>
> Are there any settings that need to be turned on for systems with more
> than
> 4 GB of RAM?