(SQL Server 2000, SP3a)
Hello all!
On regular intervals, we're seeing a lot of OS page faults on our Production server
(Windows 2000 Advanced Server), and have traced part of those symptoms to the MSSEARCH.EXE
executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
We've got a number of Full Text Catalogs in our databases (maybe around 10), and we've got
about 10 databases with the same schema. We do an incremental population once at night,
when our database activity is low.
We'll see regular MSSEARCH.EXE activity throughout the day, and sometimes it's really
heavy. I had assumed that the incremental population would kick off the MSSEARCH.EXE
service, but I was under the impression that (1) Full Text change tracking would be
incorporated wholly under the auspices of SQL Server (not invoke MSSEARCH.EXE), and (2)
that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke MSSEARCH.EXE.
I think my (2) assumption is invalid, and was hoping to get some clarification.
Thanks for any help you can provide! :-)
John PetersonHello back, John,
Yes, you will see high CPU usage from the "Microsoft Search" (mssearch.exe)
service normally at the end of your nightly Incremental Population when a
Master Merge occurs. You may also see it during a "shadow merge" while the
Incremental Population is in progress as well, both are normal and expected
as during these merges of new word lists, shadow index files into the master
index file, a lot of computational process is ongoing. Additional, while
this process does cause high-cpu usage, it is not normally for long periods
of time, and then subsides.
Actually both of your impressions/assumptions are incorrect. "Change
Tracking" with "Update Index in Background" as well as any of the SQL FTS
predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch service as
in SQL Server 2000 this is a necessary service that is external to SQL
Server that manages the FT Catalogs and other FTS requirements.
Additionally, why are you still using nightly Incremental Populations, when
you are aware of the benefits of "Change Tracking" with "Update Index in
Background"? If you enabled both of these options, then the need for your
nightly Incremental Populations should go away. If you're are doing
massive updates/deletes/inserts nightly, you can still use "Change Tracking"
and turn off "Update Index in Background", and then do a scheduled
Incremental Population.
Additionally, if you're server is a dual-proc or multiple cpu machine, there
is secured & reliable methods for setting the CPU affinity of the mssearch
service and keep separate from the MSSQLServer cpu affinity's so that the
mssearch cpu usage will not affect your SQL Server processing.
Regards,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> On regular intervals, we're seeing a lot of OS page faults on our
Production server
> (Windows 2000 Advanced Server), and have traced part of those symptoms to
the MSSEARCH.EXE
> executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
> We've got a number of Full Text Catalogs in our databases (maybe around
10), and we've got
> about 10 databases with the same schema. We do an incremental population
once at night,
> when our database activity is low.
> We'll see regular MSSEARCH.EXE activity throughout the day, and sometimes
it's really
> heavy. I had assumed that the incremental population would kick off the
MSSEARCH.EXE
> service, but I was under the impression that (1) Full Text change tracking
would be
> incorporated wholly under the auspices of SQL Server (not invoke
MSSEARCH.EXE), and (2)
> that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
MSSEARCH.EXE.
> I think my (2) assumption is invalid, and was hoping to get some
clarification.
> Thanks for any help you can provide! :-)
> John Peterson
>|||Hello John! Thank you for such a speedy and detailed reply! :-)
Please see inline:
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> Hello back, John,
> Yes, you will see high CPU usage from the "Microsoft Search" (mssearch.exe)
> service normally at the end of your nightly Incremental Population when a
> Master Merge occurs.
I haven't monitored it at night -- but I would imagine to see a spike in CPU/RAM usage at
that time. :-)
> You may also see it during a "shadow merge" while the
> Incremental Population is in progress as well, both are normal and expected
> as during these merges of new word lists, shadow index files into the master
> index file, a lot of computational process is ongoing. Additional, while
> this process does cause high-cpu usage, it is not normally for long periods
> of time, and then subsides.
I'm not familiar at *all* with this "shadow merge" concept. Is this only applicable
during an incremental population (that is, if we have just a nightly incremental
population, would it only happen during that time)?
> Actually both of your impressions/assumptions are incorrect. "Change
> Tracking" with "Update Index in Background" as well as any of the SQL FTS
> predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch service as
> in SQL Server 2000 this is a necessary service that is external to SQL
> Server that manages the FT Catalogs and other FTS requirements.
Ah! For some reason, I had assumed that the change tracking could be done solely within
the confines of SQL Server due to the requisite TIMESTAMP on the table that's
participating in the Full Text Catalog. But, it does make sense that it's doing a "bit
more work" than I expect, and would need to leverage the MSSearch service executable. :-)
And, I guess I would expect the same from the SQL FTS predicates CONTAINS*/FREETEXT*. It
was a bit naive to think that the operation could be done outside of the MSSearch service.
> Additionally, why are you still using nightly Incremental Populations, when
> you are aware of the benefits of "Change Tracking" with "Update Index in
> Background"? If you enabled both of these options, then the need for your
> nightly Incremental Populations should go away. If you're are doing
> massive updates/deletes/inserts nightly, you can still use "Change Tracking"
> and turn off "Update Index in Background", and then do a scheduled
> Incremental Population.
Actually, I'm *not* very clear on the whole Change Tracking thing. I had assumed that was
sort of like the Replication "log reader" and merely collected information about those
rows that needed to participate in the Incremental Population. Am I to understand that
the Change Tracking is sort of mutualy exclusive from the Incremental Population?
> Additionally, if you're server is a dual-proc or multiple cpu machine, there
> is secured & reliable methods for setting the CPU affinity of the mssearch
> service and keep separate from the MSSQLServer cpu affinity's so that the
> mssearch cpu usage will not affect your SQL Server processing.
Ooo...we *are* in a multiple CPU context (4, I believe). Do you have a handy link on how
we might keep these processes from potentially stepping on each others' "toes"? Is that a
good thing to do?
I ask, because we're actually considering making a "Full Text Service" server to kind of
manage all of our FT needs, for 3 reasons:
(1) To help mitigate the impact of the MSSearch service on our SQL Server box, which is
hosting a number of clients. As I say, it appears to use a lot of RAM and CPU at numerous
points during the day.
(2) We want to index .PDF files, and we have a IFilter from Adobe that works, but has
some caveats. One of them is that we have to limit the number of worker threads on the
entire server (I think I'm conveying this right -- it's kind of "third-hand" ;-). As
such, I understand that this would impact the entire server, including SQL Server on that
box.
(3) To get around the 256 Full Text Catalog limit per server. I doubt that we'll
actually ever get there, but with over 100 FTCs on one of our Production servers, it's not
unreasonable to think we could double that...
But, a lot of my knowledge of the Full Text Search service isn't as strong as it needs to
be in order to make such decisions, so any help you can provide would be *greatly*
appreciated! :-)
> Regards,
> John
As always, thank you for your time and expertise! :-)
John Peterson
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > On regular intervals, we're seeing a lot of OS page faults on our
> Production server
> > (Windows 2000 Advanced Server), and have traced part of those symptoms to
> the MSSEARCH.EXE
> > executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
> >
> > We've got a number of Full Text Catalogs in our databases (maybe around
> 10), and we've got
> > about 10 databases with the same schema. We do an incremental population
> once at night,
> > when our database activity is low.
> >
> > We'll see regular MSSEARCH.EXE activity throughout the day, and sometimes
> it's really
> > heavy. I had assumed that the incremental population would kick off the
> MSSEARCH.EXE
> > service, but I was under the impression that (1) Full Text change tracking
> would be
> > incorporated wholly under the auspices of SQL Server (not invoke
> MSSEARCH.EXE), and (2)
> > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> MSSEARCH.EXE.
> >
> > I think my (2) assumption is invalid, and was hoping to get some
> clarification.
> >
> > Thanks for any help you can provide! :-)
> >
> > John Peterson
> >
> >
>|||You're welcome, John,
See more inline comments - [jtkane].
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> Hello John! Thank you for such a speedy and detailed reply! :-)
> Please see inline:
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > Hello back, John,
> > Yes, you will see high CPU usage from the "Microsoft Search"
(mssearch.exe)
> > service normally at the end of your nightly Incremental Population when
a
> > Master Merge occurs.
> I haven't monitored it at night -- but I would imagine to see a spike in
CPU/RAM usage at
> that time. :-)
[jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
&/or Profiler to track the cpu/ram usage.
> > You may also see it during a "shadow merge" while the
> > Incremental Population is in progress as well, both are normal and
expected
> > as during these merges of new word lists, shadow index files into the
master
> > index file, a lot of computational process is ongoing. Additional, while
> > this process does cause high-cpu usage, it is not normally for long
periods
> > of time, and then subsides.
> I'm not familiar at *all* with this "shadow merge" concept. Is this only
applicable
> during an incremental population (that is, if we have just a nightly
incremental
> population, would it only happen during that time)?
[jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service as
a method of merging memory-resident word lists into "shadow files" and these
files into the "master index" file, somewhat like a sort/merge file
process...
> > Actually both of your impressions/assumptions are incorrect. "Change
> > Tracking" with "Update Index in Background" as well as any of the SQL
FTS
> > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
service as
> > in SQL Server 2000 this is a necessary service that is external to SQL
> > Server that manages the FT Catalogs and other FTS requirements.
> Ah! For some reason, I had assumed that the change tracking could be done
solely within
> the confines of SQL Server due to the requisite TIMESTAMP on the table
that's
> participating in the Full Text Catalog. But, it does make sense that it's
doing a "bit
> more work" than I expect, and would need to leverage the MSSearch service
executable. :-)
> And, I guess I would expect the same from the SQL FTS predicates
CONTAINS*/FREETEXT*. It
> was a bit naive to think that the operation could be done outside of the
MSSearch service.
>
> > Additionally, why are you still using nightly Incremental Populations,
when
> > you are aware of the benefits of "Change Tracking" with "Update Index
in
> > Background"? If you enabled both of these options, then the need for
your
> > nightly Incremental Populations should go away. If you're are doing
> > massive updates/deletes/inserts nightly, you can still use "Change
Tracking"
> > and turn off "Update Index in Background", and then do a scheduled
> > Incremental Population.
> Actually, I'm *not* very clear on the whole Change Tracking thing. I had
assumed that was
> sort of like the Replication "log reader" and merely collected information
about those
> rows that needed to participate in the Incremental Population. Am I to
understand that
> the Change Tracking is sort of mutualy exclusive from the Incremental
Population?
[jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
intentionally un-docucmented system table and is mutually exclusive from
Incremental Population.
> > Additionally, if you're server is a dual-proc or multiple cpu machine,
there
> > is secured & reliable methods for setting the CPU affinity of the
mssearch
> > service and keep separate from the MSSQLServer cpu affinity's so that
the
> > mssearch cpu usage will not affect your SQL Server processing.
> Ooo...we *are* in a multiple CPU context (4, I believe). Do you have a
handy link on how
> we might keep these processes from potentially stepping on each others'
"toes"? Is that a
> good thing to do?
[jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing.
> I ask, because we're actually considering making a "Full Text Service"
server to kind of
> manage all of our FT needs, for 3 reasons:
> (1) To help mitigate the impact of the MSSearch service on our SQL Server
box, which is
> hosting a number of clients. As I say, it appears to use a lot of RAM and
CPU at numerous
> points during the day.
[jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
where <value> is a number between 1 and 5 (default 3) to control the amount
of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
max of 512MB of RAM, but only if this amount of ram is available and not
used by any other process, incuding the OS and SQL Server.
> (2) We want to index .PDF files, and we have a IFilter from Adobe that
works, but has
> some caveats. One of them is that we have to limit the number of worker
threads on the
> entire server (I think I'm conveying this right -- it's kind of
"third-hand" ;-). As
> such, I understand that this would impact the entire server, including SQL
Server on that
> box.
[jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
Full-Text Population by Using a Single-Threaded Filter DLL or a PDF Filter
DLL May Not Succeed"
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> (3) To get around the 256 Full Text Catalog limit per server. I doubt
that we'll
> actually ever get there, but with over 100 FTCs on one of our Production
servers, it's not
> unreasonable to think we could double that...
[jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
regardless of the number of SQL Server instances on one machine, you still
have this limit... You're best bet here is to "scale-out" and use mutiple
server's possibly with Replication &/or log shipping between them to
transfer the data if necessary, otherwise put your independent databases on
separate servers...
> But, a lot of my knowledge of the Full Text Search service isn't as strong
as it needs to
> be in order to make such decisions, so any help you can provide would be
*greatly*
> appreciated! :-)
[jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact I'm
writing a book about it... <G>
> Regards,
> > John
> As always, thank you for your time and expertise! :-)
> John Peterson
>
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > On regular intervals, we're seeing a lot of OS page faults on our
> > Production server
> > > (Windows 2000 Advanced Server), and have traced part of those symptoms
to
> > the MSSEARCH.EXE
> > > executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
> > >
> > > We've got a number of Full Text Catalogs in our databases (maybe
around
> > 10), and we've got
> > > about 10 databases with the same schema. We do an incremental
population
> > once at night,
> > > when our database activity is low.
> > >
> > > We'll see regular MSSEARCH.EXE activity throughout the day, and
sometimes
> > it's really
> > > heavy. I had assumed that the incremental population would kick off
the
> > MSSEARCH.EXE
> > > service, but I was under the impression that (1) Full Text change
tracking
> > would be
> > > incorporated wholly under the auspices of SQL Server (not invoke
> > MSSEARCH.EXE), and (2)
> > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > MSSEARCH.EXE.
> > >
> > > I think my (2) assumption is invalid, and was hoping to get some
> > clarification.
> > >
> > > Thanks for any help you can provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Hello John!
Thank you *again* for the quick and informative reply! If it's not too onerous, please
see inline...
"John Kane" <jt-kane@.comcast.net> wrote in message
news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> You're welcome, John,
> See more inline comments - [jtkane].
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > Hello John! Thank you for such a speedy and detailed reply! :-)
> >
> > Please see inline:
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > Hello back, John,
> > > Yes, you will see high CPU usage from the "Microsoft Search"
> (mssearch.exe)
> > > service normally at the end of your nightly Incremental Population when
> a
> > > Master Merge occurs.
> >
> > I haven't monitored it at night -- but I would imagine to see a spike in
> CPU/RAM usage at
> > that time. :-)
> [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
> &/or Profiler to track the cpu/ram usage.
> > > You may also see it during a "shadow merge" while the
> > > Incremental Population is in progress as well, both are normal and
> expected
> > > as during these merges of new word lists, shadow index files into the
> master
> > > index file, a lot of computational process is ongoing. Additional, while
> > > this process does cause high-cpu usage, it is not normally for long
> periods
> > > of time, and then subsides.
> >
> > I'm not familiar at *all* with this "shadow merge" concept. Is this only
> applicable
> > during an incremental population (that is, if we have just a nightly
> incremental
> > population, would it only happen during that time)?
> [jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service as
> a method of merging memory-resident word lists into "shadow files" and these
> files into the "master index" file, somewhat like a sort/merge file
> process...
Ah, very interesting! Is there any way to "track" that specific activity through a
PerfMon counter, perchance?
> > > Actually both of your impressions/assumptions are incorrect. "Change
> > > Tracking" with "Update Index in Background" as well as any of the SQL
> FTS
> > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> service as
> > > in SQL Server 2000 this is a necessary service that is external to SQL
> > > Server that manages the FT Catalogs and other FTS requirements.
> >
> > Ah! For some reason, I had assumed that the change tracking could be done
> solely within
> > the confines of SQL Server due to the requisite TIMESTAMP on the table
> that's
> > participating in the Full Text Catalog. But, it does make sense that it's
> doing a "bit
> > more work" than I expect, and would need to leverage the MSSearch service
> executable. :-)
> >
> > And, I guess I would expect the same from the SQL FTS predicates
> CONTAINS*/FREETEXT*. It
> > was a bit naive to think that the operation could be done outside of the
> MSSearch service.
> >
> >
> > > Additionally, why are you still using nightly Incremental Populations,
> when
> > > you are aware of the benefits of "Change Tracking" with "Update Index
> in
> > > Background"? If you enabled both of these options, then the need for
> your
> > > nightly Incremental Populations should go away. If you're are doing
> > > massive updates/deletes/inserts nightly, you can still use "Change
> Tracking"
> > > and turn off "Update Index in Background", and then do a scheduled
> > > Incremental Population.
> >
> > Actually, I'm *not* very clear on the whole Change Tracking thing. I had
> assumed that was
> > sort of like the Replication "log reader" and merely collected information
> about those
> > rows that needed to participate in the Incremental Population. Am I to
> understand that
> > the Change Tracking is sort of mutualy exclusive from the Incremental
> Population?
> [jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
> intentionally un-docucmented system table and is mutually exclusive from
> Incremental Population.
That's *very* good to know -- because I believe we may be doing *both* techniques.
Though, wouldn't the Incremental Population do nothing (or very little) if the Change
Tracking has already done all the work? Or are they to very separate mechanisms that
don't really operate in conjunction with one another?
> > > Additionally, if you're server is a dual-proc or multiple cpu machine,
> there
> > > is secured & reliable methods for setting the CPU affinity of the
> mssearch
> > > service and keep separate from the MSSQLServer cpu affinity's so that
> the
> > > mssearch cpu usage will not affect your SQL Server processing.
> >
> > Ooo...we *are* in a multiple CPU context (4, I believe). Do you have a
> handy link on how
> > we might keep these processes from potentially stepping on each others'
> "toes"? Is that a
> > good thing to do?
> [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from the
> AT command on the multi-proc server where sql server resides, as follows:
> at <current_time+1min> /interactive taskmgr.exe
> when it launches, you can then set "cpu affinity" for the MSSearch service
> to a cpu or set of cpu's not being used by SQL Server. Then you would use
> sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> preventing the cpu usage of mssearch from affecting your sql server
> processing.
Clever! I'll give that a whirl. Is there a reason to launch the Task Manager from the AT
command, rather than just invoking it from the GUI directly? I have the ability to Remote
Desktop into that machine.
> > I ask, because we're actually considering making a "Full Text Service"
> server to kind of
> > manage all of our FT needs, for 3 reasons:
> >
> > (1) To help mitigate the impact of the MSSearch service on our SQL Server
> box, which is
> > hosting a number of clients. As I say, it appears to use a lot of RAM and
> CPU at numerous
> > points during the day.
> [jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
> where <value> is a number between 1 and 5 (default 3) to control the amount
> of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
> max of 512MB of RAM, but only if this amount of ram is available and not
> used by any other process, incuding the OS and SQL Server.
Heh! Again, I did not know that! I see that I'll be first in line whenever your book
comes out! ;-)
> > (2) We want to index .PDF files, and we have a IFilter from Adobe that
> works, but has
> > some caveats. One of them is that we have to limit the number of worker
> threads on the
> > entire server (I think I'm conveying this right -- it's kind of
> "third-hand" ;-). As
> > such, I understand that this would impact the entire server, including SQL
> Server on that
> > box.
> [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
> Full-Text Population by Using a Single-Threaded Filter DLL or a PDF Filter
> DLL May Not Succeed"
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
I haven't seen this article specifically, and it echoes a number of points on Adobe's site
about their IFilter .DLL. One thing that strikes me: is this issue something that has
been fixed with SP3? It suggests that in the KB article, and then shows the Registry
"dink" as a workaround. Does SP3 do nothing more than the Registry dink? If so, does it
do that in *all* cases, or just when the presence of an Adobe IFilter is detected? I ask,
because it'd be a shame to limit the number of Robot Threads (whatever *that* is! ;-) to
just 1 for all the "normal" MSSearch filters.
> > (3) To get around the 256 Full Text Catalog limit per server. I doubt
> that we'll
> > actually ever get there, but with over 100 FTCs on one of our Production
> servers, it's not
> > unreasonable to think we could double that...
> [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> regardless of the number of SQL Server instances on one machine, you still
> have this limit... You're best bet here is to "scale-out" and use mutiple
> server's possibly with Replication &/or log shipping between them to
> transfer the data if necessary, otherwise put your independent databases on
> separate servers...
Yeah...we had been toying with the idea of using Replication to set up a Search server, if
you will. We already have a Log Shipping scheme; so that suggestion interests me
greatly -- I wonder if you can "Tee" the Log Shipping to go to multiple destination
servers? In that way, we'd continue with our regular/usual standby server, and then
divert those same logs to our Search server. That'd allow us to pretty much leverage the
same mechanism we have today, without having to introduce some additional overhead of the
Replication features.
> > But, a lot of my knowledge of the Full Text Search service isn't as strong
> as it needs to
> > be in order to make such decisions, so any help you can provide would be
> *greatly*
> > appreciated! :-)
> [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact I'm
> writing a book about it... <G>
I can't wait! :-)
Thanks again, John, your help has been of tremendous value to me! :-)
> > Regards,
> > > John
> >
> > As always, thank you for your time and expertise! :-)
> >
> > John Peterson
> >
> >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > On regular intervals, we're seeing a lot of OS page faults on our
> > > Production server
> > > > (Windows 2000 Advanced Server), and have traced part of those symptoms
> to
> > > the MSSEARCH.EXE
> > > > executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
> > > >
> > > > We've got a number of Full Text Catalogs in our databases (maybe
> around
> > > 10), and we've got
> > > > about 10 databases with the same schema. We do an incremental
> population
> > > once at night,
> > > > when our database activity is low.
> > > >
> > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> sometimes
> > > it's really
> > > > heavy. I had assumed that the incremental population would kick off
> the
> > > MSSEARCH.EXE
> > > > service, but I was under the impression that (1) Full Text change
> tracking
> > > would be
> > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > MSSEARCH.EXE), and (2)
> > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > MSSEARCH.EXE.
> > > >
> > > > I think my (2) assumption is invalid, and was hoping to get some
> > > clarification.
> > > >
> > > > Thanks for any help you can provide! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||You're welcome again, John...
Inline comments can get a bit confusing to read in newsgroups postings, so
my comments to yours are here at the top...
Q. Is there any way to "track" that specific activity through a PerfMon
counter, perchance?
A. Yes, but you have to use the "Microsoft Search" performance counters for
the specific FT Catalog, see the " Microsoft Search Indexer Catalogs: Merge
Progress" counter as well as the other MSSearch counters and other SQL FTS
related info in a Full-Text Search Deployment white paper at
http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/fts_white%20paper.asp
(click on MONITORING FULL-TEXT SEARCH). Note, if you pick the right
counters, you will see what I call the "heart-beat" of word lists to shadow
files to master files as it looks just like a heart-beat on a medical
cardiograph (or at least it does to me ;-).
Q. Or are they to very separate mechanisms that don't really operate in
conjunction with one another?
A. They are very separate mechanisms and don't operated in conjunction with
each other.
Q. Is there a reason to launch the Task Manager from the AT command, rather
than just invoking it from the GUI directly?
A. Yes. One is security as only the Administrator of the machine or a member
of that machine's Admin. Group can use the AT command prompt. The second is
that when launched from the AT command, the TaskMgr is operating under
LocalSystem authority and this is necessary in order to access the MSSearch
*service* in order to change it's CPU affinity.
Q. Is this issue something that has been fixed with SP3?
A. It's unclear to me as well, after reading the RESOLUTION section of this
FIX KB article, whether or not anything more than a Registry key was changed
or even if it was changed. If you have not applied SP3, you can save the
Gathering Manager key and then apply SP3 and note the difference in the
RobotThreadsNumber key value and determine if any change was made. Note,
RobotThreadsNumber seems to be a "generic" key/value and seems to only
support a binary value of 1 or 0, from the KB article "The default value is
0, which allows multithreaded access to any filter DLLs".
Q. I wonder if you can "Tee" the Log Shipping to go to multiple destination
servers?
A. I've not given this much thought, but perhaps you could use the concept
of creating a "master SQL Server Agent job" and us this to "propagate" your
logs to "all involved target servers"? This would be a most interesting
solution to your question and while I've not tested this myself, try it in
your test environment and let us know if it works in your environment!!
As for my book efforts, I'm getting very good feedback from a publisher now
and time will tell how that goes...
Thanks,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uqkGJNvnDHA.964@.TK2MSFTNGP10.phx.gbl...
> Hello John!
> Thank you *again* for the quick and informative reply! If it's not too
onerous, please
> see inline...
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > You're welcome, John,
> > See more inline comments - [jtkane].
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > >
> > > Please see inline:
> > >
> > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > Hello back, John,
> > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > (mssearch.exe)
> > > > service normally at the end of your nightly Incremental Population
when
> > a
> > > > Master Merge occurs.
> > >
> > > I haven't monitored it at night -- but I would imagine to see a spike
in
> > CPU/RAM usage at
> > > that time. :-)
> >
> > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
> > &/or Profiler to track the cpu/ram usage.
> >
> > > > You may also see it during a "shadow merge" while the
> > > > Incremental Population is in progress as well, both are normal and
> > expected
> > > > as during these merges of new word lists, shadow index files into
the
> > master
> > > > index file, a lot of computational process is ongoing. Additional,
while
> > > > this process does cause high-cpu usage, it is not normally for long
> > periods
> > > > of time, and then subsides.
> > >
> > > I'm not familiar at *all* with this "shadow merge" concept. Is this
only
> > applicable
> > > during an incremental population (that is, if we have just a nightly
> > incremental
> > > population, would it only happen during that time)?
> >
> > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service
as
> > a method of merging memory-resident word lists into "shadow files" and
these
> > files into the "master index" file, somewhat like a sort/merge file
> > process...
> Ah, very interesting! Is there any way to "track" that specific activity
through a
> PerfMon counter, perchance?
>
> > > > Actually both of your impressions/assumptions are incorrect. "Change
> > > > Tracking" with "Update Index in Background" as well as any of the
SQL
> > FTS
> > > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> > service as
> > > > in SQL Server 2000 this is a necessary service that is external to
SQL
> > > > Server that manages the FT Catalogs and other FTS requirements.
> > >
> > > Ah! For some reason, I had assumed that the change tracking could be
done
> > solely within
> > > the confines of SQL Server due to the requisite TIMESTAMP on the table
> > that's
> > > participating in the Full Text Catalog. But, it does make sense that
it's
> > doing a "bit
> > > more work" than I expect, and would need to leverage the MSSearch
service
> > executable. :-)
> > >
> > > And, I guess I would expect the same from the SQL FTS predicates
> > CONTAINS*/FREETEXT*. It
> > > was a bit naive to think that the operation could be done outside of
the
> > MSSearch service.
> > >
> > >
> > > > Additionally, why are you still using nightly Incremental
Populations,
> > when
> > > > you are aware of the benefits of "Change Tracking" with "Update
Index
> > in
> > > > Background"? If you enabled both of these options, then the need for
> > your
> > > > nightly Incremental Populations should go away. If you're are
doing
> > > > massive updates/deletes/inserts nightly, you can still use "Change
> > Tracking"
> > > > and turn off "Update Index in Background", and then do a scheduled
> > > > Incremental Population.
> > >
> > > Actually, I'm *not* very clear on the whole Change Tracking thing. I
had
> > assumed that was
> > > sort of like the Replication "log reader" and merely collected
information
> > about those
> > > rows that needed to participate in the Incremental Population. Am I
to
> > understand that
> > > the Change Tracking is sort of mutualy exclusive from the Incremental
> > Population?
> >
> > [jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
> > intentionally un-docucmented system table and is mutually exclusive from
> > Incremental Population.
> That's *very* good to know -- because I believe we may be doing *both*
techniques.
> Though, wouldn't the Incremental Population do nothing (or very little) if
the Change
> Tracking has already done all the work? Or are they to very separate
mechanisms that
> don't really operate in conjunction with one another?
>
> > > > Additionally, if you're server is a dual-proc or multiple cpu
machine,
> > there
> > > > is secured & reliable methods for setting the CPU affinity of the
> > mssearch
> > > > service and keep separate from the MSSQLServer cpu affinity's so
that
> > the
> > > > mssearch cpu usage will not affect your SQL Server processing.
> > >
> > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you have
a
> > handy link on how
> > > we might keep these processes from potentially stepping on each
others'
> > "toes"? Is that a
> > > good thing to do?
> >
> > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from
the
> > AT command on the multi-proc server where sql server resides, as
follows:
> >
> > at <current_time+1min> /interactive taskmgr.exe
> >
> > when it launches, you can then set "cpu affinity" for the MSSearch
service
> > to a cpu or set of cpu's not being used by SQL Server. Then you would
use
> > sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> > preventing the cpu usage of mssearch from affecting your sql server
> > processing.
> Clever! I'll give that a whirl. Is there a reason to launch the Task
Manager from the AT
> command, rather than just invoking it from the GUI directly? I have the
ability to Remote
> Desktop into that machine.
>
> > > I ask, because we're actually considering making a "Full Text Service"
> > server to kind of
> > > manage all of our FT needs, for 3 reasons:
> > >
> > > (1) To help mitigate the impact of the MSSearch service on our SQL
Server
> > box, which is
> > > hosting a number of clients. As I say, it appears to use a lot of RAM
and
> > CPU at numerous
> > > points during the day.
> >
> > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
<value>,
> > where <value> is a number between 1 and 5 (default 3) to control the
amount
> > of RAM that the MSSearch service will use, 5 (dedicated) will use up to
a
> > max of 512MB of RAM, but only if this amount of ram is available and not
> > used by any other process, incuding the OS and SQL Server.
> Heh! Again, I did not know that! I see that I'll be first in line
whenever your book
> comes out! ;-)
>
> > > (2) We want to index .PDF files, and we have a IFilter from Adobe
that
> > works, but has
> > > some caveats. One of them is that we have to limit the number of
worker
> > threads on the
> > > entire server (I think I'm conveying this right -- it's kind of
> > "third-hand" ;-). As
> > > such, I understand that this would impact the entire server, including
SQL
> > Server on that
> > > box.
> >
> > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
> > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
Filter
> > DLL May Not Succeed"
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> I haven't seen this article specifically, and it echoes a number of points
on Adobe's site
> about their IFilter .DLL. One thing that strikes me: is this issue
something that has
> been fixed with SP3? It suggests that in the KB article, and then shows
the Registry
> "dink" as a workaround. Does SP3 do nothing more than the Registry dink?
If so, does it
> do that in *all* cases, or just when the presence of an Adobe IFilter is
detected? I ask,
> because it'd be a shame to limit the number of Robot Threads (whatever
*that* is! ;-) to
> just 1 for all the "normal" MSSearch filters.
>
> > > (3) To get around the 256 Full Text Catalog limit per server. I
doubt
> > that we'll
> > > actually ever get there, but with over 100 FTCs on one of our
Production
> > servers, it's not
> > > unreasonable to think we could double that...
> >
> > [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> > regardless of the number of SQL Server instances on one machine, you
still
> > have this limit... You're best bet here is to "scale-out" and use
mutiple
> > server's possibly with Replication &/or log shipping between them to
> > transfer the data if necessary, otherwise put your independent databases
on
> > separate servers...
> Yeah...we had been toying with the idea of using Replication to set up a
Search server, if
> you will. We already have a Log Shipping scheme; so that suggestion
interests me
> greatly -- I wonder if you can "Tee" the Log Shipping to go to multiple
destination
> servers? In that way, we'd continue with our regular/usual standby
server, and then
> divert those same logs to our Search server. That'd allow us to pretty
much leverage the
> same mechanism we have today, without having to introduce some additional
overhead of the
> Replication features.
>
> > > But, a lot of my knowledge of the Full Text Search service isn't as
strong
> > as it needs to
> > > be in order to make such decisions, so any help you can provide would
be
> > *greatly*
> > > appreciated! :-)
> >
> > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact
I'm
> > writing a book about it... <G>
> I can't wait! :-)
> Thanks again, John, your help has been of tremendous value to me! :-)
>
> > > Regards,
> > > > John
> > >
> > > As always, thank you for your time and expertise! :-)
> > >
> > > John Peterson
> > >
> > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > On regular intervals, we're seeing a lot of OS page faults on our
> > > > Production server
> > > > > (Windows 2000 Advanced Server), and have traced part of those
symptoms
> > to
> > > > the MSSEARCH.EXE
> > > > > executable. The box has 4GB of RAM, with SQL Server capped at
1.5GB.
> > > > >
> > > > > We've got a number of Full Text Catalogs in our databases (maybe
> > around
> > > > 10), and we've got
> > > > > about 10 databases with the same schema. We do an incremental
> > population
> > > > once at night,
> > > > > when our database activity is low.
> > > > >
> > > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> > sometimes
> > > > it's really
> > > > > heavy. I had assumed that the incremental population would kick
off
> > the
> > > > MSSEARCH.EXE
> > > > > service, but I was under the impression that (1) Full Text change
> > tracking
> > > > would be
> > > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > > MSSEARCH.EXE), and (2)
> > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > > MSSEARCH.EXE.
> > > > >
> > > > > I think my (2) assumption is invalid, and was hoping to get some
> > > > clarification.
> > > > >
> > > > > Thanks for any help you can provide! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||To quickly answer the question about SP3 and the PDF filter (I am on my way
out the door but did want to provide some helpful info):
The workaround and the fix (serialize the gathering process) are separate.
What we did in SP3 was include a proper single-threaded filter daemon that
loads the PDF (and other single threaded) filter. Previously what was
happening was the single-threaded PDF filter was being loaded in a
multi-threaded filter daemon, which is obviously not so good, so the
workaround was to serialize the process (slow, but worked) until a single
threaded filter daemon became available. So with SP3 there is no need to
tweak the registry for this issue.
Hope this helps,
--andrew
Andrew Cencini
Program Manager
Microsoft Corp. - SQL Server Engine
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OK0rSbwnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> You're welcome again, John...
> Inline comments can get a bit confusing to read in newsgroups postings, so
> my comments to yours are here at the top...
> Q. Is there any way to "track" that specific activity through a PerfMon
> counter, perchance?
> A. Yes, but you have to use the "Microsoft Search" performance counters
for
> the specific FT Catalog, see the " Microsoft Search Indexer Catalogs:
Merge
> Progress" counter as well as the other MSSearch counters and other SQL FTS
> related info in a Full-Text Search Deployment white paper at
>
http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/fts_white%20paper.asp
> (click on MONITORING FULL-TEXT SEARCH). Note, if you pick the right
> counters, you will see what I call the "heart-beat" of word lists to
shadow
> files to master files as it looks just like a heart-beat on a medical
> cardiograph (or at least it does to me ;-).
> Q. Or are they to very separate mechanisms that don't really operate in
> conjunction with one another?
> A. They are very separate mechanisms and don't operated in conjunction
with
> each other.
>
> Q. Is there a reason to launch the Task Manager from the AT command,
rather
> than just invoking it from the GUI directly?
> A. Yes. One is security as only the Administrator of the machine or a
member
> of that machine's Admin. Group can use the AT command prompt. The second
is
> that when launched from the AT command, the TaskMgr is operating under
> LocalSystem authority and this is necessary in order to access the
MSSearch
> *service* in order to change it's CPU affinity.
> Q. Is this issue something that has been fixed with SP3?
> A. It's unclear to me as well, after reading the RESOLUTION section of
this
> FIX KB article, whether or not anything more than a Registry key was
changed
> or even if it was changed. If you have not applied SP3, you can save the
> Gathering Manager key and then apply SP3 and note the difference in the
> RobotThreadsNumber key value and determine if any change was made. Note,
> RobotThreadsNumber seems to be a "generic" key/value and seems to only
> support a binary value of 1 or 0, from the KB article "The default value
is
> 0, which allows multithreaded access to any filter DLLs".
> Q. I wonder if you can "Tee" the Log Shipping to go to multiple
destination
> servers?
> A. I've not given this much thought, but perhaps you could use the concept
> of creating a "master SQL Server Agent job" and us this to "propagate"
your
> logs to "all involved target servers"? This would be a most interesting
> solution to your question and while I've not tested this myself, try it in
> your test environment and let us know if it works in your environment!!
> As for my book efforts, I'm getting very good feedback from a publisher
now
> and time will tell how that goes...
> Thanks,
> John
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uqkGJNvnDHA.964@.TK2MSFTNGP10.phx.gbl...
> > Hello John!
> >
> > Thank you *again* for the quick and informative reply! If it's not too
> onerous, please
> > see inline...
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > You're welcome, John,
> > > See more inline comments - [jtkane].
> > >
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > > >
> > > > Please see inline:
> > > >
> > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > Hello back, John,
> > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > (mssearch.exe)
> > > > > service normally at the end of your nightly Incremental Population
> when
> > > a
> > > > > Master Merge occurs.
> > > >
> > > > I haven't monitored it at night -- but I would imagine to see a
spike
> in
> > > CPU/RAM usage at
> > > > that time. :-)
> > >
> > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters
too)
> > > &/or Profiler to track the cpu/ram usage.
> > >
> > > > > You may also see it during a "shadow merge" while the
> > > > > Incremental Population is in progress as well, both are normal and
> > > expected
> > > > > as during these merges of new word lists, shadow index files into
> the
> > > master
> > > > > index file, a lot of computational process is ongoing. Additional,
> while
> > > > > this process does cause high-cpu usage, it is not normally for
long
> > > periods
> > > > > of time, and then subsides.
> > > >
> > > > I'm not familiar at *all* with this "shadow merge" concept. Is this
> only
> > > applicable
> > > > during an incremental population (that is, if we have just a nightly
> > > incremental
> > > > population, would it only happen during that time)?
> > >
> > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
Service
> as
> > > a method of merging memory-resident word lists into "shadow files" and
> these
> > > files into the "master index" file, somewhat like a sort/merge file
> > > process...
> >
> > Ah, very interesting! Is there any way to "track" that specific
activity
> through a
> > PerfMon counter, perchance?
> >
> >
> > > > > Actually both of your impressions/assumptions are incorrect.
"Change
> > > > > Tracking" with "Update Index in Background" as well as any of the
> SQL
> > > FTS
> > > > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> > > service as
> > > > > in SQL Server 2000 this is a necessary service that is external to
> SQL
> > > > > Server that manages the FT Catalogs and other FTS requirements.
> > > >
> > > > Ah! For some reason, I had assumed that the change tracking could
be
> done
> > > solely within
> > > > the confines of SQL Server due to the requisite TIMESTAMP on the
table
> > > that's
> > > > participating in the Full Text Catalog. But, it does make sense
that
> it's
> > > doing a "bit
> > > > more work" than I expect, and would need to leverage the MSSearch
> service
> > > executable. :-)
> > > >
> > > > And, I guess I would expect the same from the SQL FTS predicates
> > > CONTAINS*/FREETEXT*. It
> > > > was a bit naive to think that the operation could be done outside of
> the
> > > MSSearch service.
> > > >
> > > >
> > > > > Additionally, why are you still using nightly Incremental
> Populations,
> > > when
> > > > > you are aware of the benefits of "Change Tracking" with "Update
> Index
> > > in
> > > > > Background"? If you enabled both of these options, then the need
for
> > > your
> > > > > nightly Incremental Populations should go away. If you're are
> doing
> > > > > massive updates/deletes/inserts nightly, you can still use "Change
> > > Tracking"
> > > > > and turn off "Update Index in Background", and then do a scheduled
> > > > > Incremental Population.
> > > >
> > > > Actually, I'm *not* very clear on the whole Change Tracking thing.
I
> had
> > > assumed that was
> > > > sort of like the Replication "log reader" and merely collected
> information
> > > about those
> > > > rows that needed to participate in the Incremental Population. Am I
> to
> > > understand that
> > > > the Change Tracking is sort of mutualy exclusive from the
Incremental
> > > Population?
> > >
> > > [jtkane] - Yes, Change Tracking is "log reader" based as well as uses
an
> > > intentionally un-docucmented system table and is mutually exclusive
from
> > > Incremental Population.
> >
> > That's *very* good to know -- because I believe we may be doing *both*
> techniques.
> > Though, wouldn't the Incremental Population do nothing (or very little)
if
> the Change
> > Tracking has already done all the work? Or are they to very separate
> mechanisms that
> > don't really operate in conjunction with one another?
> >
> >
> > > > > Additionally, if you're server is a dual-proc or multiple cpu
> machine,
> > > there
> > > > > is secured & reliable methods for setting the CPU affinity of the
> > > mssearch
> > > > > service and keep separate from the MSSQLServer cpu affinity's so
> that
> > > the
> > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > >
> > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
have
> a
> > > handy link on how
> > > > we might keep these processes from potentially stepping on each
> others'
> > > "toes"? Is that a
> > > > good thing to do?
> > >
> > > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe
from
> the
> > > AT command on the multi-proc server where sql server resides, as
> follows:
> > >
> > > at <current_time+1min> /interactive taskmgr.exe
> > >
> > > when it launches, you can then set "cpu affinity" for the MSSearch
> service
> > > to a cpu or set of cpu's not being used by SQL Server. Then you would
> use
> > > sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> > > preventing the cpu usage of mssearch from affecting your sql server
> > > processing.
> >
> > Clever! I'll give that a whirl. Is there a reason to launch the Task
> Manager from the AT
> > command, rather than just invoking it from the GUI directly? I have the
> ability to Remote
> > Desktop into that machine.
> >
> >
> > > > I ask, because we're actually considering making a "Full Text
Service"
> > > server to kind of
> > > > manage all of our FT needs, for 3 reasons:
> > > >
> > > > (1) To help mitigate the impact of the MSSearch service on our SQL
> Server
> > > box, which is
> > > > hosting a number of clients. As I say, it appears to use a lot of
RAM
> and
> > > CPU at numerous
> > > > points during the day.
> > >
> > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> <value>,
> > > where <value> is a number between 1 and 5 (default 3) to control the
> amount
> > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
to
> a
> > > max of 512MB of RAM, but only if this amount of ram is available and
not
> > > used by any other process, incuding the OS and SQL Server.
> >
> > Heh! Again, I did not know that! I see that I'll be first in line
> whenever your book
> > comes out! ;-)
> >
> >
> > > > (2) We want to index .PDF files, and we have a IFilter from Adobe
> that
> > > works, but has
> > > > some caveats. One of them is that we have to limit the number of
> worker
> > > threads on the
> > > > entire server (I think I'm conveying this right -- it's kind of
> > > "third-hand" ;-). As
> > > > such, I understand that this would impact the entire server,
including
> SQL
> > > Server on that
> > > > box.
> > >
> > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
Server
> > > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
> Filter
> > > DLL May Not Succeed"
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> >
> > I haven't seen this article specifically, and it echoes a number of
points
> on Adobe's site
> > about their IFilter .DLL. One thing that strikes me: is this issue
> something that has
> > been fixed with SP3? It suggests that in the KB article, and then shows
> the Registry
> > "dink" as a workaround. Does SP3 do nothing more than the Registry
dink?
> If so, does it
> > do that in *all* cases, or just when the presence of an Adobe IFilter is
> detected? I ask,
> > because it'd be a shame to limit the number of Robot Threads (whatever
> *that* is! ;-) to
> > just 1 for all the "normal" MSSearch filters.
> >
> >
> > > > (3) To get around the 256 Full Text Catalog limit per server. I
> doubt
> > > that we'll
> > > > actually ever get there, but with over 100 FTCs on one of our
> Production
> > > servers, it's not
> > > > unreasonable to think we could double that...
> > >
> > > [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> > > regardless of the number of SQL Server instances on one machine, you
> still
> > > have this limit... You're best bet here is to "scale-out" and use
> mutiple
> > > server's possibly with Replication &/or log shipping between them to
> > > transfer the data if necessary, otherwise put your independent
databases
> on
> > > separate servers...
> >
> > Yeah...we had been toying with the idea of using Replication to set up a
> Search server, if
> > you will. We already have a Log Shipping scheme; so that suggestion
> interests me
> > greatly -- I wonder if you can "Tee" the Log Shipping to go to multiple
> destination
> > servers? In that way, we'd continue with our regular/usual standby
> server, and then
> > divert those same logs to our Search server. That'd allow us to pretty
> much leverage the
> > same mechanism we have today, without having to introduce some
additional
> overhead of the
> > Replication features.
> >
> >
> > > > But, a lot of my knowledge of the Full Text Search service isn't as
> strong
> > > as it needs to
> > > > be in order to make such decisions, so any help you can provide
would
> be
> > > *greatly*
> > > > appreciated! :-)
> > >
> > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in
fact
> I'm
> > > writing a book about it... <G>
> >
> > I can't wait! :-)
> >
> > Thanks again, John, your help has been of tremendous value to me! :-)
> >
> >
> > > > Regards,
> > > > > John
> > > >
> > > > As always, thank you for your time and expertise! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > On regular intervals, we're seeing a lot of OS page faults on
our
> > > > > Production server
> > > > > > (Windows 2000 Advanced Server), and have traced part of those
> symptoms
> > > to
> > > > > the MSSEARCH.EXE
> > > > > > executable. The box has 4GB of RAM, with SQL Server capped at
> 1.5GB.
> > > > > >
> > > > > > We've got a number of Full Text Catalogs in our databases (maybe
> > > around
> > > > > 10), and we've got
> > > > > > about 10 databases with the same schema. We do an incremental
> > > population
> > > > > once at night,
> > > > > > when our database activity is low.
> > > > > >
> > > > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> > > sometimes
> > > > > it's really
> > > > > > heavy. I had assumed that the incremental population would kick
> off
> > > the
> > > > > MSSEARCH.EXE
> > > > > > service, but I was under the impression that (1) Full Text
change
> > > tracking
> > > > > would be
> > > > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > > > MSSEARCH.EXE), and (2)
> > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > > > MSSEARCH.EXE.
> > > > > >
> > > > > > I think my (2) assumption is invalid, and was hoping to get some
> > > > > clarification.
> > > > > >
> > > > > > Thanks for any help you can provide! :-)
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, Andrew,
It would also be helpful to add this information to the KB article as it not
very informative. Either you or I can add a comment to this KB article, but
as you provided this more detailed info, perhaps it would be best for you to
add these additional and more informative information to this public KB
article and let this newsgroup know when the KB article has been updated...
Regards,
John
"Andrew Cencini [MS]" <acencini@.online.microsoft.com> wrote in message
news:uyNhu6wnDHA.2244@.TK2MSFTNGP12.phx.gbl...
> To quickly answer the question about SP3 and the PDF filter (I am on my
way
> out the door but did want to provide some helpful info):
> The workaround and the fix (serialize the gathering process) are separate.
> What we did in SP3 was include a proper single-threaded filter daemon that
> loads the PDF (and other single threaded) filter. Previously what was
> happening was the single-threaded PDF filter was being loaded in a
> multi-threaded filter daemon, which is obviously not so good, so the
> workaround was to serialize the process (slow, but worked) until a single
> threaded filter daemon became available. So with SP3 there is no need to
> tweak the registry for this issue.
> Hope this helps,
> --andrew
> Andrew Cencini
> Program Manager
> Microsoft Corp. - SQL Server Engine
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OK0rSbwnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > You're welcome again, John...
> > Inline comments can get a bit confusing to read in newsgroups postings,
so
> > my comments to yours are here at the top...
> >
> > Q. Is there any way to "track" that specific activity through a PerfMon
> > counter, perchance?
> > A. Yes, but you have to use the "Microsoft Search" performance counters
> for
> > the specific FT Catalog, see the " Microsoft Search Indexer Catalogs:
> Merge
> > Progress" counter as well as the other MSSearch counters and other SQL
FTS
> > related info in a Full-Text Search Deployment white paper at
> >
>
http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/fts_white%20paper.asp
> > (click on MONITORING FULL-TEXT SEARCH). Note, if you pick the right
> > counters, you will see what I call the "heart-beat" of word lists to
> shadow
> > files to master files as it looks just like a heart-beat on a medical
> > cardiograph (or at least it does to me ;-).
> >
> > Q. Or are they to very separate mechanisms that don't really operate in
> > conjunction with one another?
> > A. They are very separate mechanisms and don't operated in conjunction
> with
> > each other.
> >
> >
> > Q. Is there a reason to launch the Task Manager from the AT command,
> rather
> > than just invoking it from the GUI directly?
> > A. Yes. One is security as only the Administrator of the machine or a
> member
> > of that machine's Admin. Group can use the AT command prompt. The second
> is
> > that when launched from the AT command, the TaskMgr is operating under
> > LocalSystem authority and this is necessary in order to access the
> MSSearch
> > *service* in order to change it's CPU affinity.
> >
> > Q. Is this issue something that has been fixed with SP3?
> > A. It's unclear to me as well, after reading the RESOLUTION section of
> this
> > FIX KB article, whether or not anything more than a Registry key was
> changed
> > or even if it was changed. If you have not applied SP3, you can save the
> > Gathering Manager key and then apply SP3 and note the difference in the
> > RobotThreadsNumber key value and determine if any change was made. Note,
> > RobotThreadsNumber seems to be a "generic" key/value and seems to only
> > support a binary value of 1 or 0, from the KB article "The default value
> is
> > 0, which allows multithreaded access to any filter DLLs".
> >
> > Q. I wonder if you can "Tee" the Log Shipping to go to multiple
> destination
> > servers?
> > A. I've not given this much thought, but perhaps you could use the
concept
> > of creating a "master SQL Server Agent job" and us this to "propagate"
> your
> > logs to "all involved target servers"? This would be a most interesting
> > solution to your question and while I've not tested this myself, try it
in
> > your test environment and let us know if it works in your environment!!
> >
> > As for my book efforts, I'm getting very good feedback from a publisher
> now
> > and time will tell how that goes...
> > Thanks,
> > John
> >
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:uqkGJNvnDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > Hello John!
> > >
> > > Thank you *again* for the quick and informative reply! If it's not
too
> > onerous, please
> > > see inline...
> > >
> > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > > You're welcome, John,
> > > > See more inline comments - [jtkane].
> > > >
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > > > >
> > > > > Please see inline:
> > > > >
> > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > > Hello back, John,
> > > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > > (mssearch.exe)
> > > > > > service normally at the end of your nightly Incremental
Population
> > when
> > > > a
> > > > > > Master Merge occurs.
> > > > >
> > > > > I haven't monitored it at night -- but I would imagine to see a
> spike
> > in
> > > > CPU/RAM usage at
> > > > > that time. :-)
> > > >
> > > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters
> too)
> > > > &/or Profiler to track the cpu/ram usage.
> > > >
> > > > > > You may also see it during a "shadow merge" while the
> > > > > > Incremental Population is in progress as well, both are normal
and
> > > > expected
> > > > > > as during these merges of new word lists, shadow index files
into
> > the
> > > > master
> > > > > > index file, a lot of computational process is ongoing.
Additional,
> > while
> > > > > > this process does cause high-cpu usage, it is not normally for
> long
> > > > periods
> > > > > > of time, and then subsides.
> > > > >
> > > > > I'm not familiar at *all* with this "shadow merge" concept. Is
this
> > only
> > > > applicable
> > > > > during an incremental population (that is, if we have just a
nightly
> > > > incremental
> > > > > population, would it only happen during that time)?
> > > >
> > > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
> Service
> > as
> > > > a method of merging memory-resident word lists into "shadow files"
and
> > these
> > > > files into the "master index" file, somewhat like a sort/merge file
> > > > process...
> > >
> > > Ah, very interesting! Is there any way to "track" that specific
> activity
> > through a
> > > PerfMon counter, perchance?
> > >
> > >
> > > > > > Actually both of your impressions/assumptions are incorrect.
> "Change
> > > > > > Tracking" with "Update Index in Background" as well as any of
the
> > SQL
> > > > FTS
> > > > > > predicates CONTAINS* or FREETEXT* will use or invoke the
MSSearch
> > > > service as
> > > > > > in SQL Server 2000 this is a necessary service that is external
to
> > SQL
> > > > > > Server that manages the FT Catalogs and other FTS requirements.
> > > > >
> > > > > Ah! For some reason, I had assumed that the change tracking could
> be
> > done
> > > > solely within
> > > > > the confines of SQL Server due to the requisite TIMESTAMP on the
> table
> > > > that's
> > > > > participating in the Full Text Catalog. But, it does make sense
> that
> > it's
> > > > doing a "bit
> > > > > more work" than I expect, and would need to leverage the MSSearch
> > service
> > > > executable. :-)
> > > > >
> > > > > And, I guess I would expect the same from the SQL FTS predicates
> > > > CONTAINS*/FREETEXT*. It
> > > > > was a bit naive to think that the operation could be done outside
of
> > the
> > > > MSSearch service.
> > > > >
> > > > >
> > > > > > Additionally, why are you still using nightly Incremental
> > Populations,
> > > > when
> > > > > > you are aware of the benefits of "Change Tracking" with "Update
> > Index
> > > > in
> > > > > > Background"? If you enabled both of these options, then the need
> for
> > > > your
> > > > > > nightly Incremental Populations should go away. If you're are
> > doing
> > > > > > massive updates/deletes/inserts nightly, you can still use
"Change
> > > > Tracking"
> > > > > > and turn off "Update Index in Background", and then do a
scheduled
> > > > > > Incremental Population.
> > > > >
> > > > > Actually, I'm *not* very clear on the whole Change Tracking thing.
> I
> > had
> > > > assumed that was
> > > > > sort of like the Replication "log reader" and merely collected
> > information
> > > > about those
> > > > > rows that needed to participate in the Incremental Population. Am
I
> > to
> > > > understand that
> > > > > the Change Tracking is sort of mutualy exclusive from the
> Incremental
> > > > Population?
> > > >
> > > > [jtkane] - Yes, Change Tracking is "log reader" based as well as
uses
> an
> > > > intentionally un-docucmented system table and is mutually exclusive
> from
> > > > Incremental Population.
> > >
> > > That's *very* good to know -- because I believe we may be doing *both*
> > techniques.
> > > Though, wouldn't the Incremental Population do nothing (or very
little)
> if
> > the Change
> > > Tracking has already done all the work? Or are they to very separate
> > mechanisms that
> > > don't really operate in conjunction with one another?
> > >
> > >
> > > > > > Additionally, if you're server is a dual-proc or multiple cpu
> > machine,
> > > > there
> > > > > > is secured & reliable methods for setting the CPU affinity of
the
> > > > mssearch
> > > > > > service and keep separate from the MSSQLServer cpu affinity's so
> > that
> > > > the
> > > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > > >
> > > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
> have
> > a
> > > > handy link on how
> > > > > we might keep these processes from potentially stepping on each
> > others'
> > > > "toes"? Is that a
> > > > > good thing to do?
> > > >
> > > > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe
> from
> > the
> > > > AT command on the multi-proc server where sql server resides, as
> > follows:
> > > >
> > > > at <current_time+1min> /interactive taskmgr.exe
> > > >
> > > > when it launches, you can then set "cpu affinity" for the MSSearch
> > service
> > > > to a cpu or set of cpu's not being used by SQL Server. Then you
would
> > use
> > > > sp_configure to set SQL Server's cpu affinity to the other cpu's,
thus
> > > > preventing the cpu usage of mssearch from affecting your sql server
> > > > processing.
> > >
> > > Clever! I'll give that a whirl. Is there a reason to launch the Task
> > Manager from the AT
> > > command, rather than just invoking it from the GUI directly? I have
the
> > ability to Remote
> > > Desktop into that machine.
> > >
> > >
> > > > > I ask, because we're actually considering making a "Full Text
> Service"
> > > > server to kind of
> > > > > manage all of our FT needs, for 3 reasons:
> > > > >
> > > > > (1) To help mitigate the impact of the MSSearch service on our
SQL
> > Server
> > > > box, which is
> > > > > hosting a number of clients. As I say, it appears to use a lot of
> RAM
> > and
> > > > CPU at numerous
> > > > > points during the day.
> > > >
> > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > <value>,
> > > > where <value> is a number between 1 and 5 (default 3) to control the
> > amount
> > > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
> to
> > a
> > > > max of 512MB of RAM, but only if this amount of ram is available and
> not
> > > > used by any other process, incuding the OS and SQL Server.
> > >
> > > Heh! Again, I did not know that! I see that I'll be first in line
> > whenever your book
> > > comes out! ;-)
> > >
> > >
> > > > > (2) We want to index .PDF files, and we have a IFilter from Adobe
> > that
> > > > works, but has
> > > > > some caveats. One of them is that we have to limit the number of
> > worker
> > > > threads on the
> > > > > entire server (I think I'm conveying this right -- it's kind of
> > > > "third-hand" ;-). As
> > > > > such, I understand that this would impact the entire server,
> including
> > SQL
> > > > Server on that
> > > > > box.
> > > >
> > > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
> Server
> > > > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
> > Filter
> > > > DLL May Not Succeed"
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > >
> > > I haven't seen this article specifically, and it echoes a number of
> points
> > on Adobe's site
> > > about their IFilter .DLL. One thing that strikes me: is this issue
> > something that has
> > > been fixed with SP3? It suggests that in the KB article, and then
shows
> > the Registry
> > > "dink" as a workaround. Does SP3 do nothing more than the Registry
> dink?
> > If so, does it
> > > do that in *all* cases, or just when the presence of an Adobe IFilter
is
> > detected? I ask,
> > > because it'd be a shame to limit the number of Robot Threads (whatever
> > *that* is! ;-) to
> > > just 1 for all the "normal" MSSearch filters.
> > >
> > >
> > > > > (3) To get around the 256 Full Text Catalog limit per server. I
> > doubt
> > > > that we'll
> > > > > actually ever get there, but with over 100 FTCs on one of our
> > Production
> > > > servers, it's not
> > > > > unreasonable to think we could double that...
> > > >
> > > > [jtkane] - Yep, this is a hard one... The 256 limit is per machine,
so
> > > > regardless of the number of SQL Server instances on one machine, you
> > still
> > > > have this limit... You're best bet here is to "scale-out" and use
> > mutiple
> > > > server's possibly with Replication &/or log shipping between them to
> > > > transfer the data if necessary, otherwise put your independent
> databases
> > on
> > > > separate servers...
> > >
> > > Yeah...we had been toying with the idea of using Replication to set up
a
> > Search server, if
> > > you will. We already have a Log Shipping scheme; so that suggestion
> > interests me
> > > greatly -- I wonder if you can "Tee" the Log Shipping to go to
multiple
> > destination
> > > servers? In that way, we'd continue with our regular/usual standby
> > server, and then
> > > divert those same logs to our Search server. That'd allow us to
pretty
> > much leverage the
> > > same mechanism we have today, without having to introduce some
> additional
> > overhead of the
> > > Replication features.
> > >
> > >
> > > > > But, a lot of my knowledge of the Full Text Search service isn't
as
> > strong
> > > > as it needs to
> > > > > be in order to make such decisions, so any help you can provide
> would
> > be
> > > > *greatly*
> > > > > appreciated! :-)
> > > >
> > > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in
> fact
> > I'm
> > > > writing a book about it... <G>
> > >
> > > I can't wait! :-)
> > >
> > > Thanks again, John, your help has been of tremendous value to me! :-)
> > >
> > >
> > > > > Regards,
> > > > > > John
> > > > >
> > > > > As always, thank you for your time and expertise! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > > (SQL Server 2000, SP3a)
> > > > > > >
> > > > > > > Hello all!
> > > > > > >
> > > > > > > On regular intervals, we're seeing a lot of OS page faults on
> our
> > > > > > Production server
> > > > > > > (Windows 2000 Advanced Server), and have traced part of those
> > symptoms
> > > > to
> > > > > > the MSSEARCH.EXE
> > > > > > > executable. The box has 4GB of RAM, with SQL Server capped at
> > 1.5GB.
> > > > > > >
> > > > > > > We've got a number of Full Text Catalogs in our databases
(maybe
> > > > around
> > > > > > 10), and we've got
> > > > > > > about 10 databases with the same schema. We do an incremental
> > > > population
> > > > > > once at night,
> > > > > > > when our database activity is low.
> > > > > > >
> > > > > > > We'll see regular MSSEARCH.EXE activity throughout the day,
and
> > > > sometimes
> > > > > > it's really
> > > > > > > heavy. I had assumed that the incremental population would
kick
> > off
> > > > the
> > > > > > MSSEARCH.EXE
> > > > > > > service, but I was under the impression that (1) Full Text
> change
> > > > tracking
> > > > > > would be
> > > > > > > incorporated wholly under the auspices of SQL Server (not
invoke
> > > > > > MSSEARCH.EXE), and (2)
> > > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to
invoke
> > > > > > MSSEARCH.EXE.
> > > > > > >
> > > > > > > I think my (2) assumption is invalid, and was hoping to get
some
> > > > > > clarification.
> > > > > > >
> > > > > > > Thanks for any help you can provide! :-)
> > > > > > >
> > > > > > > John Peterson
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I've passed this along to our content folks -- they handle these things, so
they'll take care of it from here.
--andrew
Andrew Cencini
Program Manager
Microsoft Corp. - SQL Server Engine
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ef85DqxnDHA.2140@.TK2MSFTNGP09.phx.gbl...
> Thanks, Andrew,
> It would also be helpful to add this information to the KB article as it
not
> very informative. Either you or I can add a comment to this KB article,
but
> as you provided this more detailed info, perhaps it would be best for you
to
> add these additional and more informative information to this public KB
> article and let this newsgroup know when the KB article has been
updated...
> Regards,
> John
>
> "Andrew Cencini [MS]" <acencini@.online.microsoft.com> wrote in message
> news:uyNhu6wnDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > To quickly answer the question about SP3 and the PDF filter (I am on my
> way
> > out the door but did want to provide some helpful info):
> >
> > The workaround and the fix (serialize the gathering process) are
separate.
> > What we did in SP3 was include a proper single-threaded filter daemon
that
> > loads the PDF (and other single threaded) filter. Previously what was
> > happening was the single-threaded PDF filter was being loaded in a
> > multi-threaded filter daemon, which is obviously not so good, so the
> > workaround was to serialize the process (slow, but worked) until a
single
> > threaded filter daemon became available. So with SP3 there is no need
to
> > tweak the registry for this issue.
> >
> > Hope this helps,
> > --andrew
> >
> > Andrew Cencini
> > Program Manager
> > Microsoft Corp. - SQL Server Engine
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:OK0rSbwnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > You're welcome again, John...
> > > Inline comments can get a bit confusing to read in newsgroups
postings,
> so
> > > my comments to yours are here at the top...
> > >
> > > Q. Is there any way to "track" that specific activity through a
PerfMon
> > > counter, perchance?
> > > A. Yes, but you have to use the "Microsoft Search" performance
counters
> > for
> > > the specific FT Catalog, see the " Microsoft Search Indexer Catalogs:
> > Merge
> > > Progress" counter as well as the other MSSearch counters and other SQL
> FTS
> > > related info in a Full-Text Search Deployment white paper at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/fts_white%20paper.asp
> > > (click on MONITORING FULL-TEXT SEARCH). Note, if you pick the right
> > > counters, you will see what I call the "heart-beat" of word lists to
> > shadow
> > > files to master files as it looks just like a heart-beat on a medical
> > > cardiograph (or at least it does to me ;-).
> > >
> > > Q. Or are they to very separate mechanisms that don't really operate
in
> > > conjunction with one another?
> > > A. They are very separate mechanisms and don't operated in conjunction
> > with
> > > each other.
> > >
> > >
> > > Q. Is there a reason to launch the Task Manager from the AT command,
> > rather
> > > than just invoking it from the GUI directly?
> > > A. Yes. One is security as only the Administrator of the machine or a
> > member
> > > of that machine's Admin. Group can use the AT command prompt. The
second
> > is
> > > that when launched from the AT command, the TaskMgr is operating under
> > > LocalSystem authority and this is necessary in order to access the
> > MSSearch
> > > *service* in order to change it's CPU affinity.
> > >
> > > Q. Is this issue something that has been fixed with SP3?
> > > A. It's unclear to me as well, after reading the RESOLUTION section of
> > this
> > > FIX KB article, whether or not anything more than a Registry key was
> > changed
> > > or even if it was changed. If you have not applied SP3, you can save
the
> > > Gathering Manager key and then apply SP3 and note the difference in
the
> > > RobotThreadsNumber key value and determine if any change was made.
Note,
> > > RobotThreadsNumber seems to be a "generic" key/value and seems to only
> > > support a binary value of 1 or 0, from the KB article "The default
value
> > is
> > > 0, which allows multithreaded access to any filter DLLs".
> > >
> > > Q. I wonder if you can "Tee" the Log Shipping to go to multiple
> > destination
> > > servers?
> > > A. I've not given this much thought, but perhaps you could use the
> concept
> > > of creating a "master SQL Server Agent job" and us this to "propagate"
> > your
> > > logs to "all involved target servers"? This would be a most
interesting
> > > solution to your question and while I've not tested this myself, try
it
> in
> > > your test environment and let us know if it works in your
environment!!
> > >
> > > As for my book efforts, I'm getting very good feedback from a
publisher
> > now
> > > and time will tell how that goes...
> > > Thanks,
> > > John
> > >
> > >
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:uqkGJNvnDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > Hello John!
> > > >
> > > > Thank you *again* for the quick and informative reply! If it's not
> too
> > > onerous, please
> > > > see inline...
> > > >
> > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > > > You're welcome, John,
> > > > > See more inline comments - [jtkane].
> > > > >
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > > > Hello John! Thank you for such a speedy and detailed reply!
:-)
> > > > > >
> > > > > > Please see inline:
> > > > > >
> > > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > > > Hello back, John,
> > > > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > > > (mssearch.exe)
> > > > > > > service normally at the end of your nightly Incremental
> Population
> > > when
> > > > > a
> > > > > > > Master Merge occurs.
> > > > > >
> > > > > > I haven't monitored it at night -- but I would imagine to see a
> > spike
> > > in
> > > > > CPU/RAM usage at
> > > > > > that time. :-)
> > > > >
> > > > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch
counters
> > too)
> > > > > &/or Profiler to track the cpu/ram usage.
> > > > >
> > > > > > > You may also see it during a "shadow merge" while the
> > > > > > > Incremental Population is in progress as well, both are normal
> and
> > > > > expected
> > > > > > > as during these merges of new word lists, shadow index files
> into
> > > the
> > > > > master
> > > > > > > index file, a lot of computational process is ongoing.
> Additional,
> > > while
> > > > > > > this process does cause high-cpu usage, it is not normally for
> > long
> > > > > periods
> > > > > > > of time, and then subsides.
> > > > > >
> > > > > > I'm not familiar at *all* with this "shadow merge" concept. Is
> this
> > > only
> > > > > applicable
> > > > > > during an incremental population (that is, if we have just a
> nightly
> > > > > incremental
> > > > > > population, would it only happen during that time)?
> > > > >
> > > > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
> > Service
> > > as
> > > > > a method of merging memory-resident word lists into "shadow files"
> and
> > > these
> > > > > files into the "master index" file, somewhat like a sort/merge
file
> > > > > process...
> > > >
> > > > Ah, very interesting! Is there any way to "track" that specific
> > activity
> > > through a
> > > > PerfMon counter, perchance?
> > > >
> > > >
> > > > > > > Actually both of your impressions/assumptions are incorrect.
> > "Change
> > > > > > > Tracking" with "Update Index in Background" as well as any of
> the
> > > SQL
> > > > > FTS
> > > > > > > predicates CONTAINS* or FREETEXT* will use or invoke the
> MSSearch
> > > > > service as
> > > > > > > in SQL Server 2000 this is a necessary service that is
external
> to
> > > SQL
> > > > > > > Server that manages the FT Catalogs and other FTS
requirements.
> > > > > >
> > > > > > Ah! For some reason, I had assumed that the change tracking
could
> > be
> > > done
> > > > > solely within
> > > > > > the confines of SQL Server due to the requisite TIMESTAMP on the
> > table
> > > > > that's
> > > > > > participating in the Full Text Catalog. But, it does make sense
> > that
> > > it's
> > > > > doing a "bit
> > > > > > more work" than I expect, and would need to leverage the
MSSearch
> > > service
> > > > > executable. :-)
> > > > > >
> > > > > > And, I guess I would expect the same from the SQL FTS predicates
> > > > > CONTAINS*/FREETEXT*. It
> > > > > > was a bit naive to think that the operation could be done
outside
> of
> > > the
> > > > > MSSearch service.
> > > > > >
> > > > > >
> > > > > > > Additionally, why are you still using nightly Incremental
> > > Populations,
> > > > > when
> > > > > > > you are aware of the benefits of "Change Tracking" with
"Update
> > > Index
> > > > > in
> > > > > > > Background"? If you enabled both of these options, then the
need
> > for
> > > > > your
> > > > > > > nightly Incremental Populations should go away. If you're
are
> > > doing
> > > > > > > massive updates/deletes/inserts nightly, you can still use
> "Change
> > > > > Tracking"
> > > > > > > and turn off "Update Index in Background", and then do a
> scheduled
> > > > > > > Incremental Population.
> > > > > >
> > > > > > Actually, I'm *not* very clear on the whole Change Tracking
thing.
> > I
> > > had
> > > > > assumed that was
> > > > > > sort of like the Replication "log reader" and merely collected
> > > information
> > > > > about those
> > > > > > rows that needed to participate in the Incremental Population.
Am
> I
> > > to
> > > > > understand that
> > > > > > the Change Tracking is sort of mutualy exclusive from the
> > Incremental
> > > > > Population?
> > > > >
> > > > > [jtkane] - Yes, Change Tracking is "log reader" based as well as
> uses
> > an
> > > > > intentionally un-docucmented system table and is mutually
exclusive
> > from
> > > > > Incremental Population.
> > > >
> > > > That's *very* good to know -- because I believe we may be doing
*both*
> > > techniques.
> > > > Though, wouldn't the Incremental Population do nothing (or very
> little)
> > if
> > > the Change
> > > > Tracking has already done all the work? Or are they to very
separate
> > > mechanisms that
> > > > don't really operate in conjunction with one another?
> > > >
> > > >
> > > > > > > Additionally, if you're server is a dual-proc or multiple cpu
> > > machine,
> > > > > there
> > > > > > > is secured & reliable methods for setting the CPU affinity of
> the
> > > > > mssearch
> > > > > > > service and keep separate from the MSSQLServer cpu affinity's
so
> > > that
> > > > > the
> > > > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > > > >
> > > > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
> > have
> > > a
> > > > > handy link on how
> > > > > > we might keep these processes from potentially stepping on each
> > > others'
> > > > > "toes"? Is that a
> > > > > > good thing to do?
> > > > >
> > > > > [jtkane] - Yes, this is a good thing. You can launch the
Tskmgr.exe
> > from
> > > the
> > > > > AT command on the multi-proc server where sql server resides, as
> > > follows:
> > > > >
> > > > > at <current_time+1min> /interactive taskmgr.exe
> > > > >
> > > > > when it launches, you can then set "cpu affinity" for the MSSearch
> > > service
> > > > > to a cpu or set of cpu's not being used by SQL Server. Then you
> would
> > > use
> > > > > sp_configure to set SQL Server's cpu affinity to the other cpu's,
> thus
> > > > > preventing the cpu usage of mssearch from affecting your sql
server
> > > > > processing.
> > > >
> > > > Clever! I'll give that a whirl. Is there a reason to launch the
Task
> > > Manager from the AT
> > > > command, rather than just invoking it from the GUI directly? I have
> the
> > > ability to Remote
> > > > Desktop into that machine.
> > > >
> > > >
> > > > > > I ask, because we're actually considering making a "Full Text
> > Service"
> > > > > server to kind of
> > > > > > manage all of our FT needs, for 3 reasons:
> > > > > >
> > > > > > (1) To help mitigate the impact of the MSSearch service on our
> SQL
> > > Server
> > > > > box, which is
> > > > > > hosting a number of clients. As I say, it appears to use a lot
of
> > RAM
> > > and
> > > > > CPU at numerous
> > > > > > points during the day.
> > > > >
> > > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > > <value>,
> > > > > where <value> is a number between 1 and 5 (default 3) to control
the
> > > amount
> > > > > of RAM that the MSSearch service will use, 5 (dedicated) will use
up
> > to
> > > a
> > > > > max of 512MB of RAM, but only if this amount of ram is available
and
> > not
> > > > > used by any other process, incuding the OS and SQL Server.
> > > >
> > > > Heh! Again, I did not know that! I see that I'll be first in line
> > > whenever your book
> > > > comes out! ;-)
> > > >
> > > >
> > > > > > (2) We want to index .PDF files, and we have a IFilter from
Adobe
> > > that
> > > > > works, but has
> > > > > > some caveats. One of them is that we have to limit the number
of
> > > worker
> > > > > threads on the
> > > > > > entire server (I think I'm conveying this right -- it's kind of
> > > > > "third-hand" ;-). As
> > > > > > such, I understand that this would impact the entire server,
> > including
> > > SQL
> > > > > Server on that
> > > > > > box.
> > > > >
> > > > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
> > Server
> > > > > Full-Text Population by Using a Single-Threaded Filter DLL or a
PDF
> > > Filter
> > > > > DLL May Not Succeed"
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > > >
> > > > I haven't seen this article specifically, and it echoes a number of
> > points
> > > on Adobe's site
> > > > about their IFilter .DLL. One thing that strikes me: is this issue
> > > something that has
> > > > been fixed with SP3? It suggests that in the KB article, and then
> shows
> > > the Registry
> > > > "dink" as a workaround. Does SP3 do nothing more than the Registry
> > dink?
> > > If so, does it
> > > > do that in *all* cases, or just when the presence of an Adobe
IFilter
> is
> > > detected? I ask,
> > > > because it'd be a shame to limit the number of Robot Threads
(whatever
> > > *that* is! ;-) to
> > > > just 1 for all the "normal" MSSearch filters.
> > > >
> > > >
> > > > > > (3) To get around the 256 Full Text Catalog limit per server.
I
> > > doubt
> > > > > that we'll
> > > > > > actually ever get there, but with over 100 FTCs on one of our
> > > Production
> > > > > servers, it's not
> > > > > > unreasonable to think we could double that...
> > > > >
> > > > > [jtkane] - Yep, this is a hard one... The 256 limit is per
machine,
> so
> > > > > regardless of the number of SQL Server instances on one machine,
you
> > > still
> > > > > have this limit... You're best bet here is to "scale-out" and use
> > > mutiple
> > > > > server's possibly with Replication &/or log shipping between them
to
> > > > > transfer the data if necessary, otherwise put your independent
> > databases
> > > on
> > > > > separate servers...
> > > >
> > > > Yeah...we had been toying with the idea of using Replication to set
up
> a
> > > Search server, if
> > > > you will. We already have a Log Shipping scheme; so that suggestion
> > > interests me
> > > > greatly -- I wonder if you can "Tee" the Log Shipping to go to
> multiple
> > > destination
> > > > servers? In that way, we'd continue with our regular/usual standby
> > > server, and then
> > > > divert those same logs to our Search server. That'd allow us to
> pretty
> > > much leverage the
> > > > same mechanism we have today, without having to introduce some
> > additional
> > > overhead of the
> > > > Replication features.
> > > >
> > > >
> > > > > > But, a lot of my knowledge of the Full Text Search service isn't
> as
> > > strong
> > > > > as it needs to
> > > > > > be in order to make such decisions, so any help you can provide
> > would
> > > be
> > > > > *greatly*
> > > > > > appreciated! :-)
> > > > >
> > > > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong,
in
> > fact
> > > I'm
> > > > > writing a book about it... <G>
> > > >
> > > > I can't wait! :-)
> > > >
> > > > Thanks again, John, your help has been of tremendous value to me!
:-)
> > > >
> > > >
> > > > > > Regards,
> > > > > > > John
> > > > > >
> > > > > > As always, thank you for your time and expertise! :-)
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > > > (SQL Server 2000, SP3a)
> > > > > > > >
> > > > > > > > Hello all!
> > > > > > > >
> > > > > > > > On regular intervals, we're seeing a lot of OS page faults
on
> > our
> > > > > > > Production server
> > > > > > > > (Windows 2000 Advanced Server), and have traced part of
those
> > > symptoms
> > > > > to
> > > > > > > the MSSEARCH.EXE
> > > > > > > > executable. The box has 4GB of RAM, with SQL Server capped
at
> > > 1.5GB.
> > > > > > > >
> > > > > > > > We've got a number of Full Text Catalogs in our databases
> (maybe
> > > > > around
> > > > > > > 10), and we've got
> > > > > > > > about 10 databases with the same schema. We do an
incremental
> > > > > population
> > > > > > > once at night,
> > > > > > > > when our database activity is low.
> > > > > > > >
> > > > > > > > We'll see regular MSSEARCH.EXE activity throughout the day,
> and
> > > > > sometimes
> > > > > > > it's really
> > > > > > > > heavy. I had assumed that the incremental population would
> kick
> > > off
> > > > > the
> > > > > > > MSSEARCH.EXE
> > > > > > > > service, but I was under the impression that (1) Full Text
> > change
> > > > > tracking
> > > > > > > would be
> > > > > > > > incorporated wholly under the auspices of SQL Server (not
> invoke
> > > > > > > MSSEARCH.EXE), and (2)
> > > > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to
> invoke
> > > > > > > MSSEARCH.EXE.
> > > > > > > >
> > > > > > > > I think my (2) assumption is invalid, and was hoping to get
> some
> > > > > > > clarification.
> > > > > > > >
> > > > > > > > Thanks for any help you can provide! :-)
> > > > > > > >
> > > > > > > > John Peterson
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
I wanted to ask about a specific issue that you responded with:
<Quote>
[jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
where <value> is a number between 1 and 5 (default 3) to control the amount
of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
max of 512MB of RAM, but only if this amount of ram is available and not
used by any other process, incuding the OS and SQL Server.
</Quote>
Is there any way to tell what the existing resource_usage is set at? We're seeing, in
some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM. We weren't sure if
that meant that we might already be at 5 (dedicated) (like 512MB for "data" and 300+ MB
for application). Thanks!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> You're welcome, John,
> See more inline comments - [jtkane].
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > Hello John! Thank you for such a speedy and detailed reply! :-)
> >
> > Please see inline:
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > Hello back, John,
> > > Yes, you will see high CPU usage from the "Microsoft Search"
> (mssearch.exe)
> > > service normally at the end of your nightly Incremental Population when
> a
> > > Master Merge occurs.
> >
> > I haven't monitored it at night -- but I would imagine to see a spike in
> CPU/RAM usage at
> > that time. :-)
> [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
> &/or Profiler to track the cpu/ram usage.
> > > You may also see it during a "shadow merge" while the
> > > Incremental Population is in progress as well, both are normal and
> expected
> > > as during these merges of new word lists, shadow index files into the
> master
> > > index file, a lot of computational process is ongoing. Additional, while
> > > this process does cause high-cpu usage, it is not normally for long
> periods
> > > of time, and then subsides.
> >
> > I'm not familiar at *all* with this "shadow merge" concept. Is this only
> applicable
> > during an incremental population (that is, if we have just a nightly
> incremental
> > population, would it only happen during that time)?
> [jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service as
> a method of merging memory-resident word lists into "shadow files" and these
> files into the "master index" file, somewhat like a sort/merge file
> process...
> > > Actually both of your impressions/assumptions are incorrect. "Change
> > > Tracking" with "Update Index in Background" as well as any of the SQL
> FTS
> > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> service as
> > > in SQL Server 2000 this is a necessary service that is external to SQL
> > > Server that manages the FT Catalogs and other FTS requirements.
> >
> > Ah! For some reason, I had assumed that the change tracking could be done
> solely within
> > the confines of SQL Server due to the requisite TIMESTAMP on the table
> that's
> > participating in the Full Text Catalog. But, it does make sense that it's
> doing a "bit
> > more work" than I expect, and would need to leverage the MSSearch service
> executable. :-)
> >
> > And, I guess I would expect the same from the SQL FTS predicates
> CONTAINS*/FREETEXT*. It
> > was a bit naive to think that the operation could be done outside of the
> MSSearch service.
> >
> >
> > > Additionally, why are you still using nightly Incremental Populations,
> when
> > > you are aware of the benefits of "Change Tracking" with "Update Index
> in
> > > Background"? If you enabled both of these options, then the need for
> your
> > > nightly Incremental Populations should go away. If you're are doing
> > > massive updates/deletes/inserts nightly, you can still use "Change
> Tracking"
> > > and turn off "Update Index in Background", and then do a scheduled
> > > Incremental Population.
> >
> > Actually, I'm *not* very clear on the whole Change Tracking thing. I had
> assumed that was
> > sort of like the Replication "log reader" and merely collected information
> about those
> > rows that needed to participate in the Incremental Population. Am I to
> understand that
> > the Change Tracking is sort of mutualy exclusive from the Incremental
> Population?
> [jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
> intentionally un-docucmented system table and is mutually exclusive from
> Incremental Population.
> > > Additionally, if you're server is a dual-proc or multiple cpu machine,
> there
> > > is secured & reliable methods for setting the CPU affinity of the
> mssearch
> > > service and keep separate from the MSSQLServer cpu affinity's so that
> the
> > > mssearch cpu usage will not affect your SQL Server processing.
> >
> > Ooo...we *are* in a multiple CPU context (4, I believe). Do you have a
> handy link on how
> > we might keep these processes from potentially stepping on each others'
> "toes"? Is that a
> > good thing to do?
> [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from the
> AT command on the multi-proc server where sql server resides, as follows:
> at <current_time+1min> /interactive taskmgr.exe
> when it launches, you can then set "cpu affinity" for the MSSearch service
> to a cpu or set of cpu's not being used by SQL Server. Then you would use
> sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> preventing the cpu usage of mssearch from affecting your sql server
> processing.
> > I ask, because we're actually considering making a "Full Text Service"
> server to kind of
> > manage all of our FT needs, for 3 reasons:
> >
> > (1) To help mitigate the impact of the MSSearch service on our SQL Server
> box, which is
> > hosting a number of clients. As I say, it appears to use a lot of RAM and
> CPU at numerous
> > points during the day.
> [jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
> where <value> is a number between 1 and 5 (default 3) to control the amount
> of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
> max of 512MB of RAM, but only if this amount of ram is available and not
> used by any other process, incuding the OS and SQL Server.
>
> > (2) We want to index .PDF files, and we have a IFilter from Adobe that
> works, but has
> > some caveats. One of them is that we have to limit the number of worker
> threads on the
> > entire server (I think I'm conveying this right -- it's kind of
> "third-hand" ;-). As
> > such, I understand that this would impact the entire server, including SQL
> Server on that
> > box.
> [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
> Full-Text Population by Using a Single-Threaded Filter DLL or a PDF Filter
> DLL May Not Succeed"
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > (3) To get around the 256 Full Text Catalog limit per server. I doubt
> that we'll
> > actually ever get there, but with over 100 FTCs on one of our Production
> servers, it's not
> > unreasonable to think we could double that...
> [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> regardless of the number of SQL Server instances on one machine, you still
> have this limit... You're best bet here is to "scale-out" and use mutiple
> server's possibly with Replication &/or log shipping between them to
> transfer the data if necessary, otherwise put your independent databases on
> separate servers...
> > But, a lot of my knowledge of the Full Text Search service isn't as strong
> as it needs to
> > be in order to make such decisions, so any help you can provide would be
> *greatly*
> > appreciated! :-)
> [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact I'm
> writing a book about it... <G>
> > Regards,
> > > John
> >
> > As always, thank you for your time and expertise! :-)
> >
> > John Peterson
> >
> >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > On regular intervals, we're seeing a lot of OS page faults on our
> > > Production server
> > > > (Windows 2000 Advanced Server), and have traced part of those symptoms
> to
> > > the MSSEARCH.EXE
> > > > executable. The box has 4GB of RAM, with SQL Server capped at 1.5GB.
> > > >
> > > > We've got a number of Full Text Catalogs in our databases (maybe
> around
> > > 10), and we've got
> > > > about 10 databases with the same schema. We do an incremental
> population
> > > once at night,
> > > > when our database activity is low.
> > > >
> > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> sometimes
> > > it's really
> > > > heavy. I had assumed that the incremental population would kick off
> the
> > > MSSEARCH.EXE
> > > > service, but I was under the impression that (1) Full Text change
> tracking
> > > would be
> > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > MSSEARCH.EXE), and (2)
> > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > MSSEARCH.EXE.
> > > >
> > > > I think my (2) assumption is invalid, and was hoping to get some
> > > clarification.
> > > >
> > > > Thanks for any help you can provide! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
Yes, there is... actually two methods...
SELECT fulltextserviceproperty('ResourceUsage') -- returns resource_usage
value
as well as track the following Perfmon counter "Microsoft Gatherer:
Performance Level"
You might also want to track the "Process: Private Bytes" memory usage level
for the MSSearch service as well there maybe some additional memory
processing going on... How much actual RAM do you have available, when your
resource_usage level is at 5, and this memory is not being used by the OS or
SQL Server?
Thanks,
John
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O2BOaHloDHA.744@.tk2msftngp13.phx.gbl...
> John,
> I wanted to ask about a specific issue that you responded with:
> <Quote>
> [jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
> where <value> is a number between 1 and 5 (default 3) to control the
amount
> of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
> max of 512MB of RAM, but only if this amount of ram is available and not
> used by any other process, incuding the OS and SQL Server.
> </Quote>
> Is there any way to tell what the existing resource_usage is set at?
We're seeing, in
> some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM. We
weren't sure if
> that meant that we might already be at 5 (dedicated) (like 512MB for
"data" and 300+ MB
> for application). Thanks!
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > You're welcome, John,
> > See more inline comments - [jtkane].
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > >
> > > Please see inline:
> > >
> > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > Hello back, John,
> > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > (mssearch.exe)
> > > > service normally at the end of your nightly Incremental Population
when
> > a
> > > > Master Merge occurs.
> > >
> > > I haven't monitored it at night -- but I would imagine to see a spike
in
> > CPU/RAM usage at
> > > that time. :-)
> >
> > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
> > &/or Profiler to track the cpu/ram usage.
> >
> > > > You may also see it during a "shadow merge" while the
> > > > Incremental Population is in progress as well, both are normal and
> > expected
> > > > as during these merges of new word lists, shadow index files into
the
> > master
> > > > index file, a lot of computational process is ongoing. Additional,
while
> > > > this process does cause high-cpu usage, it is not normally for long
> > periods
> > > > of time, and then subsides.
> > >
> > > I'm not familiar at *all* with this "shadow merge" concept. Is this
only
> > applicable
> > > during an incremental population (that is, if we have just a nightly
> > incremental
> > > population, would it only happen during that time)?
> >
> > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service
as
> > a method of merging memory-resident word lists into "shadow files" and
these
> > files into the "master index" file, somewhat like a sort/merge file
> > process...
> >
> > > > Actually both of your impressions/assumptions are incorrect. "Change
> > > > Tracking" with "Update Index in Background" as well as any of the
SQL
> > FTS
> > > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> > service as
> > > > in SQL Server 2000 this is a necessary service that is external to
SQL
> > > > Server that manages the FT Catalogs and other FTS requirements.
> > >
> > > Ah! For some reason, I had assumed that the change tracking could be
done
> > solely within
> > > the confines of SQL Server due to the requisite TIMESTAMP on the table
> > that's
> > > participating in the Full Text Catalog. But, it does make sense that
it's
> > doing a "bit
> > > more work" than I expect, and would need to leverage the MSSearch
service
> > executable. :-)
> > >
> > > And, I guess I would expect the same from the SQL FTS predicates
> > CONTAINS*/FREETEXT*. It
> > > was a bit naive to think that the operation could be done outside of
the
> > MSSearch service.
> > >
> > >
> > > > Additionally, why are you still using nightly Incremental
Populations,
> > when
> > > > you are aware of the benefits of "Change Tracking" with "Update
Index
> > in
> > > > Background"? If you enabled both of these options, then the need for
> > your
> > > > nightly Incremental Populations should go away. If you're are
doing
> > > > massive updates/deletes/inserts nightly, you can still use "Change
> > Tracking"
> > > > and turn off "Update Index in Background", and then do a scheduled
> > > > Incremental Population.
> > >
> > > Actually, I'm *not* very clear on the whole Change Tracking thing. I
had
> > assumed that was
> > > sort of like the Replication "log reader" and merely collected
information
> > about those
> > > rows that needed to participate in the Incremental Population. Am I
to
> > understand that
> > > the Change Tracking is sort of mutualy exclusive from the Incremental
> > Population?
> >
> > [jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
> > intentionally un-docucmented system table and is mutually exclusive from
> > Incremental Population.
> >
> > > > Additionally, if you're server is a dual-proc or multiple cpu
machine,
> > there
> > > > is secured & reliable methods for setting the CPU affinity of the
> > mssearch
> > > > service and keep separate from the MSSQLServer cpu affinity's so
that
> > the
> > > > mssearch cpu usage will not affect your SQL Server processing.
> > >
> > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you have
a
> > handy link on how
> > > we might keep these processes from potentially stepping on each
others'
> > "toes"? Is that a
> > > good thing to do?
> >
> > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from
the
> > AT command on the multi-proc server where sql server resides, as
follows:
> >
> > at <current_time+1min> /interactive taskmgr.exe
> >
> > when it launches, you can then set "cpu affinity" for the MSSearch
service
> > to a cpu or set of cpu's not being used by SQL Server. Then you would
use
> > sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> > preventing the cpu usage of mssearch from affecting your sql server
> > processing.
> >
> > > I ask, because we're actually considering making a "Full Text Service"
> > server to kind of
> > > manage all of our FT needs, for 3 reasons:
> > >
> > > (1) To help mitigate the impact of the MSSearch service on our SQL
Server
> > box, which is
> > > hosting a number of clients. As I say, it appears to use a lot of RAM
and
> > CPU at numerous
> > > points during the day.
> >
> > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
<value>,
> > where <value> is a number between 1 and 5 (default 3) to control the
amount
> > of RAM that the MSSearch service will use, 5 (dedicated) will use up to
a
> > max of 512MB of RAM, but only if this amount of ram is available and not
> > used by any other process, incuding the OS and SQL Server.
> >
> >
> > > (2) We want to index .PDF files, and we have a IFilter from Adobe
that
> > works, but has
> > > some caveats. One of them is that we have to limit the number of
worker
> > threads on the
> > > entire server (I think I'm conveying this right -- it's kind of
> > "third-hand" ;-). As
> > > such, I understand that this would impact the entire server, including
SQL
> > Server on that
> > > box.
> >
> > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
> > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
Filter
> > DLL May Not Succeed"
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> >
> > > (3) To get around the 256 Full Text Catalog limit per server. I
doubt
> > that we'll
> > > actually ever get there, but with over 100 FTCs on one of our
Production
> > servers, it's not
> > > unreasonable to think we could double that...
> >
> > [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> > regardless of the number of SQL Server instances on one machine, you
still
> > have this limit... You're best bet here is to "scale-out" and use
mutiple
> > server's possibly with Replication &/or log shipping between them to
> > transfer the data if necessary, otherwise put your independent databases
on
> > separate servers...
> >
> > > But, a lot of my knowledge of the Full Text Search service isn't as
strong
> > as it needs to
> > > be in order to make such decisions, so any help you can provide would
be
> > *greatly*
> > > appreciated! :-)
> >
> > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact
I'm
> > writing a book about it... <G>
> >
> > > Regards,
> > > > John
> > >
> > > As always, thank you for your time and expertise! :-)
> > >
> > > John Peterson
> > >
> > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > (SQL Server 2000, SP3a)
> > > > >
> > > > > Hello all!
> > > > >
> > > > > On regular intervals, we're seeing a lot of OS page faults on our
> > > > Production server
> > > > > (Windows 2000 Advanced Server), and have traced part of those
symptoms
> > to
> > > > the MSSEARCH.EXE
> > > > > executable. The box has 4GB of RAM, with SQL Server capped at
1.5GB.
> > > > >
> > > > > We've got a number of Full Text Catalogs in our databases (maybe
> > around
> > > > 10), and we've got
> > > > > about 10 databases with the same schema. We do an incremental
> > population
> > > > once at night,
> > > > > when our database activity is low.
> > > > >
> > > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> > sometimes
> > > > it's really
> > > > > heavy. I had assumed that the incremental population would kick
off
> > the
> > > > MSSEARCH.EXE
> > > > > service, but I was under the impression that (1) Full Text change
> > tracking
> > > > would be
> > > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > > MSSEARCH.EXE), and (2)
> > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > > MSSEARCH.EXE.
> > > > >
> > > > > I think my (2) assumption is invalid, and was hoping to get some
> > > > clarification.
> > > > >
> > > > > Thanks for any help you can provide! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, John!
I wasn't aware of the "Process: Private Bytes" counter. Would I find that as a checkable
attribute to track from the Task Manager application? Or is that a PerfMon counter?
Thanks again! :-)
John Peterson
"John Kane" <jt-kane@.comcast.net> wrote in message
news:er3Vg5loDHA.2776@.tk2msftngp13.phx.gbl...
> John,
> Yes, there is... actually two methods...
> SELECT fulltextserviceproperty('ResourceUsage') -- returns resource_usage
> value
> as well as track the following Perfmon counter "Microsoft Gatherer:
> Performance Level"
> You might also want to track the "Process: Private Bytes" memory usage level
> for the MSSearch service as well there maybe some additional memory
> processing going on... How much actual RAM do you have available, when your
> resource_usage level is at 5, and this memory is not being used by the OS or
> SQL Server?
> Thanks,
> John
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O2BOaHloDHA.744@.tk2msftngp13.phx.gbl...
> > John,
> >
> > I wanted to ask about a specific issue that you responded with:
> >
> > <Quote>
> > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage' <value>,
> > where <value> is a number between 1 and 5 (default 3) to control the
> amount
> > of RAM that the MSSearch service will use, 5 (dedicated) will use up to a
> > max of 512MB of RAM, but only if this amount of ram is available and not
> > used by any other process, incuding the OS and SQL Server.
> > </Quote>
> >
> > Is there any way to tell what the existing resource_usage is set at?
> We're seeing, in
> > some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM. We
> weren't sure if
> > that meant that we might already be at 5 (dedicated) (like 512MB for
> "data" and 300+ MB
> > for application). Thanks!
> >
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > You're welcome, John,
> > > See more inline comments - [jtkane].
> > >
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > > >
> > > > Please see inline:
> > > >
> > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > Hello back, John,
> > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > (mssearch.exe)
> > > > > service normally at the end of your nightly Incremental Population
> when
> > > a
> > > > > Master Merge occurs.
> > > >
> > > > I haven't monitored it at night -- but I would imagine to see a spike
> in
> > > CPU/RAM usage at
> > > > that time. :-)
> > >
> > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters too)
> > > &/or Profiler to track the cpu/ram usage.
> > >
> > > > > You may also see it during a "shadow merge" while the
> > > > > Incremental Population is in progress as well, both are normal and
> > > expected
> > > > > as during these merges of new word lists, shadow index files into
> the
> > > master
> > > > > index file, a lot of computational process is ongoing. Additional,
> while
> > > > > this process does cause high-cpu usage, it is not normally for long
> > > periods
> > > > > of time, and then subsides.
> > > >
> > > > I'm not familiar at *all* with this "shadow merge" concept. Is this
> only
> > > applicable
> > > > during an incremental population (that is, if we have just a nightly
> > > incremental
> > > > population, would it only happen during that time)?
> > >
> > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing Service
> as
> > > a method of merging memory-resident word lists into "shadow files" and
> these
> > > files into the "master index" file, somewhat like a sort/merge file
> > > process...
> > >
> > > > > Actually both of your impressions/assumptions are incorrect. "Change
> > > > > Tracking" with "Update Index in Background" as well as any of the
> SQL
> > > FTS
> > > > > predicates CONTAINS* or FREETEXT* will use or invoke the MSSearch
> > > service as
> > > > > in SQL Server 2000 this is a necessary service that is external to
> SQL
> > > > > Server that manages the FT Catalogs and other FTS requirements.
> > > >
> > > > Ah! For some reason, I had assumed that the change tracking could be
> done
> > > solely within
> > > > the confines of SQL Server due to the requisite TIMESTAMP on the table
> > > that's
> > > > participating in the Full Text Catalog. But, it does make sense that
> it's
> > > doing a "bit
> > > > more work" than I expect, and would need to leverage the MSSearch
> service
> > > executable. :-)
> > > >
> > > > And, I guess I would expect the same from the SQL FTS predicates
> > > CONTAINS*/FREETEXT*. It
> > > > was a bit naive to think that the operation could be done outside of
> the
> > > MSSearch service.
> > > >
> > > >
> > > > > Additionally, why are you still using nightly Incremental
> Populations,
> > > when
> > > > > you are aware of the benefits of "Change Tracking" with "Update
> Index
> > > in
> > > > > Background"? If you enabled both of these options, then the need for
> > > your
> > > > > nightly Incremental Populations should go away. If you're are
> doing
> > > > > massive updates/deletes/inserts nightly, you can still use "Change
> > > Tracking"
> > > > > and turn off "Update Index in Background", and then do a scheduled
> > > > > Incremental Population.
> > > >
> > > > Actually, I'm *not* very clear on the whole Change Tracking thing. I
> had
> > > assumed that was
> > > > sort of like the Replication "log reader" and merely collected
> information
> > > about those
> > > > rows that needed to participate in the Incremental Population. Am I
> to
> > > understand that
> > > > the Change Tracking is sort of mutualy exclusive from the Incremental
> > > Population?
> > >
> > > [jtkane] - Yes, Change Tracking is "log reader" based as well as uses an
> > > intentionally un-docucmented system table and is mutually exclusive from
> > > Incremental Population.
> > >
> > > > > Additionally, if you're server is a dual-proc or multiple cpu
> machine,
> > > there
> > > > > is secured & reliable methods for setting the CPU affinity of the
> > > mssearch
> > > > > service and keep separate from the MSSQLServer cpu affinity's so
> that
> > > the
> > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > >
> > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you have
> a
> > > handy link on how
> > > > we might keep these processes from potentially stepping on each
> others'
> > > "toes"? Is that a
> > > > good thing to do?
> > >
> > > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe from
> the
> > > AT command on the multi-proc server where sql server resides, as
> follows:
> > >
> > > at <current_time+1min> /interactive taskmgr.exe
> > >
> > > when it launches, you can then set "cpu affinity" for the MSSearch
> service
> > > to a cpu or set of cpu's not being used by SQL Server. Then you would
> use
> > > sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
> > > preventing the cpu usage of mssearch from affecting your sql server
> > > processing.
> > >
> > > > I ask, because we're actually considering making a "Full Text Service"
> > > server to kind of
> > > > manage all of our FT needs, for 3 reasons:
> > > >
> > > > (1) To help mitigate the impact of the MSSearch service on our SQL
> Server
> > > box, which is
> > > > hosting a number of clients. As I say, it appears to use a lot of RAM
> and
> > > CPU at numerous
> > > > points during the day.
> > >
> > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> <value>,
> > > where <value> is a number between 1 and 5 (default 3) to control the
> amount
> > > of RAM that the MSSearch service will use, 5 (dedicated) will use up to
> a
> > > max of 512MB of RAM, but only if this amount of ram is available and not
> > > used by any other process, incuding the OS and SQL Server.
> > >
> > >
> > > > (2) We want to index .PDF files, and we have a IFilter from Adobe
> that
> > > works, but has
> > > > some caveats. One of them is that we have to limit the number of
> worker
> > > threads on the
> > > > entire server (I think I'm conveying this right -- it's kind of
> > > "third-hand" ;-). As
> > > > such, I understand that this would impact the entire server, including
> SQL
> > > Server on that
> > > > box.
> > >
> > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL Server
> > > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
> Filter
> > > DLL May Not Succeed"
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > >
> > > > (3) To get around the 256 Full Text Catalog limit per server. I
> doubt
> > > that we'll
> > > > actually ever get there, but with over 100 FTCs on one of our
> Production
> > > servers, it's not
> > > > unreasonable to think we could double that...
> > >
> > > [jtkane] - Yep, this is a hard one... The 256 limit is per machine, so
> > > regardless of the number of SQL Server instances on one machine, you
> still
> > > have this limit... You're best bet here is to "scale-out" and use
> mutiple
> > > server's possibly with Replication &/or log shipping between them to
> > > transfer the data if necessary, otherwise put your independent databases
> on
> > > separate servers...
> > >
> > > > But, a lot of my knowledge of the Full Text Search service isn't as
> strong
> > > as it needs to
> > > > be in order to make such decisions, so any help you can provide would
> be
> > > *greatly*
> > > > appreciated! :-)
> > >
> > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in fact
> I'm
> > > writing a book about it... <G>
> > >
> > > > Regards,
> > > > > John
> > > >
> > > > As always, thank you for your time and expertise! :-)
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > On regular intervals, we're seeing a lot of OS page faults on our
> > > > > Production server
> > > > > > (Windows 2000 Advanced Server), and have traced part of those
> symptoms
> > > to
> > > > > the MSSEARCH.EXE
> > > > > > executable. The box has 4GB of RAM, with SQL Server capped at
> 1.5GB.
> > > > > >
> > > > > > We've got a number of Full Text Catalogs in our databases (maybe
> > > around
> > > > > 10), and we've got
> > > > > > about 10 databases with the same schema. We do an incremental
> > > population
> > > > > once at night,
> > > > > > when our database activity is low.
> > > > > >
> > > > > > We'll see regular MSSEARCH.EXE activity throughout the day, and
> > > sometimes
> > > > > it's really
> > > > > > heavy. I had assumed that the incremental population would kick
> off
> > > the
> > > > > MSSEARCH.EXE
> > > > > > service, but I was under the impression that (1) Full Text change
> > > tracking
> > > > > would be
> > > > > > incorporated wholly under the auspices of SQL Server (not invoke
> > > > > MSSEARCH.EXE), and (2)
> > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to invoke
> > > > > MSSEARCH.EXE.
> > > > > >
> > > > > > I think my (2) assumption is invalid, and was hoping to get some
> > > > > clarification.
> > > > > >
> > > > > > Thanks for any help you can provide! :-)
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||It's a perfmon counter.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OkSAGMmoDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Thanks, John!
> I wasn't aware of the "Process: Private Bytes" counter. Would I find that
as a checkable
> attribute to track from the Task Manager application? Or is that a
PerfMon counter?
> Thanks again! :-)
> John Peterson
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:er3Vg5loDHA.2776@.tk2msftngp13.phx.gbl...
> > John,
> > Yes, there is... actually two methods...
> > SELECT fulltextserviceproperty('ResourceUsage') -- returns
resource_usage
> > value
> > as well as track the following Perfmon counter "Microsoft Gatherer:
> > Performance Level"
> >
> > You might also want to track the "Process: Private Bytes" memory usage
level
> > for the MSSearch service as well there maybe some additional memory
> > processing going on... How much actual RAM do you have available, when
your
> > resource_usage level is at 5, and this memory is not being used by the
OS or
> > SQL Server?
> >
> > Thanks,
> > John
> >
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O2BOaHloDHA.744@.tk2msftngp13.phx.gbl...
> > > John,
> > >
> > > I wanted to ask about a specific issue that you responded with:
> > >
> > > <Quote>
> > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
<value>,
> > > where <value> is a number between 1 and 5 (default 3) to control the
> > amount
> > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
to a
> > > max of 512MB of RAM, but only if this amount of ram is available and
not
> > > used by any other process, incuding the OS and SQL Server.
> > > </Quote>
> > >
> > > Is there any way to tell what the existing resource_usage is set at?
> > We're seeing, in
> > > some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM.
We
> > weren't sure if
> > > that meant that we might already be at 5 (dedicated) (like 512MB for
> > "data" and 300+ MB
> > > for application). Thanks!
> > >
> > >
> > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > > You're welcome, John,
> > > > See more inline comments - [jtkane].
> > > >
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > > Hello John! Thank you for such a speedy and detailed reply! :-)
> > > > >
> > > > > Please see inline:
> > > > >
> > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > > Hello back, John,
> > > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > > (mssearch.exe)
> > > > > > service normally at the end of your nightly Incremental
Population
> > when
> > > > a
> > > > > > Master Merge occurs.
> > > > >
> > > > > I haven't monitored it at night -- but I would imagine to see a
spike
> > in
> > > > CPU/RAM usage at
> > > > > that time. :-)
> > > >
> > > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch counters
too)
> > > > &/or Profiler to track the cpu/ram usage.
> > > >
> > > > > > You may also see it during a "shadow merge" while the
> > > > > > Incremental Population is in progress as well, both are normal
and
> > > > expected
> > > > > > as during these merges of new word lists, shadow index files
into
> > the
> > > > master
> > > > > > index file, a lot of computational process is ongoing.
Additional,
> > while
> > > > > > this process does cause high-cpu usage, it is not normally for
long
> > > > periods
> > > > > > of time, and then subsides.
> > > > >
> > > > > I'm not familiar at *all* with this "shadow merge" concept. Is
this
> > only
> > > > applicable
> > > > > during an incremental population (that is, if we have just a
nightly
> > > > incremental
> > > > > population, would it only happen during that time)?
> > > >
> > > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
Service
> > as
> > > > a method of merging memory-resident word lists into "shadow files"
and
> > these
> > > > files into the "master index" file, somewhat like a sort/merge file
> > > > process...
> > > >
> > > > > > Actually both of your impressions/assumptions are incorrect.
"Change
> > > > > > Tracking" with "Update Index in Background" as well as any of
the
> > SQL
> > > > FTS
> > > > > > predicates CONTAINS* or FREETEXT* will use or invoke the
MSSearch
> > > > service as
> > > > > > in SQL Server 2000 this is a necessary service that is external
to
> > SQL
> > > > > > Server that manages the FT Catalogs and other FTS requirements.
> > > > >
> > > > > Ah! For some reason, I had assumed that the change tracking could
be
> > done
> > > > solely within
> > > > > the confines of SQL Server due to the requisite TIMESTAMP on the
table
> > > > that's
> > > > > participating in the Full Text Catalog. But, it does make sense
that
> > it's
> > > > doing a "bit
> > > > > more work" than I expect, and would need to leverage the MSSearch
> > service
> > > > executable. :-)
> > > > >
> > > > > And, I guess I would expect the same from the SQL FTS predicates
> > > > CONTAINS*/FREETEXT*. It
> > > > > was a bit naive to think that the operation could be done outside
of
> > the
> > > > MSSearch service.
> > > > >
> > > > >
> > > > > > Additionally, why are you still using nightly Incremental
> > Populations,
> > > > when
> > > > > > you are aware of the benefits of "Change Tracking" with "Update
> > Index
> > > > in
> > > > > > Background"? If you enabled both of these options, then the need
for
> > > > your
> > > > > > nightly Incremental Populations should go away. If you're are
> > doing
> > > > > > massive updates/deletes/inserts nightly, you can still use
"Change
> > > > Tracking"
> > > > > > and turn off "Update Index in Background", and then do a
scheduled
> > > > > > Incremental Population.
> > > > >
> > > > > Actually, I'm *not* very clear on the whole Change Tracking thing.
I
> > had
> > > > assumed that was
> > > > > sort of like the Replication "log reader" and merely collected
> > information
> > > > about those
> > > > > rows that needed to participate in the Incremental Population. Am
I
> > to
> > > > understand that
> > > > > the Change Tracking is sort of mutualy exclusive from the
Incremental
> > > > Population?
> > > >
> > > > [jtkane] - Yes, Change Tracking is "log reader" based as well as
uses an
> > > > intentionally un-docucmented system table and is mutually exclusive
from
> > > > Incremental Population.
> > > >
> > > > > > Additionally, if you're server is a dual-proc or multiple cpu
> > machine,
> > > > there
> > > > > > is secured & reliable methods for setting the CPU affinity of
the
> > > > mssearch
> > > > > > service and keep separate from the MSSQLServer cpu affinity's so
> > that
> > > > the
> > > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > > >
> > > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
have
> > a
> > > > handy link on how
> > > > > we might keep these processes from potentially stepping on each
> > others'
> > > > "toes"? Is that a
> > > > > good thing to do?
> > > >
> > > > [jtkane] - Yes, this is a good thing. You can launch the Tskmgr.exe
from
> > the
> > > > AT command on the multi-proc server where sql server resides, as
> > follows:
> > > >
> > > > at <current_time+1min> /interactive taskmgr.exe
> > > >
> > > > when it launches, you can then set "cpu affinity" for the MSSearch
> > service
> > > > to a cpu or set of cpu's not being used by SQL Server. Then you
would
> > use
> > > > sp_configure to set SQL Server's cpu affinity to the other cpu's,
thus
> > > > preventing the cpu usage of mssearch from affecting your sql server
> > > > processing.
> > > >
> > > > > I ask, because we're actually considering making a "Full Text
Service"
> > > > server to kind of
> > > > > manage all of our FT needs, for 3 reasons:
> > > > >
> > > > > (1) To help mitigate the impact of the MSSearch service on our
SQL
> > Server
> > > > box, which is
> > > > > hosting a number of clients. As I say, it appears to use a lot of
RAM
> > and
> > > > CPU at numerous
> > > > > points during the day.
> > > >
> > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > <value>,
> > > > where <value> is a number between 1 and 5 (default 3) to control the
> > amount
> > > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
to
> > a
> > > > max of 512MB of RAM, but only if this amount of ram is available and
not
> > > > used by any other process, incuding the OS and SQL Server.
> > > >
> > > >
> > > > > (2) We want to index .PDF files, and we have a IFilter from Adobe
> > that
> > > > works, but has
> > > > > some caveats. One of them is that we have to limit the number of
> > worker
> > > > threads on the
> > > > > entire server (I think I'm conveying this right -- it's kind of
> > > > "third-hand" ;-). As
> > > > > such, I understand that this would impact the entire server,
including
> > SQL
> > > > Server on that
> > > > > box.
> > > >
> > > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
Server
> > > > Full-Text Population by Using a Single-Threaded Filter DLL or a PDF
> > Filter
> > > > DLL May Not Succeed"
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > > >
> > > > > (3) To get around the 256 Full Text Catalog limit per server. I
> > doubt
> > > > that we'll
> > > > > actually ever get there, but with over 100 FTCs on one of our
> > Production
> > > > servers, it's not
> > > > > unreasonable to think we could double that...
> > > >
> > > > [jtkane] - Yep, this is a hard one... The 256 limit is per machine,
so
> > > > regardless of the number of SQL Server instances on one machine, you
> > still
> > > > have this limit... You're best bet here is to "scale-out" and use
> > mutiple
> > > > server's possibly with Replication &/or log shipping between them to
> > > > transfer the data if necessary, otherwise put your independent
databases
> > on
> > > > separate servers...
> > > >
> > > > > But, a lot of my knowledge of the Full Text Search service isn't
as
> > strong
> > > > as it needs to
> > > > > be in order to make such decisions, so any help you can provide
would
> > be
> > > > *greatly*
> > > > > appreciated! :-)
> > > >
> > > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong, in
fact
> > I'm
> > > > writing a book about it... <G>
> > > >
> > > > > Regards,
> > > > > > John
> > > > >
> > > > > As always, thank you for your time and expertise! :-)
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > > (SQL Server 2000, SP3a)
> > > > > > >
> > > > > > > Hello all!
> > > > > > >
> > > > > > > On regular intervals, we're seeing a lot of OS page faults on
our
> > > > > > Production server
> > > > > > > (Windows 2000 Advanced Server), and have traced part of those
> > symptoms
> > > > to
> > > > > > the MSSEARCH.EXE
> > > > > > > executable. The box has 4GB of RAM, with SQL Server capped at
> > 1.5GB.
> > > > > > >
> > > > > > > We've got a number of Full Text Catalogs in our databases
(maybe
> > > > around
> > > > > > 10), and we've got
> > > > > > > about 10 databases with the same schema. We do an incremental
> > > > population
> > > > > > once at night,
> > > > > > > when our database activity is low.
> > > > > > >
> > > > > > > We'll see regular MSSEARCH.EXE activity throughout the day,
and
> > > > sometimes
> > > > > > it's really
> > > > > > > heavy. I had assumed that the incremental population would
kick
> > off
> > > > the
> > > > > > MSSEARCH.EXE
> > > > > > > service, but I was under the impression that (1) Full Text
change
> > > > tracking
> > > > > > would be
> > > > > > > incorporated wholly under the auspices of SQL Server (not
invoke
> > > > > > MSSEARCH.EXE), and (2)
> > > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to
invoke
> > > > > > MSSEARCH.EXE.
> > > > > > >
> > > > > > > I think my (2) assumption is invalid, and was hoping to get
some
> > > > > > clarification.
> > > > > > >
> > > > > > > Thanks for any help you can provide! :-)
> > > > > > >
> > > > > > > John Peterson
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
A brief follow-up... In the cases, where you saw the MSSEARCH.EXE process
use 800+ MB of RAM with the resource_usage level set to 5 (a max of 512 MB
of RAM, if it is available) and using the TaskMgr.exe memory monitor for the
mssearch.exe process is most likely not the best tool to accurately monitor
a process' use of memory and that includes the MSSQLServer (SQL Server
service) process as well. The Perfmon "Process: Private Bytes" counter is
the best and most accurate monitoring tool for this type of memory
monitoring. Perfmon explain text defines Private Bytes as "...the current
size, in bytes, of memory that this process has allocated that cannot be
shared with other processes."
Additionally, you might also want to monitor the mssdmn.exe (Search Filter
Daemon) process as well as it's a sub-process that is iniated and used by
the MSSearch service to "read" from SQL Server FT-enabled tables the data to
be FT Indexed. This process can also be monitored via the Perfmon "Process:
Private Bytes" counter, however, the daemon process must be running and then
you can add the counter.
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#duFfUmoDHA.2808@.TK2MSFTNGP10.phx.gbl...
> It's a perfmon counter.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OkSAGMmoDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Thanks, John!
> >
> > I wasn't aware of the "Process: Private Bytes" counter. Would I find
that
> as a checkable
> > attribute to track from the Task Manager application? Or is that a
> PerfMon counter?
> >
> > Thanks again! :-)
> >
> > John Peterson
> >
> >
> > "John Kane" <jt-kane@.comcast.net> wrote in message
> > news:er3Vg5loDHA.2776@.tk2msftngp13.phx.gbl...
> > > John,
> > > Yes, there is... actually two methods...
> > > SELECT fulltextserviceproperty('ResourceUsage') -- returns
> resource_usage
> > > value
> > > as well as track the following Perfmon counter "Microsoft Gatherer:
> > > Performance Level"
> > >
> > > You might also want to track the "Process: Private Bytes" memory usage
> level
> > > for the MSSearch service as well there maybe some additional memory
> > > processing going on... How much actual RAM do you have available, when
> your
> > > resource_usage level is at 5, and this memory is not being used by the
> OS or
> > > SQL Server?
> > >
> > > Thanks,
> > > John
> > >
> > >
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O2BOaHloDHA.744@.tk2msftngp13.phx.gbl...
> > > > John,
> > > >
> > > > I wanted to ask about a specific issue that you responded with:
> > > >
> > > > <Quote>
> > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> <value>,
> > > > where <value> is a number between 1 and 5 (default 3) to control the
> > > amount
> > > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
> to a
> > > > max of 512MB of RAM, but only if this amount of ram is available and
> not
> > > > used by any other process, incuding the OS and SQL Server.
> > > > </Quote>
> > > >
> > > > Is there any way to tell what the existing resource_usage is set at?
> > > We're seeing, in
> > > > some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM.
> We
> > > weren't sure if
> > > > that meant that we might already be at 5 (dedicated) (like 512MB for
> > > "data" and 300+ MB
> > > > for application). Thanks!
> > > >
> > > >
> > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > > > You're welcome, John,
> > > > > See more inline comments - [jtkane].
> > > > >
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > > > Hello John! Thank you for such a speedy and detailed reply!
:-)
> > > > > >
> > > > > > Please see inline:
> > > > > >
> > > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > > > Hello back, John,
> > > > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > > > (mssearch.exe)
> > > > > > > service normally at the end of your nightly Incremental
> Population
> > > when
> > > > > a
> > > > > > > Master Merge occurs.
> > > > > >
> > > > > > I haven't monitored it at night -- but I would imagine to see a
> spike
> > > in
> > > > > CPU/RAM usage at
> > > > > > that time. :-)
> > > > >
> > > > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch
counters
> too)
> > > > > &/or Profiler to track the cpu/ram usage.
> > > > >
> > > > > > > You may also see it during a "shadow merge" while the
> > > > > > > Incremental Population is in progress as well, both are normal
> and
> > > > > expected
> > > > > > > as during these merges of new word lists, shadow index files
> into
> > > the
> > > > > master
> > > > > > > index file, a lot of computational process is ongoing.
> Additional,
> > > while
> > > > > > > this process does cause high-cpu usage, it is not normally for
> long
> > > > > periods
> > > > > > > of time, and then subsides.
> > > > > >
> > > > > > I'm not familiar at *all* with this "shadow merge" concept. Is
> this
> > > only
> > > > > applicable
> > > > > > during an incremental population (that is, if we have just a
> nightly
> > > > > incremental
> > > > > > population, would it only happen during that time)?
> > > > >
> > > > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
> Service
> > > as
> > > > > a method of merging memory-resident word lists into "shadow files"
> and
> > > these
> > > > > files into the "master index" file, somewhat like a sort/merge
file
> > > > > process...
> > > > >
> > > > > > > Actually both of your impressions/assumptions are incorrect.
> "Change
> > > > > > > Tracking" with "Update Index in Background" as well as any of
> the
> > > SQL
> > > > > FTS
> > > > > > > predicates CONTAINS* or FREETEXT* will use or invoke the
> MSSearch
> > > > > service as
> > > > > > > in SQL Server 2000 this is a necessary service that is
external
> to
> > > SQL
> > > > > > > Server that manages the FT Catalogs and other FTS
requirements.
> > > > > >
> > > > > > Ah! For some reason, I had assumed that the change tracking
could
> be
> > > done
> > > > > solely within
> > > > > > the confines of SQL Server due to the requisite TIMESTAMP on the
> table
> > > > > that's
> > > > > > participating in the Full Text Catalog. But, it does make sense
> that
> > > it's
> > > > > doing a "bit
> > > > > > more work" than I expect, and would need to leverage the
MSSearch
> > > service
> > > > > executable. :-)
> > > > > >
> > > > > > And, I guess I would expect the same from the SQL FTS predicates
> > > > > CONTAINS*/FREETEXT*. It
> > > > > > was a bit naive to think that the operation could be done
outside
> of
> > > the
> > > > > MSSearch service.
> > > > > >
> > > > > >
> > > > > > > Additionally, why are you still using nightly Incremental
> > > Populations,
> > > > > when
> > > > > > > you are aware of the benefits of "Change Tracking" with
"Update
> > > Index
> > > > > in
> > > > > > > Background"? If you enabled both of these options, then the
need
> for
> > > > > your
> > > > > > > nightly Incremental Populations should go away. If you're
are
> > > doing
> > > > > > > massive updates/deletes/inserts nightly, you can still use
> "Change
> > > > > Tracking"
> > > > > > > and turn off "Update Index in Background", and then do a
> scheduled
> > > > > > > Incremental Population.
> > > > > >
> > > > > > Actually, I'm *not* very clear on the whole Change Tracking
thing.
> I
> > > had
> > > > > assumed that was
> > > > > > sort of like the Replication "log reader" and merely collected
> > > information
> > > > > about those
> > > > > > rows that needed to participate in the Incremental Population.
Am
> I
> > > to
> > > > > understand that
> > > > > > the Change Tracking is sort of mutualy exclusive from the
> Incremental
> > > > > Population?
> > > > >
> > > > > [jtkane] - Yes, Change Tracking is "log reader" based as well as
> uses an
> > > > > intentionally un-docucmented system table and is mutually
exclusive
> from
> > > > > Incremental Population.
> > > > >
> > > > > > > Additionally, if you're server is a dual-proc or multiple cpu
> > > machine,
> > > > > there
> > > > > > > is secured & reliable methods for setting the CPU affinity of
> the
> > > > > mssearch
> > > > > > > service and keep separate from the MSSQLServer cpu affinity's
so
> > > that
> > > > > the
> > > > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > > > >
> > > > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
> have
> > > a
> > > > > handy link on how
> > > > > > we might keep these processes from potentially stepping on each
> > > others'
> > > > > "toes"? Is that a
> > > > > > good thing to do?
> > > > >
> > > > > [jtkane] - Yes, this is a good thing. You can launch the
Tskmgr.exe
> from
> > > the
> > > > > AT command on the multi-proc server where sql server resides, as
> > > follows:
> > > > >
> > > > > at <current_time+1min> /interactive taskmgr.exe
> > > > >
> > > > > when it launches, you can then set "cpu affinity" for the MSSearch
> > > service
> > > > > to a cpu or set of cpu's not being used by SQL Server. Then you
> would
> > > use
> > > > > sp_configure to set SQL Server's cpu affinity to the other cpu's,
> thus
> > > > > preventing the cpu usage of mssearch from affecting your sql
server
> > > > > processing.
> > > > >
> > > > > > I ask, because we're actually considering making a "Full Text
> Service"
> > > > > server to kind of
> > > > > > manage all of our FT needs, for 3 reasons:
> > > > > >
> > > > > > (1) To help mitigate the impact of the MSSearch service on our
> SQL
> > > Server
> > > > > box, which is
> > > > > > hosting a number of clients. As I say, it appears to use a lot
of
> RAM
> > > and
> > > > > CPU at numerous
> > > > > > points during the day.
> > > > >
> > > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > > <value>,
> > > > > where <value> is a number between 1 and 5 (default 3) to control
the
> > > amount
> > > > > of RAM that the MSSearch service will use, 5 (dedicated) will use
up
> to
> > > a
> > > > > max of 512MB of RAM, but only if this amount of ram is available
and
> not
> > > > > used by any other process, incuding the OS and SQL Server.
> > > > >
> > > > >
> > > > > > (2) We want to index .PDF files, and we have a IFilter from
Adobe
> > > that
> > > > > works, but has
> > > > > > some caveats. One of them is that we have to limit the number
of
> > > worker
> > > > > threads on the
> > > > > > entire server (I think I'm conveying this right -- it's kind of
> > > > > "third-hand" ;-). As
> > > > > > such, I understand that this would impact the entire server,
> including
> > > SQL
> > > > > Server on that
> > > > > > box.
> > > > >
> > > > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
> Server
> > > > > Full-Text Population by Using a Single-Threaded Filter DLL or a
PDF
> > > Filter
> > > > > DLL May Not Succeed"
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > > > >
> > > > > > (3) To get around the 256 Full Text Catalog limit per server.
I
> > > doubt
> > > > > that we'll
> > > > > > actually ever get there, but with over 100 FTCs on one of our
> > > Production
> > > > > servers, it's not
> > > > > > unreasonable to think we could double that...
> > > > >
> > > > > [jtkane] - Yep, this is a hard one... The 256 limit is per
machine,
> so
> > > > > regardless of the number of SQL Server instances on one machine,
you
> > > still
> > > > > have this limit... You're best bet here is to "scale-out" and use
> > > mutiple
> > > > > server's possibly with Replication &/or log shipping between them
to
> > > > > transfer the data if necessary, otherwise put your independent
> databases
> > > on
> > > > > separate servers...
> > > > >
> > > > > > But, a lot of my knowledge of the Full Text Search service isn't
> as
> > > strong
> > > > > as it needs to
> > > > > > be in order to make such decisions, so any help you can provide
> would
> > > be
> > > > > *greatly*
> > > > > > appreciated! :-)
> > > > >
> > > > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong,
in
> fact
> > > I'm
> > > > > writing a book about it... <G>
> > > > >
> > > > > > Regards,
> > > > > > > John
> > > > > >
> > > > > > As always, thank you for your time and expertise! :-)
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > > > (SQL Server 2000, SP3a)
> > > > > > > >
> > > > > > > > Hello all!
> > > > > > > >
> > > > > > > > On regular intervals, we're seeing a lot of OS page faults
on
> our
> > > > > > > Production server
> > > > > > > > (Windows 2000 Advanced Server), and have traced part of
those
> > > symptoms
> > > > > to
> > > > > > > the MSSEARCH.EXE
> > > > > > > > executable. The box has 4GB of RAM, with SQL Server capped
at
> > > 1.5GB.
> > > > > > > >
> > > > > > > > We've got a number of Full Text Catalogs in our databases
> (maybe
> > > > > around
> > > > > > > 10), and we've got
> > > > > > > > about 10 databases with the same schema. We do an
incremental
> > > > > population
> > > > > > > once at night,
> > > > > > > > when our database activity is low.
> > > > > > > >
> > > > > > > > We'll see regular MSSEARCH.EXE activity throughout the day,
> and
> > > > > sometimes
> > > > > > > it's really
> > > > > > > > heavy. I had assumed that the incremental population would
> kick
> > > off
> > > > > the
> > > > > > > MSSEARCH.EXE
> > > > > > > > service, but I was under the impression that (1) Full Text
> change
> > > > > tracking
> > > > > > > would be
> > > > > > > > incorporated wholly under the auspices of SQL Server (not
> invoke
> > > > > > > MSSEARCH.EXE), and (2)
> > > > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to
> invoke
> > > > > > > MSSEARCH.EXE.
> > > > > > > >
> > > > > > > > I think my (2) assumption is invalid, and was hoping to get
> some
> > > > > > > clarification.
> > > > > > > >
> > > > > > > > Thanks for any help you can provide! :-)
> > > > > > > >
> > > > > > > > John Peterson
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||That's *terrific* info! Thanks so much, John!
BTW: I'm not sure if our resource_usage level *was* set at 5 -- I was just assuming so
based on the 800MB allocation that Task Manager seemed to report. That's why I was so
grateful to learn how to identify what the resource_usage is. I haven't followed up on
that, but your suggestion to use the Process: Private Bytes definitely sounds more
accurate than TaskMan. :-)
Thanks again!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OG5ZhI1oDHA.1656@.tk2msftngp13.phx.gbl...
> John,
> A brief follow-up... In the cases, where you saw the MSSEARCH.EXE process
> use 800+ MB of RAM with the resource_usage level set to 5 (a max of 512 MB
> of RAM, if it is available) and using the TaskMgr.exe memory monitor for the
> mssearch.exe process is most likely not the best tool to accurately monitor
> a process' use of memory and that includes the MSSQLServer (SQL Server
> service) process as well. The Perfmon "Process: Private Bytes" counter is
> the best and most accurate monitoring tool for this type of memory
> monitoring. Perfmon explain text defines Private Bytes as "...the current
> size, in bytes, of memory that this process has allocated that cannot be
> shared with other processes."
> Additionally, you might also want to monitor the mssdmn.exe (Search Filter
> Daemon) process as well as it's a sub-process that is iniated and used by
> the MSSearch service to "read" from SQL Server FT-enabled tables the data to
> be FT Indexed. This process can also be monitored via the Perfmon "Process:
> Private Bytes" counter, however, the daemon process must be running and then
> you can add the counter.
> Regards,
> John
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#duFfUmoDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > It's a perfmon counter.
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OkSAGMmoDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > > Thanks, John!
> > >
> > > I wasn't aware of the "Process: Private Bytes" counter. Would I find
> that
> > as a checkable
> > > attribute to track from the Task Manager application? Or is that a
> > PerfMon counter?
> > >
> > > Thanks again! :-)
> > >
> > > John Peterson
> > >
> > >
> > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > news:er3Vg5loDHA.2776@.tk2msftngp13.phx.gbl...
> > > > John,
> > > > Yes, there is... actually two methods...
> > > > SELECT fulltextserviceproperty('ResourceUsage') -- returns
> > resource_usage
> > > > value
> > > > as well as track the following Perfmon counter "Microsoft Gatherer:
> > > > Performance Level"
> > > >
> > > > You might also want to track the "Process: Private Bytes" memory usage
> > level
> > > > for the MSSearch service as well there maybe some additional memory
> > > > processing going on... How much actual RAM do you have available, when
> > your
> > > > resource_usage level is at 5, and this memory is not being used by the
> > OS or
> > > > SQL Server?
> > > >
> > > > Thanks,
> > > > John
> > > >
> > > >
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:O2BOaHloDHA.744@.tk2msftngp13.phx.gbl...
> > > > > John,
> > > > >
> > > > > I wanted to ask about a specific issue that you responded with:
> > > > >
> > > > > <Quote>
> > > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > <value>,
> > > > > where <value> is a number between 1 and 5 (default 3) to control the
> > > > amount
> > > > > of RAM that the MSSearch service will use, 5 (dedicated) will use up
> > to a
> > > > > max of 512MB of RAM, but only if this amount of ram is available and
> > not
> > > > > used by any other process, incuding the OS and SQL Server.
> > > > > </Quote>
> > > > >
> > > > > Is there any way to tell what the existing resource_usage is set at?
> > > > We're seeing, in
> > > > > some cases, where the MSSEARCH.EXE process will use 800+ MB of RAM.
> > We
> > > > weren't sure if
> > > > > that meant that we might already be at 5 (dedicated) (like 512MB for
> > > > "data" and 300+ MB
> > > > > for application). Thanks!
> > > > >
> > > > >
> > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > news:egfvRdqnDHA.2432@.TK2MSFTNGP10.phx.gbl...
> > > > > > You're welcome, John,
> > > > > > See more inline comments - [jtkane].
> > > > > >
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:#yxyl5pnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > > > > > > Hello John! Thank you for such a speedy and detailed reply!
> :-)
> > > > > > >
> > > > > > > Please see inline:
> > > > > > >
> > > > > > > "John Kane" <jt-kane@.comcast.net> wrote in message
> > > > > > > news:eQ8mxspnDHA.2776@.tk2msftngp13.phx.gbl...
> > > > > > > > Hello back, John,
> > > > > > > > Yes, you will see high CPU usage from the "Microsoft Search"
> > > > > > (mssearch.exe)
> > > > > > > > service normally at the end of your nightly Incremental
> > Population
> > > > when
> > > > > > a
> > > > > > > > Master Merge occurs.
> > > > > > >
> > > > > > > I haven't monitored it at night -- but I would imagine to see a
> > spike
> > > > in
> > > > > > CPU/RAM usage at
> > > > > > > that time. :-)
> > > > > >
> > > > > > [jtkane] - Yes. Use a scheduled Perfmon log (with MSSearch
> counters
> > too)
> > > > > > &/or Profiler to track the cpu/ram usage.
> > > > > >
> > > > > > > > You may also see it during a "shadow merge" while the
> > > > > > > > Incremental Population is in progress as well, both are normal
> > and
> > > > > > expected
> > > > > > > > as during these merges of new word lists, shadow index files
> > into
> > > > the
> > > > > > master
> > > > > > > > index file, a lot of computational process is ongoing.
> > Additional,
> > > > while
> > > > > > > > this process does cause high-cpu usage, it is not normally for
> > long
> > > > > > periods
> > > > > > > > of time, and then subsides.
> > > > > > >
> > > > > > > I'm not familiar at *all* with this "shadow merge" concept. Is
> > this
> > > > only
> > > > > > applicable
> > > > > > > during an incremental population (that is, if we have just a
> > nightly
> > > > > > incremental
> > > > > > > population, would it only happen during that time)?
> > > > > >
> > > > > > [jtkane] - It's a concept somewhat unique to MSSearch & Indexing
> > Service
> > > > as
> > > > > > a method of merging memory-resident word lists into "shadow files"
> > and
> > > > these
> > > > > > files into the "master index" file, somewhat like a sort/merge
> file
> > > > > > process...
> > > > > >
> > > > > > > > Actually both of your impressions/assumptions are incorrect.
> > "Change
> > > > > > > > Tracking" with "Update Index in Background" as well as any of
> > the
> > > > SQL
> > > > > > FTS
> > > > > > > > predicates CONTAINS* or FREETEXT* will use or invoke the
> > MSSearch
> > > > > > service as
> > > > > > > > in SQL Server 2000 this is a necessary service that is
> external
> > to
> > > > SQL
> > > > > > > > Server that manages the FT Catalogs and other FTS
> requirements.
> > > > > > >
> > > > > > > Ah! For some reason, I had assumed that the change tracking
> could
> > be
> > > > done
> > > > > > solely within
> > > > > > > the confines of SQL Server due to the requisite TIMESTAMP on the
> > table
> > > > > > that's
> > > > > > > participating in the Full Text Catalog. But, it does make sense
> > that
> > > > it's
> > > > > > doing a "bit
> > > > > > > more work" than I expect, and would need to leverage the
> MSSearch
> > > > service
> > > > > > executable. :-)
> > > > > > >
> > > > > > > And, I guess I would expect the same from the SQL FTS predicates
> > > > > > CONTAINS*/FREETEXT*. It
> > > > > > > was a bit naive to think that the operation could be done
> outside
> > of
> > > > the
> > > > > > MSSearch service.
> > > > > > >
> > > > > > >
> > > > > > > > Additionally, why are you still using nightly Incremental
> > > > Populations,
> > > > > > when
> > > > > > > > you are aware of the benefits of "Change Tracking" with
> "Update
> > > > Index
> > > > > > in
> > > > > > > > Background"? If you enabled both of these options, then the
> need
> > for
> > > > > > your
> > > > > > > > nightly Incremental Populations should go away. If you're
> are
> > > > doing
> > > > > > > > massive updates/deletes/inserts nightly, you can still use
> > "Change
> > > > > > Tracking"
> > > > > > > > and turn off "Update Index in Background", and then do a
> > scheduled
> > > > > > > > Incremental Population.
> > > > > > >
> > > > > > > Actually, I'm *not* very clear on the whole Change Tracking
> thing.
> > I
> > > > had
> > > > > > assumed that was
> > > > > > > sort of like the Replication "log reader" and merely collected
> > > > information
> > > > > > about those
> > > > > > > rows that needed to participate in the Incremental Population.
> Am
> > I
> > > > to
> > > > > > understand that
> > > > > > > the Change Tracking is sort of mutualy exclusive from the
> > Incremental
> > > > > > Population?
> > > > > >
> > > > > > [jtkane] - Yes, Change Tracking is "log reader" based as well as
> > uses an
> > > > > > intentionally un-docucmented system table and is mutually
> exclusive
> > from
> > > > > > Incremental Population.
> > > > > >
> > > > > > > > Additionally, if you're server is a dual-proc or multiple cpu
> > > > machine,
> > > > > > there
> > > > > > > > is secured & reliable methods for setting the CPU affinity of
> > the
> > > > > > mssearch
> > > > > > > > service and keep separate from the MSSQLServer cpu affinity's
> so
> > > > that
> > > > > > the
> > > > > > > > mssearch cpu usage will not affect your SQL Server processing.
> > > > > > >
> > > > > > > Ooo...we *are* in a multiple CPU context (4, I believe). Do you
> > have
> > > > a
> > > > > > handy link on how
> > > > > > > we might keep these processes from potentially stepping on each
> > > > others'
> > > > > > "toes"? Is that a
> > > > > > > good thing to do?
> > > > > >
> > > > > > [jtkane] - Yes, this is a good thing. You can launch the
> Tskmgr.exe
> > from
> > > > the
> > > > > > AT command on the multi-proc server where sql server resides, as
> > > > follows:
> > > > > >
> > > > > > at <current_time+1min> /interactive taskmgr.exe
> > > > > >
> > > > > > when it launches, you can then set "cpu affinity" for the MSSearch
> > > > service
> > > > > > to a cpu or set of cpu's not being used by SQL Server. Then you
> > would
> > > > use
> > > > > > sp_configure to set SQL Server's cpu affinity to the other cpu's,
> > thus
> > > > > > preventing the cpu usage of mssearch from affecting your sql
> server
> > > > > > processing.
> > > > > >
> > > > > > > I ask, because we're actually considering making a "Full Text
> > Service"
> > > > > > server to kind of
> > > > > > > manage all of our FT needs, for 3 reasons:
> > > > > > >
> > > > > > > (1) To help mitigate the impact of the MSSearch service on our
> > SQL
> > > > Server
> > > > > > box, which is
> > > > > > > hosting a number of clients. As I say, it appears to use a lot
> of
> > RAM
> > > > and
> > > > > > CPU at numerous
> > > > > > > points during the day.
> > > > > >
> > > > > > [jtkane] - Note, you can use sp_fulltext_service 'resource_usage'
> > > > <value>,
> > > > > > where <value> is a number between 1 and 5 (default 3) to control
> the
> > > > amount
> > > > > > of RAM that the MSSearch service will use, 5 (dedicated) will use
> up
> > to
> > > > a
> > > > > > max of 512MB of RAM, but only if this amount of ram is available
> and
> > not
> > > > > > used by any other process, incuding the OS and SQL Server.
> > > > > >
> > > > > >
> > > > > > > (2) We want to index .PDF files, and we have a IFilter from
> Adobe
> > > > that
> > > > > > works, but has
> > > > > > > some caveats. One of them is that we have to limit the number
> of
> > > > worker
> > > > > > threads on the
> > > > > > > entire server (I think I'm conveying this right -- it's kind of
> > > > > > "third-hand" ;-). As
> > > > > > > such, I understand that this would impact the entire server,
> > including
> > > > SQL
> > > > > > Server on that
> > > > > > > box.
> > > > > >
> > > > > > [jtkane] - I'm assuming that you've reviewed KB Q323040 "BUG: SQL
> > Server
> > > > > > Full-Text Population by Using a Single-Threaded Filter DLL or a
> PDF
> > > > Filter
> > > > > > DLL May Not Succeed"
> > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q323040
> > > > > >
> > > > > > > (3) To get around the 256 Full Text Catalog limit per server.
> I
> > > > doubt
> > > > > > that we'll
> > > > > > > actually ever get there, but with over 100 FTCs on one of our
> > > > Production
> > > > > > servers, it's not
> > > > > > > unreasonable to think we could double that...
> > > > > >
> > > > > > [jtkane] - Yep, this is a hard one... The 256 limit is per
> machine,
> > so
> > > > > > regardless of the number of SQL Server instances on one machine,
> you
> > > > still
> > > > > > have this limit... You're best bet here is to "scale-out" and use
> > > > mutiple
> > > > > > server's possibly with Replication &/or log shipping between them
> to
> > > > > > transfer the data if necessary, otherwise put your independent
> > databases
> > > > on
> > > > > > separate servers...
> > > > > >
> > > > > > > But, a lot of my knowledge of the Full Text Search service isn't
> > as
> > > > strong
> > > > > > as it needs to
> > > > > > > be in order to make such decisions, so any help you can provide
> > would
> > > > be
> > > > > > *greatly*
> > > > > > > appreciated! :-)
> > > > > >
> > > > > > [jtkane] - Not to worry, my knowledge in SQL FTS is very strong,
> in
> > fact
> > > > I'm
> > > > > > writing a book about it... <G>
> > > > > >
> > > > > > > Regards,
> > > > > > > > John
> > > > > > >
> > > > > > > As always, thank you for your time and expertise! :-)
> > > > > > >
> > > > > > > John Peterson
> > > > > > >
> > > > > > >
> > > > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > > > news:Opa#e3onDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > > > > > > > (SQL Server 2000, SP3a)
> > > > > > > > >
> > > > > > > > > Hello all!
> > > > > > > > >
> > > > > > > > > On regular intervals, we're seeing a lot of OS page faults
> on
> > our
> > > > > > > > Production server
> > > > > > > > > (Windows 2000 Advanced Server), and have traced part of
> those
> > > > symptoms
> > > > > > to
> > > > > > > > the MSSEARCH.EXE
> > > > > > > > > executable. The box has 4GB of RAM, with SQL Server capped
> at
> > > > 1.5GB.
> > > > > > > > >
> > > > > > > > > We've got a number of Full Text Catalogs in our databases
> > (maybe
> > > > > > around
> > > > > > > > 10), and we've got
> > > > > > > > > about 10 databases with the same schema. We do an
> incremental
> > > > > > population
> > > > > > > > once at night,
> > > > > > > > > when our database activity is low.
> > > > > > > > >
> > > > > > > > > We'll see regular MSSEARCH.EXE activity throughout the day,
> > and
> > > > > > sometimes
> > > > > > > > it's really
> > > > > > > > > heavy. I had assumed that the incremental population would
> > kick
> > > > off
> > > > > > the
> > > > > > > > MSSEARCH.EXE
> > > > > > > > > service, but I was under the impression that (1) Full Text
> > change
> > > > > > tracking
> > > > > > > > would be
> > > > > > > > > incorporated wholly under the auspices of SQL Server (not
> > invoke
> > > > > > > > MSSEARCH.EXE), and (2)
> > > > > > > > > that queries with CONTAINS/CONTAINSTABLE wouldn't need to
> > invoke
> > > > > > > > MSSEARCH.EXE.
> > > > > > > > >
> > > > > > > > > I think my (2) assumption is invalid, and was hoping to get
> > some
> > > > > > > > clarification.
> > > > > > > > >
> > > > > > > > > Thanks for any help you can provide! :-)
> > > > > > > > >
> > > > > > > > > John Peterson
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||It all depends on how efficiently the database and full text search is
indexed... Pls verify ur indexes to help lower memory usage and better
performance.
Keyur Shah
Verizon Communications
732-423-0745
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment