Not every application benefits from multiple cores, and there are also natural limits to parallelization for applications that can be parallelized. Even miracle technologies like "Grand Central" won't change that. And Snow Leopard alone won't make existing apps faster - they must be written for Grand Central, and they must be able to take advantage from parallelization (which already rules out most desktop or office applications).
Games are traditionally single-threaded (Half-Life 2 being just one famous example), because they require heavy synchronization and threading only adds to complexity, but not to performance. Games want ONE fast CPU core and a fast graphics card to be happy.
In Final Cut Pro, the encoding of videos will be faster on a multi-core machine, but you also need lots of RAM to speed up the process (after all, each core requires RAM to work). How well Final Cut Pro scales beyond four cores is another question.
Handbrake is another app that takes advantage of multiple cores, but like Final Cut Pro this is a video encoder, so it's not very surprising.
Aperture also uses multiple cores for RAW processing and it is an application that can easily scale to all available CPU cores - it just needs to spawn a worker thread for each CPU and there's not that much to synchronize.
Most other applications are not very likely to benefit from multiple CPU cores, so you will almost always be better off with less, but faster CPU cores. And, again, Grand Central won't change that either. It will just make it easier to write applications that by definition can benefit from parallelization - but it won't be of any help for apps where there is nothing to parallelize.
You can also expect that most of Snow Leopard's promised performance boosts are not coming from heavy usage of Grand Central, but from very old fashioned code optimization, code rewrites and the dumping of legacy code.
But once again, Apple's marketing "just works", and you've all already bought into the secret weapon "Grand Central", believing that it will make a super computer even out of your toaster. It's more likely that all available additional computing power will be burned on some eye candy instead of finishing the task at hand faster. After all, the company wants to sell you faster machines at higher prices.