Incremental Population Schedule at Catalog level is not working

Question

Hello all,

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?

Answer 1

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.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hello Stephanie,

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?

Thanks

Samba

Answer 3

Samba,

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.

---

Stephanie Lv

TechNet Community Support