You could search for "dependency" in the source code, but I don't remember seeing it. It's pretty easy to set up if you want it, but it's not real friendly to shared hosting. If you have a hosting company that understands SQL 2005 they wouldn't mind you using it, because its really not a heavy load on the DB. But with SQL 2000 it's more invasive because they have to poll the DB.
I don't see many areas where it would help. If you set a cache lifetime of only 3 seconds, for example, that's going to give you new data every 3 seconds. It's only in rare cases where you truly need an immediate cache invalidation. And if it's your own code that updates the underlying data, then you can invalidate your own cache at the same time you update that data.
Even a small 3 second cache will dramatically improve scalability for a heavily used site. The longer your cache stays alive, the less incremental benefit you get from it, anyway.
However, you can crank up the cache lifetime if you don't mind having stale data. For something like Articles, it's a fair bet that a 1 minute cache isn't going to be a problem (or even 10 minutes). But I'd like a shorter cache lifetime for the forums.