Azure SQL DB Serverless won't auto pause

Question

Looking at Query Performance Insight I found this query running 3-4 times/hr (my autopause setting is 1hr):

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 int) as merge_status,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextDocsProcessed') as int) as docs_processed,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextFailCount') as int) as fail_count,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextItemCount') as int) as item_count,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextKeyColumn') as int) as key_column,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPendingChanges') as int) as pending_changes,
                    cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPopulateStatus') as int) as populate_status
                    FROM [46e881b7-c5f1-41cb-8eee-7c92a89cba41].sys.dm_fts_active_catalogs c
                    JOIN [46e881b7-c5f1-41cb-8eee-7c92a89cba41].sys.fulltext_indexes i on c.catalog_id = i.fulltext_catalog_id

Any thoughts on what might be going on? Is there a way to detect the origin of the query? I only have one VM hooked up to the db, and I have services turned off, so not sure what is causing this periodic call.

Answer 1

I found the issue using Query Performance Insight, where I was able to view actual activity including query text. Even though I had configured the service's own scheduler to make it go to sleep, the service was still querying the DB. Once I actually turned the service off using the Task Scheduler, the DB paused after an hour of inactivity.

Query Performance Insight is a great tool for determining exactly what is going on with your database. One thing to remember though, if the database is paused and you navigate to Query Performance Insight, the database will be brought online.

Answer 2

Unfortunately this is a system query that is fired on Azure SQL Database when your databases use Full-Text indexes. You are seeing this running almost every hour, but you may see it running every 5 minutes sometimes. This is happening since early this year.

Full-text Search is considered an external service on Azure Serverless that explains that query coming regularly to execute on the database. Please read below:

Excerpt: "The resources of a serverless database are encapsulated by app package, SQL instance, and user resource pool entities.

The app package is the outer most resource management boundary for a database, regardless of whether the database is in a serverless or provisioned compute tier. The app package contains the SQL instance and external services that together scope all user and system resources used by a database in SQL Database. Examples of external services include R and full-text search." Source here.