Classic ASP application experiencing SQL Server “T

Question

We've been seeing this problem for a while now and I'm really trying to wrap my head around what's causing it.

A couple of times a day we'll see periods where web pages start throwing "[Microsoft][ODBC SQL Server Driver]Timeout expired" then shortly afterward pages start throwing "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied."

We have many different applications that connect to this database server. It averages around 2500 concurrent connections processing on average 10,000 transactions per second. Most of our applications have no problems whatsoever, the problems seem only to happen on the web server. (Perhaps it's related to connection pooling?)

I'm not sure what to attribute this problem to. The SQL server in question is vastly overpowered for the work that it does, and is equipped with per-processor licensing. So I don't think we're looking at a licensing/performance issue.

I thought maybe there was an IP connectivity issue, so I changed the ConnectionString to use the IP address and ran some long-running pings. I got 0 packets lost between the Web server and the Database server.

The ASP connection string now looks like this:

Provider=MSDASQL; Driver={SQL Server}; Server=10.0.100.100; Database=DBName; UID=WebUserName; PWD=WebUserPassword; ConnectionTimeout=15; CommandTimeout=120;

The user is a non-domain user connecting using Sql Server authentication. So I don't think it's a domain-related issue. I've checked the SQL server log files and have found nothing whatsoever corresponding to the incidents.

I've found another stackoverflow question describing similar behavior, but without a resolution.

The Details:

• Web Server: Windows 2003 Standard SP2, IIS 6.
• Database Server: Microsoft SQL Server 9.0.4035

Has anyone seen/resolved this type issue? Does anyone have any suggestions as to where I should look next?

Thanks!

-Zorlack

EDIT

Can anyone tell me what the best practice is for performing sql queries in classic high-load asp? Do we want to try to leverage connection pooling?

In looking at the code, quite a lot looks like this:

Set objCn = Server.CreateObject("ADODB.Connection") 
objCn.Open(Application("RoConnStr"))
'do some stuff
objCn.Close
Set objCn = Nothing

Solution (per ScottE's advice)

This article described, to a tee, my problem. I made the registry change and then rebooted the server.

Problem Solved!

Answer 1

The times I had such problems, always had to to with someone holding the data open in a client application without commiting a 'select ..' statement.

Don't know if this solves your problem here... though.

Answer 2

I solved this problem by recreating the stored procedure!
Just a simple DROP and then CREATE stopped the timeouts in my case!

I had suffered with this problem for a week; a classic ASP said "SQL timeout" when I could run the same query direct on the database in less than a second. (I did not see the "does not exist" message though.) The ASP had been working fine for a whole month.

A genius friend of mine said, "Going through ASP uses a 'cached' execution plan, which is not very efficient. Try dropping and recreating. This does suggest your stored procedure could do with re-writing to make it more efficient, as it could happen again."

Since the proc was running fine when tested with SQL Management Studio, I assume that does not used the cached plan, but ASP does.

Answer 3

Is your web app closing and disposing (set to nothing) of database connections?

Also, have you tried using SQLOLEDB instead of ODBC? Can't think of any reason why you'd be using ODBC here.

here's my connection string on a very busy classic asp app:

Dim strcConn
strConn = "Provider=SQLOLEDB; Data Source=someserver; Initial Catalog=somedb; User ID=someuserid; Password=somepassword"

Edit

I came across this blog posting. Kind of interesting.

http://www.ryanbutcher.com/2006/02/classic-asp-on-2003-server-with.html