Running a query using ADODB Connection randomly takes a long time to execute

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.

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?