Re: ADO - Bad when it is time to Optimize

cvsudheer@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.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default.aspx?scid=kb;en-us;191572
http://support.microsoft.com/default.aspx?scid=kb;en-us;324686

connection failures caused by pooling disablement
http://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: ADO and COM+ problem
    ... Have you got COM+ obj pooling set up for the ADO objects, ... Do you make use of Thread Local Storage in the ... have let go) kill of any open connections. ...
    (microsoft.public.data.ado)
  • Re: VS 2003.net
    ... retry some connections and then try the create function again. ... myRS is an ado recordset connecting to an ODBC database and a single table. ... IsDBNull does not return the correct value causing runtime errors. ...
    (microsoft.public.dotnet.languages.vb)
  • ADO - Bad when it is time to Optimize
    ... in COM-ATL and the data access is thru ADO to SQL. ... You cannot set the max pool size. ... You cannot know whether non-pooled connections are happening ... to get an idea about the health of the ADO Pooling. ...
    (microsoft.public.data.ado)
  • Re: MS Access 97 vs. XP/2003
    ... > be a web-based app). ... All the connections to the SQL DB are via ADO. ... recordset, containing modified data, may or may not come back eventually. ...
    (microsoft.public.access.modulesdaovba)
  • app roles and ADO connections
    ... I'm having problems with the combination of SQL Server ... application roles and ADO connections. ... problematic since the approles is strictly a SQL Server ... In some cases ADO will create additional connections ...
    (microsoft.public.sqlserver.security)