question

AHawes avatar image
0 Votes"
AHawes asked AHawes commented

What could be causing a very wide range in performace for a SQL query from Azure Functions?

I have a WPF application that uses an API running on Azure Functions as the backend. The API is written in C# and uses EF to store and retrieve data from an Azure SQL Serverless database. Most of the time the application is very performant. However, users do notice significant delay from time to time. Using AppInsights logs and metrics I have identified that at least some of the problem lies in the fact that in a small but significant number of cases a process that usually takes less than 2 milliseconds and 95% of the time takes less than 170 milliseconds can sometimes take over 15 seconds. In this example case the process is just a simple get entity request where I am retrieving and entity by id. For all these cases the query is identical except for the id may change. And this process is just the time from when the Azure Functions C# code executes a query and gets a response. So this should be just the Azure Functions server requesting data from the Azure SQL server and getting the results. Why would this sometimes take over 15 seconds and how can I fix this.

I have a support ticket open with Azure SQL support but so far they don't have any answers. So, I thought I would ask around.

117502-image.png


azure-sql-databaseazure-functions
image.png (16.7 KiB)
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.

1 Answer

KalyanChanumolu-MSFT avatar image
0 Votes"
KalyanChanumolu-MSFT answered AHawes commented

@AHawes Thank you for reaching out.

You mentioned that you are using Azure SQL Serverless database.

When there are no requests to an Azure SQL Serverless database for an extended period, the database is put into a paused state to save cost. More details are here
The first request to a database in a "paused state" will "resume" the database first and then perform a query operation. This usually results in an increased response time.
I suspect this to be the issue in your case.

To check the Auto-Pause configuration, please navigate to the Overview blade for your database on the Azure portal.
117759-image.png

Serverless database is a great choice for burst workloads where you can predict the utilization patterns and can afford the latency in resuming from a paused state.
Otherwise, you should switch to a provisioned pricing tier instead.

Please let us know if you have any further questions.


If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.


image.png (34.9 KiB)
· 7
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.

@KalyanChanumolu-MSFT Thanks. Yes, I was aware of the auto-pause setting and have had it disabled. The reason I went with serverless SQL was to get the benefit of only paying for what was being used. And while turning off the auto-pause does take away some of that savings we still might save because the number vCores can fluctuate between high and low utilization periods.

Also, the long running queries don't just happen when users first start using the app. They occur throughout the day with no apparent pattern to them.

Is there any other reason why the serverless option would be less performant (or inconsistently performant) than provisioned?

Why would a query take 2 milliseconds one time and 15 seconds the next?

0 Votes 0 ·

@AHawes If you have disabled Auto-Pause, you are technically not using the capabilities of the serverless tier and should move to a provisioned tier.
Because Memory for serverless databases is reclaimed more frequently than for provisioned compute databases.
Also, when there is a spike in the number of connections or demand for more compute, a serverless database could occasionally take a few minutes to provision the additional cores.

I would recommend you change the pricing tier, and you should see the problem go away.
Please let us know if you face any issues.


If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.


0 Votes 0 ·
AHawes avatar image AHawes KalyanChanumolu-MSFT ·

I guess I expected that the serverless option would gracefully add more vCores such that there would not be any interruption. Can you point me to anywhere in the docs where it says this? I understood that there would be a delay in spinning up the server from auto-pause but not what you have explained.

Also, here is a graph of our CPU usage from a few days ago. I expanded the MAX vCores from 2 to 6 at the suggestion of someone else at Microsoft as a test. But as you can see, our maximum CPU utilization seems to always stay below one core. Therefore, I would not expect that the variability we are see in query times has anything to do with provisioning additional cores.

118219-cpu-used.png

I want to believe you but I don't want to go through the work of switching to provisioned only to find that it is not the solution and at this point I am not convinced that switching to provisioned will solve the problem.


0 Votes 0 ·
cpu-used.png (22.7 KiB)
Show more comments