0

I have created a simple data fetch method which fetches data from a SQL Server database.

Each time I fetch data by calling this method the memory grows (application pool memory). When I explicitly call GC.Collect() after fetching the data, the memory is half of what it was without GC.Collect().

I know it is bad practice to call GC.Collect(), but without calling I am struggling to reduce memory.

private void DataPopulate(int ID)
{
    using (SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
    {
        sqlCon.Open();

        if (sqlCon.State == ConnectionState.Open)
        {
            using (SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("engine_connection_sendername_get", sqlCon) { CommandType = CommandType.StoredProcedure })
            {
                sqlCmd.Parameters.Add(new SqlParameter("id", ID));

                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.SelectCommand = sqlCmd;
                        sqlCmd.CommandTimeout = 300;
                        da.Fill(dt);
                    }
                }
            }
        }

        GC.Collect();
    }
}

I am using the above test method to just check the memory issue. I created a sample .aspx page which only has a button in it . When I click the button this method is called. The Datatable is filled with data from the SQL Server database - that's all.

And I check the memory in application pool. Below is the summary of each time I click the button and the corresponding memory with and without using GC.Collect()

[MemoryUsage_AfterEachClick

Alex
  • 4,885
  • 3
  • 19
  • 39
Sam
  • 1
  • 5
  • Please paste the console output into your question instead of a link to a screenshot. It is more useful to those that wish to help. Thanks! – Gene Z. Ragan Oct 09 '19 at 04:43
  • 1
    is `private void DataPopulate(int ID)` called in a loop? if so, please dont open and close the `SqlConnection` in a single execution, keep it around until your operation completed. – Bagus Tesa Oct 09 '19 at 04:48
  • `sqlCmd.Parameters.Add(new SqlParameter("id", ID));` Tricky way to do .AddWithValue but don't. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html – Mary Oct 09 '19 at 06:51
  • Follow up on @BagusTesa comment. Send an array or list of the ID's and run your loop in the method. – Mary Oct 09 '19 at 06:53
  • Although I don't know what you stored procedure does, I see you get a DataTable but you dispose it immediately. What's the point of retrieving data and throwing it away? – Mary Oct 09 '19 at 06:55
  • There are numerous questions on SO (https://stackoverflow.com/questions/18448894/datatable-memory-huge-consumption , https://stackoverflow.com/questions/45490237/asp-net-memory-leak-high-memory-using-datatable ) and internet in general (https://bytes.com/topic/visual-basic-net/answers/367474-releasing-memory-datatable) about DataTable high memory consumption. Have you looked at them? – Alex Oct 09 '19 at 07:34
  • Possible duplicate of [DataTable memory huge consumption](https://stackoverflow.com/questions/18448894/datatable-memory-huge-consumption) – Alex Oct 09 '19 at 07:35
  • Thanks everyone for the replies. My actual problem is the memory is not released back without explicitly calling GC.Collect(). Because of this the server becomes slow and stuck. Based on my knowledge I expected the memory to be released after some time, but it is not happening. – Sam Oct 09 '19 at 08:37

2 Answers2

4

GC.Collect() forces an immediate garbage collection of all generations. so if you wait a while, or when the application is running out of memory, GC will call automatically. So, in my opinion, it seems not to be a memory leak.

Nabi Sobhi
  • 369
  • 4
  • 16
  • My problem is the memory is not releasing back without explicitly calling GC.Collect(). Because of this the server becomes slow. – Sam Oct 09 '19 at 08:34
  • 1
    This part of your code does not seem to have a problem, but you can see common things that can cause this issue. https://michaelscodingspot.com/ways-to-cause-memory-leaks-in-dotnet/ might be useful. – Nabi Sobhi Oct 09 '19 at 08:51
  • 1
    You can investigate your program by some tools such as .Net memory from JetBrains. https://stackoverflow.com/questions/12474321/finding-memory-leaks-in-c-sharp-application would be useful too. – Nabi Sobhi Oct 09 '19 at 08:52
0

Here is problem,

When finish this method, we just de-reference the DataTable object, not delete it.

And also check GC.GetGeneration(dt), if this object promote to Generation 1 or 2 (It will be the performance issue)

But i guess, GC will do its job, since there is no reference root on your DataTable,

so GC just enlarge Generation 0 size, when next generation 0 GC comes in, it will clear these DataTables.

Max CHien
  • 133
  • 1
  • 8