Re: PageFaults & MSSearch
From: Rich (richbrownesq_at_hotmail.com)
Date: 03/05/04
- Next message: Brad: "Re: Q. FTS property cache and storing commonly searched queries"
- Previous message: Brad: "Re: Q. FTS property cache and storing commonly searched queries"
- In reply to: John Kane: "Re: PageFaults & MSSearch"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 5 Mar 2004 09:43:45 -0000
Hi Jon,
I'm going to look at configuring the resoure_level to 5 as per your
suggestion. Hopefully this should alleviate some of the problems. Moving to
a dedicated RAID10 with its own controller is something for further down the
line. Thanks for all the information!
Rich
"John Kane" <jt-kane@comcast.net> wrote in message
news:uEbxRPUAEHA.1560@tk2msftngp13.phx.gbl...
> You're welcome, Rich,
> Yep, you're right, CatalogB with relative high updates and queries is most
> likely the source of the pagefaults as well as a bottleneck on query &
> update performance... With this much updates (roughly, 33%), I'd recommend
> keeping "Change Tracking" turned on, but turn off "Update Index in
> Background" and at key times run a scheduled Incremental Population as
> necessary. Of course, this will leave your FT Catalog not as up-to-date as
> the table, but it will relieve some of the CPU, Memory and pagefault
> pressures your are seeing. See SQL 2000 BOL title "Maintaining Full-Text
> Indexes" for more info.
>
> In the long run, and in order to reduce the pagefaults, I'd also recommend
> that you consider additional & separate disk arrays to put the catalogs on
> their own disk array as RAID10.
>
> Most likely the error "Failed to reserve contiguous memory..." is not
> related to MSSearch and the above SQL FTS issues.
>
> Q. If there is 512 available will it not take all of it when the setting
is
> 3?
> A. No, with a setting of 3, the MSSearch service would use less, depending
> upon the available memory. You can monitor Perfmon counters - Microsoft
> Gatherer: Reason to back off. For more details, see KB/whitepaper: 323739
> (Q323739) "INF: SQL Server 2000 Full-Text Search Deployment White Paper"
at:
> http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;323739
>
> Q. Does this include virtual memory? With this in mind, how much would
> recommend leaving for the OS?
> A. No. Memory for the OS? That would depend upon processing levels you
need
> for SQL Server as well as for the MSSearch service and other processes
> running on your server.
>
> Regards,
> John
>
>
>
> "Rich" <richbrownesq@hotmail.com> wrote in message
> news:#scoJTQAEHA.3348@TK2MSFTNGP11.phx.gbl...
> > Hi John, thanks for you reply.
> >
> > Here are the answers to your questions.
> >
> > - How many rows in your FT-enable table(s) that are in your FT
Catalog(s)
> > and what is the size of your FT Catalogs?
> >
> > I have 2 FTCatalogs, CatalogA has 200k row and about 300MB. We run an
> > incrememntal population on this each day on this and it receives
> > approximately 15k queries per day
> > CatalogB has about 150k rows and is about 45MB. This has change tracking
> > with background update and receives approximately 50000 updates to the
> > catalog per day and 100k queries. This is the catalog i believe the
> > bottleneck exists on.
> >
> > - Additionally, were are your FT Catalogs located? Are they on separate
> > drive from your pagefile.sys file(s)?
> >
> > Yes, our page file is on its own drive completely. However, due to space
> > constraints we are unable to put the catalogs on their own disk array.
We
> > have 2 arrays, one RAID10 and one RAID5. Currently CatalogA is on the
> RAID5
> > and is on the data files are on the RAID10. CatalogB is on the RAID10
> while
> > the datafiles are on the RAID5.
> >
> > - In regards to "Failed to reserve contiguous memory..." is this
recorded
> in
> > the SQL Server errorlog file or the server's Application or System event
> > logs?
> >
> > This is recorded in the SQL Server errorlog file. I have managed to
reduce
> > the frequency of this occurning to once per day by regularly scheduling
> the
> > DBCC FREEPROCCACHE command to run once an hour.
> >
> > Just so i understand, you're saying that MSSearch can only use a max of
> > 512MB and by setting the resource_usage to 5, it will take all of this.
If
> > there is 512 available will it not take all of it when the setting is 3?
> > Does this include virtual memory? With this in mind, how much would
> > recommend leaving for the OS?
> >
> > Regards
> > Rich
> >
> > "John Kane" <jt-kane@comcast.net> wrote in message
> > news:Oq$biSIAEHA.2336@TK2MSFTNGP11.phx.gbl...
> > > Rich,
> > > Yes, I can. First of all, while SQL Server can use the AWE memory, the
> > > MSSearch service cannot. In fact, it's memory allocation is limited to
> > 512MB
> > > of RAM - if and only if 512MB of RAM is available and not used by
either
> > the
> > > OS, SQL Server or any other process on the server. The memory
allocation
> > for
> > > the MSSearch service is can be configured via:
> > >
> > > sp_fulltext_service 'resource_usage', <value>
> > >
> > > where <value> is 1 (background) - 5 (dedicated) with a default of 3.
I'd
> > > recommend that you increase the default setting for resource_usage to
5
> on
> > > your server to give the MSSearch the max amount of memory available.
> > > Additionally, were are your FT Catalogs located? Are they on separate
> > drive
> > > from your pagefile.sys file(s)? If not, you should move them to
another
> > > drive, and preferable to another disk controller and disk array
(RAID10
> > and
> > > not RAID5) and separate from your database files (*.mdf, *.ndf &
*.ldf).
> > See
> > > SQL Server 2000 BOL title "Full-Text Search Recommendations" for more
> > info.
> > >
> > > Furthermore, how many rows in your FT-enable table(s) that are in your
> FT
> > > Catalog(s) and what is the size of your FT Catalogs? In regards to
> "Failed
> > > to reserve contiguous memory..." is this recorded in the SQL Server
> > errorlog
> > > file or the server's Application or System event logs? If the latter,
> what
> > > is the source event for this message?
> > >
> > > Regards,
> > > John
> > >
> > >
> > >
> > > "Rich" <richbrownesq@hotmail.com> wrote in message
> > > news:O3hLtfHAEHA.1464@tk2msftngp13.phx.gbl...
> > > > Hi,
> > > >
> > > > I'm running SQL2K, SP3, on Win2K on a 4 x 2Ghz box with 8GB memory
> with
> > > AWE
> > > > enabled.
> > > >
> > > > We currently have particularly high process usage on our box and we
> have
> > > > pretty much narrowed this down to the MSSearch Service. One of the
> > things
> > > > that has lead us to this conclusion is the high amount of page
faults
> > that
> > > > this service reports. All other perfmon counters appear fairly
healthy
> > > > (according to articles i've read). I'm therefore focusing my
attention
> > on
> > > > memory and have a few queries.
> > > >
> > > > I am a bit confused with the microsoft documentation on how to
> configure
> > > > virtual memory and max server memory.
> > > >
> > > > It recommends that you set if using the MSSearch Service the max
> server
> > > > memory option to 1.5times the physical memory of the machine,
> therefore
> > in
> > > > our case 12GB. Also it recommends that you configure the virtual
> memory
> > to
> > > 3
> > > > times the physical memory, in our case 24GB.
> > > >
> > > > Currently we have our paging file configured as such, but our max
> server
> > > > memory is just over 7GB. I have read that high page faults could be
> > caused
> > > > by not enough memory being available to the OS and other services
that
> > > > require memory (MSSearch?). Therefore, this leads me to think that
> > perhaps
> > > > we should reduce this value. (As an aside, we also see regular
> > occurances
> > > of
> > > > the following error in the sql server error log which also makes me
> > thing
> > > a
> > > > reduction could be necessary:- WARNING: Failed to reserve
contiguous
> > > memory
> > > > of Size= 8519680)
> > > >
> > > > However, i would also expect that if the MSSearch doesn't have
enough
> > > memory
> > > > and is causing pagefaults then there should be a high paging, but
> > > according
> > > > to our perfmon, paging is under 1% (of 24GB) which seems very low.
> > > >
> > > > Can anyone give me any pointers as i seem to be going round in
> circles?
> > > >
> > > > Thanks in advance
> > > > Rich
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Brad: "Re: Q. FTS property cache and storing commonly searched queries"
- Previous message: Brad: "Re: Q. FTS property cache and storing commonly searched queries"
- In reply to: John Kane: "Re: PageFaults & MSSearch"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
