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

SubPerformance Monitor Results: A maximum of two connections are made, and the rest appear to be pooled. The connections are destroyed when the application is closed in both VB and ASP.


SQL Server Profiler Trace Results: Only two connections are made, they aren’t destroyed until the application is closed, or the connections time out after 60 seconds. 

Connect 
ExistingConnection 
Connect 
ExistingConnection 
Disconnect 
Disconnect

Total Time: 2.56 Seconds

If Connections are Closed and Set to Nothin

Private Sub Form_Load()
   Dim rs
   Dim n
  For n = 0 To 2
    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
  NextEnd 

SubPerformance Monitor Results: Only one new connection is made – it doesn’t appear that pooling is working for VB, but Performance Monitor shows proper pooling behavior for ASP. 


SQL Server Profiler Trace Results: For VB, three connections and disconnections are made, unlike in the example where pooling was working in which starting with the 3rd connection no more new connections were created. For ASP, on the other hand, destroying the connections has no effect and pooling is still in effect. 

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.

ASP Trace Results

Connect 
ExistingConnection 
Connect 
ExistingConnection 
[No more trace activity even though a 3rd connection is created!] 
(after 60 seconds) 
Disconnect 
Disconnect

Total Time: 2.62 seconds

If No Pooling is Attempted (Unique UID/PWD

Private Sub Form_Load()
  Dim rs
  Dim n
  Dim str
  For n = 0 To 2
    Set rs = CreateObject(“ADODB.Recordset”)
    Set cn(n) = CreateObject(“ADODB.Connection”)
    Select Case n
      Case Is = 0
        str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test1″
        cn(n).Open str
      Case Is = 1
        str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test2″
        cn(n).Open str
      Case Is = 2
        str = “DRIVER={SQL Server};SERVER=SQLSERVER;DATABASE=Pubs;UID=test3″
        cn(n).Open str
                End Select
        rs.Open “select * from authors”, cn(n)
        rs.Close
        Set rs = Nothing
        cn(n).Close
        Set cn(n) = Nothing
      Next n

Continues…

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 |