How can I find why some classic asp pages randomly take a real long time to execute ?

Question

I'm working on a rather large classic asp / SQL Server application. A new version was rolled out a few months ago with a lot of new features, and I must have a very nasty bug somewhere : some very basic pages randomly take a very long time to execute.

A few clues :

• It isn't the database : when I run the query profiler, it doesn't detect any long running query
• When I launch IIS Diagnostic tools, reqviewer shows that the request is in state "processing"
• This can happen on ANY page
• I can't reproduce it easily, it's completely random.
• To have an idea of "a very long time" : this morning I had a page take more than 5 minutes to execute, when it normaly should be returned to the client in less than 100 ms.
• The application can handle rather large upload and download of files (up to 2 gb in size). This is also handled with a classic asp script, using SoftArtisan FileUp. Don't think it can cause the problem though, we've had these uploads for quite a while now.
• I've had the problem on two separate servers (in two separate locations, with different sets of data). One is running the application with good ol' SQL Server 2000 and the other runs SQL Server 2005. The web server is IIS 6 in both cases.

Any idea what the problem is or on how to solve that kind of problem ?

Thanks.

Sebastien

Answer 1

Can you establish whether this is a problem for all pages or a common subset of pages?

If a subset examine what these pages have in common, for example they all use a specific COM dll, that other pages don't.

Does this problem affect multiple clients or just a few?

IOW is there an issue with a specific browser OS version.

Is this public or intranet?

Can you reproduce the problem from a client you own?

Answer 2

Is there any chance there are some full-text search queries going on SQL Server?

Because if so, and if SQL Server has no access to internet, it may cause a 45-second delay every few hours or so when it tries to check the certifications (though this does not apply to SQL Server 2000).

For a detailed explanation of what I'm referring to, read this.

Answer 3

Are any other apps running on your web server? If so, is your problematic in the same app pool as any of them? If so, try creating a dedicated app pool for it. Maybe one of the other apps is having a problem and is adversely affecting yours.