Long Running Query - Azure SQL server

Question

Long Running Query - Azure SQL server

abdulaziz 86

(@backupTypeEquals nvarchar(1),@backupPathLike nvarchar(229))SELECT TOP 1  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm]  FROM [0254e7e5-6388-43b8-8865-b09767c7ff3c].[sys].[backup_metadata_store] WHERE (backup_type = @backupTypeEquals) AND (backup_path LIKE '%' + @backupPathLike + '%') ORDER BY backup_start_date DESC

Above query is taking longer time on every hour. It is not related to our app. 

Will it cost us?
Why this is happening every hour?
Azure SQL Backup is 12/24 and 1 week no every hour.
how we can get rid of this?

1 answer

Your answer

Answer 1

Ronen Ariely 15,206

Hi,

Will it cost us?

Depend on the Service tier, the storage and more.

Why this is happening every hour?

You should monitor and check the setting of your backup including the long-term backup retention. Maybe you changes something

https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure?view=azuresql&tabs=portal&WT.mc_id=DP-MVP-5001699

Azure SQL Backup is 12/24 and 1 week no every hour.

As I mentioned, you check since the default can be change. In addition what you wrote is not accurate. In addition to these, by default there is a Transaction log backups approximately every 10 minutes.

https://learn.microsoft.com/azure/azure-sql/database/automated-backups-overview?view=azuresql&WT.mc_id=DP-MVP-5001699#backup-frequency

User's image

taking longer time on every hour.

If you mean that every hour it take longer than previous hour then this is not normal and you should monitor the size of the transaction log file and the data file and the activity during the backup. You can use Extended Event to monitor activity and simple queries to get the size of the files.

More information if you can share might point us a bit better but the above is the general answer. I hope this help you to solve the issue.

Ronen Ariely 15,206 Reputation points

2023-02-26T11:21:34.2933333+00:00

sound great

Don't forget us :-)
abdulaziz 86 Reputation points

2023-03-01T17:44:52.19+00:00

my doubt was wrong. Still the script is running which is not related to the applications.

Let me create a support ticket for this.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-03-02T16:59:22.9433333+00:00

abdulaziz Thank you for posting your question on Microsoft Q&A.

We would like to thank you for the feedback you provided. We found out that your recent engagement on this question was rated low. Was the solution above not helpful.? Please let us know how we can make your experience better.

Please let us know what additional clarification you need and also if you are still seeing long running queries.

Regards,

Oury
abdulaziz 86 Reputation points

2023-03-03T07:37:22.3333333+00:00

Still don't know what is this scripts and why it is running.

I am curious, whether is consuming our so called DUTS
abdulaziz 86 Reputation points

2023-03-03T07:37:47.8666667+00:00

Hi Ariely,

Thanks for your answer. nothing change in Backups but it show most expansive query in Query Performance Insight. One thing I realize that In failover group the Read/Write grace period was 1 hour. To check this scenario, I made it to 5 hours. I will observe it occurrence and update here.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-03-06T22:16:05.95+00:00

abdulaziz the query you posted is a SQL query that is used to retrieve data from the backup_metadata_store table.

The query is taking longer time to execute because of the conditions specified in the WHERE clause. The conditions are filtering the data based on the backup_type and backup_path columns. The query is also sorting the data based on the backup_start_date column in descending order.

You can consider the following steps:

Create an index on the columns used in the WHERE clause (backup_type and backup_path). This will help the query to retrieve the data faster.

As for the cost, it depends on the resources used by the query and the Azure SQL database. You can check the Azure pricing page for more information on the cost of Azure SQL databases.

As for why the query is happening every hour, it could be due to a scheduled task or a recurring process in your application. To get rid of this, you can try to identify the source of the query and modify or remove it.

It's also worth noting that Azure SQL Backup is performed daily and weekly, not every hour. If you need to change the backup schedule, you can do so in the Azure portal.

I hope this information helps. Let me know if you need further assistance.

As you mentioned above, please go ahead and create a support case so we can further investigate. Please do share the ticket number here.

Regards,

Oury
abdulaziz 86 Reputation points

2023-03-07T16:41:14.21+00:00

Dear,

Thanks for the comment but this query is not related to our application, which makes us more curious about that. This query is showing long running query in query insight. As long Microsoft will not cost it, definitely we need to know why it is showing the long running query.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-03-09T18:22:54.56+00:00

abdulaziz please create a support case so we can further investigate this issue. Please do share the ticket number here.

Regards,

Oury

Share via

Long Running Query - Azure SQL server

1 answer

Your answer