ColdFusion Muse

CF 10 Does Not Honor "Maxrows" on Sybase Stored Procedures

In working with CF 10 on a site that uses Sybase as a backend database server one of our tasks was to convert various "inline" queries over to stored procedures. In some cases these queries used the "maxrow" attribute to limit the number of rows returned to the driver. Personally I usually revert to LIMIT or TOP (or whatever the DB Server syntax provides) for that purpose. With maxrows the DB server works just as hard (my best guess) and the driver simply counts the number of rows sent to the buffer and limits it there. In other words, I have always suspected that maxrows limits the number of rows sent from the DB Server and not the number of rows actually produced by the query. Still there are situations where it hardly matters in either case - and maxrows has a purpose there I guess.

So when it came time to convert our queries over to stored procedures - and with a requirement to change as little as possible on the DB Server - we dutifully added the maxrow attribute to our cfprocresult tag like so:

<Cfprocresult maxrows="15" name="blah"/>
But to our suprise this had no effect. Sybase (or perhaps the Sybase db driver) simply failed ot honor the maxrows attribute. This might be one of those cases where only some drivers or DB servers are capable of implementing the attribute for a stored proc. For example the attribute dbvarname used on the cfprocparam tag is ignored by several DB servers (MSSQL among them). You must place your params in the correct order instead. Still, I wish an appropriate error message would be thrown rather than simply allowing the stored proc to execute and ignoring the attribute like a quirky basement dwelling uncle. Anyway I thought it was worth blogging this nuance of the Sybase driver for the 6 ColdFusion servers out there still connected to Sybase. :)

Hanging Jrun Threads and MS SQL Parallelism

Recently one of our systems started misbehaving. In this system we had 2 ColdFusion 8 servers connecting to a single MSSQL 2005 server. All the hardware was quite good - plenty of RAM, Fast disks, moderate traffic etc. The system had been in place for some time. But (and isn't this often the case) we moved a new design in place with some changes to the query code and suddenly our well-behaved system started acting like a sugar-laden toddler in the cereal aisle.

Watching the "running request" counter for ColdFusion I noticed that it was slowly accumulating requests. When that happens (threads slowing building up over time) you usually have to prepare for some frustrating troubleshooting ahead. When a server is "crashing" you can often pinpoint the error. Crashing servers tend to suddenly fill up running requests and the request queue and the log files will generally have some clues occurring right around that time. But this was different. In this case the request count climbed slowly and was seemingly random. And these threads did not show up in the list of "active requests" in the CF monitor either. Aha! I thought. This is my old networking issue! You might not remember this but a few years ago we discovered that auto-synching ports can sometimes cause phantom connections to hang on a DB intensive application (see this post).

But a quick checkup of network settings showed that this was not the case. Network connectivity was excellent and both DB and the 2 servers were connected through the same Cisco switch. So it was on to the database. Why the DB? Why not scour through JVM settings and fiddle with CF request settings? For one thing, 80% of the time it's not CF or the DB but some combination of the 2 (bad query writing, resource constrained DB, drivers etc). In this case the 2 common denominators were the database and the new code - but I believed the DB was our lowest hanging fruit.

Processor Usage

Sure enough a check of the database showed processor usage that did not look normal. Wait a minute Muse... don't you have any baseline numbers for that assumption? Nope, not at this point. I'm letting my experience guide me. When you have 4 cores and 2 of them are at a flat lined 50% you generally know something is wrong. In fact a quick check of the accumulating requests on CF showed a 20-25% per thread correlation. In other words, each of my hanging threads was using 20% or so of one core on the SQL server. Once it was hung that thread continued to use 20% of one SQL server core in perpetuity until CF was restarted.

The funny thing was that under regular load the DB processors was extremely underutilized until one of these threads was produced. The DB processors would stay at between 1 and 5 percent most of the time - practically idle. When one of these "special threads" came along, one proc out of the 4 of would "jump up" to 20 or 25 percent but the rest would idle along as before.

Finding the Problem

We tried a great many things. We patched and hot fixed, shrunk and optimized files, added and removed indexing etc - all of which was helpful and necessary, but none of which permanently "fixed" our problem). Finally, I was looking at the "activity monitor" in MSSQL05. The activity monitor "process info" view shows a list of connections along with some extra data, process ID, database, status etc. If you double click on an item in the row you will see the currently running query or task. You have to sort of "get lucky" to see it since most of them fly by pretty fast.

