Re: ADO - Bad when it is time to Optimize

Oops..my last reply got lost.
I was testing by posting an empty reply and that came up..!
So trying again..

Thanks a lot Bob for the reply.

This KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
tells the settings to be done after ADO pooling is turned off, but it
does not tell hot to turn it off.

My requirement is not to turn it off either.
We have a situation where SQL server gets slow in processing requests
due to some contention issues and at that point I can see the User
Connections shooting up. I think the reason for that is ADO Pool goes
on creating new connections when it finds no free connection in the
pool. If there were a way to set the max pool size, I could have
avoided over-stressing the SQL server which is already in a bad state.

I appreciate your help and will be happy to hear some thing about
connection pool throttling.

Thanks!
SK






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: ODBC/OLE DB Connection Pool
    ... > connection be kept open for the application as this will serialize all ... threads ONCE they are returned to the pool. ... > Tips for ADO Users ... > The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.data.ado)
  • Re: ODBC/OLE DB Connection Pool
    ... > connection be kept open for the application as this will serialize all ... threads ONCE they are returned to the pool. ... > Tips for ADO Users ... > The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.inetserver.asp.db)
  • Re: ODBC/OLE DB Connection Pool
    ... > connection be kept open for the application as this will serialize all ... threads ONCE they are returned to the pool. ... > Tips for ADO Users ... > The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.data.oledb)
  • 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)