CODEWITHSUNDEEP
azure-sql-databaseserverless
Daltin
Daltin

Reputation: 111

Azure SQL DB Serverless won't auto pause

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.

Upvotes: 3

Views: 1455

Answers (4)

FFFffff
FFFffff

Reputation: 1070

Assuming your low-time is predictable, with just a bit of effort you can setup a schedule with logic apps. E.g. switch to 100 DTUs every morning, and switch back to 10 DTUs every evening.

It's easy to do and compared to auto-pause it gives you total control. Lower tiers are almost free so it serves the purpose just as good.

IMO In the long run it's easier to maintain than troubleshooting random wake-up events.

Upvotes: 0

Logan
Logan

Reputation: 73

This may not be completely related to the OP's issue but seems like a good place to add this info as it may be of help to others running into this issue. I recently ran into an issue with Azure not auto-pausing as well. I was struggling to figure out the source of it as all of my connections were closed every evening and I ran a script to kill all active connections from the server side as well. Sometimes 3rd party software can keep the connection alive - in my case Redgate Sql Search tool. After removing the 3rd party tool, it now works as intended again.

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15688

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.

Upvotes: 1

Daltin
Daltin

Reputation: 111

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.

Upvotes: 1

Related Questions