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

Andrew Hawes 181

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.

1 answer

KalyanChanumolu-MSFT 8,331 Reputation points

2021-07-26T04:40:44.91+00:00

@Andrew Hawes 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.

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.
Please sign in to rate this answer.
Andrew Hawes 181 Reputation points

2021-07-26T13:01:25.767+00:00

@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?

KalyanChanumolu-MSFT 8,331 Reputation points

2021-07-27T10:20:42.6+00:00

@Andrew Hawes 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.

Andrew Hawes 181 Reputation points

2021-07-27T13:01:06.003+00:00

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.

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.

KalyanChanumolu-MSFT 8,331 Reputation points

2021-07-28T04:35:17.303+00:00

@Andrew Hawes The scaling and memory management behaviors of serverless are mentioned here

----------

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

Andrew Hawes 181 Reputation points

2021-07-28T13:15:13.933+00:00

Yes, I read that information before choosing serverless but I guess I understood it to having less impact than what I am seeing. Also, that does not seem to explain what we are seeing.

From what I see in my case, the number of vCores should not be changing so there should be no impact from autoscaling.

Also, as you can see in this new chart, memory never seems to go above 25% so memory pressures would not seem to come into play.

Cache reclamation could account for why we aren't getting optimal performance but should not factor into why a basic SELECT query takes 15 seconds.

KalyanChanumolu-MSFT 8,331 Reputation points

2021-07-30T11:46:09.78+00:00

@Andrew Hawes Do you have Automatic tuning turned on? Do you have any index recommendations that could be applied?

Unfortunately, we can't troubleshoot this remotely without looking at your environment.
I would recommend switching the pricing tier and checking if it resolves the issue. Otherwise please reach out to Azure Support so that an engineer can work with you and help you resolve the issue.

Andrew Hawes 181 Reputation points

2021-07-30T13:56:53.587+00:00

Auto-tuning is enabled for Force Plan but not the indexes. I did not enable for Index because I was unsure if it would interfere with the EF Code First setup in the application.

There are no index recommendations to apply.

I have been working with Azure Support engineers for over a month now and have made little to no progress. That's why I started this thread. I really need this issue escalated but it isn't happening.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

1 answer

Your answer