In any case I was watching this view (refreshing every 10 seconds) while there were no hanging threads and suddenly I saw something that made me scratch my head. A process ID was duplicated about 3 or 4 times. Each of the duplications had a "wait time" and a "wait type" of CXPACKET. So this process ID was spawning multiple threads under a single ID. And the wait time made me think that this might be our offending process. Looking at the processor utilization I noticed that sure enough, I had a 20% utilization on one core. Going back to my CF servers my suspicions were confirmed. We had a hanging thread on one of the servers - so this CXPACKET thing required some more investigation.

First however, I thought I might try to mitigate the problem from within the activity monitor by killing off this process ID. If I was successful I would have a new mitigation technique that would not involve any potential user disruption, with the exception of whoever was running the query that was locking up these threads (and they were probably tapping their fingers on the desk waiting anyway). So I tried the "kill process" button from the activity monitor, but I had to kill all of them individually and I couldn't catch them all before they re-spawned - or maybe I'm just too old. Turning to SQL Studio I ran the query KILL 55 (where 55 was the process ID in question). That did the trick and it was indeed a magic bullet. As soon as I "killed" that process ID - all the sub-processes were terminated as well. My CF server dropped the hanging thread and SQL Processor usage went back down to normal.

The Fix

Ok so now what? I could hire a temp to sit in front of the activity monitor all day and kill off any process ID with a CXPACKET Wait type that correlated to a CF hanging thread. I could probably write a complicated SQL script to find these threads and terminate them (I kind of liked that idea actually). In the end I chose to do a little research into CXPACKET wait types. I was fortunate to stumble onto this post by Pinal Dave. It turns out that a CXPACKET wait is related to parallelism. Now parallelism is how MS SQL chops up the work load of a query and makes full uses of your processors to get the work done. Much like cfthread splits work out and then joins it back together, SQL splits the work out and then an "organizing" thread "waits" for all the individual threads to complete. Once they have all completed it assembles the data for return to the client. Make sure and read the full article as well as the comment by Jonathan Kehayias at the bottom - excellent stuff!

In any case my SQL server was suffering from not being able to reassemble threads from this division of labor. I'm not sure why that might be (I have some ideas) but the long and short of it is that attempts at parallelism for query execution were causing hanging Jrun threads on my CF server. Following Pinal's guide (and a couple of MS resource pages) I tried setting the max degree to 2 and the threshold to 20, 25, 30... looking for a "sweet spot" where most of my queries would execute without parallelism, reserving it for the report or aggregation queries in the admin section of this site. Unfortunately that didn't work. The issue here was likely a specific query with some new joins in it that was always going to trigger parallelism and ofen fail to complete - causing our hanging thread issue.

Finally, I set the "max degree" to 1. Doing this meant that there would be a 1 to 1 relationship between threads, process IDs and queries. In other words, a given query would never use more than one core execution thread. Now you might think this is problematic because it doesn't make full use of SQL's tuning engine. Technically you are right. I would only say that in a typical web application the query traffic generally consists of dozens of very short queries where parallelism would actually add additional time to the process. So in a typical web application you lose very little by minimizing the degree of parallelism. And indeed that appeared to be the case in our web application. Our CXPACKET waits, hanging threads and egregious processor usage all went away and things have been functioning smoothly since then.

The Aftermath

The Muse knows his readers well. Some of you want to hammer me about not fixing the real problem - that specific query in the code. Not to worry. Using SQL's performance dashboard we teased out the worst offenders and set our ColdFusion developers to analyzing the code. But I suspect the version of SQL or something about the hardware, hyperthreading or NUMA to be a more likely culprit. I have never seen SQL's execution planner cause a problem when it turns to parallel execution before. Still - it's always a good idea to fine tune that query code.

Access on 64bit ColdFusion? Yes we Can!!

