Compared to other drives and sites around Banff National Park, this one is pretty weak. I want to disclaim that right away. So if your visit is short on time, keep moving, there's better.
However, if you have time to fill and don't want to travel far from the town: this is a nice option.
1) Along the drive it's possible to run into herds of elk roaming around. They're fairly tame, in the sense that they wont flee if they see you or your vehicle pull up. Do NOT approach or attempt to feed them (they can be unpredictable and dangerous).
2) There's a few trails to follow, including naturally formed hoodoos. Some trails will take you down the "mountain" to the river as well; and its a rather nice trip.
3) There's a number of nice lookouts that a great for taking pictures of the valley (though the town is not in view, but you can see the Banff Springs), and excellent panoramic photos.
Things you likely won't find:
a) Food. There's only a few hotels up there that mainly service their own patrons.
b) Washrooms. If it's urgent, I imagine the hotels could help... but plan ahead.
If you own or manage Tunnel Mountain Drive, register now for free tools to enhance your listing, attract new reviews, and respond to reviewers.