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 two 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.

    Thursday, November 14, 2019 3:19 AM

All replies

  • Another day has passed and it's still running. 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:02 AM