none
Full Text Search in Azure - Population Issue RRS feed

  • Question

  • I'm running a Azure SQL Server (Single Instance, General Purpose, Serverless, Gen5, 4 vCores, auto-pause disabled). I've setup a Full Text index on a table but the Population process has been running for three days. I'm not sure if I've done something wrong and I should start over.

    The table does have 17.8 million rows.

    I've create the Catalog and Full Text Index using the following scripts:

    CREATE FULLTEXT CATALOG [WC_Contacts_Cat] WITH ACCENT_SENSITIVITY = ON 

    CREATE FULLTEXT INDEX ON [CS].[WCContact] KEY INDEX [PK_WCContact] ON ([WC_Contacts_Cat]) WITH (CHANGE_TRACKING AUTO)

    I've added only one column for testing purposes which is the smallest column with only 80 distinct values

    ALTER FULLTEXT INDEX ON [CS].[WCContact] ADD ([filename]) 

    ALTER FULLTEXT INDEX ON [CS].[WCContact] ENABLE

    This table has a clustered int Primary Key (not nullable) called PK_WCContact

    Here is the results from SELECT * FROM sys.fulltext_indexes

    object_id 1285579618
    unique_index_id 1
    fulltext_catalog_id 6
    is_enabled 1
    change_tracking_state A
    change_tracking_state_desc AUTO
    has_crawl_completed 0
    crawl_type F
    crawl_type_desc FULL_CRAWL
    crawl_start_date 12/11/2019
    crawl_end_date NULL
    incremental_timestamp NULL
    stoplist_id 0
    property_list_id NULL
    data_space_id 1

    Why this is taking so long considering I have only added one column? I have 5 more columns to add so I am concerned how long the Population process will take. Any advice would be greatly appreciated.

    Friday, November 15, 2019 1:41 AM

All replies

  • Here is some additional results:

    ItemCount 17507392
    UniqueKeyCount 0
    IndexSize 1170
    MergeStatus 0
    PopulateCompletionAge 0
    PopulateStatus 1
    ImportStatus 0

    If UniqueKeyCount is 0 then does that show population is not working.

    Friday, November 15, 2019 2:03 AM
  • Please query sys.dm_fts_index_population to check the status of population.

    If you need to start over again, since this is a large table please create the indexes with Change tracking disabled and then start the population.

    Steps are documented here.

    Do let us know if you face any issues.

    -

    If this answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .


    Friday, November 15, 2019 10:20 AM
    Moderator
  • Thanks Kalyan

    I raised a ticket to Microsoft and they did identify high level of locks, and suggested I turn on Automatic Tuning.

    One column has finally finished. It only had 225 UniqueKeyCount but took 3 days to populate. I've now started on the population of the next column.

    What is the advantage of create the indexes with Change tracking disabled?

    Is it better to add all the columns to the index and then start the population process?

    Here is the results from that query:

    database_id 5
    catalog_id 6
    table_id 1285579618
    memory_address 0x23DDF865D1D2BA2A
    population_type 1
    population_type_description FULL
    is_clustered_index_scan 0
    range_count 1
    completed_range_count 1
    outstanding_batch_count 2
    status 5
    status_description Processing normally
    completion_type 0
    completion_type_description NONE
    worker_count 0
    queued_population_type 4
    queued_population_type_description AUTO
    start_time 00:18.4
    incremental_timestamp 0x00000000016D72BF

    Friday, November 15, 2019 11:56 AM