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.