In just over three years of being diamond with Hilton, I'm aware of one upgrade that I've received (at a Hilton in New Orleans). I nearly always get exactly the room type I book. Further, I've never asked, nor would I ask as this is a published benefit that should be automatic. Would I complain about getting the room I booked? No. Would I complain that a benefit is published with no expected reality behind it? No, I'd simply note it, build it into my evaluation and move on.
I travel with others regularly and have seen them ask, seen them present their diamond card (it's in the profile!), etc. I see hit or miss success. More often than not, I witness them being asked to pay to upgrade to another level of room, and it makes me chuckle silently that they don't have an upgrade available (demonstrated by the lack of upgrade of any of those checking in at that time) but can find one for an upsell. (Always separate rooms / reservations, etc.)
For me, Diamond vs. Gold = more points that will eventually assist me in booking free nights. Outside of that, there is no difference.