very helpful, thank you. Ran a query with logparser to look at the top 10 files on our server that take the maximum time (time-taken). As suspected, all the files (also looked at the top 50, same situation) are taking over 2-3 minutes. No distinction between pages where there is a connection to the SQL database or not -- all taking a long time to execute and eventually timing out. Something must be wrong that is impacting the entire performance.
When we set the application pool recycling to "every 10 minutes" we thought that it would be an effective band-aid. That seems to have no effect. Even a reboot of the server doesn't seem to correct the problem. after the reboot, ASP requests executing quickly maxes out at 50 and rest starts to get piled up in ASP Requests Queued.
I will now run IIS Debug DiagTool to analyze Memory and Handle Leak for w3wp. Should we also try other rules (e.g., Crash or IIS Hang) or other processes than w3wp? I would be very interested to post the analysis report of the proper rule here so that expert guys can take a look at and provide any insights (assuming this is the best logical next step).
Also, one bit of observation (for what it's worth), when we stop the SQL Server on the machine, the asp pages where there is no database connection in them start to execute fine (still slow response, but browser displays the file eventually). However, when we do that, we observed that the CPU utilization (%processor time) jumps to 100% and stays there. When the SQL Server is started again, the %processor time drops to something between 0-1% and stays there, spiking to 20% level occasionally. Not exactly sure what that means, but wanted to post this as well.