Azure SQL Server Stored Procedure runs infinitely if not executedhourly.

Question

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.

Answer 1

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.

Answer 2

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?