My company is responsible for a website for a well known british organisation that receives a massive amount of traffic daily. However its also one of our oldest sites and one of the few still running on ASP. Thankfully its attached to SQL server and not Access!
Recently we started getting the ambigous "Server does not exist or access denied" message during high traffic periods (lunch times, weekends) and we eventually figured it was a connection pooling issue.
I had a look at the code and the original developer used an include file at the start of each page with
Set connection = Server.CreateObject("ADODB.Connection")
connection.open connstring
Subsequent functions would then call
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open sQuery connection
Although rs.close was employed (but not always set rs = nothing) I noticed that at no time was connection.close ever called. So I tried to add an include file at the base of the page containing
connection.close
set connection = nothing
However this causes "object does not exist" errors. It seems that the connection object is implicitly closing - but I'm guessing not always.
I've been gradually going through the code and changing key pages and functions so that they don't call on this global connection object and instead creating local connection objects which I explicity close and set to nothing as soon as the query/recordset is no longer needed. In the meantime obscure code which I haven't yet got to is still calling the original connection object.
Please understand I'm doing this incrementally to the live site because its the only way I can monitor the results under high traffic.
Before I did this, in SSMS 2005 Activity Monitor there would only need to be 6 or so processes attached to the database for the site to fall over momentarily. Since the code changes I've seen upwards of 15 separate processes calling the database in Activity Monitor but the site is as fast as lightning, where previously I think it would have falled over by now.
Is this a sign that my code changes are doing the right thing and the site is pooling better under heavy loads by adding the extra processes as it needs them or that I've just messed things up further by introducing the additional connection objects?
I'm not from a classic asp background, I made the switch to ASP.NET from PHP so some code is a bit foreign to me when I look at this project. We don't have any .NET sites that attract anywhere near as much traffic as this site so I have nothing to compare to, but most of these rarely show more than one or two processes in Activity Monitor.