Use the Correct ASP (ADO) Syntax to Enable Connection Pooling for Best SQL Server Performance

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

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Use the Correct ASP (ADO) Syntax to Enable Connection Pooling for Best SQL Server Performance”

  1. First and foremost, I am thrilled to see that someone took the time to actually test this. It’s been on my mind as I’ve noticed variations in several very high traffic sites I run. You took the time to not only write this up with images, but you graphed the results. Thank you!

    Here are my findings, there is much more to this then one might think.

    - SQL Server itself has keep alives in connection configuration. It also has a keep alive ping to check. This has to be high enough so that the keep alives are not expiring before the connection can be reused depends on your web app

    - IIS, according to Microsoft and now I cannot find the MSDN link, keep alives have to be enabled there too. Make sure not too high or you will have a lot of HTTP TIME_WAIT on your stateful firewall and server.

    - Best driver.. You have a few options, you can use SQLOLEDB as a provider or the native client which is SQLNCLI10 or SQLNCLI if not SQL Server 2008. OR, you can use ODBC to handle SQL, depending on your Windows version ODBC has a connection pooling tab that lets you define the number.

    I found using SQLOLEDB or native client to be much faster than ODBC, by many times too.

    If your using classic ASP it’s a bit tough if your site constantly requires database connections and using global#asa for a session based db open is a bad idea.

    ASP.NET on the other hand allows better performance. Also found setting the SQL Packet size in the connection string to have a big impact. 4096 is the default max network packet, but if your returning or using less than 4096 bytes in your query (including recordset) then lower it in your connection string, NOT THE SQL SERVER. To due this just append “yourstring…;Packet Size=512;” <- added Packet Size=X; If it needs more, it will just send multiple packets but it will reduce network I/O significantly. If your queries are huge (which shouldn't be, use stored procedures and try to avoid cursors) Remember the most common MTU is 1500. You could also do 1024. Default when unspecified is 4096.

    - If your SQL Server is on the same machine as IIS forget TCP, use shared memory. See here: "Provider=SQLNCLI10;Data Source=lpc:(local);…rest of string" lpc:(local) implies its local use memory.

    Many might say well does this all really matter. If you have minimal or little traffic and don't mind resetting IIS every so often then no but running a high traffic site such as one of ours its tremendous. Reliability wise and resource wise. Code should be clean and efficient, the author of this page did a good job of detailing the differences.

    I will attempt to run some tests too, but I ended up writing my own .NET lib for pooling. It runs as a Windows Service and then you use the query with the lib instead of Microsoft ADO. It's a way of semi transforming a old (but huge) ASP site in to .NET in some aspects such as this.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |