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 ..
output unique_id
, I don;t know if this adds lots of overhead .. - webnoob 2012-04-04 18:19
SqlCommand
...what am I missing - Bryan 2012-04-04 18:28
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()