Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Using CF9, IIS, and MSSQL, is there a way to monitor SQL commands sent by a script? Something that shows the raw query, preferrably.

I got this idea after seeing that WEBrick and Ruby on Rails actually dumps the queries being generated to the console and was wondering if CF9 had something similar.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You can use SQL Server Profiler to do this. You'll have to set up the trace to capture exactly what you want, but it will definitely give you the raw query.

Have a look at this MSDN article for information about Profiler.

share|improve this answer
    
Thanks squillman! I know it seems like a rather fundamental request. I assume the process is similar for 2005? –  Nic Aug 18 '11 at 20:12
    
That would be yes. Profiler's been around nearly forever. –  squillman Aug 18 '11 at 20:19

There are multiple solutions. As noted by @squillman, you can view it in SQL Server via Profiler. But just within CF, you have others choices.

  • You can turn on CF's debugging output to view query info at the end of the request. That has pros and cons, of course. If in development, it's generally good. In prod, best to avoid it (even if you limit the IP addresses that can view debug output, as many assert that even otehr users still pay the cost of the debugging being generated).
  • You can also log the info yourself, within code. Since CF7, there's been a new RESULT attribute, which hold a struct with data about the query, including the SQL statement. You could write that out with CFLOG, CFTRACE, and other tags (or related script functions).
  • You can also have CF log it itself. A little-known feature was added in CF8, a new checkbox within the "advanced settings" for a datasource definition (in the CF Admin) lets you "log activity". Be careful, it's quite verbose.
  • You can also use built-in monitoring features within the various CF Server Monitors, whether the free one built into CF Enterprise (or Developer, but not Standard), or the 3rd party ones, FusionReactor and SeeFusion. The CFSM focuses only on slow queries, though. FR and SF can show you every query within a given request (or all queries on the server, including those not associated with any query, like the client variable purge, and so on.) One think I like about FR in particular is that it logs every query (if you enable query logging), whereas SF again only logs slow queries. (Each can be changed to the other alternative.)
  • Finally, all those tools do their query logging by way of wrapping the JDBC drivers. You can also do it yourself, manually, without those tools, using any of many jdbc wrapper alternatives. Just google jdbc wrapper and you'll find many.

I also talk about all these options in more detail in a blog entry I did, which while it focused on spying on ORM queries within CF, it all applies just as well to any queries that CF generates (and on any CFML engine): Spying on ORM database interactions: Hibernate, Transfer, etc. on any CFML engine.

Finally, I'll note that as for using SQL Server Profiler, note that it too has its plusses and minuses (it can generate far more details about query available as options, including even the execution plan graph per query, which is great, but it also like any monitoring has some risk of overhead depending on how things are configured. Many resources exist addressing that concern and solutions.

Hope that's helpful.

share|improve this answer
    
Very helpful, thank you - I actually needed to monitor the production server results and couldn't make edits to the expansive codebase (unfortunately). I tried to use some of the built in features before posting. Server Profiler was the most transparent of the options, so I ended up using that. I really appreciate your insight, though. –  Nic Aug 18 '11 at 23:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.