SQL Stored Proc eats memory over time

I've been trying to diagnosis this 30 MB memory leak in this webservice. It has some bulk gets from SQL using standard stored procs. Each "bulk" returns around 10,000-50,000 rows. The webservice generates its report and sends it to the caller.

It seems everytime you call for this report 30 MB of memory gets "eaten" and not released. So if you hit the same call about 1,000 times the 3 GB system runs out of memory. If you recycle the app pool the mem is released. You can sit there for a day and the mem will not be released. So overtime the webserver runs out of memory.

I steped through the code, anaylysised hte heck out of it, and finally determined it was these 3 "bulk"开发者_如何学Python gets that get a moderate amount of data. Each takes out 10 MB and doesn't give it back after they're done. The stored procs are fine. The only odd thing about the calls is that it uses LINQ to call the stored procs and translate the data into Poco objects, using an XML map file.

I tried changing it so they use a new DataContext every single time the stored proc is called and then set the DataContext to null. I tried setting all sort of objects to null and then calling GC.Collect and the mem is still being eaten and not released until you recycle the app pool.

I would appreciate any leads. I suspect this has something to do with LINQ and large data returns.

---

That's the ugly side of having garbage collection: developers tend to think they do not need worry about freeing memory anymore. But that's not entirely true!

Some class instances have to be Disposed (as it's called in .Net). In fact, every object that implements IDisposable has to have its memory cycle handled by the developer.

Streams are a good example. Streams are disposable AND they usually carry a larger amount of data (larger than a simple string). Imagine a service where every 5 seconds you handle a 2KB stream. That's not much right? It's, say, a small XML. But what happens if you do not dispose it? Well, you can do the math yourself, of course, but I'll give you the result: ~33.7 MB of unused junk.

Bottom line: get disposed of your disposables.