Of course, ORM support is just a different way that the CFML engine (through the ORM framework) sends SQL to a database via a regular DSN, just like any other request, so there's nothing really "tricky" about this. It's just about realizing that while you don't write the SQL yourself, it's still generated by the CFML engine/ORM framework, and you may not realize/consider the available tools which can spy on it, just like any other DB processing from within CF. Indeed, some people may not even realize how many options exist to spy on JDBC interactions from their CFML engine to the database engine.

The good news is that there are several approaches, some included in CF (some depending on the edition), and some available separately which would work in any edition of CF or the other CFML engines (Open BlueDragon, Railo, etc.), and with any of the ORM frameworks.

(FWIW, besides the aforementioned Transfer and Reactor, there are still others, which I mention in my CF411 list as CFML ORM Frameworks. Indeed, note that you can run Hibernate on CF prior to CF9.)

Built-in ORM Logging Option

There are several resources where you can learn more on that. Besides the CF9 docs page on the ORM settings, there is also a blog entry by Adobe engineer Rupesh Kumar. I've not yet used it, so don't know how it compares to the other options below. I will leave them for all to consider.

Using FusionReactor or SeeFusion

Users of any CF edition (6+) or CFML engine can use tools like SeeFusion and FusionReactor, which have always had the ability to monitor database interactions by "wrapping" the datasource to be monitored. FusionReactor engineer John Hawksley has posted a recent article specifically on monitoring CF9's ORM interaction, in the FR Devnet site, Using FusionReactor's JDBC Driver Wrapper With ColdFusion 9 ORM. Its concepts would apply to any ORM, of course.

Similarly, I've written generically about its database monitoring feature in What is the FusionReactor datasource monitoring feature? Why would I use it? Powerful stuff. As I point out in that article, the concepts discussed apply as well to SeeFusion's ability to monitor queries by wrapping datasources.

CF Enterprise, with a caveat

I also mention in that article that those running CF 8 (or now 9) Enterprise will find that it has built-in wrapping of datasources, such that its Enterprise Server Monitor reports database interactivity, so that can also monitor the ORM interactions.

A key difference for FR and SeeFusion over that, though, is that while they all have means to highlight queries that exceed certain limits, only FR and SeeFusion have the built-in ability to log every DB interaction (FR to text logs and SF to a database, and they both do so with low overhead). The CF Enterprise Server Monitor has no logging ability at all, and while you can set its interface to lower the threshold for what active queries to show, it has no means to show queries which have executed in the past.

It's just that it can be useful sometimes to see every single DB interaction for a request (or all requests), and it may be all the more interesting for discovering/observing what's happening with ORM interactivity.

Another alternative CF feature

Still another little-known feature for spying on JDBC interactions in CF is by way of the JDBC "spy" feature, which does in fact allow logging of all JDBC interactions mde from within CF. This feature was first enabled by way of the DataDirect 3.5 driver update which was made available (as an optional upgrade for 6 and 7) in the CF 7.02 timeframe. I wrote about the Spy feature back back in Aug 2006.

Since then, CF 8 (and now 9) offer it instead as a new "log activity" option in the "advanced settings" for a datasource definition in the CF Admin (which is disabled by default). I pointed this out in another entry from 2007 as one of many easily missed changes for the CF 8 Admin.

This "log activity" output is not as easy to interpret as FusionReactor's logs, and can indeed be voluminous (moreso than FR's), so be careful. Anyway, it's one of the several ways you can monitor JDBC interactions between CFML and your DB engine. Again, any of these may be useful for monitoring any of your CFML/database interactions.

Generic DB Monitoring tools

Indeed, it's worth noting finally that while the focus here has been watching the DB interaction from CF (and the ORM framework) to the database (by watching the JDBC traffic going out of CF and returning), you could just as well watch the DB interactivity from the DB's perspective instead (watching it coming and and being returned).

