Re: ADO - Bad when it is time to Optimize


On Mar 9, 1:54 am, "Bob Barrows [MVP]" <reb01...@xxxxxxxxxxxxxxx>
wrote:
cvsudh...@xxxxxxxxx wrote:
I am working on a huge application where the business components are
in COM-ATL and the data access is thru ADO to SQL.
We are having major scalability issues with huge user connections and
then it was time to see whether are we using the ADO pooling in the
right way. But to my surprise I noted that there is no way to monitor
the ADO pool and its performance.

1. You cannot set the max pool size.
2. There is no way to disable the pooling
3. You cannot know whether non-pooled connections are happening
4. Donot know whether connection creeping is happening.
5. There are no performance counters to get some data abt pools. But
there exists counters for ODBC which microsoft does not support.

The only way to check whether pooling is happening is thru the SQL
Profiler where for every connection reused from the pool, you can see
SP_RESET_CONNECTION stored procedure being called.
Yes ..You can check the pools, the connections, thier life time by
taking a hang dump, provided you have ADO private symbols..!

This is with classic ADO.
Dont ask me to use ADO.NET (I already have some.). There there is no
way control the time a connection lives in the pool. But there is one
in ADO (SPTimeout).

It is really pathethic to find that when you find that there is no way
to get an idea about the health of the ADO Pooling.

If anybody knows about any tool, code etc. please do let me know.

Thanks!
SK

There are definitely several ways to disable pooling (which is something
that is not recommended by the way). Everything I know about session pooling
can be found in these articles:

http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asphttp://support.microsoft.com/?scid=kb;en-us;Q176056http://support.microsoft.com/default.aspx?scid=kb;en-us;191572http://support.microsoft.com/default.aspx?scid=kb;en-us;324686

connection failures caused by pooling disablementhttp://support.microsoft.com/default.aspx?scid=kb;en-us;328476

implicit connections:http://support.microsoft.com/?kbid=271128
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Random database connectivity failure
    ... We can’t find any problems with the pooling, that it uses to many resources. ... pointing to its not a pool problem. ... The server is only used for this web-application, ... white paper on handling the connection pool on my website. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO - Bad when it is time to Optimize
    ... tells the settings to be done after ADO pooling is turned off, ... I think the reason for that is ADO Pool goes ... on creating new connections when it finds no free connection in the ...
    (microsoft.public.data.ado)
  • Re: Problem with database connectivity performance
    ... can you confirm that only one pool is getting created ... ... generate error messages, ADO.NET exceptions. ... If we use connection pooling we ... If we turn off connection pooling we are getting the following exception ...
    (microsoft.public.dotnet.framework.performance)
  • Re: C++ support for ADO
    ... TESTHR(pConn.CreateInstance(__uuidof(Connection))); ... In answer to your question I'm interested in ADO not the ADO.net. ... data classes for working with ADO/OLE DB. ... You can also use the OLE SDK. ...
    (microsoft.public.data.ado)
  • Re: -2147467259 Unspecified Error
    ... but consider that ADO also uses a connection ... If the server fails for some reason (high severity level or other ... the connection in the pool will be "dented". ... That's what I get when opening an ADO ...
    (microsoft.public.data.ado)