I was recently consulting with a client who was experiencing timeout issues due to long running requests. This issue has plagued their site for quite awhile. My approach to solving their long running requests was to jump into the CF logs to find out what might be the problem.
The challenge was that it was a legacy fusebox site and because of the architecture of this version of fusebox every error pointed to index.cfm. The logs were somewhat helpful with regard to the timeouts and where exactly the error was coming from at a higher level of the multiple application supporting server. Noticing that it was a particular application, I challenged the development team to wrap all cfquery/cfstoredproc requests in a cftry/cfcatch. As is the case with many CF developers (including myself when I was starting out) it is easy to take for granted that the database is always going to be stable and ColdFusion's very straightforward ability to query the database will execute with no problem.
This is a huge mistakes in application development no matter what technology you are working with. In code for the immediate application i.e. CF, java, C#, VB, etc. anytime the application has to go outside of itself to query a database, web service, shared api call, etc. this code must be contained in a try or catch because it can fail due to multiple reasons.
Back to the CF issue... I recommended that the client update the queries (no timeout for cfstoredproc, wonder why?) with a timeout settings forcing it to fail (this would identify the long running query) and due to CF throwing a time out error the cfcatch could write a very detailed description from the given cfcatch scope and then they could write the actual page and line of code where the failure happened. At this point after this code was implemented they could then place a cfthrow tag at the end of the catch to bubble up the error to a global error handler in Application.cfc so that it would be handled gracefully by displaying a friendly error page.
Sorry if I'm getting wordy... Thoughts tend to flow when blogging and sentences run-on... So you may be thinking, I have a huge application with queries all over the place where the heck do I start? If you are a dreamweaverer or cfeclipser or homesiter you have the tools at your disposal to do a sitewide search. I like dreamweaver's and homesite's search capabilities due to the fact that you can export the file list in various formats and organize a plan of attack with your team to patch up the application. You'll have your site ready to diagnose itself and in turn be able to stabilize or reengineer the failed module.