I don't know if you've already seen it, but you might be able to get some information from the topic "Help! Trying to create classic SNES sound." It is, as you mentioned, more focussed on music rather than sound effects, but there's some discussion of the hardware and limitations which should also apply at least partially to the sound effects.
Yeah it was that intelligent thread that prompted me to post here.
What I'd recommend doing is getting some source sounds you like then use an audio editor to downsample the sounds.
I figured that's what I'd be doing, but I don't know exactly what specs to which it needs to be downgraded. I tried altering a few by taking the sample rate down to what I read the system was capable of, but it barely sounded any different. I turned the sample rate as low as I could but all that did was make the sound muffled and a little bit like an AM station. It doesn't match the right sound at all, in fact it lost a lot of the tone and clarity that I CAN hear in many SNES sounds.
I'd also be interested in other limitations the system faces. What I imagine is the case is that the system can support some pretty clear audio, but it comes at a cost which leads most games to have only select sounds use such quality. For example, the main character may grunt when he is hit, which on some games sounds very clear and realistic, while the other sounds are a bit more generated and grainy. Knowing the precise limitations helps me figure out how the sounds should arranged.
I mean, Tales of Phantasia had mother-lovin' dialogue spoken, but the other sound effects were practically simple chip tones.