Thursday, March 22, 2012 8:46 AM
I have enabled FTS on two tables with change tracking as MAN and common catalog for both the full text indexes.
During full text index wizard, i haven't defined the population schedule.
I tried to define population schedule at catalog level by navigating to Storage-->Full Text Catalogs-->My Catalog
After adding one schedule it displays the population type as catalog-optimize and actually its not populating the index.
If i define the catalog population schedule in the FTI wizard, it displays catalog-incremental population and it is able to populate all the indexes refering to the catalog.
My question is
1.What is catalog-optimize?
2.Why the incremental population is not working in the first approach?
Friday, March 23, 2012 8:33 AMModerator
Hi sambasiva reddy,
Based on your description, I demonstrate on my environment. First, to define full-text index via SQL Server Management Studio, with manually change tracking, and no population schedule defined, it will perform a full population after you completed the wizard. You can view the full-text index populated from sys.dm_fts_index_keywords:
select * from sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name') )
In this case, if you insert data into this table, and create a population schedule with catalog-optimize fixed population type via ‘Storage-->Full Text Catalogs-->My Catalog’, you will find the a job related this schedule. After looking into the job step in this job, it issues statements which are used to reorganize the fulltext catalog as below:
USE <database_name> ALTER FULLTEXT CATALOG <catalog_name> REORGANIZE
That is why you find that by executing this job cannot populate the full-text index inserted after creating.
If you define the catalog population schedule in the wizard with catalog-incremental fixed population type, you will also find a related job under SQL Server Agent. Meanwhile, you can see queries which are used to populate the full-text index incrementally as below:
USE <database_name> ALTER FULLTEXT INDEX ON <table_name> START INCREMENTAL POPULATION
In this case, SQL Server will perform incremental populations according to the specified schedule. With a schedule type of ‘one time’, you can see the index populated immediately after you insert data into this table. The first time of incremental population is equal to a full population.
If the initial volume of data in the table is large, it will take relatively long time to finish the first population.
TechNet Community Support
Friday, March 23, 2012 10:28 AM
I appreciate your time to clarify this query.I have few more questions based on your response.
1.If I want to use incremental population on multiple FT indexes present in one catalog, I have to define the catalog population schedule in the wizard of any one of the FT indexes and it will be applied to all other FT indexes refering to that catalog. Is my nunderstanding correct? Is it the only option to define the catalog population schedule?
2.Creating a population schedule with catalog-optimize fixed population type via ‘Storage-->Full Text Catalogs-->My Catalog’ is misleading from the context menu name(properties-->Population Schedule) since its not actually populating the base table
3.I know that ALTER FULLTEXT CATALOG <catalog_name> REORGANIZE performs master merge and improves performance.But why developer has to manually perform this step via schedule?
Friday, March 23, 2012 1:18 PMModerator
For the first time specifying a catalog and creating a catalog schedule for a table in definition full-text index wizard, it will create a job with catalog schedule for this table to populate full-text index. For the second time for another table with the same catalog, it will display the catalog schedule of the catalog, however, you are required to create a new table schedule on the Define Population Schedules step, the schedule name can be the same as the first you created, to create a new job for this table to populate full-text index.
To define these operations via wizard may be a little confused. You can manually create a job with category of ‘Full-Text’, and create a job step with type of ‘Transact-SQL script (T-SQL)’, add the ‘ALTER FULLTEXT INDEX ON <table_name> START INCREMENTAL POPULATION’ statements for all of tables which you want to incrementally populate, meanwhile, configure a schedule for this job.
As for the last question, it is the operation of management for the fulltext catalog. Just as indexes on a table, you are required to manually manage them to improve performance.
TechNet Community Support
- Marked As Answer by Stephanie LvModerator Monday, April 02, 2012 8:25 AM