There are many tools that can monitor database processing, available for each of the major databases (free and commercial). I list several such tools in one of my CF411 section, Database/SQL Monitoring Tools.

Hope all that's helpful, whether you use ORM or not.

For readers not familiar with FusionReactor, it's a server monitor (and more) for CF, Railo, OpenBD, and indeed any J2EE/Java EE server. I've written and spoken about it a lot. Here is the link to the category of my blog entries about it.

As for 3.5, here is what has been shared in an email to their customers. I wanted to pass it along to everyone:

WHAT'S NEW

EXTENDED PLATFORM SUPPORT - FusionReactor 3.5 now supports ColdFusion 9 plus a range of new servers and operating system platforms - including Windows 7, Windows 2008 Server R2, Mac OS X 10.6 "Snow Leopard", Railo 3.1.1 and JBoss 5.1.

IMPROVED INSTALLER - supporting 64 bit Windows machines

INCREASED MONITORING PERFORMANCE - FusionReactor continues to be the ColdFusion production monitor of choice because of its incredibly LOW overhead of less than 1%!

EXTENDED FRAPI INTERFACE - FRAPI, the FusionReactor API, gives you the ability to access FusionReactor functionality from your ColdFusion pages. This interface has now been extended to include additional Request information.

NEW AMF PROCESSOR - Action Message Format (AMF) is the file format used with Flash Remoting and applications such as Flex 2 and 3. FusionReactor has a completely new AMF processor supporting externalizable Objects.

A NUMBER OF MINOR ENHANCEMENTS AND IMPROVEMENTS - With every new release we continue to extend FusionReactor to make it even more stable and secure.

Click here to see the FusionReactor 3.5 Release Notes and Resolved Issues http://www.fusion-reactor.com/support/kb/FRS-230.cfm.

To upgrade, please download FusionReactor 3.5 from the FusionReactor download pag.e

Click here to download 3.5: http://www.fusion-reactor.com/fr/downloads.cfm

If you have any questions or feedback please email sales@fusion-reactor.com.

Basic CF Monitoring in Nagios

First let me share that if you've not explored monitoring CF with Nagios at all, here's at least one blog entry discussing that.

Of course, you can easily use Nagios primarily to report whether CF's up or down, but you can also monitor basic statistics such as are provided via CFSTAT (also available on Windows via Perfmon, which also exposes generic stats about all processes), as demonstrated in that blog entry.

But you may have noticed that, if you run the Multiserver (multiple instances) version of CF, those CFSTAT and Windows Perfmon Stats aren't available on that version of CF.

So what to do if you wanted to monitor more about what's going on inside of CF?

FusionReactor Nagios Plug-in

