Re: How do you uniquely identify a connection in a connection pool

Doug,

Have you taken a look at the CLR Profiler?
http://www.microsoft.com/downloads/details.aspx?FamilyId=86CE6052-D7F4-4AEB-9B7A-94635BEEBDDA&displaylang=en

Basically you can profile any application through it, stop it as it is
running, and get a nice graphical view of all the objects created and who is
holding references to what.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------------------------


"Doug Ramirez" <DougRamirez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5D1C38BF-9321-477C-8DAF-2BE50C306C54@xxxxxxxxxxxxxxxx
> 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
>
> "Sahil Malik [MVP]" wrote:
>
>> Doug,
>>
>> Okay so let me try and understand your problem ...
>>
>> "You want to know, what DAO (Data access object?) is being a pig by not
>> closing connections" .. Right?
>>
>> How is that DAO implemented? Is it an EXE? Is it a data layer? Is it a
>> class?
>>
>> - Sahil Malik [MVP]
>> ADO.NET 2.0 book -
>> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
>> -------------------------------------------------------------------------------------------
>>
>>
>>
>> "Doug Ramirez" <DougRamirez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:A7B2A257-F04E-4CA0-98AD-016C458E662B@xxxxxxxxxxxxxxxx
>> > 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
>> >
>> > "Sahil Malik [MVP]" wrote:
>> >
>> >> Doug,
>> >>
>> >> Funny but a simpler approach is probably easier. Just execute sp_who2,
>> >> and
>> >> filter by ".NET SqlClient Provider".
>> >>
>> >> - Sahil Malik [MVP]
>> >> ADO.NET 2.0 book -
>> >> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
>> >> -------------------------------------------------------------------------------------------
>> >>
>> >> "Doug Ramirez" <DougRamirez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> message
>> >> news:83C62647-251E-4CF3-8042-0F19FB46EB6C@xxxxxxxxxxxxxxxx
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>
>>


.

Relevant Pages

  • Re: DAOs and Connection sharing
    ... > same connection and transaction. ... > - Connection objects are not passed as method arguments. ... Have you looked at the Spring Framework? ... Framework manages the Datastore and DAOs nicely. ...
    (comp.lang.java.programmer)
  • Re: Container-managed Transactions scope
    ... In my case I am working on now, only one JDBC connection is ... The DAOs use this Connection to do their work but do not ... This method is being called from my EJB. ... >> the Container-managed Transactions be extended to the DAOs ...
    (comp.lang.java.programmer)
  • Re: How do you uniquely identify a connection in a connection pool
    ... Some approaches you can take to find which DAOs are not following the rules: ... Create a sql account and have it be the *only* account ... connection and you'll get an exception for those cases. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Container-managed Transactions scope
    ... Frankly, it's been a while since I mucked with JBoss configuration, but as I recall, in the servers I worked with the configuration information for each DataSource was in its own file in the server's filespace. ... If your DAOs all want to use the same Connection then you should pass them a Connection to use as a parameter to each method that needs one. ...
    (comp.lang.java.programmer)
  • Re: Close and Dispose argument
    ... > fact that even Microsoft is not clear on the issue. ... > connections from the pool is still a debate. ... > method removes the conneciton from the connection pool". ... > Your issue was forwarded to us here at Microsoft Courseware Support. ...
    (microsoft.public.dotnet.framework.adonet)