JacquesGrundlingh-8191 avatar image
0 Votes"
JacquesGrundlingh-8191 asked AlbertoMorillo edited

Azure SQL Server Stored Procedure runs infinitely if not executedhourly.

Hi all

I have a very simple Azure SQL Server Database with a table that gets about 300 values inserted from readers every 5 seconds, I have a Azure App Service with one page
to view this data coming into the database.

I have a very simple select query (stored procedure) that runs on the app service where I call the stored procedure to fetch me about 300 records at a time, the query itself takes less than 1 second to complete and show me the data.

My problem is that I have noticed everyday in the morning when I call that stored procedure from the app service it runs infinitely and times out, then I have to manually connect to the Azure SQL Database on my SQL Server Management Studio and execute the stored procedure manually with a random ID and then it executes infinitely, but if I cancel the query execute and rerun it a few times eventually it is successful. Then after that I can run it as many times as I want to it is less than 1 second and works 100% , but the next morning same thing again.

The Azure SQL Database has indexes on and all health and optimization checks that Azure provides, I have tried countless of fixes on the query itself but it seems to be environmental as this issue never occurs with a identical database and app service on our local server.

Any help or information would be appreciated thank you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
1 Vote"
DanGuzman answered JacquesGrundlingh-8191 commented

The initial delay may be symptomatic of synchronous statistics updates during query compilation. Check the AUTO_UPDATE_STATISTICS_ASYNC database option and consider turning it ON so that queries don't wait for auto stats update to be complete before query compilation and execution.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the response, that sounds like it might be the problem.

I have checked and my AUTO_UPDATE_STATISTICS_ASYNC was disabled, I have enabled it now and will wait till tomorrow morning to see if it solved it.

Then I will let you know and mark as accepted answer.

0 Votes 0 ·
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

On similar scenarios I have found the culprit is query optimization or required database maintenance. Here are a few suggestions.

  1. Can you please add a job on Azure Automation that runs like **[Ola maintenance scripts][1**] at 6:00 am.

  2. You mentioned you have implemented many fixes on the query but did you implemented those fixes based on examination of query plan generated by the stored procedure, and its most costly operators? Where the estimated rows by each operator similar to the actual number of rows after execution?

  3. I know you told us the automatic creation of indexes is enabled in your SQL Azure database, but I do not rely on that as that feature creates indexes and you later see SQL Azure rolls them back. Could you please verify there are no missing indexes on tables related to that query using the tool/query provided in this article?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi , thank you for the response.

  1. I will have to find the correct database maintenance scripts to run, but will give that a go.

  2. I have tried fixes based on what I could research at the time with similar issues , I have not checked the query plan costly operators, because the query does run in 1ms when working, meaning the query is optimized as best as it can be at the moments, I was trying fixes related to "recompile" or "caching" issues related to the stored procedure and not the actual execution.

  3. I will have a look at the indexes using your tool and reply on this comment, thank you. I was under the impression the Azure indexes were working as expected.

Thanks again

0 Votes 0 ·
AlbertoMorillo avatar image AlbertoMorillo JacquesGrundlingh-8191 ·

The site of Ola Hallengren shows some examples of how to use the scripts. Run them manually in the morning first before running the query. If that works, add it to a scheduled task.

1 Vote 1 ·

Thank you, will have a look at the site.

0 Votes 0 ·