| Groups | Blog | Home |
sql server full text search : Full Text Search Population Method...
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/ [quoted text, click to view] "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. [quoted text, click to view] "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... > > 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? > > > > > >
Hi,
Can anybody confirm my conclusion? [quoted text, click to view] "tts" wrote:
> 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... > > > 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? > > > > > > > > > >
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 [quoted text, click to view] "tts" <tts@discussions.microsoft.com> wrote in message news:F8A58279-7077-4CFC-9F3F-57F60787F615@microsoft.com... > Hi, > > Can anybody confirm my conclusion? > > "tts" wrote: > > > 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... > > > > 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? > > > > > > > > > > > > > > > > >
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. [quoted text, click to view] "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'TableFullTextPopulateStatus') > > Hope that help! > John > > > > > "tts" <tts@discussions.microsoft.com> wrote in message > news:F8A58279-7077-4CFC-9F3F-57F60787F615@microsoft.com... > > Hi, > > > > Can anybody confirm my conclusion? > > > > "tts" wrote: > > > > > 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... > > > > > 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? > > > > > > > > > > > > > > > > > > > > > > > >
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 -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "tts" <tts@discussions.microsoft.com> wrote in message Indexes?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), > 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'TableFullTextPopulateStatus') > > > > Hope that help! > > John > > > > > > > > > > "tts" <tts@discussions.microsoft.com> wrote in message > > news:F8A58279-7077-4CFC-9F3F-57F60787F615@microsoft.com... > > > Hi, > > > > > > Can anybody confirm my conclusion? > > > > > > "tts" wrote: > > > > > > > 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... > > > > > > 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
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. [quoted text, click to view] "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.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 > -- > 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), > > 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'TableFullTextPopulateStatus') > > > > > > Hope that help! > > > John > > > > > > > > > > > > > > > "tts" <tts@discussions.microsoft.com> wrote in message > > > news:F8A58279-7077-4CFC-9F3F-57F60787F615@microsoft.com... > > > > Hi, > > > > > > > > Can anybody confirm my conclusion? > > > > > > > > "tts" wrote: > > > > > > > > > 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" -
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/ [quoted text, click to view] "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 > 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.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 > > -- > > 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), > > > 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'TableFullTextPopulateStatus') > > > > > > > > Hope that help! > > > > John > > > > > > > > > > > > > > > > > > > > "tts" <tts@discussions.microsoft.com> wrote in message > > > > news:F8A58279-7077-4CFC-9F3F-57F60787F615@microsoft.com... > > > > > Hi, > > > > > > > > > > Can anybody confirm my conclusion? > > > > > > > > > > "tts" wrote: > > > > > > > > > > > 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |