SQL Azure: Bad query performance despite being well under DTU limit?

Question

I have a web application running in an Azure App Service, which is connected to a SQL Azure database at 50 DTUs in the standard tier. I'm using EF Core 7 in my ASP.NET Core 7 app. Traffic to my site is very spikey. I've noticed that when traffic spikes, DB queries across the board become very slow. For context: I show that during this peek, I only used 6% of my 50 DTU limit.

Despite this, even what seem to be very simple queries begin to crawl. I have several queries but to give a specific example: This is firing right before a traffic spike, and has a very quick execution time of 10ms.

Executed DbCommand (10ms) [Parameters=[@__userId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [a0].[Name]
FROM [AspNetUserRoles] AS [a]
INNER JOIN [AspNetRoles] AS [a0] ON [a].[RoleId] = [a0].[Id]
WHERE [a].[UserId] = @__userId_0

Then, two minutes into the traffic spike, here's the same query again, taking 18 seconds:

Executed DbCommand (18,088ms) [Parameters=[@__userId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [a0].[Name]
FROM [AspNetUserRoles] AS [a]
INNER JOIN [AspNetRoles] AS [a0] ON [a].[RoleId] = [a0].[Id]
WHERE [a].[UserId] = @__userId_0

(This is one of the slowest instances - most are around 3 seconds, still strangely slow.) I suspected that my database was underpowered. But it's unclear to me why I'm not going higher than 6% of my allocated DTUs if this is the case.

Lots of the research I've found has been in cases where the DTU consumption is high but that isn't the case. I'm looking for help to understand what type of resource starvation could be causing this drastic performance hit if I'm still well under my DTU limit.

other stats:

Thanks...

Answer 1

The best way to check the Performance of your SQL is to visit the Diagnose and Solve Problems section of your Azure SQL Database and get insights into performance issues:-

You can refer my SO thread answer for detailed steps to check and improve Azure SQL performance.

Select your Azure SQL database > Diagnose and solve problems > Performance and Query execution > Index maintenance and statistics:-

I have selected Index maintenance and statistics and received one query to reduce the long running query output:-

You can also use Intelligent Performance to tune your queries refer below:-

You can also use Azure Advisor to check if you are using correct DTU size for your SQL to perform efficiently like below:-

In your Azure Web App, Try to check Diagnose and Solve Problems > Availability and Performance > Web App Slow for more issue:-