I've never used profanity much in my fiction, though at least in part, it's because I write a lot of secondary world fantasy and I don't feel all of our modern cursewords translate very well. In a world with strong religious beliefs and where sex outside of marriage is social taboo, there are some that can make the transition (you know the ones I'm talking about), but oftentimes, it's better to use something more directly evocative. Inventing unique profanity is tricky, and it's difficult to please everyone, so I tend to go for broader exclamatory phrases.
Even in my contemporary or SF stories, though, I tend to use it sparingly. People who critiqued the opening chapters of Flow commented on Kit's tendency to use "Holy schnitzel," telling me that I didn't need to sanitize for a young audience, but that was never the idea: it's simply a verbal quirk of hers. Hadrian drops a few well-placed words, but even at that, they're few and far between ... and I just checked now, and there is quite literally *one* swearword in the entirety of Scylla and Charybdis. (Unless my editor asks me to put in more cussing as we go.)
Am I prude? Not hardly. I work in a profession - culinary - where profanity is often seasoning as liberal as salt. It's not even an expression of conflict: it just speckles the dialogue, and when directional, it's almost invariably aimed at an inanimate object. We talk to our equipment and our food, and it had better behave.
But I keep two separate rules in mind when it comes to profanity:
Every time you use a particular word or punctuation (exclamation points come to mind), you decrease its impact. If every other sentence ends with ... ! then it becomes invisible. This is part of why "said" vanishes so effectively in dialogue. It's a word that you want to disappear, to be recognized as a handle but to stay out of the way. I have an ongoing problem with the word "subtle." It's such a fun modifier to use in unexpected places ... but in repeated use, it becomes expected.
The second rule applies more specifically to profanity: it looks much dirtier on the page than it sounds in real-life dialogue (or even movie dialogue). If rendered literally, real dialogue might be crowded out by the four-letter adjectives. It would make a character seem incoherent, not impassioned. To create the illusion of reality, you actually *need* less profanity.
The difference is, in part, that the spoken word flits quickly out of the brain within a few seconds, but the written word is visually on the page and at least in the reader's peripheral vision for however long it takes to read the full page. It lingers longer and has a bigger impact.
I'm put in mind of listening to Orson Scott Card speak many years ago about his experience writing for comic books. He had written a script that involved a character being tortured. The artist pointed out that it would be too intense for readers because unlike a written description, the image would remain there, permanent, on the page.
In this case, of course, the "image" is the written word, carrying much stronger than the imagined reality.
So as far as efficacy and verisimilitude ... a little goes a long way. Maren, the narrator of Surgeburnt, has something of a foul mouth, but I'm still watching to make sure that it doesn't weigh on the narrative. When an expletive does come into play, it does exactly what it's supposed to.
Of course, this has the potential to get me into trouble as the majority of my writing is still clean or almost completely clean, so I have to remind myself to check it before I submit to a family-friendly market, just on the off-chance ...