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

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 ..

2012-04-04 18:08
by webnoob

The connection pooling will re-use the connection, so I don't think this is a problem. Did anything else change - Bryan Crosby 2012-04-04 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 2012-04-04 18:19

Why create a separate connection for each insert - Bryan 2012-04-04 18:20

@beargle this is the best practice http://msdn.microsoft.com/en-us/library/ms254507.asp - the_joric 2012-04-04 18:24

@the_joric I don't see anything in that page advising separate connections for each SqlCommand...what am I missing - Bryan 2012-04-04 18:28

OK, I missed the part about 8000 inserts being a 'test'...I assumed this was a normal app function and these should be batched - Bryan 2012-04-04 18:33

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 2012-04-04 18:34

@beargle I think that if commands are executed in a loop then single connection would be better, so you are correct :) - the_joric 2012-04-04 18:35

@RemusRusanu even if those connections are made for a single SQL server - the_joric 2012-04-04 18:37

Can you explain a little more please Remus? I am getting rather confused, should I be using 1 connection and sharing it between all the commands here or creating 1 connection per command - webnoob 2012-04-04 19:29

@the_joric: even then. There is no need for an external coordinator in such case, but is still a distributed transaction - Remus Rusanu 2012-04-05 00:34

@webnoob: you should avoid using more than one connection in a transaction scope at all cost. You should avoid closing and opening connections unnecessarily (even when pooled, closing and opening has a cost) - Remus Rusanu 2012-04-05 00:36

@the_joric: for a longer post discussing this topic see http://stackoverflow.com/a/1693270/10592 - Remus Rusanu 2012-04-05 00:48

@Remus - So I guess if I had a overloaded ExecSQL that allowed me to pass a SqlConnection param I could decide beforehand if I wanted to use a shared connection on the "bunch" of queries being performed but by default it will .Open() and .Close() each one - webnoob 2012-04-05 05:53

@Remus I am also using the code as suggested in the link to cretae my TransactionScope. Thanks - webnoob 2012-04-05 06:21

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()

2012-04-04 18:12
by the_joric

so new SqlConnection won't be causing the problem - webnoob 2012-04-04 18:13

As far as I know -- it should not - the_joric 2012-04-04 18:24

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 : - webnoob 2012-04-04 22:00