Clearly, if you hate DRM for philosophical reasons, you will hate Steam. If you're pragmatic, you may love it.
I do use steam. A lot. Unfortunately, everything sometimes doesn't just work. For example, I've been left without access to my games a number of times due to internet problems (either in general or just with my gaming desktop) and while offline mode could get around that, I need to be connected to the internet to put it into offline mode and its rare, if ever, that I'll know about internet problems in advance.
So not everything just works for me.
Besides that, though, Steam is the first store in which I've ever impulse bought games - and I've done so multiple times now and probably the majority of all games I own are through Steam. They just make it so easy to find games I want, check the forums for criticism, buy the game, download it and then connect to friends. That experience is one they've done very well and why I still use it despite having had problems. Luckily my account has never been locked - thats a horror story I don't want to experience...
What overhead? You mean the 100MB RAM and 0.1% CPU the Steam client takes? Wow, that really is a high price to pay for the ability to re-download any game I own on any PC, and the automatic updates.
requiring one to connected to their server prior to playing is a ridiculous restriction.
Steam offers offline mode. You only need connect to their server when you download the game. Now, some games are more restrictive, but those are not made by Valve- only offered on their platform. That was someone else's choice.
It is a good model, IMO, and similar to what Amazon does when an e-book is priced at $14.99 ("This price was set by the publisher"). Let other companies do what they like, and let the market decide for itself.
For example requiring one to connect to their server prior to playing (implicit requirement of having an Internet connection) is a ridiculous restriction.