Regarding YouTube in particular, I tend to open up videos in background tabs for later viewing and find it very annoying that they start playing automatically before I get around to that tab. I did go there to watch the video—eventually. Just not the second that the page finishes loading.
YMMV. A persistent setting to enable or disable auto-play would be ideal.