2

I have come across an issue that seems to be somehow connected to a web server configuration, and resulting in queries randomly taking a long time to execute. The application is created using old plain Classic ASP and ADODB Connection is used.

The scenario goes as follows:

  • there is a single connection opened in a script at the beginning of processing each HTTP request
  • this connection is used to execute a query against a SQL Server, that resides on a separate box. conn.Execute is used. Connection is NOT closed afterwards
  • there are usually a few to a few dozens of conn.Execute in a single ASP page

All has been working well until recently, when some of the conn.Execute started to take much longer to execute, totally on random.

  • the difference is e.g. 15ms normal execution time vs. 2000ms long execution time
  • on the SQL Server side, Profiler does not show longer query execution times, so there must be something blocking the conn.Execute request

When a proper practice of closing a connection after each conn.Execute has been implemented, the issue goes away. However, as I have stated before, all has been working flawlessly until recently. This web app is a fairly large one and rewriting it to close and reopen connections properly will take some time. And I need a short-term solution.

My guess is that it could have something to do with the connection pool size, however this is not ADO.NET, therefore I am not sure, whether a connection pool issue should be taken into the consideration at all. On the SQL Server side, there is no limit on the number of concurrent connections to the server.

I need some hints. Brainstorming possible ideas.

Sebastian Zaklada
  • 2,308
  • 1
  • 14
  • 25
  • Hint: I eventually found out, that when a connection is established through a System DNS (ODBC, TCP/IP transport), the issue is there. When I forced SQLNCLI provider in the connection string (using Provider=SQLNCLI), the issue went away. Great, but why did it happen in the first place...? – Sebastian Zaklada Sep 26 '11 at 09:56
  • 1
    Have there been recent patches or updates applied to the server? – Jesse Sep 26 '11 at 22:39
  • Yes, there were. This is a dedicated box hosted and administered by a 3rd party in a remote location. Good point, forgot to add this in my original description of the problem, I thought I did. – Sebastian Zaklada Sep 27 '11 at 09:00
  • Just noticed, that when a proper practice of closing a connection after each conn.Execute has been implemented, the issue does NOT go away. It happens much less often, but yet, still can be observed. – Sebastian Zaklada Sep 27 '11 at 12:30

1 Answers1

0

Could be related to delays resolving the hostname in the connection string via DNS - have you tried putting an IP address in the connection string instead of the hostname?

Dylan Beattie
  • 53,688
  • 35
  • 128
  • 197
  • Changing it in the connection string would override using the System DSN entry, but no, I have not changed the DSN to use the IP instead of the server name, however I don't think that this would be the cause of issues here, as both boxes are in the same network and resolving host name should be cached, or should it not? – Sebastian Zaklada Sep 27 '11 at 08:59
  • Host name was taken from the /etc/hosts file, therefore unfortunately this is not related to resolving IP address... – Sebastian Zaklada Sep 27 '11 at 12:19