Connection Pool Misconceptions

Connection Pool Misconceptions

by JBrooks 12. February 2009 08:38
Below is the sql to show how many connection are being used from my application.

declare @Database varchar(30),
    @LoginName varchar(30)

set @Database = 'MyDB'
set @LoginName = 'MyLoginName'

select max(blocked) hasBlocking,
    count(1) [Count],
    sum(open_tran) open_trans,
    max(waittime) maxWaitTime
from master.dbo.sysprocesses (nolock)
where db_name(dbid) = @Database and convert(sysname, rtrim(loginame)) = @LoginName
        
We needed this because there was a problem where our application would use up 100 connections (the default) and then bomb out.  The root cause was because of 2 misconceptions that a developer had.

First misconception:
Calling Close() on a DataReader closed the Connection.  This only releases the Connection object from the DataReader, but this does not close it.  You need to call Close() on the Connection object.  You can test this by seeing the Connection object's State will still be "Open" after calling Close()on the DataReader.

Second misconception:
Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, you should only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information. For more information on this, see:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close(VS.85).aspx

So if these 2 misconceptions are cleared up in your mind you are a lot less likely to run into this connection pool problem.

 

Tags:

ASP.Net | Development