|
|
You're in the
sql server full text search
group:Questions about automatic change tracking versus incremental population.
sql server full text search:
(SQL Server 2000, SP3a)
Hello all! At my company, we have a database with 12 Full-Text Catalogs defined. Some of these FTCs are apparently defined to use automatic change tracking (CT) and others use incremental population via scheduled jobs. Is there any way to determine which of these FTCs are using the various update methods? When I look at the definition of the FTC, I don't really see anything there that would indicate which ones are CT versus incremental. Also, I believe that we have scheduled jobs to perform an incremental population on *all* of our FTCs, regardless of whether they're CT or otherwise. Is that a good practice? I'm given to understand that FTCs can't use CT if the table/column is updated through WRITETEXT or UPDATETEXT operations. Does that mean that columns of TEXT/IMAGE data can't use CT? How could I determine whether WRITE/UPDATETEXT were being used? Does a normal UPDATE statement on a TEXT column result in a UPDATETEXT operation? Thanks for any help you can provide! John Peterson
John,
Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata functions: "Transact-SQL full-text properties" FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') You can use one of the following metadata functions: sp_help_fulltext_catalogs -- see STATUS column for 9 = Change tracking SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') -- returns: 9 for Change tracking -- or select ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') select objectproperty(object_id(N'<table_name>'),N'TableFulltextChangeTrackingOn') select objectproperty(object_id(N'<table_name>'),N'TableFullTextBackgroundUpdateInd exOn') select objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus') You can then modify the above sql to generalize this to be used for any able table or in any FT-enabled database. As for CT and WRITETEXT or UPDATETEXT operations, it's only changes via these means that are not detected and therefore not populated to the FT Catalog via "Change Tracking" the changes will be updated or written to the FT-enabled text column. You will then need to run either a Full or Incremental (if timestamp column present) Population to ensure that all changes are included in the FT Catalog. You can use Profiler to track if WRITETEXT or UPDATETEXT operations are being performed and no, you must specify a WRITETEXT or UPDATETEXT operation specificly in your code. Regards, John [quoted text, click to view] "John Peterson" <j0hnp@comcast.net> wrote in message news:#z60RZQ5DHA.2740@TK2MSFTNGP09.phx.gbl... > (SQL Server 2000, SP3a) > > Hello all! > > At my company, we have a database with 12 Full-Text Catalogs defined. Some of these FTCs > are apparently defined to use automatic change tracking (CT) and others use incremental > population via scheduled jobs. Is there any way to determine which of these FTCs are > using the various update methods? When I look at the definition of the FTC, I don't > really see anything there that would indicate which ones are CT versus incremental. > > Also, I believe that we have scheduled jobs to perform an incremental population on *all* > of our FTCs, regardless of whether they're CT or otherwise. Is that a good practice? > > I'm given to understand that FTCs can't use CT if the table/column is updated through > WRITETEXT or UPDATETEXT operations. Does that mean that columns of TEXT/IMAGE data can't > use CT? How could I determine whether WRITE/UPDATETEXT were being used? Does a normal > UPDATE statement on a TEXT column result in a UPDATETEXT operation? > > Thanks for any help you can provide! > > John Peterson > >
Fantastic insight, John, thank you!
I probably should have examined BOL for more of the FT properties -- but now that I know they exist, that'll make it easier to learn how we're setting ours up. Good suggestion to use the Profiler to see if we're using WRITE/UPDATETEXT. To my knowledge, we're not -- and that sounds like we might be able to potentially have *all* our FTCs use CT (which would be desirable in our application). One other question: do you see a problem with running an Incremental Population over all of our FTCs, even those that are CTs? Thanks again (as always! :-). John Peterson [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:O%23ZMyRR5DHA.2764@TK2MSFTNGP09.phx.gbl... > John, > Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata > functions: "Transact-SQL full-text properties" > FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') > FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') > DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') > > You can use one of the following metadata functions: > sp_help_fulltext_catalogs > -- see STATUS column for 9 = Change tracking > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > -- returns: 9 for Change tracking > -- or > select > ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') > select > objectproperty(object_id(N'<table_name>'),N'TableFulltextChangeTrackingOn') > select > objectproperty(object_id(N'<table_name>'),N'TableFullTextBackgroundUpdateInd > exOn') > select > objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus') > > You can then modify the above sql to generalize this to be used for any able > table or in any FT-enabled database. > > As for CT and WRITETEXT or UPDATETEXT operations, it's only changes via > these means that are not detected and therefore not populated to the FT > Catalog via "Change Tracking" the changes will be updated or written to the > FT-enabled text column. You will then need to run either a Full or > Incremental (if timestamp column present) Population to ensure that all > changes are included in the FT Catalog. You can use Profiler to track if > WRITETEXT or UPDATETEXT operations are being performed and no, you must > specify a WRITETEXT or UPDATETEXT operation specificly in your code. > > Regards, > John > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > news:#z60RZQ5DHA.2740@TK2MSFTNGP09.phx.gbl... > > (SQL Server 2000, SP3a) > > > > Hello all! > > > > At my company, we have a database with 12 Full-Text Catalogs defined. > Some of these FTCs > > are apparently defined to use automatic change tracking (CT) and others > use incremental > > population via scheduled jobs. Is there any way to determine which of > these FTCs are > > using the various update methods? When I look at the definition of the > FTC, I don't > > really see anything there that would indicate which ones are CT versus > incremental. > > > > Also, I believe that we have scheduled jobs to perform an incremental > population on *all* > > of our FTCs, regardless of whether they're CT or otherwise. Is that a > good practice? > > > > I'm given to understand that FTCs can't use CT if the table/column is > updated through > > WRITETEXT or UPDATETEXT operations. Does that mean that columns of > TEXT/IMAGE data can't > > use CT? How could I determine whether WRITE/UPDATETEXT were being used? > Does a normal > > UPDATE statement on a TEXT column result in a UPDATETEXT operation? > > > > Thanks for any help you can provide! > > > > John Peterson > > > > > >
You're welcome, John,
No... no problems. I've tested this under SQL Server 2000 SP3 on Win2003 with a Change Tracking and Update Index in Background enabled and populated table and ran both an Incremental and Full Populations with inserted data before and afterwards with no errors and the expected results. However, while I tested this on very small tables, you should test this as well in your own test environment with a larger, near-production sized table, so you have some idea on how long an Incremental population will take (almost as long as a full Population). Best Regards, John [quoted text, click to view] "John Peterson" <j0hnp@comcast.net> wrote in message news:#EZst4R5DHA.2392@TK2MSFTNGP11.phx.gbl... > Fantastic insight, John, thank you! > > I probably should have examined BOL for more of the FT properties -- but now that I know > they exist, that'll make it easier to learn how we're setting ours up. > > Good suggestion to use the Profiler to see if we're using WRITE/UPDATETEXT. To my > knowledge, we're not -- and that sounds like we might be able to potentially have *all* > our FTCs use CT (which would be desirable in our application). > > One other question: do you see a problem with running an Incremental Population over all > of our FTCs, even those that are CTs? > > Thanks again (as always! :-). > > John Peterson > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:O%23ZMyRR5DHA.2764@TK2MSFTNGP09.phx.gbl... > > John, > > Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata > > functions: "Transact-SQL full-text properties" > > FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') > > FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') > > DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') > > > > You can use one of the following metadata functions: > > sp_help_fulltext_catalogs > > -- see STATUS column for 9 = Change tracking > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > > -- returns: 9 for Change tracking > > -- or > > select > > ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') > > select > > objectproperty(object_id(N'<table_name>'),N'TableFulltextChangeTrackingOn') > > select > > objectproperty(object_id(N'<table_name>'),N'TableFullTextBackgroundUpdateInd > > exOn') > > select > > objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus') > > > > You can then modify the above sql to generalize this to be used for any able > > table or in any FT-enabled database. > > > > As for CT and WRITETEXT or UPDATETEXT operations, it's only changes via > > these means that are not detected and therefore not populated to the FT > > Catalog via "Change Tracking" the changes will be updated or written to the > > FT-enabled text column. You will then need to run either a Full or > > Incremental (if timestamp column present) Population to ensure that all > > changes are included in the FT Catalog. You can use Profiler to track if > > WRITETEXT or UPDATETEXT operations are being performed and no, you must > > specify a WRITETEXT or UPDATETEXT operation specificly in your code. > > > > Regards, > > John > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > news:#z60RZQ5DHA.2740@TK2MSFTNGP09.phx.gbl... > > > (SQL Server 2000, SP3a) > > > > > > Hello all! > > > > > > At my company, we have a database with 12 Full-Text Catalogs defined. > > Some of these FTCs > > > are apparently defined to use automatic change tracking (CT) and others > > use incremental > > > population via scheduled jobs. Is there any way to determine which of > > these FTCs are > > > using the various update methods? When I look at the definition of the > > FTC, I don't > > > really see anything there that would indicate which ones are CT versus > > incremental. > > > > > > Also, I believe that we have scheduled jobs to perform an incremental > > population on *all* > > > of our FTCs, regardless of whether they're CT or otherwise. Is that a > > good practice? > > > > > > I'm given to understand that FTCs can't use CT if the table/column is > > updated through > > > WRITETEXT or UPDATETEXT operations. Does that mean that columns of > > TEXT/IMAGE data can't > > > use CT? How could I determine whether WRITE/UPDATETEXT were being used? > > Does a normal > > > UPDATE statement on a TEXT column result in a UPDATETEXT operation? > > > > > > Thanks for any help you can provide! > > > > > > John Peterson > > > > > > > > > > > >
John,
I guess that (the needless work) would depend upon the amount (number of rows) that you *suspect* would of been updated or overwritten by UPDATETEXT & WRITETEXT respectively... If you are doing a nightly Incremental Population and assuming that these tables have timestamp columns, then these Incremental Populations will take the time to complete that they normally do. I think you are asking what is the true difference between Full and Incremental Populations and why Incremental Populations, even with little or no change seem to take as long to run as a Full Population. Correct? Assuming so, below is one of the many postings I've made regarding why Incremental Populations take nearly as long as Full Populations.. "Assuming that your FT-enabled table has a timestamp column as this is a requirement for true Incremental Populations, when an Incremental Population is started, the SQL/MSSearch population process could of determined the rows' changed status with the current value of @@DBTS and then selecting rows that have a timestamp values that are greater than the @@DBTS value from the last FT Population. However, with this method, you would not be able to detect what rows have been deleted from your FT-enable table since these rows do not exist in your table, but these deleted rows and their non-noise unique words do exist in the FT Catalog. Therefore, the SQL/MSSearch population process must read ALL rows again just as it would for a Full Population to detect and compare what rows need to be deleted from the FT Catalog. This is by design for Incremental Populations and why in SQL Server 2000 you should use "Change Tracking" with "Update Index in Background" instead of Incremental population for large FT-enabled tables. See the BOL for information on these options as well as BOL title "Full-text Search Recommendations". Note, for tables without the timestamp column, a Full population would be executed regardless of what you had selected to run." Regards, John [quoted text, click to view] "John Peterson" <j0hnp@comcast.net> wrote in message
news:#pnKc9S5DHA.1672@TK2MSFTNGP12.phx.gbl... > Excellent -- thanks for the update, John! > > So, it sounds as if doing a Incremental Population on a table that is CT won't be a > problem, but it might be needless work? Would *each* Incremental Population be nearly as > long as a Full Population (that is, if we did it nightly, is it like we're just doing a > Full each time)? Can you shed some light on why that might be? (Or am I misunderstanding > you somehow?) > > Thanks again! > > John Peterson > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:%235hhx5S5DHA.1052@TK2MSFTNGP12.phx.gbl... > > You're welcome, John, > > No... no problems. I've tested this under SQL Server 2000 SP3 on Win2003 > > with a Change Tracking and Update Index in Background enabled and populated > > table and ran both an Incremental and Full Populations with inserted data > > before and afterwards with no errors and the expected results. > > > > However, while I tested this on very small tables, you should test this as > > well in your own test environment with a larger, near-production sized > > table, so you have some idea on how long an Incremental population will take > > (almost as long as a full Population). > > > > Best Regards, > > John > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > news:#EZst4R5DHA.2392@TK2MSFTNGP11.phx.gbl... > > > Fantastic insight, John, thank you! > > > > > > I probably should have examined BOL for more of the FT properties -- but > > now that I know > > > they exist, that'll make it easier to learn how we're setting ours up. > > > > > > Good suggestion to use the Profiler to see if we're using > > WRITE/UPDATETEXT. To my > > > knowledge, we're not -- and that sounds like we might be able to > > potentially have *all* > > > our FTCs use CT (which would be desirable in our application). > > > > > > One other question: do you see a problem with running an Incremental > > Population over all > > > of our FTCs, even those that are CTs? > > > > > > Thanks again (as always! :-). > > > > > > John Peterson > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > news:O%23ZMyRR5DHA.2764@TK2MSFTNGP09.phx.gbl... > > > > John, > > > > Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata > > > > functions: "Transact-SQL full-text properties" > > > > FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') > > > > FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') > > > > DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') > > > > > > > > You can use one of the following metadata functions: > > > > sp_help_fulltext_catalogs > > > > -- see STATUS column for 9 = Change tracking > > > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > > > > -- returns: 9 for Change tracking > > > > -- or > > > > select > > > > > > ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') > > > > select > > > > > > objectproperty(object_id(N'<table_name>'),N'TableFulltextChangeTrackingOn') > > > > select > > > > > > objectproperty(object_id(N'<table_name>'),N'TableFullTextBackgroundUpdateInd > > > > exOn') > > > > select > > > > > > objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus') > > > > > > > > You can then modify the above sql to generalize this to be used for any > > able > > > > table or in any FT-enabled database. > > > > > > > > As for CT and WRITETEXT or UPDATETEXT operations, it's only changes via > > > > these means that are not detected and therefore not populated to the FT > > > > Catalog via "Change Tracking" the changes will be updated or written to > > the > > > > FT-enabled text column. You will then need to run either a Full or > > > > Incremental (if timestamp column present) Population to ensure that all > > > > changes are included in the FT Catalog. You can use Profiler to track if > > > > WRITETEXT or UPDATETEXT operations are being performed and no, you must > > > > specify a WRITETEXT or UPDATETEXT operation specificly in your code. > > > > > > > > Regards, > > > > John > > > > > > > > > > > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > > > news:#z60RZQ5DHA.2740@TK2MSFTNGP09.phx.gbl... > > > > > (SQL Server 2000, SP3a) > > > > > > > > > > Hello all! > > > > > > > > > > At my company, we have a database with 12 Full-Text Catalogs defined. > > > > Some of these FTCs > > > > > are apparently defined to use automatic change tracking (CT) and > > others > > > > use incremental > > > > > population via scheduled jobs. Is there any way to determine which of > > > > these FTCs are > > > > > using the various update methods? When I look at the definition of > > the > > > > FTC, I don't
Excellent -- thanks for the update, John!
So, it sounds as if doing a Incremental Population on a table that is CT won't be a problem, but it might be needless work? Would *each* Incremental Population be nearly as long as a Full Population (that is, if we did it nightly, is it like we're just doing a Full each time)? Can you shed some light on why that might be? (Or am I misunderstanding you somehow?) Thanks again! John Peterson [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:%235hhx5S5DHA.1052@TK2MSFTNGP12.phx.gbl... > You're welcome, John, > No... no problems. I've tested this under SQL Server 2000 SP3 on Win2003 > with a Change Tracking and Update Index in Background enabled and populated > table and ran both an Incremental and Full Populations with inserted data > before and afterwards with no errors and the expected results. > > However, while I tested this on very small tables, you should test this as > well in your own test environment with a larger, near-production sized > table, so you have some idea on how long an Incremental population will take > (almost as long as a full Population). > > Best Regards, > John > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > news:#EZst4R5DHA.2392@TK2MSFTNGP11.phx.gbl... > > Fantastic insight, John, thank you! > > > > I probably should have examined BOL for more of the FT properties -- but > now that I know > > they exist, that'll make it easier to learn how we're setting ours up. > > > > Good suggestion to use the Profiler to see if we're using > WRITE/UPDATETEXT. To my > > knowledge, we're not -- and that sounds like we might be able to > potentially have *all* > > our FTCs use CT (which would be desirable in our application). > > > > One other question: do you see a problem with running an Incremental > Population over all > > of our FTCs, even those that are CTs? > > > > Thanks again (as always! :-). > > > > John Peterson > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:O%23ZMyRR5DHA.2764@TK2MSFTNGP09.phx.gbl... > > > John, > > > Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata > > > functions: "Transact-SQL full-text properties" > > > FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') > > > FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') > > > DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') > > > > > > You can use one of the following metadata functions: > > > sp_help_fulltext_catalogs > > > -- see STATUS column for 9 = Change tracking > > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > > > -- returns: 9 for Change tracking > > > -- or > > > select > > > > ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') > > > select > > > > objectproperty(object_id(N'<table_name>'),N'TableFulltextChangeTrackingOn') > > > select > > > > objectproperty(object_id(N'<table_name>'),N'TableFullTextBackgroundUpdateInd > > > exOn') > > > select > > > > objectproperty(object_id(N'<table_name>'),N'TableFullTextPopulateStatus') > > > > > > You can then modify the above sql to generalize this to be used for any > able > > > table or in any FT-enabled database. > > > > > > As for CT and WRITETEXT or UPDATETEXT operations, it's only changes via > > > these means that are not detected and therefore not populated to the FT > > > Catalog via "Change Tracking" the changes will be updated or written to > the > > > FT-enabled text column. You will then need to run either a Full or > > > Incremental (if timestamp column present) Population to ensure that all > > > changes are included in the FT Catalog. You can use Profiler to track if > > > WRITETEXT or UPDATETEXT operations are being performed and no, you must > > > specify a WRITETEXT or UPDATETEXT operation specificly in your code. > > > > > > Regards, > > > John > > > > > > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > > news:#z60RZQ5DHA.2740@TK2MSFTNGP09.phx.gbl... > > > > (SQL Server 2000, SP3a) > > > > > > > > Hello all! > > > > > > > > At my company, we have a database with 12 Full-Text Catalogs defined. > > > Some of these FTCs > > > > are apparently defined to use automatic change tracking (CT) and > others > > > use incremental > > > > population via scheduled jobs. Is there any way to determine which of > > > these FTCs are > > > > using the various update methods? When I look at the definition of > the > > > FTC, I don't > > > > really see anything there that would indicate which ones are CT versus > > > incremental. > > > > > > > > Also, I believe that we have scheduled jobs to perform an incremental > > > population on *all* > > > > of our FTCs, regardless of whether they're CT or otherwise. Is that a > > > good practice? > > > > > > > > I'm given to understand that FTCs can't use CT if the table/column is > > > updated through > > > > WRITETEXT or UPDATETEXT operations. Does that mean that columns of > > > TEXT/IMAGE data can't > > > > use CT? How could I determine whether WRITE/UPDATETEXT were being > used? > > > Does a normal > > > > UPDATE statement on a TEXT column result in a UPDATETEXT operation? > > > > > > > > Thanks for any help you can provide! > > > > > > > > John Peterson > > > > > > > > > > > > > > > > > > > >
Sorry, John...I should have been more specific on our implementation:
I suspect that we have CT FTCs that are being updated by a scheduled job that does incremental population. However, I believe that these tables are *never* updated with UPDATE/WRITETEXT -- so I think that maybe this incremental update is unnecessary on the CT FTCs. But, as you suggest, I'll verify with the Profiler or scour our code base. If I understand your "FAQ" correctly, I think I see why SQL Server still needs to scan all the records (in the event of a deletion). In our model, records are rarely deleted, but can be updated many times. Presumably an Incremental Population would "do the right thing" most of the time. However, I will say, we've had some *very* lengthy Incremental Populations that made me wonder if it was doing a Full Population. It sounds like we may have been experiencing the symptoms that you describe. To the extent that we can, I'd prefer to have *all* our tables use Change Tracking. It sounds like we can, if we can verify that we don't do an UPDATE/WRITETEXT. That would be a pretty big "win", I think. Out of curiosity, does CT have any latency associated with it? That is, if the server is "busy", does it defer the updating of the FTC until some "idle" time? Or does it treat the FTC updating like a "regular" index -- and have it all incorporated in the same (or associated) transaction/batch? Thanks again for your help! [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message
news:ueciDbT5DHA.1852@TK2MSFTNGP10.phx.gbl... > John, > I guess that (the needless work) would depend upon the amount (number of > rows) that you *suspect* would of been updated or overwritten by UPDATETEXT > & WRITETEXT respectively... If you are doing a nightly Incremental > Population and assuming that these tables have timestamp columns, then these > Incremental Populations will take the time to complete that they normally > do. > > I think you are asking what is the true difference between Full and > Incremental Populations and why Incremental Populations, even with little or > no change seem to take as long to run as a Full Population. Correct? > Assuming so, below is one of the many postings I've made regarding why > Incremental Populations take nearly as long as Full Populations.. > > "Assuming that your FT-enabled table has a timestamp column as this is a > requirement for true Incremental Populations, when an Incremental Population > is started, the SQL/MSSearch population process could of determined the > rows' changed status with the current value of @@DBTS and then selecting > rows that have a timestamp values that are greater than the @@DBTS value > from the last FT Population. However, with this method, you would not be > able to detect what rows have been deleted from your FT-enable table since > these rows do not exist in your table, but these deleted rows and their > non-noise unique words do exist in the FT Catalog. Therefore, the > SQL/MSSearch population process must read ALL rows again just as it would > for a Full Population to detect and compare what rows need to be deleted > from the FT Catalog. This is by design for Incremental Populations and why > in SQL Server 2000 you should use "Change Tracking" with "Update Index in > Background" instead of Incremental population for large FT-enabled tables. > See the BOL for information on these options as well as BOL title "Full-text > Search Recommendations". Note, for tables without the timestamp column, a > Full population would be executed regardless of what you had selected to > run." > > Regards, > John > > > "John Peterson" <j0hnp@comcast.net> wrote in message > news:#pnKc9S5DHA.1672@TK2MSFTNGP12.phx.gbl... > > Excellent -- thanks for the update, John! > > > > So, it sounds as if doing a Incremental Population on a table that is CT > won't be a > > problem, but it might be needless work? Would *each* Incremental > Population be nearly as > > long as a Full Population (that is, if we did it nightly, is it like we're > just doing a > > Full each time)? Can you shed some light on why that might be? (Or am I > misunderstanding > > you somehow?) > > > > Thanks again! > > > > John Peterson > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:%235hhx5S5DHA.1052@TK2MSFTNGP12.phx.gbl... > > > You're welcome, John, > > > No... no problems. I've tested this under SQL Server 2000 SP3 on Win2003 > > > with a Change Tracking and Update Index in Background enabled and > populated > > > table and ran both an Incremental and Full Populations with inserted > data > > > before and afterwards with no errors and the expected results. > > > > > > However, while I tested this on very small tables, you should test this > as > > > well in your own test environment with a larger, near-production sized > > > table, so you have some idea on how long an Incremental population will > take > > > (almost as long as a full Population). > > > > > > Best Regards, > > > John > > > > > > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > > news:#EZst4R5DHA.2392@TK2MSFTNGP11.phx.gbl... > > > > Fantastic insight, John, thank you! > > > > > > > > I probably should have examined BOL for more of the FT properties -- > but > > > now that I know > > > > they exist, that'll make it easier to learn how we're setting ours up. > > > > > > > > Good suggestion to use the Profiler to see if we're using > > > WRITE/UPDATETEXT. To my > > > > knowledge, we're not -- and that sounds like we might be able to > > > potentially have *all* > > > > our FTCs use CT (which would be desirable in our application). > > > > > > > > One other question: do you see a problem with running an Incremental > > > Population over all > > > > of our FTCs, even those that are CTs? > > > > > > > > Thanks again (as always! :-). > > > > > > > > John Peterson > > > > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > > news:O%23ZMyRR5DHA.2764@TK2MSFTNGP09.phx.gbl... > > > > > John, > > > > > Sure... see SQL Server 2000 BOL and the following T-SQL FTS metadata > > > > > functions: "Transact-SQL full-text properties" > > > > > FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') > > > > > FULLTEXTCATALOGPROPERTY('<FT_Catalog_name>','PopulateStatus') > > > > > DATABASEPROPERTY ('pubs', 'IsFullTextEnabled') > > > > > > > > > > You can use one of the following metadata functions: > > > > > sp_help_fulltext_catalogs > > > > > -- see STATUS column for 9 = Change tracking > > > > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > > > > > -- returns: 9 for Change tracking > > > > > -- or > > > > > select > > > > > > > > > ObjectProperty(object_id(N'<table_name>'),N'TableHasActiveFulltextIndex') > > > > > select > > > > > > > >
John,
Not to worry... For your FT-enabled tables that experience "some *very* lengthy Incremental Populations", do these tables have (1) a timestamp column or (2) are very large tables on the order of several million rows? If #1 - tables without the timestamp column, a Full population would be executed regardless if you selected a nightly "Incremental Population" scheduled job to run. Yes, there is a small latency associated with CT and UIiB, with approx 1 second delays, but if you have a large update (percentage-wise), you may want to turn-off "Update in Background" and then run an scheduled Incremental Population, again assuming a timestamp column is present. SQL Server 2000 BOL title "Maintaining Full-Text Indexes" talks a bit about this in regards to when to use "Timestamp-based incremental rebuild" vs. "Change Tracking" based upon various factors including CPU and memory availability and the need to keep the FT Index up-to-date.... Regards, John [quoted text, click to view] "John Peterson" <j0hnp@comcast.net> wrote in message
news:#S0aMuT5DHA.2524@TK2MSFTNGP11.phx.gbl... > Sorry, John...I should have been more specific on our implementation: > > I suspect that we have CT FTCs that are being updated by a scheduled job that does > incremental population. However, I believe that these tables are *never* updated with > UPDATE/WRITETEXT -- so I think that maybe this incremental update is unnecessary on the CT > FTCs. But, as you suggest, I'll verify with the Profiler or scour our code base. > > If I understand your "FAQ" correctly, I think I see why SQL Server still needs to scan all > the records (in the event of a deletion). In our model, records are rarely deleted, but > can be updated many times. Presumably an Incremental Population would "do the right > thing" most of the time. However, I will say, we've had some *very* lengthy Incremental > Populations that made me wonder if it was doing a Full Population. It sounds like we may > have been experiencing the symptoms that you describe. > > To the extent that we can, I'd prefer to have *all* our tables use Change Tracking. It > sounds like we can, if we can verify that we don't do an UPDATE/WRITETEXT. That would be > a pretty big "win", I think. Out of curiosity, does CT have any latency associated with > it? That is, if the server is "busy", does it defer the updating of the FTC until some > "idle" time? Or does it treat the FTC updating like a "regular" index -- and have it all > incorporated in the same (or associated) transaction/batch? > > Thanks again for your help! > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:ueciDbT5DHA.1852@TK2MSFTNGP10.phx.gbl... > > John, > > I guess that (the needless work) would depend upon the amount (number of > > rows) that you *suspect* would of been updated or overwritten by UPDATETEXT > > & WRITETEXT respectively... If you are doing a nightly Incremental > > Population and assuming that these tables have timestamp columns, then these > > Incremental Populations will take the time to complete that they normally > > do. > > > > I think you are asking what is the true difference between Full and > > Incremental Populations and why Incremental Populations, even with little or > > no change seem to take as long to run as a Full Population. Correct? > > Assuming so, below is one of the many postings I've made regarding why > > Incremental Populations take nearly as long as Full Populations.. > > > > "Assuming that your FT-enabled table has a timestamp column as this is a > > requirement for true Incremental Populations, when an Incremental Population > > is started, the SQL/MSSearch population process could of determined the > > rows' changed status with the current value of @@DBTS and then selecting > > rows that have a timestamp values that are greater than the @@DBTS value > > from the last FT Population. However, with this method, you would not be > > able to detect what rows have been deleted from your FT-enable table since > > these rows do not exist in your table, but these deleted rows and their > > non-noise unique words do exist in the FT Catalog. Therefore, the > > SQL/MSSearch population process must read ALL rows again just as it would > > for a Full Population to detect and compare what rows need to be deleted > > from the FT Catalog. This is by design for Incremental Populations and why > > in SQL Server 2000 you should use "Change Tracking" with "Update Index in > > Background" instead of Incremental population for large FT-enabled tables. > > See the BOL for information on these options as well as BOL title "Full-text > > Search Recommendations". Note, for tables without the timestamp column, a > > Full population would be executed regardless of what you had selected to > > run." > > > > Regards, > > John > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > news:#pnKc9S5DHA.1672@TK2MSFTNGP12.phx.gbl... > > > Excellent -- thanks for the update, John! > > > > > > So, it sounds as if doing a Incremental Population on a table that is CT > > won't be a > > > problem, but it might be needless work? Would *each* Incremental > > Population be nearly as > > > long as a Full Population (that is, if we did it nightly, is it like we're > > just doing a > > > Full each time)? Can you shed some light on why that might be? (Or am I > > misunderstanding > > > you somehow?) > > > > > > Thanks again! > > > > > > John Peterson > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > news:%235hhx5S5DHA.1052@TK2MSFTNGP12.phx.gbl... > > > > You're welcome, John, > > > > No... no problems. I've tested this under SQL Server 2000 SP3 on Win2003 > > > > with a Change Tracking and Update Index in Background enabled and > > populated > > > > table and ran both an Incremental and Full Populations with inserted > > data > > > > before and afterwards with no errors and the expected results. > > > > > > > > However, while I tested this on very small tables, you should test this > > as > > > > well in your own test environment with a larger, near-production sized > > > > table, so you have some idea on how long an Incremental population will > > take > > > > (almost as long as a full Population). > > > > > > > > Best Regards, > > > > John > > > > > > > > > > > > > > > > > > > > "John Peterson" <j0hnp@comcast.net> wrote in message > > > > news:#EZst4R5DHA.2392@TK2MSFTNGP11.phx.gbl... > > > > > Fantastic insight, John, thank you! > > > > > > > > > > I probably should have examined BOL for more of the FT properties -- > > but > > > > now that I know > > > > > they exist, that'll make it easier to learn how we're setting ours up. > > > > > > > > > > Good suggestion to use the Profiler to see if we're using > > > > WRITE/UPDATETEXT. To my
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 |