Sometimes it's just useful to be able to make a pitched sound effect that interrupts the noise channel rather than one of the squares.
It's very good for a "
triangle" type sound (percussion instrument, not waveform), like in
Solstice.
Often I like 1 frame of periodic noise at the start of an otherwise non-periodic noise instrument. It's good for a "metallic" chip at the start of the sound; sometimes that's good for suggesting something like a cymbal. Sometimes this is just useful for making a different sound that pokes through the rest of the noise, suggesting an extra voice of percussion that can play in its own independent rhythm.
The opposite, 1 frame of noise followed by periodic can be nice just to put an edge on the attack of the triangle type percussion too; not really trying to simulate its realistic sound, but still a useful way to give its attack some defined character.
Tepples mentioned an instrument that alternates between periodic and non every frame, which creates kind of a "fluttery" noise sound; I find this can be really good for sustained cymbal instruments, like a cymbal roll.
Shiru's
LAN Master game uses periodic noise constantly for buzzing/beeping noises in the background music. Mega Man 2's
Quick Man stage makes similar prominent use of it.
Sometimes people have tried to use the pitches of the noise unit to make melodies. Sivak's jingle was mentioned. I think B00daW did a few little tunes like this but I can't find them right now. Did tepples use it like this for a NIN cover? (Memory hazy.) This is kind of a problem if you want to support both NTSC and PAL, since they have different pitches. (Similar problem to DPCM.)
Anyhow, it's just another sound that you can use. If you try it out and remember the sound, maybe you'll occasionally think of places it might fit. Also, depending whether or not you care about the early Famicom that didn't have it, you may want to test everything with periodic noise disabled just to make sure it still sounds OK without it.