It's an implementation detail. You don't know for sure, it doesn't matter, it might differ between platforms, and it might change in the future.
However you can make an educated guess: First of all the fact that
SoundEffectInstance exists, and that you load sound files into
SoundEffect indicates that
SoundEffect is probably responsible for holding the sound effect in memory. And the existence of
SoundEffect.FromStream and the buffer-based
SoundEffect constructors are strong indications that
SoundEffect must have a mechanism for keeping a sound buffer in memory. Therefore it is fairly safe to assume that when you load a
SoundEffect from a file, it uses the same mechanism.
If it's really important, you could test it by deleting or modifying the sound file, after loading the
SoundEffect, and then creating an instance.
As always, if performance is really important, you should measure it.
Of course, creating a
SoundEffectInstance does allocate resources (audio voices, managed and probably unmanaged memory). So it's not something that you should be creating regularly if you can avoid it - such as by pooling and reusing instances. When you're using
SoundEffect is internally managing a pool of
SoundEffectInstance objects for you.