Dissonance as narrative tool
Dissonance is instability; consonance is rest. A minor second (C and B natural, only one semitone apart) is maximally dissonant and creates urgency. A major third is consonant and comfortable. In dramatic music, composers use dissonance to create conflict and consonance to resolve it. Without dissonance, there is no drama, only monotone comfort.
The power comes from contrast. A song built entirely on major triads grows boring. Introducing a diminished chord or a suspended note injects surprise. Resolving that surprise back to the major chord feels earned and satisfying. This push-pull is the engine of musical storytelling.
Harmonic and melodic tension
Harmonic tension comes from chord choice. A vi chord in a major key adds darkness without leaving the home center. A tritone (augmented fourth, the 'devil's interval') between two notes creates maximum tension. Melodic tension comes from note choice: a leading tone (the 7th scale degree) pulls toward the octave, creating expectancy.
Producers and composers exploit both. A vocal melody that reaches a high note over a diminished chord creates maximum impact precisely because both melody and harmony are tense. Timing the release of that tension (resolving the melody down, moving to a major chord) amplifies the emotional payoff. Every dramatic moment in music hinges on this interplay.