0

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

webnoob
  • 15,747
  • 13
  • 83
  • 165
  • The connection pooling will re-use the connection, so I don't think this is a problem. Did anything else change? – Bryan Crosby Apr 04 '12 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 Apr 04 '12 at 18:19
  • Why create a separate connection for each insert? – Bryan Apr 04 '12 at 18:20
  • @beargle this is the best practice http://msdn.microsoft.com/en-us/library/ms254507.aspx – the_joric Apr 04 '12 at 18:24
  • @the_joric I don't see anything in that page advising separate connections for each `SqlCommand`...what am I missing? – Bryan Apr 04 '12 at 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 Apr 04 '12 at 18:33
  • 1
    R u aware that [TransactionScope creates by default serializable transactions](http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx)? And also multiple connections opened from a single TransactionScope enroll in distributed transaction (two phase commit) ? – Remus Rusanu Apr 04 '12 at 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 Apr 04 '12 at 18:35
  • @RemusRusanu even if those connections are made for a single SQL server? – the_joric Apr 04 '12 at 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 Apr 04 '12 at 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 Apr 05 '12 at 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 Apr 05 '12 at 00:36
  • @the_joric: for a longer post discussing this topic see http://stackoverflow.com/a/1693270/105929 – Remus Rusanu Apr 05 '12 at 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 Apr 05 '12 at 05:53
  • @Remus I am also using the code as suggested in the link to cretae my TransactionScope. Thanks. – webnoob Apr 05 '12 at 06:21

1 Answers1

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

the_joric
  • 11,986
  • 6
  • 36
  • 57