End SubPerformance Monitor Results: Only one new connection is made – it doesn’t appear that pooling is working
.
Note: This blip was induced by a slight delay so Performance Monitor could pick up the connection. Normally, no change is seen in Performance Monitor at all.
SQL Server Profiler Trace: Four connections and disconnections are made, which is exactly like the example where pooling was not implemented properly and each new connection had to be created anew.
ASP Trace Results
Connect
ExistingConnection
Disconnect
Connect
ExistingConnection
Disconnect
Connect
ExistingConnection
Disconnect
Total Time: 7.66 Seconds*
*This performance number was extrapolated because I didn’t have 100 unique user connections. I used the average of the 2nd two connections (not including the first) and multiplied by 50.
Conclusions
The performance results were significant in that the pooled connections were considerably faster. This stands to reason as connections are being retrieved from a pool and not created from scratch like in the other two cases where the object is either destroyed and not released into the pool, or held onto and not released into the pool. Not closing connections results in unneeded connections to “hang,” causing “connection creep”. Be sure to explicitly close all of your connections when you are done with them.
Other Useful References
http://support.microsoft.com/support/kb/articles/Q191/5/72.ASPhttp://msdn.microsoft.com/library/techart/pooling2.htm
Appendix
The SQL Server Profile result set was obtained using a VB test driver and setting “Connections” to “Nothing” after every iteration.Here’s a screen shot of SQL Server Profiler using the following code:
The code to run “as is” in either VB or ASP environments:Dim cn(20)
Dim rs
Dim n
For n = 0 To 20
Set rs = CreateObject(“ADODB.Recordset”)
Set cn(n) = CreateObject(“ADODB.Connection”)
cn(n).Open “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=sa”
rs.Open “select * from authors”, cn(n)
rs.Close
Set rs = Nothing
cn(n).Close
Set cn(n) = Nothing]]>