System.Data.Odbc Memory Management and AccessViolationException on DataAdapter.Fill

Question

I've written a server application in C# .NET framework 4.5. It goes into a database every 30-120 seconds to fetch updates on necessary data using the tools defined in the System.Data.Odbc namespace. The data is stored in a List<> of a container class I've created to store all the required data. The List is Xml Serialized and sent to connected clients with TCP. The application typically runs for 5-6 hours before it's brought to screeching halt by an AccessViolationException. The exception seems to be thrown when called OdbcDataAdapter.Fill(). Continuing on, the main problem I've been faced with is that my data collection function causes the working set of my application to increase by about 4 megabytes every time it runs, and at times it can run 3 times in 2 minutes. The data collection process is hectic but here it is in a nutshell.

EDIT: I've recently profiled the application using Scitech Memory Profiler. It turns out that the number of managed bytes only temporarily grows to about 1MB and then resets to about 400KB. So contrary to what I initially thought there may be no memory leaks in my application. Yet the process working set size still increases rapidly, the explanation for this eludes me. I have put a breakpoint at the AccessViolationException and hopefully taking a memory snapshot with this profiler will reveal the cause.

For starters, here's what my container class looks like.

public class Alert
{
    public enum OrderType
    {
        ...
    }
    public enum AlertType
    {
        ...
    }
    //All the members of these structs are managed
    public struct Unreleased
    {
        ...
    }
    public struct AlloData
    {
        ...
    }
    public AlloData AllocationData { get; set; }
    public Unreleased UnreleasedData { get; set; }
    public string OrderNO { get; set; }
    public string PickNO { get; set; }
    public OrderType Type { get; set; }
    public AlertType Code { get; set; }
    public string Customer { get; set; }
    public Int64 ElapsedSeconds { get; set; }
    public BackOrderData BackOrderData { get; set; }
}

Here's the function which fetches the data

    private static XmlSerializer XMLS = new XmlSerializer(typeof(List<Alert>))
    [System.Runtime.ExceptionServices.HandleProcessCorruptedStateExceptions()]
    public Byte[] getAlerts()
    {
        try
        {  
            OdbcConnection Conn = new OdbcConnection(SB.ConnectionString);
            Conn.Open();
            List<Alert> Alerts = new List<Alert>();
            String Query = "...";
            var cmd = new OdbcCommand(Query, Conn);
            int cnt = Convert.ToInt32(cmd.ExecuteScalar());
        //After I'm done with OdbcCommand/Data Adapter instances I dispose and null them
            cmd.Dispose(); cmd = null;
            Query = "...";
            cmd = new OdbcCommand(Query, Conn); 
            cnt += Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Dispose(); cmd = null;
            ... 
            var DT = new DataTable();
            var DA = new OdbcDataAdapter(Query, Conn);
            DA.Fill(DT);
            DA.Dispose(); DA = null;
            ... //A ton of data collection etc..
            foreach (DataRow DR in DT.Rows)
            {
                var Alert = new Alert();
                ... //Data Collection
                Alerts.Add(Alert);
            }
            DT.Dispose(); DT = null;
            ... //More
            byte[] bytes = null; 
            MemoryStream MS = new MemoryStream();
            XMLS.Serialize(MS, Alerts);
            bytes = MS.ToArray();
            MS.Dispose(); MS = null;
            Alerts = null; 
            Conn.Close();
            Conn.Dispose();             
            Conn = null;
            return bytes;
         catch(Exception ex) {
             ...
         }
     }   

I'm not 100% sure what causes this memory growth. I have made sure to dispose all unmanaged resources and I make a call to GC.Collect after each run. To inhibit the ram growth I call the windows API function SetProcessWorkingSetSize

    [DllImport("kernel32.dll")]
    public static extern bool SetProcessWorkingSetSize(IntPtr proc, int min, int max);

    //... After each data collection
        GC.Collect();
        GC.WaitForPendingFinalizers();
        SetProcessWorkingSetSize(Process.GetCurrentProcess().Handle, -1, -1);

I think if anything has the chance to cause an AccessViolationException it is this behaviour, however this is the only way I have found to prevent an OutOfMemoryException occurring after an hour of runtime.

Here's the exception:

System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at System.Data.Common.UnsafeNativeMethods.SQLExecDirectW(OdbcStatementHandle StatementHandle, String StatementText, Int32 TextLength)
at System.Data.Odbc.OdbcStatementHandle.ExecuteDirect(String commandText)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PickWatchServer.DBSearch.getAlerts()

Any advice/help on this matter would be greatly appreciated, so thanks in advance! If there is any other code/data that you would like to see just ask.

Answer 1

It may be that you are facing fragmentation problems on the Large Object Heap (other interesting question on SO: Large Arrays, and LOH Fragmentation. What is the accepted convention?, Large Object Heap Fragmentation). The problem is known, and some workarounds have been provided in .NET fx 4.5 (e.g. http://dailydotnettips.com/2013/08/26/largeobjectheapcomapaction-in-net-garbage-collection/).

What is commonly done to reduce the impact (or completely remove it) is use pre-allocated object pools, especially in multi-threaded applications. You pre-allocate arrays of a sufficient size, and keep a size variable you increment to effectively track the size of the used part of your array.

If your code is single-threaded I may suggest you replace your List<Alert> with Alert[], with a sufficient size, and to keep it static. Add an int variable to keep count of the used elements in the array, e.g.

//Somewhere in the class
static Alert[] alertList = new Alert[4096];
static int alertListSize = 0;

//In your method
alertListSize = 0;
foreach (DataRow DR in DT.Rows)
{
    var Alert = new Alert();
    ... //Data Collection
    //REMOVED Alerts.Add(Alert);
    alertList[alertListSize++] = Alert;
}

Note that you will not be able to use the XmlSerializer on this array, because it would serialize all of the elements (including any null ones), so you will have to use something like an XmlWriter. Since you happen to write only a series of elements, the transition should not be hard (although out of scope for this answer).

You may do the same with a fixed-size byte[] to use in the MemoryStream, you can pass it to the constructor. Remember to rewind the MemoryStream to the start of the buffer with Seek() after initialization, so the XmlSerializer will overwrite anything in the buffer from the beginning.

Also, for further help, please convert your code with using statements, it makes it clearer for everybody to understand (and be sure there are no errors in calling Dispose() methods).

The LOH Fragmentation problem is one of the likely candidates for apparently-unexplainable out-of-memory errors in long-running C# programs handling serialization (big objects), but may not be the only one; further investigation may be needed.