What does “more cowbell” mean? Has it gotten the currency as an idiom or a buzz word?

You certainly did your research!

Two crucial points that it's not clear if you came across, though:

1) SNL is an abbreviation for Saturday Night Live, a popular comedy show that features comedic stand-up skits and musical guests.

2) The cowbell, in addition to being a countable noun, is also an instrument.

Now, let's take a break and watch an excerpt from the skit in question.

The skit is a reenactment and parody of the studio recording of the song "(Don't Fear) The Reaper" by the band Blue Öyster Cult. The guy screaming "I gotta have more cowbell" is the producer. Because the cowbell player (Will Ferrell) and the producer (Christopher Walken) are popular actors, and because SNL was a successful show at the time, this skit became a big hit. The humor lies in that "more cowbell" seems like an absurd request - a background instrument becomes louder than the vocals, the melody, etc.

The phrase is "more cowbell" and not "more cowbells" because Walken is asking that the one cowbell that's there be played more loudly. This is actually standard usage - someone might similarly ask for "more bass" or "more guitar", both in the singular. (Or, in another context, someone might stay a stew needs "more carrot".)

The "more cowbell" joke can now be seen all over US pop culture, though at this point, I have to say it feels rather stale. While it's not an idiom, you could call it a "pop culture reference" or "meme".

Understanding it as a cry for "more excitement" isn't bad, actually, but I wouldn't read too much meaning into it. One of the appeals of SNL was its randomness, and people often quote it quite randomly.

If you haven't seen the SNL sketch, it's Christopher Walken's recurring line throughout. All the other permutations are just references back to the original sketch. You can see the sketch here. I'd put it in the category of Internet Meme.

Onomatomaniak's wonderful response was spot-on.

I wish to share a completely unrelated, much more esoteric usage of the phrase that I am sure is less common.

Some independent Baptist churches (at least in West Virginia, USA) have cowbell services that features several preachers presenting brief sermons in quick succession. Each preacher has a time limit (five minutes to ten minutes time limits are common.) If the preacher goes over the proscribed time limit, the moderator rings a cowbell, which is the preacher's signal to wrap it up. This bell gives the service its name.

Thus, when one sees members of the congregation posting "More cowbell!" to Facebook, the meaning is "we would like to have another cowbell service!" To the uninitiated, however, it looks as if it is a chic cultural reference to the SNL skit.