Menu
Recently I have run into some pretty weird performance issues with SQL Azure / Web Apps / Entity Framework.
It would appear that occasionally calls to the database (both read and write queries) would hang for anywhere between a few seconds and a few minutes (!). This happens even on a select query on a table with 4 columns containing 5 rows of data.
The issue appears to happen at random and is not reproducible. Upgrading DB to a higher performance tier seems not to have any effect. Both the web app and the sql azure database are in the same region.
DB performance graph is generally flat lining around 0.5% of resource utilization with an occasional spike to around 5% - so the issue certainly does not lie with the resource constraints.
I would have no clue how to start investigating the issue given the intermittent nature of it. I would greatly appreciate any feedback. Last week the issue
Could it have something to do with the way Entity Framework handles the DB connections specifically to the sql azure? Testing on the local SQL express has never caused anything similar.
472
There are three primary options for Automatic Tuning with Azure SQL Database: CREATE INDEX: Creates new indices that can improve the performance. DROP INDEX: Drops redundant and unused indices (>90 days) FORCE LAST GOOD PLAN: Identifies queries using the last known good execution plan.
EF Core 6.0 performance is now 70% faster on the industry-standard TechEmpower Fortunes benchmark, compared to 5.0. This is the full-stack perf improvement, including improvements in the benchmark code, the . NET runtime, etc. EF Core 6.0 itself is 31% faster executing queries.
If you tune queries in Azure SQL Database and Azure SQL Managed Instance, you might get the additional benefit of reducing aggregate resource demands. Your application might be able to run at a lower cost than an un-tuned equivalent because it can run at a lower compute size.
Thank you. APPLIES TO: Azure SQL Database Azure SQL Managed Instance When trying to resolve a performance bottleneck, start by determining whether the bottleneck is occurring while the query is in a running state or a waiting state. Different resolutions apply depending upon this determination.
Although Azure SQL Database and Azure SQL Managed Instance service tiers are designed to improve performance stability and predictability for an application, some best practices can help you tune your application to better take advantage of the resources at a compute size.
The sys.resource_stats DMV returns CPU usage and storage data for Azure SQL Database. The data is collected and aggregated in five-minute intervals. If you identify the problem as insufficient resource, you can upgrade resources to increase the capacity of your database to absorb the CPU requirements.
After battling performance issues with Entity Framework, we finally switched over to Dapper and saw a huge increase in performance. They have some benchmarks on their GitHub page showing the speed difference.
https://github.com/StackExchange/dapper-dot-net
Also, I am unsure what version of EF you are using, but if it is EF Core, the performance of that is currently worse than that of previous versions. Another comparison of performance can be found here: https://www.exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/.
155
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
