Full Text Search Population Method...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tts - 20 Dec 2004 08:33 GMT

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?

Reply to this Message

John Kane - 20 Dec 2004 09:03 GMT

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/

> Hi,
>
[quoted text clipped - 6 lines]
> Server to do an incremental update on both the Catalog and Indexes? If yes,
> which method should I use?

Reply to this Message

tts - 20 Dec 2004 09:33 GMT

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.

> TTS,
> The short answer is no. You do not need to run a scheduled, i.e.. instruct
[quoted text clipped - 34 lines]
> yes,
> > which method should I use?

Reply to this Message

tts - 21 Dec 2004 02:00 GMT

Hi,

Can anybody confirm my conclusion?

> Thanks John.
>
[quoted text clipped - 42 lines]
> > yes,
> > > which method should I use?

Reply to this Message

John Kane - 21 Dec 2004 02:42 GMT

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'TableFullTextPopulateStatus')

Hope that help!
John

> Hi,
>
[quoted text clipped - 46 lines]
> > > yes,
> > > > which method should I use?

Reply to this Message

tts - 21 Dec 2004 08:41 GMT

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.

> Hi TTS,
> Sorry for the delay in replying! No, and depending upon how much content you
[quoted text clipped - 92 lines]
> > > > yes,
> > > > > which method should I use?

Reply to this Message

John Kane - 21 Dec 2004 15:05 GMT

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.aspx?scid=KB;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

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello John,
>
[quoted text clipped - 39 lines]
> >
> > select

objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus')

> > Hope that help!
> > John
[quoted text clipped - 67 lines]
> > > > > yes,
> > > > > > which method should I use?

Reply to this Message

tts - 27 Dec 2004 06:11 GMT

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.

> You're welcome, TTS,
> Is it not populating every time or just some of the time? If it is the
[quoted text clipped - 169 lines]
> > > > > > yes,
> > > > > > > which method should I use?

Reply to this Message

John Kane - 27 Dec 2004 07:01 GMT

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

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello John,
>
[quoted text clipped - 99 lines]
> > > >
> > > > select

objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus')

> > > > Hope that help!
> > > > John
[quoted text clipped - 82 lines]
> > > > > > > yes,
> > > > > > > > which method should I use?

Reply to this Message