It was good to have it there. Even the most skilled gamer can get stuck on a puzzle every once in a while, and the Sheikah stone was a good way to stop and find the answer you needed. I had some difficulty, so it was handy to have it there just in case I had tried everything and didn't know what to do.
I don't think they should have put it in Hero Mode, though. It was all the same dungeons and all the same puzzles, so there wasn't much of a need since people playing Hero Mode had beaten the game once already. It only took up some time telling you how cool it is that you beat the entire game and solved all the puzzles. He really wasn't necessary the second time around.
I Haven't used the Seikah Stone yet in Skyward Sword, but I know of some people who would need it. I pride myself in being more of a puzzle-solver,, so I try not to have outside help. However, I don't think having a Super guide isn't necesarily making the game too easy. In reality, it makes it more user-friendly and appealing to a wider demographic. Gamers who want a challenge need to only ignore the help.
I never used it, and yes, I did find it a little aggrivating for those who got stuck on a puzzle and can just go to the Sheikah Stone and just find out how to do it. I think that players should be challenged and not have a super-guide in the game.