Diagnosing ASP.NET Core SQL Azure sporadic performance

Question

I have an ASP.NET Core 6 application using EF Core and SQL Azure 'Business Critical' pricing tier with 2 VCPUs. 90% of the time the application is lightning fast, but occasionally throughout the day, it will get into a state where the requests are very slow...10-15 seconds. This sluggishness lasts for 1-2 mins then everything is back to normal.

I have lots of logging installed and so far I have not been able to pinpoint the problem. I use Serilog and SEQ to find long running requests and queries and my research as been unfruitful. When I see a long running request, I look at other requests around that time period to see whats going on and nothing obvious jumps out.

Web server CPU, database CPU are never anywhere close to pegged when this problem occurs (or ever). When the problem occurs, there also doesn't appear to be any blocking. I have a script that I can run to identify blocking queries and it doesn't return any result.

It definitely feels like a database problem though, because when the problem occurs I can hit endpoints with no database access and they return very fast...under 100ms. When I hit an endpoint with database calls, they hang until the "problem" is over, and then everything responds sub 100ms again. It doesn't seem to matter what table is being accessed so there isn't any locking going on that would affect this.

Azure Web App (2 instances Premium v2) Azure SQL Database: Business Critical tier with 2 VCPU

I would love some ideas of where to look or where to add some logging.

UPDATE: SEQ logs show that during the request pipeline, the database calls are definitely hanging, but it doesn't appear to be happening on the database side, it appears to be happening on the webserver itself.

When the problem occurs, I point my local dev environment at the production SQL server, it still handles requests instantly. It now feels like it is something on the WebServer but only as it relates to SQL calls. If the endpoint has no SQL calls, it responds instantly. Could there be an issue with EF core leaving connections open to the DB?

Answer 1

Make sure the issue is not associated with the Azure SQL automated backups and their impact in performance while they occur. The following query can help you correlate that. The query return dates in UTC time.

Select * from sys.dm_database_backups ORDER BY backup_finish_date DESC 

In addition, outdated statistics may be causing high compilation times of T-SQL statements impacting then the performance. Please set asynchronous statistics update on using below ALTER statement. In addition, use Ola Hallengren scripts to defragment indexes and update statistics daily.

ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON