This question means "Is ___ good?" or "Is ___ good at all?"

Use "any good" in spoken English. Use it to ask about something that you don't know anything about, or that you don't have any opinion on.

For example, you can ask about a movie that you've seen previews for:

Oh, you've seen it? Is it any good?

You could also ask:

Oh, you've seen it? Is it good?

...but that might makes it seem like like you expect the movie to be good, and you're just asking for to confirm that your expectation is correct.