I am using the following pattern to update my SQL Server records using Linq to SQL:
List<int> allIds;
using (MyDataContext dc= new MyDataContext())
{
dc.CommandTimeout = 0;
allIds = dc.MyTables.Select(x => x.Id).ToList();
}
Parallel.ForEach(allIds, x => ComputeAndSave(x));
and
ComputeAndSave(int x, MyDataContext dc)
{
var myRecord= dc.MyTables.Select(x => x).Where(x => x.Id == id).FirstOrDefault();
myRecord.Total = myRecord.Total + 1; //some modification(s)
dc.SubmitChanges();
}
I use the same pattern in multiple parts of my application to update all rows in some tables. And in all these cases, memory used by SQL Server slowly increases and increases and increases. The only way I find to release all that memory is to stop and restart the SQL Server Service. Why is there a memory leak here and how can it be fixed?
Many thanks!