First, let me make sure I do not get off on the wrong foot here. I am most explicitly not saying that you should be using Access on a production server with heavy traffic as a regular application data source. In my opinion Access should be limited to the smallest use possible on web servers. It's fine if you have a small intranet application to keep track of equipment or handle tickets among 4 or 5 people - although why you wouldn't at least move to MySQL or MSSQL express is puzzling. However (and the Muse can't be clearer than this) if you are using Access to backstop a high traffic ecommerce site or content management for an important customer then shame on you. It's a problem you will have to solve - maybe not today, maybe not tomorrow... but someday and for the rest of your product life cycle.

Having said all that you might remember my post on setting up an Access Proxy. The problem I was solving in that case was quite specific. I was using Access as an export format for an enterprise application. Users would run a very large report and then have the choice to export it to Excel or Access for offline viewing or mining. As far as I'm concerned this is an appropriate use of Access - as an offline data mining tool. My process basically copied the file to a unique location and used the Access Passthrough method to populate it, zipped it up and sent it to the users browser.

When I moved to 64bit ColdFusion I was forced to come up with an elaborate proxy service to insure that the process continued to function as usual. This was because I could not figure out how to get Sequelink (those funky "ODBC Server" and "ODBC Agent" services that install with ColdFusion) to talk to ColdFusion any more in the 64bit environment. Recently however, Brent Fry delivered this excellent post as well as provided me with some additional info for the windows 2003 server platform. So I now have a new process that is capable of synching up Sequelink with ColdFusion.

There's nothing wrong with Brent's post of course, but I have a desire to codify this in my own words. Plus I know I'll need it again and if I don't write it down for myself I forget where I found it the last time (no offense to Brent and and with all credit due!). With that in mind, here's the Muse version of Access on 64bit ColdFusion with a little "under the hood" stuff thrown in.


Query Caching Run Amuck: Know Your FIFO Buffer

Query caching is one of those underutilized features of ColdFusion that can exponentially speed up your application. It is also one of those misunderstood features that, when used incorrectly can be very disappointing. Let me say at the start that the Muse believes you should use query caching. If you don't believe I'm a fan then check out my post titled, Good Developers Practice Safe Query Caching. It's not a panacea, but it definitely has it's uses. Almost every application has some queries that can be cached - and saving round trips to the database is the holy grail of application tuning. But in this post we want to talk about naughty developers who cache irresponsibly... developers who do not understand the nature of the FIFO buffer.


Good Developers Practice Safe Query Caching

First let me say that query caching on a CF server is not a panacea. There are many ways to improve performance and there are many techniques for caching. All techniques have trade-offs. Still, there are instances where caching will save you time and money - and those are both things in short supply. More to the point, query caching is so easy to implement that it can be done for an entire site or application in a relatively short time - as long as you follow some simple rules and take some precautions (please people - use "safe caching").


Data Truncation Error: Migrating MySQL to MSSQL

I have one more tip as a follow up to my previous post on Migrating Between MySQL and MSSQL. It has to do with the dreaded "data trunction error". If you have used MSSQL you may have seen this error crop up from time to time. It is a common error and very easy to remedy. The error occurs when you have a character field with a length that is too short for the size of the string you are trying to insert. Check out this example....


Migrating Between MySQL to MSSQL

I recently did an emergency stint of troubleshooting for a site owner (a designer who owned a complex ColdFusion site) who was hit with the HTML injection issue on his site. He had done a good deal of work on his own and cleaned up the HTML as best he could. He was busy moving the sites to a more secure environment (a better hosting company, no more FTP, intrusion detection and solid VPN support). He had managed to travel a long way down the migration path before he ran into trouble. His new environment used MSSQL and his old environment used MySQL.

Now I love MSSQL and I think it is a wonderful choice (price notwithstanding), but had he contacted me before he decided to go this route I would have suggested that he stick with MySQL for the sake of compatibility. Unfortunately he had already "flipped the switch" before I got there and so there was a lot of "on the fly" changes to make just to get his site working correctly again. One of the biggest issues had to do with his choice for migrating the actual data. He had chosen to use an export tool to move the MySQL data into an Microsoft Access file. He then used Microsoft Access "upsize" wizard to send the data to the MSSQL server. The biggest flaw with this approach is that it resulted in missing dates which were not translated correctly from MySQL to Access to MSSQL. So we had to re-export the data in to SQL dumps, modify them and then run them against MSSQL.

The date problem is not a typical incompatibility with MSSQL, but there are several we ran into that we had to account for. Here they are in random order:


Tabular Data Stream Error (Who's Using a Cursor Select Method?)

It's not often I see an error that I have simply never seen before. Here's one that some of you may recognize. It happened on a query using SELECT * against a fairly limited table on an MSSQL 2005 database. There wasn't anything unusual about the query (except for the bad form of using the asterisk). Here's the error that was thrown:

rror Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol
stream is incorrect.
Parameter 2 (""""): Data type 0x38 is unknown.
The specific sequence of files included or processed is: ******

The Fix


Migrating to MSSQL 2005 and UNION Queries

For the most part, migrating your ColdFusion site from Microsoft's SQL 2000 server to SQL 2005 is a snap. Import the databases from 2000 to 2005, re-point your data sources to the new instance using the ColdFusion Administrator and you are done. No muss, no fuss. There is very little query code that you will need to change. Sometimes you needn't change anything in your ColdFusion code at all. Here is one that I found recently however that you may run across - especially if you are a fan of UNION queries. Here's the skinny.


Ask-a-muse: Proper Use of Cachedafter

Muse Reader Asks:
I'm trying to use cachedAfter in a query. I have tried cachedAfter="#dateAdd("d", 1, now())# but it doesn't seem to ever cache after a day has passed. What am I doing wrong? cachedwithin seems to work fine.

This is one of those obscure tags that gets very little use in the community. I confess to never actually having found a use for it in production. Still, it bears some looking at because it is a clever notion that might come in handy under certain circumstances. Here's a primer:


Coldfusion and Oracle 8i Performance

Recently I had the opportunity to work with a server setup that included two load balanced servers running Coldfusion 7 enterprise backstopped by a hefty Enterprise Oracle 8i server. The CF 7 servers were going down repeatedly. The database people blamed the web people who blamed the networking people who blamed the government. No one seemed to make any headway on the issue. I was engaged to get the CF 7 (and 2 CF 8 servers using a separate configuration) stable. I arrived on site with my change document in hand. I learned, or I was reminded of, a number of things during the experience that I will try to boil down into two or three posts - including:

  • Don't Accept the Default Settings - It's surprising how many enterprise servers I run into that have been installed but left with the "default settings". Naturally the default settings are simply inadequate to support any usage beyond the bare minimum.
  • Most Code Evolves Without Planning
  • Team Troubleshooting is Key - When systems are highly complex, no one person will ever have all the necessary information to explore all the possible solutions. It takes bringing together folks from the various disciplines (networking, database, Coldfusion, Linux etc.) to really make it work.
  • Big Oracle Tip - This will be the topic of this post


Creating Views With CF Query: DDL Follies

Most queries in your Coldfusion code do one of four things - Select, Insert, Update or Delete. Maybe you did not know that, given the proper permissions, you can do just about anything that can be done on the DB server from within a query. You can backup and restore, drop users, even execute shell commands. That's why you should never create a datasource using the SA user. Instead you should define what you want a datasource to do and create a user for that purpose. Still, sometimes it is useful to be able to do other things using Coldfusion and Cfquery.

For example, I have a generic table with rows that look like "col1, col2" that holds form data. In this particular application the customer creates custom forms to collect data from specific clients. All the forms look different. One might have fullname, address, city, Postal code, and the next one might see first name, last name zip. When the data is submitted it is put in col1, col2, col3. But he has a reporting tool that allows him to query tables from the database and run reports for his customer. What can we do to make it easier for him to report? Surely "select col1, col2" isn't going to do it. The answer is to use T-SQL Data Definition Language (DDL) to create a view for each customer.


Using Rowcount in T-SQL

Here's a tip for limiting the number of rows returned from a query. Now I know you think "that's easy - just use TOP." Ah... but what if TOP is not an option? Consider this example. You have a reporting stored procedure that you want to use to return multiple results sets to your Coldfusion Page. Easy - right?


Handling Variable Form Data in a Stored Procedure

Lots of projects have a requirement that interaction with a database must be done using stored procedures only. Stored procedures are generally quite easy to write, but there are some things that are slightly more difficult that using a straight CFQUERY. For example, perhaps you have seen code that handles a search form. You might see a query that looks something like this:

<cfquery name="get" datasource="#dsn#">
    <cfif NOT isEmpty(form.username)>
        AND UserName = '#form.username#'
    <cfif NOT isEmpty(form.address)>
        AND Address = '#form.address#'
Please note, I'm use a UDF called "isEmpty" that simply trims the string and checks the length. Also keep in mind that I'm not adding the required Cfqueryparam to save room. How would you duplicate this code in a T-SQL stored procedure?


JDBC Drivers, Data Binding and Implicit Conversion

You may know that MSSQL allows for "implicit conversion" between data types. For example if you have a character column and you pass it a number without single quotes (as in myCharCol = 1), MSSQL will automatically convert the value 1 into a character for the purpose of the query. On a Coldfusion 5 server this behavior carries through seamlessly even when you are binding data using cfqueryparam. However, if you are using cfqueryparam in a query on a CF 5 server and also relying on implicit conversion within the same query you should know that you may have a problem getting that cfquery to work in Coldfusion MX. The issue is that JDBC "prepares" the statement by validating against the schema. Here's an example:


More Entries

Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.