We have a fog/mist system at the entrance to our African Journey. On very humid days it is very, very foggy and difficult to see. That's when we have to turn the timer down so folks don't crash into each other. Its a great effect to see the sitatunga emerging from the mist that drifts into their exhibit! I'm not sure what system we have but I'm fairly certain that it works on tap water, no small feat in a city with fairly hard water.
If you want it for a one-time short term effect how about using dry ice? Putting a bit of water on it will create quite the cloud.