How do you uniquely identify a connection in a connection pool?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am in the process of debugging a connection pooling problem in a
traditional 3-tiered app. There is a connection factory that all DAOs are
supposed to call to get a connection (yes, some DAOs probably aren't and I am
looking at that). In the meantime, I want to add some instrumentation that
shows when a particular connection in created in a particular connection pool
and when that particular connection is disposed.

Does anyone know of a way, probably using reflection, to identify a
connection? Perhaps somewhere in the SqlInternalConnection?

Any help or other ideas are greatly appreciated.

Regards,

Doug
 
Sahil:

Thanks for responding (I've refer to your blog quite frequently).

I have been doing this, but it doesn't provide me insight into which DAOs in
the application are creating a specific connection, and ultimately the
process in the db that sp_who or sp_who2 return.

If I can do the instumentation I'd like to do, then I could also instrument
the DAO and then I would know for sure which DAO is creating 20 connections
instead of creating one, doing some work, disposing the connection, creating
another connection, doing some work, etc, etc, etc.

Does that make sense?

Doug
 
Hi Doug,

Some approaches you can take to find which DAOs are not following the rules:

* Use Sql Security. Create a sql account and have it be the *only* account
with access to the DB (Integrated Security may not work if you are not
impersonating each user when connecting to the DB (which you're probably not
doing) 'cause then the DAO may still connect successfully). Then, add that
info to your object factory so that it will be the only way to create and
return connections. The misbehaving DAOs will not be able to create the
connection and you'll get an exception for those cases.

* Add "ApplicationName=App<PID><DAO>" (<PID> and <DAO> are naturally
provided by you) to the connection string. This way, on SQL you'll be able
to know who is creating the connections. A side effect is that you'll end up
with a lot of connection pools so make sure to revert back to the single
connection string afterwards.

hth

--VV [MS]
 
Sahil:

The problem isn't primarily related to NOT closing connections, rather, the
creation of a lot of connections by one or more DAOs that are used by a page
in the web app. For example, one of the pages in the web app when displayed
will create as many as 25 new connections in the connection pool. I have
been able to determine this by running the entire application on one machine
and watching connections via:

- exec sp_who and sp_who2
- perfmon monitoring of the .NET CLR Data Performance Object
- a custom console application that I wrote to display # global connection
pools and # global connections

Regarding the architecture, the DAOs are in an assembly that is called by a
facade, which has been called by the 'business logic' tier, which has been
called by the pages in the web app (there are a few more layers, but it's not
relevant). All of the DAOs obtain connections from a factory so that we
ensure consistency of the connection string, the user, etc.

As I have been profiling and instrumenting the application I have found at
least one problem. DAOs are calling other DAOs after obtaining a connection
and prior to disposing the connection. Is some cases the 2nd DAO that is
called does the same thing with a 3rd DAO. So as you probably have surmized,
this creates a cascading affect of multiple new connections being created in
the pool to obtain the data required to service a call from the page in the
web app.

At this point I know that I can do some refactoring to either have the
intial DAOs load the result set into an collection of some type, close it's
connection, then iterate through the collection and call the other DAOs as
needed. Or use a DataSet and do the same thing.

But the utility that I am envisioning would be used in other parts of the
application and other applications. Again, the goal being able to uniquely
identify a connection in the pool, and then use refectlion, the stack trace,
or a combintation to write a log of the sequence of connection creation and
who (which DAO) created it, and then write out the disposal of the
connection. In order for this data to be useful I need a way to uniquely
identify the connection.

2 ideas that I've received are to use the DateTime stamp on the connection
which you can get with reflection from the SqlInternalConnection class.
Also, changing the connection string to include the DAO name in the
ApplicationName, which would create a lot of pools but may provide some
valuable debugging clues.

An enhancement to The SqlConnection class to include a unique identifier and
'parent' would be really nice. The parent being the assembly and class that
created the connection.

HTH

Doug
 
Vasco:

Thanks for your suggestion, which I will try.

Regarding your first point, the application already has a connection factory.

Thanks for your help.

Doug

Vasco Veiga said:
Hi Doug,

Some approaches you can take to find which DAOs are not following the rules:

* Use Sql Security. Create a sql account and have it be the *only* account
with access to the DB (Integrated Security may not work if you are not
impersonating each user when connecting to the DB (which you're probably not
doing) 'cause then the DAO may still connect successfully). Then, add that
info to your object factory so that it will be the only way to create and
return connections. The misbehaving DAOs will not be able to create the
connection and you'll get an exception for those cases.

* Add "ApplicationName=App<PID><DAO>" (<PID> and <DAO> are naturally
provided by you) to the connection string. This way, on SQL you'll be able
to know who is creating the connections. A side effect is that you'll end up
with a lot of connection pools so make sure to revert back to the single
connection string afterwards.

hth

--VV [MS]

Doug Ramirez said:
Sahil:

Thanks for responding (I've refer to your blog quite frequently).

I have been doing this, but it doesn't provide me insight into which DAOs
in
the application are creating a specific connection, and ultimately the
process in the db that sp_who or sp_who2 return.

If I can do the instumentation I'd like to do, then I could also
instrument
the DAO and then I would know for sure which DAO is creating 20
connections
instead of creating one, doing some work, disposing the connection,
creating
another connection, doing some work, etc, etc, etc.

Does that make sense?

Doug
Click to expand...
Click to expand...
 
Back
Top