Remove the need to use MARS for SQL Server

[emleder]

We have 120+ sites hosted in Azure. Most of the time they run fine but they've been crashing and looking into it, there are database queries that sometimes take sub-second to run but otherwise can take almost 3 mins before it comes back. The site will crash when it does. And when it does, will sometimes also crash other apps within the same App Service. Feel free to directly contact me for more detailed info or if you want to walk through this over chat/zoom/etc.

[tidyui]

Hi there! We also run a lot of sites in Azure, and given the error description have you double checked that your Database Server isn’t running out of DTU’s at the same time as these errors occur, this is something that could happen if you run a lot of databases on the same server. The same goes for the App Service Plan, but this sounds more like a database performance issue.

Regarding the length of the queries this is simply how EF formats and creates them. In 9.0 we’re using a new feature in EF 5.0 where you split a query in multiple small ones instead of these large joined queries for better performance, but this is a completely new feature released with .net 5.

Please start by double checking DTU usage on all Azure instances before we proceed as it’s not logical that it’s code related when the same query performs badly every once in a while.

Best regards

Håkan

[emleder]

Thanks, I'll look into it. But question for you on Azure configuration. How many sites do you run per App Service Plan? How many databases per SQL server? Trying to strike a balance between performance and costs.

[firedog]

I don't know if this applicable to the Azure environment...but I have experienced the same problems on a linux server (VPS) with SQL Server installed and Piranha sites running on the same server. I get sudden slowdowns for even the simplest queries and eventually the site will crash and require a restart.

There is an issue with MARS for the combo linux + SQL Server (dotnet/SqlClient#422). Maybe this could have something to do with it as Piranha requires MARS in the connection string when SQL Server is used.

I guess it should be possible to modify Piranha so that MARS wouldn't be required (or possibly support configurations both with MARS and without)?

[tidyui]

@emleder We run about 25 web apps in the same plan & sql server. We could probably go higher as it’s cheaper to scale up than creating a new plan. It is however crucial that you use blob storage for the assets in all of the apps, otherwise your app service plan will run out of disc storage.

[tidyui]

@firedog Good points. I’ll investigate what needs to be done to the application to eliminate MARS. We will also look into integrating the new connection pool functionality from EF Core 3.1 in the upcoming version. https://www.google.se/amp/s/neelbhatt.com/2018/02/27/use-dbcontextpooling-to-improve-the-performance-net-core-2-1-feature/amp/

[tidyui]

I see now the functionality was actually introduced in 2.1, but I totally missed its existence 😁

[tidyui]

@emleder Of course this all depends on what service level you have for your App Service Plan 😁

[tidyui]

I'll go ahead and change the title of this issue and then create another one since it's really a combination of two things.

[tidyui]

All integration test have been verified and are running successful on SqlServer with MultipleActiveResultsets=false.