I've been running dual processors in my DAW for the last five years - never had any problems using the full potential of both CPUs.
Pretty much any multitrack software will benefit from multiple processors - this is because each instance of each plug-in runs on its own thread - so the load from effects processing - which is where processing power is primarily used in audio - naturally gets balanced between multiple processors. I believe this is the case whether or not the core multitrack software is itself multithreaded.
Windows XP Pro, 2000 pro, and NT all support multiple processors. If you run more than one program at a time, it will naturally take advantage of all your processors. In some cases, cpu intensive programs - say some kind of video encoder - will be written with a single thread, and won't be able to take advantage of multiple processors. These days, most of the good quality, CPU intensive software is multi-threaded.
So basically, dual core is great. Even better is dual dual-cores.