If you're running FusionReactor Enterprise, you can now get that additional information via the free FusionReactor Nagios Plugin. It's a perl app that when implemented will expose certain key statistics in a way that Nagio can process them (and some are things that CFSTAT and the CF Permon stats don't report):

• Count of current running requests, and total count of all requests run
• Count of request queued by FusionReactor
• Average request runtime (since server start), and Recent request runtime (in past 60s, by default)
• Count of recent slow pages
• Memory free in bytes and percentage
• Memory Total and Max
• Instance and System CPU Use percentage
• Count of recently completed JDBC requests

Note that this is technically community-contributed feature (from an Intergral employee, David Stockton), not a supported feature.

If you're running or are interested in FusionReactor Enterprise, check it out.

Besides a fresher appearance there's improved organization and navigation. There are high-level views of the products (available on the front page, with tabs along the bottom for each product) and more detailed pages on each (FR and FD), with product tours, benefits, faqs, and much more. This breakdown of high-level and detail views applies as well to discussions of the Air Enterprise Dashboard and their Services (consulting, training, etc.)

Through the updated web site you can also learn about many other resources available, including articles, videos, and blogs by them and others; product docs, screenshots, FAQs and more; info on customers and free/paid support; community tool contributions, and lots more.

The folks behind FusionReactor/FusionDebug are really great. They're very open to communications and always welcome (and act effectively on) feedback. I've been a long-time fan, so much that I work with them directly on doing training and consulting based on their products (while remaining an independent consultant.) It's their very openness and willingness to listen to feedback that brought me to working with them more and more closely over the years. I'm glad to help others take notice of the company and their great products.

Well now you can, if you have FusionReactor, by way of a simple 2-step process to configure CF to do this. You can learn more about it in this article Capturing ColdFusion's Debug Output in FusionReactor by Darren Pywell, CTO of Intergral (makers of FusionReactor).

In it, he explains what this is all about (a combination of using FR's API and the "markers" feature of the FR Request Detail page, in conjunction with CF's built-in feature to let you add and use new debugging templates). More important, he gives you all you need to know to set things up, from the simple snippet of code needed (downloadable) to a walkthrough of the simple steps needed to configure CF to hand the debugging output to FR. You can be up and running with this new capability in a matter of minutes (skip to the bottom for the "fast track" steps he offers, for proof.) Check it out.

But what about the performance impact?

And yes, he addresses briefly the performance and memory implications of using such a feature in production. You should certainly take care to ensure that doing this isn't causing any harm, especially if doing this in production. That said, I see lots of shops that leave debugging turned on and use the IP address limiting feature in the CF Admin, which many will argue is equally detrimental. I think a point to make is that the negative impact may be more "theoretical" to some than to others.

The bottom line, as he recommends, is that you should test such things before rolling them into production. Sadly, many shops can't or don't bother with testing (which is very unfortunate).

If you can't test the impact, measure it...with FusionReactor

At least then you should try to have some measure you can watch to see if processing is being in any way harmed, whether it's tracking fewer requests being processed per day, requests taking longer to run each time on average, more CPU being used by CF per day, and so on.

The very good news is that those who have FusionReactor can use FR's own tremendous logging to help report this kind of information. I talk about how to report against that using a tool like Microsoft's free Log Parser, in this page on the google group for FusionReactor.

I'll do a future blog entry on the tremendous logging that's possible. It far exceeds anything provided by any other tool, including the CF8 Server Monitor (which does no logging at all), and it does it with very low overhead.

So if you're using FusionReactor, check out the debug tool. It's very easy to enable and disable (via the CF admin, once you've added the new debugging template). It's also a useful demonstration of the FRAPI.

And if you don't have FusionReactor, check that out, too. I use it or help people use it every day to solve CF problems. It's much more than "just a monitor". For more info, see the site's many resources (brief feature highlights, docs, online help, a live demo, mailing list, and more). See also some of the other entries I've done here on FusionReactor.

Some of the Improvements

Here are some hidden gems among the improvements.

• Support for 64-bit CF and JVM -

  Pretty self-explanatory

• Reports if you have any URLs set to be ignored

  I'd asked for this feature. You will now see an indication in the Running Requests, Request History, and some other pages when you have set any requests to be ignored by FusionReactor "restrictions". This deserves some explanation.
  
  This is referring to a setting made in the final menu option in the FR interface, labeled simply FusionReactor. It's an option (that many miss, I think) called "Restrictions" which can be used to prevent some URLs (exact match or regular expression) from being monitored at all by FR. This is not to be confused with the similar Crash Protection restrictions feature, which can prevent some pages from being tracked for CP. This other setting is more global, controlling whether they're monitored by FR at all.
  
  I felt that users might not realize that they (or someone else) may have marked some URLs to be ignored by this FR Restrictions feature. The Intergral folks kindly responded by adding this improvement, which just adds some text to the bottom of the affected pages where URLs would be shown, to indicate that some may not be shown since the "restrictions" feature had been used. It does not appear if there are no restrictions enabled, of course.

• Can re-run installer to add FR into additional instances/servers

  This is a nifty enhancement, especially for those running FR Standard. The FR installer normally offers to install only into one installation of CF (or other supported J2EE servers) on your machine. If you wanted to install it into another CF installation (perhaps 7 and 8, or one of multiple instances) or another supported CFML or J2EE server after installing into the first, in FR Standard you previously had to add it manually (With FR Enterprise, you could have used its available Instance Manager feature.)
  
  Now with FR 3.0.1, you can re-run the FR installer and there will be an option offering to install it into a newly selected instance (meaning another CF deployment or other CFML or J2EE server) on this machine.
  
  (I'm being careful not to call them CF "instances", since that could be read to mean an instance in the Multiserver mode of CF. FR supports those, but when it says "instance" it means either one of those or a Standalone/Server deployment of CF, or any other supported CFML or J2EE server.)

• New URL params for redirected CP URLs -

  If you choose to have users redirected to a new URL (if their page is aborted or queued too long by FR crash protection), you can optionally have that new URL include URL parms that describe what went wrong to trigger the redirection.
  
  In addition to the current detection_method, threshold_value, and actual_value, there are 4 new values included on the URL: source_url, source_url_params, source_method and start_time. For more on all of these, see the online help for the CP Settings page.

• Port checker on the installer -

  When newly adding FR into an instance, you're prompted to choose a port for FR's built-in web server to use for you to access the interface. It now checks and reports if the port selected is already in use.

• socketWrite0 protection -

  This is an experimental (and optionally enabled) new feature, which deserves its own discussion as a separate entry which I hope to offer in coming days.

Again, this is just some of the improvements. To see a list of all them (and bug fixes), see the release notes.

Take care in reading about the items

As I said at the opening, the list of items in the release notes is pretty terse. They're basically just a few words per change. I'll suggest you be careful in reading them, though.

As if often the case in bug fix lists (though these are more), they appear to really be the brief description of each change as was entered by the developers in a source control system. The bummer in then using these for release notes (as Adobe has sometimes, too) is that the wording could be misconstrued.

For example, one improvement says, "When a server has an expired licesne [sic] and the server is selected within the ED a runtime error is thrown". Ignoring the typo, this reads as if it's an improvement that an error is thrown. Instead, this is saying that the problem is fixed, really. And the "ED" means Enterprise Dashboard. So just keep this in mind while reading the release notes.

It's understandable that some may think that a release notes list of enhancements doesn't need much more. Sometimes the CF release notes are equally terse or unclear. Hey, they have people like us who will blog clarifications. :-)

But I'll also point out that the FR folks are great about support, so post any questions to the FusionReactor mailing list (http://groups.google.com/group/fusionreactor) and not only will you get an answer, but other users will benefit. (Or feel free to drop any comments/observations here, too.)

Check out the JDBC Wrapper Tool. For now it's technically a "community contributed" tool (not in labs, and not from Intergral as a formal product). But it was written by an Intergral employee, Dave Stockton, and the company is very interested in your feedback about it. (Intergral is the company behind FusionReactor and FusionDebug.)

Check out the page for the download, docs, screenshots, a change log, tested databases and CFML server configirations, and more. (And note that it's not just for CF, but is intended to work with others. Railo support is listed coming soon **that's an update from the original post, where I said Railo support was there**.) If you can try it on other DBMSs and CFML servers, they'd welcome that input.

For those who have needed to monitor/wrap datasources, especially many, or on many servers, this is a real gem. Whether you are or not using a supported server, it's worth a few minutes of your time to check things out. I've used it, and it worked great.

For those not sure what this datasource monitoring/wrapping is all about, check out an entry I wrote earlier this year, What is the FusionReactor datasource monitoring feature? Why would I use it? Powerful stuff!. Both that and the docs I link to will give you more info. The datasource monitoring really is really a great tool for troubleshooting and trend analysis.

FusionReactor: ColdFusion Server Healthcare (and What's New in Version 3) (PDF)

Thanks, folks! I hope it might serve as a useful intro for those who've not seen the tool (whether in its older or newer version), which can be used to monitor CF 6, 7, and 8. That CF8 support is important not only for those running CF8 Standard, which doesn't have the CF8 Server Monitor, and also even for those who can run the CF8 Server Monitor, as there are some useful benefits of using it even on CF8 Enterprise). Note that FR also works with Railo and OpenBlueDragon, as well as BlueDragon/J2EE and indeed any J2EE application (including LiveCycle Data Services) and any J2EE engine (JBoss, Tomcat, WebSphere, etc.)

The article can also serve as a review of features that those already using the tool may have missed, or you can focus on what's new in FR 3 (quite a bit).

Check out the article, and the tool, including the available live demo in addition to the 10 day trial. I'll note that the live demo is running at a lower level of authority (one of the new features of FR 3), so you don't see and can't do all with it that you might as a full admin user.

I'll arrange to do a demo of the tool on an upcoming CF Meetup session, whether on its own or along with other monitoring tools for CF which I've discussed before.

I've written about both these tools quite a lot in the past, if you're interested in exploring my respective blog entry categories: FusionReactor and FusionDebug.

I've also written about them in the FusionAuthority Quarterly Update, if you happen to get that great magazine. Most recently I did, "FusionReactor: ColdFusion Server HealthCare (and What's New in Version 3)", in Volume ii Issue iv (Summer 2008), and a couple of years ago I did, "FusionDebug Explained: Interactive Step Debugging for CFML", in Volume i Issue ii (Fall 2006).

I'll also have news coming up about more cool stuff coming from the folks, including FusionAnalytics and FusionReactor Enterprise Monitor, both discussed in the FusionReactor Labs, as well as the recently released AIR Enterprise Dashboard, recently promoted from the labs to an available product.

Until then, go enjoy the discount code to get these great products, which help you with debugging and monitoring whether you run on CF 6, 7, or 8. (For more on why they still matter even with CF 8, see this previous entry.)

What is the datasource monitoring feature? Why would I use it? Powerful stuff!

The datasource monitoring feature, referred to in the docs as the "JDBC wrapper", is a low-overhead way to configure FusionReactor so that it captures information about each query (against a given datasource), for every request, in detail and in aggregate. It captures and reports:

• the SQL statement (including CFQUERYPARAM values)
• the execution time (time spent in the database, and time spent getting it from the database to CF)
• the number of rows returned
• the CFML template and line of code where it was executed
• and more

This query information is tracked per request and also in aggregate across all requests (in the graphs in the Enterprise Dashboard and System Metrics pages, the Longest JDBC page, and other places.)

It's really powerful information to have when you're trying to understand why and how your page, application, or server it performing.

You can see more about using the information in these feature focus pages at the FR site: What are System Metrics? and Resource Graphs, among others.

As I said in the last entry, you can learn more about setting up the FR JDBC wrapper feature (including the simple steps on how to implement it--which have been updated in the FusionReactor 3 docs) in the Tutorial (pdf) and User Guide (pdf).

What's the overhead of this datasource monitoring? What's it doing?

That's a natural and reasonable question. The folks at Intergral (makers of FusionReactor) say that it's very low overhead, and they've done considerable testing (millions of requests before each release) to confirm it.

Maybe it would help to understand how it works. It's quite simple, really. When you configure a datasource to be wrapped (per the instructions pointed to above), you're basically just implementing an alternative JDBC driver (FusionReactor's) to literally "wrap" the driver that was being used originally by your datasource.

Your code continues to use the same Datasource Name, but the change of the datasource causes CF to pass the SQL first to the FusionReactor JDBC wrapper, which takes note of the starting time, the SQL statement, etc., and then passes it the query on to the real (original) database driver. That then passes it to the DB, and gets the result, and before it's passed back to CF, it goes back through the FusionReactor wrapper, which notes the execution time, recordcount, etc.

So you see, the wrapper is really quite lightweight. It's just an observer, watching what's going to and from the database. The info is then written to FusionReactor's JDBC log (if enabled) and stored in FusionReactor's small memory space for presentation in the various interfaces that show JDBC status information. Again there's more about this in the documentation.

Some other tips on the JDBC Wrapper

To see information on what queries were executed (if the datasource was wrapped), see the bottom of the request details page (for either a running request or the request history page). Further, note the available JDBC tab in that request details page. That will show each SQL statement and the details (time, count, etc.). The bottom of that page will also show counts and averages for all the queries in that request.

You'll see at the bottom of that page that you can also configure the JDBC settings page to restrict how many queries are shown in the JDBC request details, and you can limit it to only those exceeding a certain amount of time, as well as whether to show them in chronological order or executiontime order.

I'll note as well that if you do implement the JDBC wrapper feature, you'll probably want to enable JDBC logging, which is disabled by default. This writes all the detail above to a log file, for each SQL statement, which can be great for either post-mortem analysis (after a crash) or for trend analysis (perhaps across several restarts). You can choose to limit the logging to queries slower than a set number of milliseconds (separate from the control above about what queries to show in the request detail page.)See the JDBC>JDBC Settings option in the left nav bar of the FR interface.

The wrapper configuration for each DSN itself also offers still more features that many miss, so check out the docs. The Tutorial concludes with an example of using the rowlimiter feature to put the brakes on a rogue page, while the user guide details this and all the available features of the wrapper.

Learning more about FusionReactor

For more on FusionReactor in general, see their web site or my past blog entries on it.

(And yes, before anyone else would point it out in a comment, both the CF 8 Server Monitor and SeeFusion also provide this sort of JDBC query information. In the CF 8 Monitor, it's built-in (no need to configure the DSNs at all). And in SeeFusion, there's no need to manually configure each DSN, as there's a one-button configuration option. Intergral are said to be working on something similar for a future release of FR, so some of the concepts above apply across the monitors.)

FusionReactor works with ColdFusion Standard and Enterprise in version 6, 7, and 8, as well as Railo, Open BlueDragon, and BlueDragon/J2EE, and indeed any J2EE application server or Servlet engine. (BTW, technically, the term J2EE has been obsoleted by the more version-neutral Java EE or JEE. So many things in CF docs and related tools still refer to it as J2EE simply because most CFers aren't paying that close attention to such details, so there's been little motivation to make the clarification.)

PS: Why is it called the JDBC wrapper feature, instead of the datasource monitor?

That's an interesting question. It might feel a little clumsy calling it the JDBC wrapper. I wonder sometimes if CF users may miss the feature. Hopefully the explanation above about how it works helps. But you may wonder why it's not called the "datasource monitor", which might be friendlier for CFers.

But here's the thing: as I said a moment ago, FusionReactor is not JUST a CF server monitor. It can be used by any J2EE server or servlet engine. And not all J2EE app servers would use a concept of a "datasource".

Consider a Tomcat developer who might write a servlet or JSP that calls a database. They could leverage this JDBC wrapper feature (and all of FusionReactor's features) just as readily, though instead of changing the configuration of a datasource they would modify the JDBC URL in their code or a config file. That said, there are some J2EE servers that do use JNDI datasources.

So the makers of FusionReactor (Intergral) have to walk a bit of a fine line about labeling features in a way that might apply only to CF.

It's amazing, really, that one might not otherwise have any reason to notice that the product is designed to be used by either CF or J2EE shops. Most of us think it's just a CF monitor. It's much more.

And BTW, it's licensed per server, so you can install it on as many engines as you have (that it can monitor) on a single machine. In fact, you can even configure a free Tomcat engine just to be a monitor of other FusionReactor (Enterprise) instances.

This is something that Intergral is now offering (in beta) as a new packaging of the tool as "FusionReactor Enterprise Monitor - FREM". You can get it (or learn more) at their Fusion Labs site. I'll have more on that in a later entry.