Ive heard alot about it. What is it and how does it work?

generally sss describes how incoming light is affected (spread, absorbed,...) inside an object by its material (physical characteristics)

Indeed it adds a lot more realism
usefull for creatures, characters , but i don't know ,
in fast games like FPS you won't notice it.

it can be usefull for cutscene or adevnture games.
when the characters are in action , i think the shader could be deactivated to
improve perfs.

So it lights the model from the inside?

No, from the backside - like when you hold your hand against a light.

Has any game/real-time application used this yet?

OK, can someone explain how its working in the picture above?

exessus, the picture above is from the upcoming game by crytek, crysis.
Check the link in the tools forum.

yes there are already some dx9 realtime sss shaders out there

Im not exactly sure what method they use, but there are a number of ways to approximate this effect. I do know you can use depth maps somehow.

The basic idea is that light tends to scatter inside a solid, semi-translucent object. So lighting is not always purely a surface effect. The best way to imagine it is to look at something like milky glass.

Somehow you use a depth map to determine, more or less, which parts are thinner, and so should get some light scattering or bleed. So perhaps you would render two depth maps, one with normal culling, and one with reverse culling, then you take the depth values and find the closest values, and you have your thinnest areas. Then project the composite depth maps on the objects when you render them, and use it as a mask for a brightening factor.

This is only my theory on how it could be done, i havent really done any reading on the subject.