I'm delighted to announce that I'll be presenting at next week's RIAcon conference in the DC area, August 6-7 2011.

My session will be "CF911 ColdFusion Performance Report 2011", a new talk/concept I've created. Here's the description:

Starting a new tradition, veteran CF troubleshooter Charlie Arehart will present a review of the performance aspects of making various choices when working with ColdFusion, especially in recent version(s) of ColdFusion. Leveraging the important value of real load testing (as opposed to the less accurate conclusions from "large loop" testing), Charlie's annual report will help attendees appreciate the performance-related improvements of new/changed features, as well some older features where choices can make an important impact. Depending on the timing of the release of "CF next", the session may cover its new features, but it will certainly cover some things new in CF 9 and 9.0.1.

As I note there, I hope this may become an annual event which I might present at this and/or other conferences. (It's an idea rooted in a similar presentation made by a former colleague, from my first IT career from 1982-1997, where he presented the annual "Jim Damon model 204 Performance Report".)

About RIACon

As for RIAcon, I hope you're considering it. Phil Nacelli and the folks at AboutWeb have been working hard to put together the conference, which in some minds is kind of picking up where CFUnited left off. It will be a more intimate event, much like CFunited was when it first started.

Indeed, some will recognize that the location for RIAcon is across the street from one of the hotels where CFunited was held in its early years, right next to the Twinbrook Metro station in Rockville, MD.

A Personal Connection to the Hotel Location

Even more of a delight for me personally is that the hotel (The Legacy Hotel) is right on the land that was once the location of Congressional Roller Skating Rink (until the late 70's), where my sister and I (and many friends) spent our teen years pretty much whenever we weren't in school. Yep, I was a skating nerd: dance, figures, freestyle, and more. Here's incriminating evidence!

Did you know there's a "request execution limit" on IIS? It's 3, 10, or unlimited, depending on the version of Windows (Vista, 7, 2008) and edition (such as Starter/Home/Basic/Pro/Server).

I'll detail the limits per version/edition below.

I'll also offer a (possibly surprising) workaround that can allow you to get even more requests through IIS, even for a single web site.

(Before I elaborate on that, note that there is a separate issue if you're finding that CF doesn't let you see more than 25 requests at once. That's instead due to a setting in CF/JRun, the maxworkerthreads setting. For more on that, see this blog entry.)

That said, this is a problem which could affect anyone regardless of the app server they may be running behind IIS. (And yes, I do realize that for some, the answer to this problem will be, "see, that's another reason to run Apache." We get that. Let's just focus on this problem for those who choose/have to remain on IIS.)

Background

I was doing some load testing noticed that I couldn't get beyond 3 requests running at a time. I suspected the problem may be IIS.

So at first I tried just making the same requests against CF's built-in web server (such as might be running at port 8500, which can be enabled/configured during CF installation or manually).

Sure enough, CF could execute more requests at once (up to the "simultaneous requests" setting) through the built-in web server.

So that told me the problem had to be in IIS. I did some digging and found the documentation that affirmed what I was seeing.

(Some may recall there used to be a limit of 10 for IIS on Windows XP, so it may surprise some to run into an even lower limit on later Windows versions.)

IIS 7 does have a "request execution limit"

The first resource I found was with respect to the limits in Vista. This page says specifically: "With Starter and Home Basic editions of Windows Vista, the simultaneous request execution limit for IIS is three for Windows Vista Home Premium. ...For Windows Vista Business, Enterprise, and Ultimate edition...the simultaneous request execution limit is ten. ..With server editions of Window, IIS 7.0 has no request execution limit."

But I'm sure some reading this will be using Windows 2008. I found a page discussing its limits: for the Basic & Starter editions: 3 requests, for Premium: 3, for Pro: 10, and for Server: unlimited.

I happened to be running Windows 7 when I hit the limit, and while I've not found a document stating the limits specifically for Windows 7, I can again confirm the same limit of 3 on my copy of Windows 7 Home Premium, so I'm sure things are the same, to whatever degree the edition names parallel the other versions. (It's curious that the learn.iis.net page above does discuss both Vista and Windows 2008, but makes no mention of Windows 7.)

A workaround for this limit: multiple worker processes

With respect to this limit, here's a valuable bonus tip that I've not seen documented anywhere: the limit is really per application pool, or more technically per worker process. So you can certainly get more request simultaneously against your box by using multiple app pools.

But perhaps you want to have more requests against a single site, which obviously can be connected only to a single app pool. There's still good news: you can increase the number of worker processes per app pool in the "advanced settings" for a given app pool (right-click on the app pool), increasing "Maximum Worker Processes" from the default of 1. (Some will recognize that as being the same as what was known as "web gardens" for app pools in IIS 6.)

For those new to them, whether creating new app pools or more worker processes for an app pool, for each new worker process, you'll see a new w3wp.exe in task manager.

A caution, for those using ASP.NET, about setting more than one worker process per app pool

Finally, there's a caution to consider if you decide to increase the number of worker processes. At least back in IIS 6, I documented that if you're running an ASP.NET app using sessions that are "inproc" (or in memory), the default, there is a problem with using multiple worker processes (web gardens), in that the sessions are not replicated among the worker processes. That may not be an issue for the OP, so I'll say you can learn more at an entry I did a few years ago.

Did any of this help you? Let me know below.

This is a very interesting change in CF9 (and 9.0.1), which has slipped under the radar for the most part as far as I can tell.

Did you know there is now a setting in the DSN page of the CF Admin (for most DB drivers) that allows you to set a maximum timeout for queries against that DSN? It's a new feature enabled for the DataDirect drivers udpated in CF 9. The caveat? It is ONLY settable there, not in CFQUERY itself, which is a shame (the existing TIMEOUT attribute is not the same and generally does not work). Still, the value of this even at the DSN level is too important to ignore for some challenges. More on that (and some other thoughts) in a moment.

As for the setting, it's in the "Advanced Settings" section for a DSN in the CF 9 Admin, and it's called "Query Timeout". This should not to be confused with the older settings, "Timeout" (which is about inactive connections) or "Login Timeout" (which is about logging in to the connection). The screenshot at right shows all 3. (This blog entry continues, with more information below it.)

I've run a test, and it really does do the job, which is huge. Why? Because it's been a long-time issue that if a CFQUERY got hung up waiting for a response, that request thread (doing the CFQUERY) is then hung until the query finishes, which can sometimes be many minutes, or even hours or days, due to some odd situations. More important, a thread waiting for a query with no timeout can't be terminated (by the JVM, or CF, or the monitoring tools) because the thread was in a native thread state.

With this new option specified, if the request exceeds the timeout, the request does now fail, with a JDBC error, "Execution timeout expired." The same test does NOT timeout with the older cfquery TIMEOUT attribute.

Here are some other notes on the new feature:

• It works with most of the database drivers. I have confirmed that the setting appears in the DSN settings page for SQL Server, Oracle, MySQL (datadirect), DB2, Informix, Sybase) in CF 9 Enterprise, and in both SQL Server and MySQL (Datadirect) on CF 9 Standard.
• The timeout's specified in seconds.
• You can learn more in the CF Admin guide, in this specific page.
• Oddly, the Admin manual page above only references this new setting in that CF Admin manual is in the the MySQL settings, but again it does appear in all the drivers above.
• The manual page does even reference the other DBMSs by name in its naming the methods of the Admin API (for other DBMSs) which you can use as well, which can be used to set this default setting in the DSN programmatically.
• That said, again there is sadly no new QueryTimeout (or QTimeout) attribute for CFQUERY, so for now we can only set this at the datasource level, not per query.
• I've raised the concerns above on the CF Admin livedocs page (or whatever we are to call it now.)
• If you look under the covers (in [cf]\lib\neo-datasource.xml), there is in fact a querytimeout connectionstring that this setting controls. If only there was a way we could pass connectionstring values to the CFQUERY, we'd be golden. Some may recall use used to have just such an attribute (ConnectString), but sadly it was deprecated in CF 6. I did try it, to no avail.
• I will raise have raised a bug with Adobe to get a new attribute for CFQUERY related to this. When I do that, I'll report it here. It's bug 83592. Please add your vote of support for it.
• There's another page on this Admin setting, in the CF Dev Guide, for those who may be interested in following any other possible places where this feature may be discussed (in the comments there).
• If you want some code to use to test a request waiting a long time for the DB to return, most databases offer a statement that tells the DB to wait for some time. In SQL Server, that's WAITFOR DELAY 'hrs:mins:secs'. Just use that in a CFQUERY, assuming your DSN definition doesn't limit what SQL statements you can use, in the 'Allowed SQL' section of the Adcanced Settings page.

Why is this setting important?

I think this is a very important setting, and though it has been a hidden gem, it seems, it's one that people should consider. That's why I've flagged this in my CF911 category.

If you're suffering situations where requests are hanging due to long-running queries, and you have not been able to solve the real root cause for why they are hanging (which I always recommend first and foremost), then at least this option can help avoid a situation where queries can run without time limit. An Admin can decide that no queries should be allowed to run more than x seconds.

With that power comes responsibility, of course, and caution. You wouldn't want to preclude someone being able to run a query that really needed to take a long time. That's why it's really better if this was settable on a per-query basis. (And no, the CF page timeout settings are NOT the solution here, because again as I said above, they cannot timeout some kinds of long-running tags, like CFQUERY, CFHTTP, CFINVOKE of a web service, etc.)

What one could do, though (for now), is create different DSNs, where one could be used for most query processing, and another could be used for long-running requests. Yes, it's ok to have 2 DSNs point to the same DB. This same technique has been used when wanting to have most queries run against one DSN with a limited set of "allowed SQL" (per the DSN advanced settings) while another DSN has unfettered SQL access.

Hope this is helpful to some. Let me know what you think, whether this was helpful or if you feel I left something out. Especially please let me know if you may know of a way that we can indeed pass querystring values on a per-query basis.

Some time ago I implemented some code on my own site to throttle when any single IP address (bot, spider, hacker, user) made too many requests at once. I've mentioned it occasionally and people have often asked me to share it, which I've happily done by email. Today with another request I decided to post it and of course seek any feedback.

It's just a rough cut. I haven't thought it through thoroughly (wow, how's that for an alliteration!). Still, while I know there are couple of concerns that will come to mind for some readers and I try to address those at the end, it does work for me and has helped improve my server's stability and reliability.

Background: do you need to care? Perhaps more than you realize

As background, in my consulting to help people troubleshoot CF server problems, one of the most common surprises I help people discover is that their servers are often being bombarded by spiders, bots, hackers, people grabbing their content, rss readers, or even just their own internal/external ping tools (monitoring whether the server is up.)

It can either be that there are many more than they expect, coming more often than they expect, or they may come extremely fast to your server (even many times a second). This throttle tool helps deal with the latter.

Why you can't "just use robots.txt and call it a day"

Yes, I do know that there is a robots.txt standard (or "robots exclusion protocol") which, if implemented on your server, robots should follow so as not to abuse your site. And it does offer a crawl-delay option.

The first problem is that some of the things I allude to above aren't bots in the classic sense (such as RSS readers, ping tools). They don't "crawl" your site, so they don't regard that they need to be told how/where to look. They're just coming looking for a given page.

The second problem is that some bots simply ignore the robots.txt, or don't honor all of it. For instance, while Google honors the file in terms of what it should look at, my understanding is that it instead requires you to implement the webmaster toolkit for your site to control its crawl rate.

Then, too, if you may have multiple sites on your server, the spider or bot may not consider that in deciding to send a wave of requests to your server. It may say "I'll only send requests to domain x at a rate of 1 per second", but it may not realize that it's sending requests to domains x, y, z (and a, b, and c) all of which are one server/cluster, which could lead a single server to in fact be hit far more than once a second (in that scenario). It may seem that's an edge case, but honestly it's not that unusual from what I've observed.

Finally, another reason all this becomes a concern is that of course there can be many spiders, bots, and other automated requests all hitting your server at once sometimes. My tool can't help with that, but it can at least the other points above.

(As with so much in IT and this very space, things do change, so what's true today may change, or one may have old knowledge, so as always I welcome feedback.)

The code

So I hope I've made the case for why you should consider some throttling, such that too many requests from one IP address are rejected. I've done it in a two-fold approach, sending both plain text and an http header that is appropriate for this sort of "slow down" kind of rejection. You can certainly change it to your taste.

I've just implemented it as a UDF (user-defined function). Yes, I could have also written at in all CFscript (which would run in any release, as there nothing that couldn't be written in script in that code--well, except the CFLOG, which could be removed). But since CF6 added the ability to define UDFs with tags, and to keep things simplest for the most people, I've just done it as tags. Feel free to modify it to all script if you'd like. It's just a starting point.

I simply drop the UDF into my application.cfm (or application.cfc, as appropriate). Yes, one could include it, or implement it as a CFC method if they wished.

Then I call the UDF, using simply cfset limiter(), as shown below. That's it. No arguments need be passed to it, unless you want to override the defaults of limiting things to 3 requests from one IP address within 3 seconds.

Note that since the UDF relies on the application scope, you need to place the call to it AFTER a cfapplication tag if using application.cfm.

Caveats and more

There are definitely a few points to consider, and some concerns/observations that readers may have.

• First, BlueDragon fans will want to point out that they don't need to code a solution at all (or use this), because it's had a CFTHROTTLE tag for several years. Indeed it has. I do wish Adobe would implement it in CF (I'm not aware of it existing in Railo). Until then, perhaps this will help others has it has me.
• More important, some will be quick to point out a potential flaw in the approach of throttling by IP address is that you may have some visitors who are behind a proxy where they appear to your server to all be coming from one ip address. Fair enough. This is a dilemma that requires more handling. For instance, the BD CFThrottle tag implements this with a TOKEN attribute allowing you to key on yet another field in the request headers. I didn't choose to bother with that, as in my case (on my site), I just am not that worried about the problem. You may need to, so beware. Again, the log will help you determine how much it's doing any work at all.
• And some may recommend (and others may want to consider) instead doing this throttling at the servlet filter level, rather than CFML (something I've written about before .) Yep, since CF runs atop a servlet engine (JRun by default), you could indeed do that, which could apply then to all applications on your entire CF server (rather than implemented per application like above.) And there are indeed throttling servlet filters, such as this one. Again, I offer this for those who aren't interested in that.
• And of course, an inevitable question/concern some may have is, "but if you slow down a bots, might that that not affect what they think about your site? Might they stop crawling entirely?" I suppose that's a consideration that each will have to make for themselves. I implemented this several months ago and haven't noticed any change either in my page ranks, my own search results, etc. That's all just anecdotal, of course. And again, things can change. I'll say that of course you use this at your own risk. I just offer it for those who may want to consider it, and want to save a little time trying to code up a solution. Again, I welcome feedback if it could be improved.
• Now, one other gotcha to consider, if you implement this and try to test it: some browsers have a built-in throttling mechanism of their own and they won't send more than x requests to a given domain from the browser at a time. I've spoken on this before, and you can read more from yslow creator Steve Souders. So while you may think you can just hit refresh 4 times to force this, it may not quite work that way. What I have found is that if you wait for each request to finish and then do the refresh (and do that 4 times), you'll get the expected message. Again, use the logs for real verification of whether the throttling is really working for real users, and to what extent.
• There is of course another nasty effect of spiders, bots, and other automated requests, and that's the risk of an explosion of sessions which could eat away at your java heap space. People often accuse CF of a memory leak, which it's really just this issue. I've written on it before (see the related entries at the bottom here, above the comments). This suggestion about throttling requests may help a little with that, but it really is a bigger problem with other solutions, that I allude to in the other entries.
• Finally, yes, I realize I could and should post this to the wonderful CFlib repository, and I surely will. I wouldn't mind getting some feedback if anyone sees any issues with it. I'm sure there's some improvement that could be made. I just wanted to get it out, as is, given that it works for me and may help others.

Besides feedback/corrections/suggestions, please do also let me know here if it's helpful for you.

I think most folks know that the underlying database drivers in CF are from DataDirect. Well, they've announced new "Type 5" drivers. While you would have to buy and install them separately from those built-into CF (for now, as Adobe has not yet certified CF for use with them), I think some people may want to give them serious consideration even before then.

Several Performance Advantages, and Failover As Well

Among their advantages (over the older Type 4 drivers that are built into CF) are faster performance and lower memory use (more here), as well as greater tunability in general (discussed among other features between 4 and 5), and still more.

For some, more compelling still is the new driver-based failover, to have the driver detect a connection failure and switch communications to another DB instance (much like CF 8 Enterprise added the ability to do the same with mail server configuration in CF). This could be a huge improvement for many.

Getting More Info, Trials, Purchase

You can learn more about (and purchase or get a trial of) the drivers at http://www.datadirect.com/products/jdbc/index.ssp. The page also includes links to more on the benefits of type 5, comparison to Type 4, and so on. (Sadly, I just missed their cleverly titled "These go to 11" webinar earlier today. Perhaps they'll offer another soon.)

When Might Adobe Bundle Them in CF?

You may have caught that I referred to "purchase" of the drivers. Sadly, no, the drivers are not free. Adobe has licensed and included them in past releases of CF. So when Adobe may modify CF to bundle the new drivers?

Some have heard that Adobe's working on an updater for CF 9 (mentioned publicly at cf.Objective() among other places). Can we expect the drivers to be updated in that release? It's hard to say. In the 7.0.2 timeframe, the then-new drivers (3.5) came out just after the release and so were not included in the updater but instead were mentioned as a manual option in the CHF 1 technote. (Many missed that and to this day I suspect many shops still running CF 7 still have updated those drivers.) The point is, they did not include it with the updater (as I blogged about at the time in this blog entry.)

I can understand why they may not bundle new DB drivers with a CF updater: with updaters, they have to rebuild and retest all the installers on all the platforms, and updaters are generally not about adding "new features". It's a slippery slope as to whether driver updates are "new". It may also be a matter of timing, with respect to when Adobe learned about the drivers (likely sooner than most of us) and whether they found them compelling enough to consider, and at what cost in terms of backward compatibility. It's worth noting that the Type 5 drivers do assert to bring all their benefits "without code changes", so who knows?

The bigger question for some may really be simply, "I'm ok if they make me install it manually. It would just be nice to not have to license them ourselves." That's a fair point, and we just won't know until Adobe does address this issue (perhaps as they did in 7.0.2 CHF 1.) Until then, we have to wait and see.

But again, I do think the update compelling enough that shops with interest in performance should at least consider testing out the free trial to see how things go, or to prepare for the possibility of leveraging that new failover feature. (It also remains to be seen if Adobe may somehow try to force that failover feature to be an Enterprise-only feature, just as the mail server failover is.)

About the Company and Product Name Variations

Some may wonder if they visit the site for the drivers and notice the company being named Progress, when it used to be Merant. You can read about the fairly long chain of changes in the company history page.

One other thing worth noting is that the product is sometimes referred to as "DataDirect JDBC Type 5 drivers" (as I show in the subject here) and yet also sometimes as "DataDirect Connect for JDBC". They're the same thing.

One Boo Boo on the DataDirect Site

As I was tooling around their site, I happened on the page about use of the tool with JBoss servers. They said this:

The top web and application server vendors embed or recommend DataDirect JDBC drivers as part of their J2EE certification strategies (JBoss, IBM WebSphere, Sun Microsystems, Oracle BEA, and Macromedia.)

Can you spot the mistake? There is no more Macromedia, of course. I tried to find a page on the site to send a comment, but their Contact page didn't seem to offer a suitable option for such a comment. Oh well, so I am adding it here in case someone who cares can get something done about it.

Finally, I'll point out another useful page on their site, not new to the Type 5 drivers, but a helpful list of performance tips.

Ever wanted to count how many sessions are active on your server, in total and per application, whether on CF 7 or 8? And regardless of whether you're using CF's regular sessions or the "new" J2EE sessions feature introduced in CF 6? Would you be surprised to find you could have a shocking number of active sessions?

Here's a nice simple solution, the free ServerStats tool, that provides that info. It's from Mark Lynch and while a couple years old still works just fine. Besides printing out the total count of sessions and the count per application, it also some information about CF memory use, number of processors, and more.

Of course, some will want to point out that the CF 8 Server Monitor offers session and memory info, but for those not on CF8 Enterprise, this is a useful alternative. Also, some may want to point out that the solution described here uses internal objects that may be disabled (and are also undocumented and unsupported). I discuss both these points below. There are also some other approaches people take, which I also mention briefly.

Why care about number of sessions?

So why care so much about how many sessions are active on a CF server? If there are 10 or 20, or maybe a 100, does it really make a difference? No, not likely. But what if you find that there are 90,000? I'm not kidding. I was helping someone just last week when we discovered this. It's not as unusual as you may think.

Often when I'm helping people solve problems with their CF servers, one of the first things I want to help them discover is just how many sessions they have active at any time. With the potential impact of spiders and bots creating huge numbers of sessions, which could have an impact on memory and also on other aspects of performance, it's definitely one of the first things to look into.

So how's Mark's code tracking them?

Mark's entry is a summary of things he'd discussed in a few blog entries about how to leverage internal CF/Java objects to provide the info, and that one entry offers a downloadable zip with a single CFM file and a directory of CFCs and custom tags. You can just extract the zip into a web-accessible directory and run it. No need to create custom tag mappings, etc.

Many folks have known about and blogged about these internal CF/Java objects, coldfusion.runtime.SessionTracker and coldfusion.runtime.ApplicationScopeTracker, over the years. They're undocumented and unsupported, so you use them at your own risk, but again I've confirmed that Mark's code works against CF 7 and 8 (and I'll assume 6 as well.) You can google to learn more from him and others on these.

CF8 Enterprise has other solutions

Of course, those on CF 8 Enterprise (or Developer) have still other approaches to this, whether in the CF8 Server Monitor (see its active sessions page) or the Admin API's servermonitoring.cfc. But if you're not CF8 Enterprise (and even if you are) this solution can help.

(For any who may wonder, no, neither FusionReactor nor SeeFusion offer this info, since it's technically internal to CF. They could call upon these methods, but since they're undocumented, I can understand that they choose not to. And while they could call upon underlying J2EE objects for J2EE sessions, those would not work for those who don't use J2EE sessions.)

JRun Metrics and other solutions

And yes, there's still another way to get at least a count of all J2EE sessions, using the available JRun Metrics. While it has the benefit of tracking the info over time, it does track the session count only if you've enabled J2EE Sessions in the CF Admin, and even then it tracks only the total of all sessions, not a count per app.

Of course, there have been various solutions offered to solve the problem of session tracking, such as those that track sessions in code (within your application) by storing them in an array in the application or server scope, and so on. The nice thing about using the internal CF/Java objects is that the apps being tracked don't need to be altered at all.

Potential Security Gotcha

Of course, therein lies a potential security risk. Anyone running such code on any CF server can have access to the sort of info that these internal CF/Java objects offer. The example above (Mark Lynch's) doesn't really expose much to worry about (unless the names of apps on a server is sensitive, where one user on the server shouldn't know that another app exists on it, which may be true in a shared hosting environment.) But more important, if one digs deeper into these objects, they do expose more details, including the values of variables in session scopes across all applications. That could be considered very sensitive.

As such, there have long been a couple of solutions that Adobe has provided to enable an administrator to shut down the use of these internal objects.

First, in CF 5 and above, it's been possible to set security for the entire server (or a given app in CF Enterprise) to make it impossible to call any Java objects (yes, you could call Java objects starting in CF 4.51). To learn more about this option, called Resource Security in CF Standard, and Sandbox Security in CF Enterprise, see my 2-part article series for the Adobe Security Developer Center from Sept 2002, "ColdFusion Security, Part One: Understanding Sandbox/Resource Security" and "Part Two: Sandbox/Resource Basics", both available at my articles site.

That approach is a little brute-force though, and throws the baby out with the bathwater if you have legitimate use of java objects.

So in CF8 finally, Adobe added a new feature that permits an Administrator to disable JUST access to the these underlying internal CF/java objects. See the CF Admin "Server Settings" section, "Settings" page, and its "Disable access to internal ColdFusion Java components" checkbox. The option takes effect immediately.

It's certainly more effective if one wants to control to this info, but of course it will make code that relies on it fail, with an error like:

Permission denied for creating Java object: coldfusion.runtime.SessionTracker

I'll point out that since most of the folks I help with run their own servers, they're just not worried about disabling these internal java objects, since they can control what code runs on the servers.

So if you want to quickly find out how many sessions you're running, per application, check out Mark's code. You can also get much more info from these internal objects, if you want to explore. I'm working on a tool to provide some still more powerful information that I started working on years ago based on these objects. But until that's ready, I had occasion today to point out this working alternative to a customer, and thought I'd pass it along here.

Need to solve problems with SQL Server performance? Here are several resources that can help:

• How to troubleshoot SQL Server performance issues
• How to troubleshoot application performance issues
• HOW TO: Troubleshoot Application Performance with SQL Server
• How to troubleshoot slow-running queries on SQL Server 7.0 or on later versions
• How to troubleshoot the performance of ad-hoc queries
• Troubleshooting stored procedure recompilation
• Understanding and resolving SQL Server 7.0 or 2000 blocking problems
• How to monitor blocking in SQL Server 2005 and in SQL Server 2000
• How to resolve blocking problems that are caused by lock escalation in SQL Server
• How to capture error messages that are sent to clients from an instance of SQL Server
• How to determine proper SQL Server configuration settings

Some of these offer knowledge and understanding, others offer specific steps to follow. Most offer links to still other resources (including often specific entries in Books Online).

Note that some may be written more to those still running SQL Server 7 (less likely) or SQL Server 2000 (not so unlikely), though many do cover SQL Server 2005 as well. Just keep this in mind while reading, both if some step doesn't seem to follow, and also in case it may be that some setting that suits one release may not suit another. In fact, some of the resources specifically discuss how things have changed in later releases, and how in fact some settings or techniques for older releases may be very different for later ones. All this just calls for discernment while you read.

These are all in addition to a couple of entries I wrote back in April (starting here) on some other advanced tools and techniques for diagnosing SQL Server problems.

Sometimes CF gets blamed for problems when in fact the problem is in the DBMS--and it's not always a problem due to the SQL being sent from CF. Sometimes the same code can run very differently one time than another. In that case, you really need to understand why this is happening. I hope the resources above may help you. If you ever want direct assistance, this is one of the things I help people with in my available consulting.

Ever felt stumped trying to solve a CF problem? You've tried everything? Searched the web? Asked on forums and lists, and you're still stuck? Or maybe you're just pressed for time.

Maybe you've wished you could hire someone with more experience but can't justify a high hourly rate. Of course, with so many web tools available to share a desktop, travel need no longer be a significant issue. Sometimes, it could help to simply have someone "look over your shoulder" via the web to resolve a problem.

Recognizing all those challenges, I've created a new service that I'm tentatively calling "AskCharlie", to be able to offer just such assistance.

Via buttons on my site or an 888 number you call, you can arrange to speak with me by phone (and optionally join me in a shared desktop session) to solve some knotty problem.

Best of all, it's very low cost and at a per minute rate (first-time callers can use a 10 minutes free option, and everyone gets a money-back guarantee). That and more are explained on my site:

http://www.carehart.org/askcharlie/

There you can learn more about why I did it, how it works, how the remote desktop sharing assistance works (no problem with firewalls, for instance), and more.

I'd welcome your thoughts on what you think of the idea.

If you're trying to get to the bottom of high memory or CPU use or database contention on CFML servers, you may be missing a seemingly innocuous but deadly invader, especially if you're focusing only on "what are my long running requests?" or wondering "Why does CF have a memory leak?" What if the problem isn't really a "leak" at all but, instead, is due thousands or millions of small page requests that are unexpectedly creating thousands or millions of sessions and client variables each day? They may even be something you're causing (but more likely not). It's a pernicious problem that many may dismiss too readily.

It may seem innocent enough: search bots or other automated tools may be visiting your site many times a day, due to many different search engines, and perhaps even many times per day per bot. These may be outside search bots or internal ones (like a Google appliance). Or it may be caused by load testing tools, or simple monitoring ping tools. Or you may have hundreds or thousands of folks signed up with RSS readers to watch your RSS feeds. You may have code that uses CFHTTP to request templates on your own site (or others may be scraping your site).

The problem is that if the pages being visited by these tools are CFML-based, then the bots and RSS readers will not likely track the session/client cookies CF sends, which starts a real waterfall of problems. There are two potential impacts, sessions and clients.

Update: Since writing this blog entry in 2006, I have since given a talk on the subject, which was recorded. More here.

Creation of many new sessions

Assuming you have SessionManagement="yes" enabled, each such new request without a cookie will cause the CFML server (CF, BD, Railo, or other) to create a new session. Normally, that's not a problem. The browser for a typical user will usually store these cookies for reuse on a future page request.

But since these bots do not typically track cookies, then this causes the CFML server to create a new session for each page request.

And that's not one new session per bot, but one new session per bot per page requested.

And these new sessions will live as long as your sessiontimeout is set--which could be minutes, hours, or days. That could become a substantial resource for CFML server to manage, even if there's "nothing" in the session.

(Props to Mark Kruger whose blog entry, Sessions and Cookies and Bots (oh my), was the first I saw to point this out. As I pointed out in his comments, the problem could be still worse with respect to client variables also, as I'll explain below. And since then, I've realized that RSS Readers could be another, and different problem, since the number of individuals running them may be far greater than the number of search engines.)

So the challenge is to find out how many sessions you have currently. More in a moment.

Creation of many new sessions with large amounts of memory per session

Further, beyond the number of sessions is the question of how large the sessions are. If you DO for some reason put a lot of data into "new sessions" when they're created, then this could become a huge memory burden. And as memory use increases substantially, so could the cost of garbage collection. Eventually the CPU to manage that GC could become problematic, and your system could become unresponsive or even unstable.

Creation of many client records

Finally, there's even a more pernicious (and more persistent) problem due to client variables. Now, even if you'd say "we don't use them", consider this: if you've got ClientManagement="yes" set in your CFAPPLICATION, and you've not disabled in the CF admin the "Disable Global Client Variable Updates" option for your client repository (and they are enabled by default), then the CFML server will create/update fixed client variables (hitcount, lastvisit, etc.) for every page visit. Besides the possible I/O burden (whether in a database or the Registry), given the problem of bots discussed above, this would also mean a new set of these client tracking records would be created for each visiting bot request!

If you're storing these in the Registry, any great increase in the number of entries is clearly bad enough.

But whether you're storing these auto-created variables there or in a database, the problem is quite different from the wildcat creation of sessions. At least those are removed when their sessions timeout or the server is restarted. With client variables, though, these are typically set to expire in days, weeks, or even months! That means they last that long on your server.

So either your registry or client database tables (or both, since each app can choose its own) could become very large and burdensome to be managed. Note as well that CF wakes up every 67 minutes (by default) to go through the client repositories to find any clients that can be expired.

Between that and the initial creation of client records, the volume of updates (or inserts) into the DB can cause significant contention, preventing legitimate CF queries from processing. Do you see how very pernicious this problem becomes?

Diagnosis

If you think this may be happening to you (and even if you don't), you should set up monitoring to see how many unique new sessions or clients are being created. You have a few ways to do this.

Sadly, there's no documented mechanism for CF to tell you how many new sessions or clients have been created in a day.

Update: since writing this entry in 2006, there are now documented and undocumented solutions for both CF 8 and earlier.

First, if you use J2EE sessions, you can get a report of them in the JRun metrics. Just search for any of many articles on using JRun metrics.

But what if you use regular CF sessions? Well, here's good news. Whether running CF 6, 7, or 8, you can in fact get a count of sessions. See my later blog entry about some simple code by Mark Lynch that uses undocumented functions to report the number of sessions (J2EE or plain CF sessions), in total and per application. Very helpful for this challenge.

And if you use CF 8 Enterprise, its Server Monitor (and Admin API) also report the number of sessions, and you don't need to turn on memory tracking for this. Just see the Statistics tab>Active sessions, and click the chart icon on the right, which will show a screen with the total number of sessions.

That will tell you how many sessions you have, which can be very enlightening. I helped a client recently where we found they had 90,000 active sessions. Yikes!

Of course, the next question, then, is to find out why and how. Is it search engines bots? External or internal? Monitor pings to CF pages? RSS feeds? CFHTTP requests? Load testing tools?

If you can access them, you could analyze your web server logs to find out how many pages are coming in that have no CFID/CFTOKEN values sent in their cookies or query string. (Web server logs can be set to report incoming cookies.) That would be a clue, as it would cause CF to create new ones in response. Of course, it could be legitimate first time users.

Update: You can also view the "user agent" being presented by the browser requests, which can be set to be tracked in web server logs, and is also viewable in CF's CGI scope, as well as in the CF 8 Enterprise Server Monitor and FusionReactor's Request Details page. While bots and other automated request tools can mimic (report) any user agent, some do identify themselves plainly. You may be surprised to find how many visits you get from such requesters. A good log file analysis tool like Log Parser, or any others listed at my CF411 site list of Log Analysis tools.

You could also look at either the registry or client variable database to see how many entries there are. You may be shocked by the number. (Update: Just be careful not to open the Registry location for CF client variables using Regedit or similar tools. If there are a huge number defined there, just trying to view it could bring down your server. A tool like Log Parser, mentioned above, can help, though, as it is in fact able to read and count data in the Registry.) You could also export the key from the registry, to see how large it is, but again it could take a long time if it's large, so it may be best to do this during a scheduled outage.

Remediation

The simplest thing is to ensure that any code that may be hit by bots, search engines, RSS readers, etc. do not use code that has CFAPPLICATION SessionManagement="yes" or ClientManagemenet="yes" (or the equivalent properties in application.cfc). That may be trivial, or it may be a hassle, depending on the complexity of your application.

Update: here's a thought that's come up from discussions on a private mailing list. One solution to consider would be to detect if the request has the CFID cookie--which it wouldn't for bots, and if not, set the session timeout to a short value. Be careful, though, because legitmate first-time visitors will also have no CFID token, so if you set any session vars on the page they request or in the application.cfm/.cfc file, don't set it so short that those will be gone when they go to the next page they visit. Perhaps set it to a minute, but realize the implications.

Update: As far as the global client variables updates are concerned, you should first check if in fact your client variable repositories (in the CF Admin Client Variables page) have been modified to disable global variable updates. If not, it's not an obvious thing to disable that. You need to make sure there's no code trying to use client.hitcount or client.lastvisit, which are the variables created by this feature. Assuming there is none, then you could disable this. That doesn't remove the entries created to this point, but it does stop the inserting and updating of entries there. Just beware that you still need to get rid of those old unneeded entries, or CF will continue to purge them every hour.

Update: As far as purging client variables if perhaps a large number have been created, there are two different solutions, for the Registry and for any datasource.

First, as for client variables stored in a database, see an explanation and code offered in Adobe technote 18514 (again, no longer available at http://www.adobe.com/go/tn_18514, but available via archive.org.)

Second, as for the Registry, it may be tempting to go in and delete the key that holds the client variables, but beware of that. It could take a very long time, if there are hundreds of thousands or millions of keys. You may want to do it overnight or during system downtime.

While you could use RegEdit to view and delete it, it would be better to delete it from the command line. Veteran troubleshooter Mark Kruger shared a helpful tip: create a reg file and place this line in it:

HKEY_LOCAL_MACHINE\SOFTWARE\Macromedia\ColdFusion\CurrentVersion\Clients

(where path is the path to where the CF client variables are located in your registry. Your location may differ. Use the pattern provided to locate it within your registry, but remember the admonishment above: don't be tempted to expand the tree for this key, or you may bog down or crash your server. By running this command from the command line, while it may still take a long time, you at least remove the risk of tying up resources in the RegEdit GUI.)

The inimitable Dave Watt's also recommended the Reg command-line tool (built into Windows) as another way to do the same thing:

REG DELETE HKLM\SOFTWARE\Macromedia\ColdFusion\CurrentVersion\Clients

There is an available /f argument you can add at the end to cause it to delete without prompting for each key. Note as well that this tool also offers query, export/save, and even compare arguments.

(While there is also code available from Adobe to help view/purge client variables in the registry, it's CFML that uses CFREGISTRY, which may not be wise to tie up a CF request thread for a long time. But if you're interested, see the zip file in Adobe technote 17881. The old link, http://www.adobe.com/go/tn_17881, fails, but here it is via archive.org.)

Finally, if you may just want to stop CF from being able to even TRY to write to this directory, consider the idea offered by Russ Michaels, where you can disable CF's ability to write to this part of the registry and prevent therefore any attempt to use it.

Also, if you're on Linux, note that there is no real registry on your server, but CF stores client variables (if told to use the "registry") in a file called cf.registry, typically in /opt/coldfusion/registry/cf.registry. You can edit or delete that file as desired to resolve client variable problems.

Other thoughts

If you use load testing tools, be sure to enable any option to have them honor cookies. Otherwise your testing results may not be accurate, as you're imposing this burden on the server of it creating new sessions and clients for each user request you're simulating, which would not happen in production (except for the bots and RSS readers, etc.).

Hope this info may help others.