Kbase 21076: ASP and SQL92 Resource Versus Connection Pooling
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
How to trouble shooting ASP - SQL-92 connection hang problems.
FIX:
1) Disable both OLE DB resource pooling and ODBC connection pooling. Disable OLE DB resource pooling by setting:
HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\OLEDB_SERVICES to 0x00000000
(the CLSID is for the Microsoft OLE DB Provider for ODBC Drivers). Disable ODBC connection from the ODBC Administration applet.
Reboot the server to effect the changes.
2) With both disabled, observe ASP/IIS pages behavior. Notice in PROMON the SQL user logging on and quickly logging off. This type of behavior is similar to what would be seen when connecting and disconnecting using SQL Explorer. None of the users hanging on for 10, 20, or more minutes as had happened previously.
3) Disable just OLE DB resource pooling by setting the same registry entry mentioned above to 0xFFFFFFFF. In the same registry key, also set SPTimeout to 10. Again, reboot. Observe the behavior.
The resource pooling seemed to behave as advertised. You would also see that the resource pooling can be seen starting two SQL server processes (_SQLSRV2.EXE). This works following the formula below, where N is the number of pools, P is the number of processors, and C is the number of distinct sets of connection attributes on the system:
N = (P + 1) * C
PROMON showed users staying logged on for about 10 seconds after the ASP request finished. The conclusion that was reached after all this was that OLE DB resource pooling seems to work pretty well by itself, but things seem to go awry with OLE DB resource pooling and ODBC connection pooling both enabled.
Another thing that became very apparent is that the coding techniques used in the ASP pages are very significant. Creating an implicit connection object in the ASP page does not work well with resource or connection pooling. As the following link stresses, It is prefered to open one explicit connection and keep it open throughout the ASP page, and close explicitly any open record sets and the connection.
http://support.microsoft.com/support/kb/articles/Q191/5/72.ASP