This sort of secondary behavior is something I am very interested in and often times feel is lacking in lots of effects in games. We have a tendency to be so constrained by budgets it is tough to think beyond the primary effect.
Just for sake of illustration here is an image of the source texture I created for the test. This version is a very compressed jpeg, the targa is much higher quality.
I mostly use a combination of painting in photoshop, photo-mashup and Maya's fluid dynamics systems to generate my art. Even if it is not an animated page there are tons of good frames in a well executed sim. Often times I even use Maya's other dynamic systems to create regular textures like noise, lens flares, halos, water surface normals, fluttering paper and fabric, the list goes on. The great part about using something like Maya to generate your texture has to do with creation of similar effects in the future, or offshoots of a particular effects look. Instead of searching for stock footage or finding images, you have a scene file that has settings you know already work. Just go in the file, tweak some values and tune the visual to what you need and re-render. Want it higher resolution? Re-render...want the lighting to be different? Re-render...want more detail in the flames? Re-render...
This process takes longer than rotoscoping some stock footage, however when were you able to take your stock footage from 300 frames to 600 frames without loosing quality? Or could you speed up the swirling in your smoke without speeding up the footage?
Maya is also not the only bag of tricks out there either, many people swear by FumeFX for Max, Houdini has an entire system, and XSI even has their new dynamics system that looks interesting.
The other upshot, if some of these systems ever make it into games (Little Big Planet uses 2d fluid sims for smoke and fire) at least you have a leg up on the technology as well.