Full Text Search Population Method...

Hi,
I am confused by the different method of population, of both Full Text
index and Full Text Catalog.
Currently, we are running a application (3rd party) on SQL 2000, and we
have set the Full Text indexing to be "Change Tracking" and "background
update". By having this combination, do we still need to instruct the SQL
Server to do an incremental update on both the Catalog and Indexes? If yes,
which method should I use?
TTS,
The short answer is no. You do not need to run a scheduled, i.e.. instruct
the SQL Server to do an incremental population. In the background, SQL
Server uses an internal (and un-documented) system table to gather changes
based upon the database logged changes (updates, inserts & deletes) for the
FT-enable table column changes and then pushes these changes to the FT
Catalog in near real-time performance.
However and depending upon the amount (number of rows) and the frequency
(once every minute, once ever hour, etc.) you may want to turn off "Update
Index in Background" if the amount and frequency of changes is very high,
say >50% of the FT-enable table would change. In this case, you would
disenable "Update Index in Background" and setup a scheduled Incremental
Population to handle this one time massive update. You can read more about
this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
specifically, "The method you use depends on factors such as the CPU and
available memory, the amount and rate of change of data, the amount of
available disk space, and the importance of the full-text index being
current. Use these recommendations as a guide for selecting a maintenance
method".
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> Hi,
> I am confused by the different method of population, of both Full Text
> index and Full Text Catalog.
> Currently, we are running a application (3rd party) on SQL 2000, and
we
> have set the Full Text indexing to be "Change Tracking" and "background
> update". By having this combination, do we still need to instruct the SQL
> Server to do an incremental update on both the Catalog and Indexes? If
yes,
> which method should I use?
>
|||Thanks John.
So to conclude, even though the status of the Full Text Catelog is
"IDLE", the Full Text index and Full Text Catelog will be populated with the
"Change Tracking" and "background update" configuration. And we will not have
any ways of telling whether the Full Text index and Catelog has been
populated, we can only trust that it has been populated.
"John Kane" wrote:

> TTS,
> The short answer is no. You do not need to run a scheduled, i.e.. instruct
> the SQL Server to do an incremental population. In the background, SQL
> Server uses an internal (and un-documented) system table to gather changes
> based upon the database logged changes (updates, inserts & deletes) for the
> FT-enable table column changes and then pushes these changes to the FT
> Catalog in near real-time performance.
> However and depending upon the amount (number of rows) and the frequency
> (once every minute, once ever hour, etc.) you may want to turn off "Update
> Index in Background" if the amount and frequency of changes is very high,
> say >50% of the FT-enable table would change. In this case, you would
> disenable "Update Index in Background" and setup a scheduled Incremental
> Population to handle this one time massive update. You can read more about
> this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
> specifically, "The method you use depends on factors such as the CPU and
> available memory, the amount and rate of change of data, the amount of
> available disk space, and the importance of the full-text index being
> current. Use these recommendations as a guide for selecting a maintenance
> method".
> Regards,
> John
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> we
> yes,
>
>
|||Hi,
Can anybody confirm my conclusion?
"tts" wrote:
[vbcol=seagreen]
> Thanks John.
> So to conclude, even though the status of the Full Text Catelog is
> "IDLE", the Full Text index and Full Text Catelog will be populated with the
> "Change Tracking" and "background update" configuration. And we will not have
> any ways of telling whether the Full Text index and Catelog has been
> populated, we can only trust that it has been populated.
> "John Kane" wrote:
|||Hi TTS,
Sorry for the delay in replying! No, and depending upon how much content you
changed, you would see the following status values as well (from SQL 2000
BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
Property Description
PopulateStatus 0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
For CT with UIiB and depending upon what was occurring, you could see
PopulateStatus values of 1, 6, 7 and 9. You can also use the
FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
example:
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Hope that help!
John
"tts" <tts@.discussions.microsoft.com> wrote in message
news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...[vbcol=seagreen]
> Hi,
> Can anybody confirm my conclusion?
> "tts" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
instruct[vbcol=seagreen]
changes[vbcol=seagreen]
for the[vbcol=seagreen]
frequency[vbcol=seagreen]
"Update[vbcol=seagreen]
high,[vbcol=seagreen]
Incremental[vbcol=seagreen]
about[vbcol=seagreen]
Indexes" -[vbcol=seagreen]
and[vbcol=seagreen]
maintenance[vbcol=seagreen]
Full Text[vbcol=seagreen]
and[vbcol=seagreen]
"background[vbcol=seagreen]
the SQL[vbcol=seagreen]
If[vbcol=seagreen]
|||Hello John,
Thanks for your explanation. I have encountered a strange situation.
The index is not populated everytime with the insertion of record.
Please correct me if I am wrong. From what I understand, if I am
using Change Tracking and Background update, the index will be updated almost
immediately, if not, when the CPU is free to do the processing. However, this
do not apply all the time. I have tested out quite a number of time, not all
the insertion will cause the population of the indexes. From what I have
tested, if the keyword has been inserted into index, then the index will be
updated. Otherwise, the keyword will not be inserted into the index. Please
advise me what I should lookup for in the population and using of Full Text
Search.
Although I have done quite a number of testing, the size and
datetime stamp of the Full Text Catalog remain the same (as of last Friday),
and the status is IDLE. So I am not sure whether the population did take
place or not.
"John Kane" wrote:

> Hi TTS,
> Sorry for the delay in replying! No, and depending upon how much content you
> changed, you would see the following status values as well (from SQL 2000
> BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
> Property Description
> PopulateStatus 0 = Idle
> 1 = Full population in progress
> 2 = Paused
> 3 = Throttled
> 4 = Recovering
> 5 = Shutdown
> 6 = Incremental population in progress
> 7 = Building index
> 8 = Disk is full. Paused.
> 9 = Change tracking
>
> For CT with UIiB and depending upon what was occurring, you could see
> PopulateStatus values of 1, 6, 7 and 9. You can also use the
> FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
> example:
> select
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> Hope that help!
> John
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...
> the
> have
> instruct
> changes
> for the
> frequency
> "Update
> high,
> Incremental
> about
> Indexes" -
> and
> maintenance
> Full Text
> and
> "background
> the SQL
> If
>
>
|||You're welcome, TTS,
Is it not populating every time or just some of the time? If it is the
former, then this is not only possible, but can also occur when CT & UIiB is
not enabled. The usual symptom is that the FT Catalog property indicates
that the FT Catalog only contains an item count of 1 (row count + 1) row and
a unique key count (unique non-noise word count) of 0.
If this is the case, the most likely the FT Catalog is not populating for
several reasons, but the most likely is that the MSSQLServer service account
&/or password has been changed by Win2K's Component services (or control
panel) and not via the Enterprise Manager's server security tab as this is
the only place that the security of the two services (MSSQLServer &
MSSearch) are sync'ed up.
Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
service is started & under the "system account" (LocalSystem) and not some
other account? If it is not started under the LocalSystem, please change it
to LocalSystem and stop & restart the service. See the following KB article
for more details:
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services] at http://support.microsoft.com/default...B;EN-US;277549
Yes, your assumption is correct, but the actual *near* real-time update can
sometimes take up to 5 seconds before you can issue a SQL FTS query and see
the inserted row. This can vary to as much as 7 to 10 seconds, depending
upon the amount & frequency of updates.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> Hello John,
> Thanks for your explanation. I have encountered a strange
situation.
> The index is not populated everytime with the insertion of record.
> Please correct me if I am wrong. From what I understand, if I am
> using Change Tracking and Background update, the index will be updated
almost
> immediately, if not, when the CPU is free to do the processing. However,
this
> do not apply all the time. I have tested out quite a number of time, not
all
> the insertion will cause the population of the indexes. From what I have
> tested, if the keyword has been inserted into index, then the index will
be
> updated. Otherwise, the keyword will not be inserted into the index.
Please
> advise me what I should lookup for in the population and using of Full
Text
> Search.
> Although I have done quite a number of testing, the size and
> datetime stamp of the Full Text Catalog remain the same (as of last
Friday),[vbcol=seagreen]
> and the status is IDLE. So I am not sure whether the population did take
> place or not.
> "John Kane" wrote:
you[vbcol=seagreen]
2000[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
is[vbcol=seagreen]
with[vbcol=seagreen]
not[vbcol=seagreen]
SQL[vbcol=seagreen]
deletes)[vbcol=seagreen]
the FT[vbcol=seagreen]
very[vbcol=seagreen]
would[vbcol=seagreen]
more[vbcol=seagreen]
CPU[vbcol=seagreen]
amount of[vbcol=seagreen]
being[vbcol=seagreen]
2000,[vbcol=seagreen]
instruct[vbcol=seagreen]
Indexes?[vbcol=seagreen]
|||Hello John,
Sorry, did not get back to you regarding this issue. The problem that
I have raised is because of the software bug, so the indexes are not
populated correctly.
However, this lead me to another few questions.
1) If a table is Change Tracking enabled, when we specify a "On
demand" or "Schedule" population, is it performing a Full Population? The
table does not have any date-time stamp.
2) If a full population is taking place, and there are records going
into the Indexed table, how will the FTS behave? Does that means that the FTS
will redo the full population of the index again, or it will just include the
new text, and perform a incremental population?
I am trying to determine which type of population method has the best
perfomance since there is a slight flaw in the software.
"John Kane" wrote:

