Open Steve887 opened 2 weeks ago
Before taking a look at your repro, it's well-known that SqlClient has some severe memory/performance issues with reading large binary columns asynchronously (https://github.com/dotnet/SqlClient/issues/593), so that could explain the memory behavior when adding your "image" property. This should be easily verifiable by switching to sync I/O (SaveChanges() instead of SaveChangesAsync()) as a test - can you please do that?
Before taking a look at your repro, it's well-known that SqlClient has some severe memory/performance issues with reading large binary columns asynchronously (dotnet/SqlClient#593), so that could explain the memory behavior when adding your "image" property. This should be easily verifiable by switching to sync I/O (SaveChanges() instead of SaveChangesAsync()) as a test - can you please do that?
It's not a binary column, it's just a 50 character string. It also doesn't have any data in it.
@Steve887 Do you see the same behavior if you change the query to be no-tracking? For example:
return await _context.Set<VisitView>()
.Include(x => x.ConsultationViews).ThenInclude(x => x.ConsultationItems).ThenInclude(x => x.Item)
.AsNoTracking()
.FirstOrDefaultAsync(p => p.VisitNumber == key);
@ajcvickers Hi, tried adding this and memory behaviour is similar. In fact total memory is actually higher.
@ajcvickers is there any more information I can provide for this one?
try using splitquery , might works
return await _context.Set
@satviktechie1986 Setting AsSplitQuery
does result in normal memory usage. However, in our actual app this isn't really a good solution as we have many queries across the app, and we don't want the dramatic increase in network calls enabling this setting globally would result in.
I would still like to find out why the original query has such a dramatic difference by simply including one extra property, so I can take those findings and implement in our main application.
alternative you can use
return await _context.Set
@satviktechie1986 again, this wouldn't work for our actual app as we have too many queries to realistically select just the required columns
I have run into a strange issue with our app that results in strange memory usage: spiky memory loads and increasing memory usage over time, when there is a large number of properties in the model being selected. What's particularly strange, if I comment out just one property, the memory issues no longer occur and memory usage is extremely consistent.
A requirement of our system is to change connection strings at runtime, so instead of using
Services.AddDbContext
, I am registering the data context with Autofac and passing in a connection string at runtime, then using an overriddenOnConfiguring
to setup the SqlServer provider. If I change this to useServices.AddDbContext
then memory does return the normal, but I'm confused why this would only seem to have an effect at a large number of properties.The rest of my setup is very normal, with a new entities being selected with
Includes
.I have attached an app that reproduces the issue, the steps are as follows:
(localdb)\MSSQLLocalDB
database, so change this for SqlExpress or other server etc inProgram.cs
.StressTestApi.ps1
file. This will execute an API call against the endpoint constantly to simulate a load.Running the application as is, results in a memory graph as follows:
Open
Item.cs
and comment out theItemImage
property. Then openImageMap.cs
and comment out theItemImage
property mapping. Start the application again, attach the memory profiler and rerun the powershell script. This results in the following memory graph:The big differences seem to be in the gen 1 and 2 heaps, although taking memory snapshots doesn't really reveal anything obvious. The total memory also grows over time when the column is there.
While it's easy enough to say, just decrease the model size, I am working with a large, legacy, model and cannot make big changes like that. I would also like the know the underlying reason why the memory behaviour changes so drastically just by changing one column. I would expect if the setup is wrong for it to happen all the time.
This also occurs in .Net 7 and EFCore 7 versions, on windows and linux.
Please let me know if there's any more information to supply.
MemoryTest.zip
Include provider and version information
EF Core version: 8.0.8 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .Net 8 Operating system: Windows