Creating a database connection for every database insert - High SQL server memory usage

Question

I have recently changed my web app to create a database connection per command instead of creating one connection and just reusing it for all commands. I used this code this afternoon and my database memory went up to 24GB usage peforming about 8k inserts. My code is like this (semi pseudo code):

public int ExecSQL(string SQLStr)
{
    using (SqlConnection Con = new SqlConnection(MyConStr))
    {
        using (SqlCommand Cmd = new SqlCommand(SQLStr, Con))
        {
            return Cmd.ExecuteNonQuery();
        }
    }
}

using (TransactionScope TX = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    //Loop and perform 8000 x
    int ID = ExecSQL("insert into something (column) output unique_id values ('data')").
    // I also perform 1 or 2 selects per insert based on the ID returned from the insert. I don't use a .Supress for my inserts.
}

Could this of caused the high database memory usage? I was under the impression it should create 100 connections (default) then just keep re-using it but I am guessing I am missing something.

Answered: Ran the following SQL:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

and there is only one open connection for my database so this isn't causing the issue. Now to find out what is ..

The connection pooling will re-use the connection, so I don't think this is a problem. Did anything else change? — Bryan Crosby
– Bryan Crosby, Commented Apr 4, 2012 at 18:17
I changed my code to use Datasets instead of SqlReader but I don't think that would do it as its database memory. I will test it again tomorrow. It could be that something else was causing it. I also changed my inserts to output unique_id, I don;t know if this adds lots of overhead ... — webnoob
– webnoob, Commented Apr 4, 2012 at 18:19
@beargle this is the best practice msdn.microsoft.com/en-us/library/ms254507.aspx — the_joric
– the_joric, Commented Apr 4, 2012 at 18:24
R u aware that TransactionScope creates by default serializable transactions? And also multiple connections opened from a single TransactionScope enroll in distributed transaction (two phase commit) ? — Remus Rusanu
– Remus Rusanu, Commented Apr 4, 2012 at 18:34

the_joric · Accepted Answer · 2012-04-04 18:12:34Z

1

ADO.NET uses connection pools, so multiple SqlConnection objects with the same connection strings reuse the same physical database connection. Hardly your memory increase was caused by using new SqlConnection()

answered Apr 4, 2012 at 18:12

the_joric

12.3k6 gold badges39 silver badges58 bronze badges

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

3 Comments

webnoob Over a year ago

so new SqlConnection won't be causing the problem?

the_joric Over a year ago

As far as I know -- it should not.

webnoob Over a year ago

From what I can see you are correct. The memory on the server now seems to be Ok so I am going to go out on a limb and blame someone else for their bad timing on using all the memory :)

Collectives™ on Stack Overflow

Creating a database connection for every database insert - High SQL server memory usage

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related