> You're welcome, TTS,
> Is it not populating every time or just some of the time? If it is the
> former, then this is not only possible, but can also occur when CT & UIiB is
> not enabled. The usual symptom is that the FT Catalog property indicates
> that the FT Catalog only contains an item count of 1 (row count + 1) row and
> a unique key count (unique non-noise word count) of 0.
> If this is the case, the most likely the FT Catalog is not populating for
> several reasons, but the most likely is that the MSSQLServer service account
> &/or password has been changed by Win2K's Component services (or control
> panel) and not via the Enterprise Manager's server security tab as this is
> the only place that the security of the two services (MSSQLServer &
> MSSearch) are sync'ed up.
> Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
> service is started & under the "system account" (LocalSystem) and not some
> other account? If it is not started under the LocalSystem, please change it
> to LocalSystem and stop & restart the service. See the following KB article
> for more details:
> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
> Services] at http://support.microsoft.com/default...B;EN-US;277549
> Yes, your assumption is correct, but the actual *near* real-time update can
> sometimes take up to 5 seconds before you can issue a SQL FTS query and see
> the inserted row. This can vary to as much as 7 to 10 seconds, depending
> upon the amount & frequency of updates.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> situation.
> almost
> this
> all
> be
> Please
> Text
> Friday),
> you
> 2000
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> is
> with
> not
> SQL
> deletes)
> the FT
> very
> would
> more
> CPU
> amount of
> being
> 2000,
> instruct
> Indexes?
>
>
|||Not to worry, TTS, as Christmas was this past weekend!
1) If a table is Change Tracking enabled, when we specify a "On demand" or
"Schedule" population, is it performing a Full Population? The table does
not have any date-time stamp.
A. Yes. Because the table does not have a timestamp column, a Full
Population is always done, even if you select an Incremental Population.
2) If a full population is taking place, and there are records going into
the Indexed table, how will the FTS behave?
A. An interesting question... I've not actually tested this, as a Full
Population removes all previous data in the FT Catalog (much like a Truncate
table in T-SQL does to a sql table's data), and then starts reading from the
SQL Table's data from the beginning. I do *suspect* that the new data will
be read from the database log and populated to the FT Catalog after Full
Population is completed, but I think this may only be true if a timestamp
column was present. You should test this yourself before making any
decisions.
Hope this helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:5B0EC1EA-0DA4-4A5E-B3DC-52A3F8C6C5C0@.microsoft.com...
> Hello John,
> Sorry, did not get back to you regarding this issue. The problem
that
> I have raised is because of the software bug, so the indexes are not
> populated correctly.
> However, this lead me to another few questions.
> 1) If a table is Change Tracking enabled, when we specify a "On
> demand" or "Schedule" population, is it performing a Full Population? The
> table does not have any date-time stamp.
> 2) If a full population is taking place, and there are records going
> into the Indexed table, how will the FTS behave? Does that means that the
FTS
> will redo the full population of the index again, or it will just include
the
> new text, and perform a incremental population?
> I am trying to determine which type of population method has the
best[vbcol=seagreen]
> perfomance since there is a slight flaw in the software.
> "John Kane" wrote:
UIiB is[vbcol=seagreen]
and[vbcol=seagreen]
for[vbcol=seagreen]
account[vbcol=seagreen]
is[vbcol=seagreen]
(mssearch.exe)[vbcol=seagreen]
some[vbcol=seagreen]
it[vbcol=seagreen]
article[vbcol=seagreen]
Component[vbcol=seagreen]
http://support.microsoft.com/default...B;EN-US;277549[vbcol=seagreen]
can[vbcol=seagreen]
see[vbcol=seagreen]
am[vbcol=seagreen]
However,[vbcol=seagreen]
not[vbcol=seagreen]
have[vbcol=seagreen]
will[vbcol=seagreen]
take[vbcol=seagreen]
content[vbcol=seagreen]
see[vbcol=seagreen]
for[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
Catelog[vbcol=seagreen]
populated[vbcol=seagreen]
will[vbcol=seagreen]
been[vbcol=seagreen]
i.e..[vbcol=seagreen]
background,[vbcol=seagreen]
gather[vbcol=seagreen]
to[vbcol=seagreen]
off[vbcol=seagreen]
you[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
index[vbcol=seagreen]
both[vbcol=seagreen]
SQL[vbcol=seagreen]