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

8
  • 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! Commented Oct 9, 2019 at 4: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. Commented Oct 9, 2019 at 4:48
  • sqlCmd.Parameters.Add(new SqlParameter("id", ID)); Tricky way to do .AddWithValue but don't. See dbdelta.com/addwithvalue-is-evil and blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… and another one: dba.stackexchange.com/questions/195937/… Here is another andrevdm.blogspot.com/2010/12/… Commented Oct 9, 2019 at 6:51
  • Follow up on @BagusTesa comment. Send an array or list of the ID's and run your loop in the method. Commented Oct 9, 2019 at 6: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? Commented Oct 9, 2019 at 6:55

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

3 Comments

My problem is the memory is not releasing back without explicitly calling GC.Collect(). Because of this the server becomes slow.
This part of your code does not seem to have a problem, but you can see common things that can cause this issue. michaelscodingspot.com/ways-to-cause-memory-leaks-in-dotnet might be useful.
You can investigate your program by some tools such as .Net memory from JetBrains. stackoverflow.com/questions/12474321/… would be useful too.
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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.