Poor performance after upgrading to EFCore9 - high amount of sessions with open transactions.

[rob-smartmoving]

Bug description

We have a .NET 9 project. We were upgrading our EFCore libraries from 8.0.7 to 9.0.8. The only other EF related package changes were to AspNetCore.HealthChecks (honestly, we barely use it due to too many false positives, and ended up removing most of it), moving from JamieEncryptColumn to EncryptColumn, and updating EF Plus.

The first sign we noticed was that our base response time went from ~35ms to ~50ms. As the day ramped up and our system got busier, the response time got into the seconds, and eventually things would time out.

We don't have anything special with our DBContext configuration. We have the compat level set to 120, because the EFCore8 change to how it handles constants tanked our performance (which can now be explicitly set with TranslateParameterizedCollectionsToConstants for EFCore9). Here's our entire options builder:

var builder = options.UseSqlServer(configuration.GetConnectionString(connectionStringName),
 x =>
 {
	 x.UseCompatibilityLevel(120);
	 if (!string.IsNullOrWhiteSpace(migrationsAssembly))
	 {
		 x.MigrationsAssembly(migrationsAssembly);
	 }

	 x.ExecutionStrategy(c => new CustomSqlServerRetryingExecutionStrategy(c));
	 x.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
 })
.EnableSensitiveDataLogging();

The retry strategy just inherits the one from EFCore and explicitly checks for some MARS errors, that's all. The builder and retry logic code hasn't changed since 2021 (other than adding the compat level). We were at EFCore 2.x at the time (and made a huge jump from 2 to 6 in 2022, and since then have updated every year). So these haven't been issues since 2.x.

Literally the only code change to get performance back to proper was removing "TranslateParameterizedCollectionsToConstants" and changing the EFCore packages in csproj files.

We have a very busy application, handling tens of thousands of requests a minute. Our DBAs were also monitoring things. We did seem to have some different queries showing up between the 2 versions. However, it's not like any specific queries were performing particularly poorly, or enough to bring the system down like it was.

The biggest issue though we believe was the data returned from this SQL query:

SELECT	 [s].[host_name]
		,COUNT(*) AS [SessionCount]
		,SUM( IIF( [open_transaction_count] > 0, 1, 0 ) ) AS [SessionWithOpenTranCount]
FROM	[sys].[dm_exec_sessions] AS [s]
WHERE	[security_id] != 0x01
GROUP BY [s].[host_name]
ORDER BY COUNT(*) DESC

After rolling back to EFCore8, that query has at most 10-15 open sessions for a given host. When EFCore9 was out, many hosts would slowly build, until they topped out at 125 (our max connection limit) after 10-15 minutes (most others would get there, just took more time).

Was something changed in how connections or transactions are handled by default? Is there some new setting / options we should be configuring? I've searched the breaking changes list, and docs, but I don't see anything new that we'd need to change there. And it only being really visible after high usage makes it difficult for us to reproduce anything in isolation (and obviously we don't want more fires in Production).

Your code

Here is what our builder looked like with EFCore9 (we tried both with and without the compat level):

var builder = options.UseSqlServer(configuration.GetConnectionString(connectionStringName),
 x =>
 {
	 x.UseCompatibilityLevel(120);
	 if (!string.IsNullOrWhiteSpace(migrationsAssembly))
	 {
		 x.MigrationsAssembly(migrationsAssembly);
	 }

	 x.ExecutionStrategy(c => new CustomSqlServerRetryingExecutionStrategy(c));
	 x.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
	 x.TranslateParameterizedCollectionsToConstants();
 })
.EnableSensitiveDataLogging();


Here's the package references we were using. These were compiled across 8 projects, so some references are just inherited.


        <PackageReference Include="AspNetCore.HealthChecks.SqlServer" Version="9.0.0" />
        <PackageReference Include="AspNetCore.HealthChecks.UI" Version="9.0.0" />
        <PackageReference Include="AspNetCore.HealthChecks.UI.Client" Version="9.0.0" />
        <PackageReference Include="AspNetCore.HealthChecks.UI.InMemory.Storage" Version="9.0.0" />
		
        <PackageReference Include="EntityFrameworkCore.EncryptColumn" Version="9.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.8">
        <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="9.0.8" />
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.8" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.8" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="9.0.8">
		
        <PackageReference Include="Z.EntityFramework.Plus.EFCore" Version="9.103.9.2" />

Stack traces

Verbose output

EF Core version

9.0.8

Database provider

No response

Target framework

.NET 9.0

Operating system

No response

IDE

No response