SqlClient high memory consumption when there is disk IO saturation

[DavidObando]

Repro environment

• Windows 10
• LocalDb
• npm
• loadtest: npm install -g loadtest
• dotnet 1.0.0-preview2-002823

Scenario description

Stress test for ASP.NET and Entity Framework. The code is here. There's a server side and a client side to the test.
For the server side, execute the app with dotnet run -c Release.
For the client side, execute .\loadtest.ps1 -iterations 50000 -rps 500
The -rps 500 part is important, as the issue only manifests itself when disk IO is saturated.

Observed results

Once the test starts running we're reaching disk IO saturation almost immediately. The memory increases dramatically, up to 1 GB in private bytes when loadtest is run with the parameters stated above. After the test is up, we force GC collection once and take some measurements of the managed heap:

• Gen 0: 49,440 bytes
• Gen 1: 384,344 bytes
• Gen 2: 5,178,176 bytes
• LOH: 6,375,520 bytes

Totalling roughly 12 MB.

That said, the application is currently taking 959 MB in total committed memory. I'm assuming then that there's native memory being held.

A profile trace of the execution shows that most of the time is being spent in ReadSniSyncOverAsync.

Expected results

We expect the memory to not grow as much as it's doing now.

Notes

After forcing GC collection a number of times, we end up bringing the committed memory down to 515 MB and it stabilizes there. The managed heap shrinks from 12 MB to 10 MB.

[DavidObando]

/cc @divega @rynowak @mnltejaswini @AndriySvyryd

[rynowak]

The most interesting part of this is that we're seeing really significant time spent in ReadSniSyncOverAsync inside a call to DbTransaction.Commit done by EntityFramework.

CPU usage of process is very very low (~2% cpu) and the number of threads grows to 50-58.

Our suspicion is that we're blocking thread pool threads doing synchronous work inside the call to Commit(). It looks like a few operations in System.Data.Common don't have async alternatives:

• Close()
• BeginTransaction()
• Commit()
• Rollback()

[YoungGah]

We will investigate the issue but meantime, we will move it to 1.1.0.

[divega]

@DavidObando do you remember if this was measured with server GC enabled?

[DavidObando]

@divega yes, as indicated here.

[divega]

@DavidObando thanks!

[saurabh500]

@DavidObando @divega
I need some help running these benchmarks

I cloned the repo https://github.com/aspnet/Performance and tried building it using build.cmd (after installing the npm dependencies)

I got an error message
Errors in E:\work\gitclone\aspnet\Performance\test\TestApp.Test\project.json
Project BasicApi is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0). Project BasicApi supports:
- net451 (.NETFramework,Version=v4.5.1)
- netcoreapp1.1 (.NETCoreApp,Version=v1.1)
Project BasicViews is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0). Project BasicViews supports:
- net451 (.NETFramework,Version=v4.5.1)
- netcoreapp1.1 (.NETCoreApp,Version=v1.1)
Project BigModelBinding is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0). Project BigModelBinding supports:
- net451 (.NETFramework,Version=v4.5.1)
- netcoreapp1.1 (.NETCoreApp,Version=v1.1)
One or more projects are incompatible with .NETCoreApp,Version=v1.0.fail: Exit code 1 from dotnet
fail: Stack trace:
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.b__a8() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 9479
at Sake.Engine.Builder.BuilderBase1.CallTarget(String name) at Sake.Engine.Builder.BuilderBase1.StartingTarget(String name)
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.StartingTarget(String name) in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 3595
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.b__6a() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 4162
at Sake.Engine.Builder.BuilderBase1.CallTarget(String name) at Sake.Engine.Builder.BuilderBase1.StartingTarget(String name)
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.StartingTarget(String name) in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 3595
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.b__6b() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 4179
at Sake.Engine.Builder.BuilderBase1.CallTarget(String name) at Sake.Engine.Builder.BuilderBase1.StartingTarget(String name)
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.StartingTarget(String name) in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 3595
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.b__6c() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 4196
at Sake.Engine.Builder.BuilderBase1.CallTarget(String name) at Sake.Engine.Builder.BuilderBase1.StartingTarget(String name)
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.StartingTarget(String name) in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 3595
at Viewb27f3bb794ce4a7c8824bcb2f40ba585.b__6d() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 4213
at Sake.Engine.Builder.BuilderBase1.CallTarget(String name) at Viewb27f3bb794ce4a7c8824bcb2f40ba585.<RenderViewLevel0>b__65() in c:\Users\username\AppData\Local\Temp\7a5dee3125d14f64bbe127da83313eb7-1.cs:line 4069 at Sake.Engine.Builder.BuilderBase1.CallTarget(String name)
at Sake.Engine.SakeEngine.Execute(Options options)
at Sake.Engine.SakeEngine.Execute(String[] args)
at Sake.Program.Main(String[] args)

[rynowak]

@saurabh500 - you dont' need to run build.cmd. Just go to the site directory and dotnet restore dotnet run -c Release

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.