locked
Azure SQL Full text indexes constantly generating queries RRS feed

  • Question

  • Hello, 

    We have been using Azure sql databases for quite a while now. All of them have full text indexes. 

    Lately i have noticed that in the list of queries that are being fired at the database (in Query performance insights), there is a query that is being fired very frequently. It does not originate from our application, and is clearly a system query. 

    The query is being fired approximately every 5 minutes. Since some of our databases are on the new serverless plan with auto pause after an hour, these queries are preventing them from going into sleep mode. 

    I have noticed this behaviour for a few weeks. Before that there was no problem. 

    Could this be a change in functionality in Azure sql ? And is there a way to avoid this, or to reconfigure something ? 

    This is the query : 

    SELECT c.*,
                        i.object_id, i.unique_index_id, i.is_enabled, i.change_tracking_state_desc, i.has_crawl_completed,
                        i.crawl_type_desc, i.crawl_start_date, crawl_end_date,
                        i.incremental_timestamp, i.stoplist_id, i.data_space_id, i.property_list_id,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFullTextMergeStatus'as intas merge_status,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextDocsProcessed'as intas docs_processed,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextFailCount'as intas fail_count,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextItemCount'as intas item_count,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextKeyColumn'as intas key_column,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextPendingChanges'as intas pending_changes,
                        cast(OBJECTPROPERTYEX(i.object_id'TableFulltextPopulateStatus'as intas populate_status
                        FROM [57933ff2-c6f3-410d-a6ff-fa5204000fc3].sys.dm_fts_active_catalogs c
                        JOIN [57933ff2-c6f3-410d-a6ff-fa5204000fc3].sys.fulltext_indexes i on c.catalog_id = i.fulltext_catalog_id

    Thanks in advance

    Rudy


    VB.NET Developer

    Thursday, March 26, 2020 7:55 AM

All replies

  • We are seeing the same query. Were you able to resolve this?
    Friday, June 5, 2020 9:03 PM