It's all about the top end performance.
What can be done if the performance needed is greater than the best card available can offer? Right, using two of them, and that's the whole idea of SLI, adding another card to satisfy demand. Quite the same reason why in the mid 90s dual socket workstations kept popping up to satisfy the demand for power, despite the inability of most software to use more than one core.
It's difficult to support.
No. It isn't. Or better, not more complex than any advanced feature.
Buying 2 graphics cards of the most current generation is very expensive and doesn't have a good price-to-performance ratio.
Of course not. Buying at the top end performance never gives a good performance per money ratio. This is already true when buying a graphics card alone. A top end NVIDIA RTX 3090 is about three times as expensive as a RTX 3070, but gives only about 50% more. Bang per Buck ratio is never good at the top end. Much like with processors, cars, or anything else. Anyone buying for an optimal BpB ratio, will always end at a middle class CPU/GPU/car. If one needs/wants top end performance, the price will skyrocket in a way that buying two cards is quite within reason.
Buying a current generation card gives you new features and almost always gives similar performance when compared to 2 previous generation cards using SLI.
Sure, if one got time to wait for the next generation (like a few years) to play a game at the resolution his screen can provide and in maximum details, fine - but I'm not sure how many gamer there ever were who would wait a few years before being able to play the newest game.
A fair comparison is only possible within a generation - in fact only within a current offering. Looking at that you'll note that low to mid range models (*1) will never offer SLI, AFR, Crossfire or alike (*2), as there is more to gain from using a top end card. Only when a top end card can not supply a required performance coupling them makes sense.
It feels as if the question is written in hindsight with all past options available and no real need for improvement. It hasn't always been that.
It seems to me that these reasons should have also applied back when SLI was introduced.
I dare you to get some Voodoo or early GeForce card and install a 2000s. Compared to these games Lego bricks are high resolution. Graphic cards were far from being able to display what game designers wanted in simple triangles - not to mention higher functions.
And that's why 3dfx developed SLI - coupling multiple chips (2 or 4) on a card to increase performance to satisfy needs by games. Since then development has gone a long way and GPUs did speed up way past a point that is needed to generate even most complex scenes.
If that's the case, then why was SLI for gaming introduced and supported in the first place?
Triangles, Triangles, Triangles.
It allowed more detailed content.
Why did it last so long?
Because there was such a long way for GPUs to deliver a performance sufficient to give a level of detail that is sufficient to display content in a way that closes in to natural.
Finally, what changed to cause it to fall out of favor?
In the game area mostly because, as said, detail level reached a point were adding more doesn't result in much better rendering, so requirements from game designers no longer have to be cut down to fit existing cards.
On a technical level SLI became obsolete with the availability of (local) busses fast enough to exchange abstract data between GPUs instead of linking and joining partitial rendered screens.
*1 - If they did, it's usually to support buyers interested more in bragging not top performance.
*2 - SLI being either 3dfx Scan Line Interleave (~1998) or Nvidia's Scalable Link Interface (~2005). AFR is alternate Frame Rendering of ATI, while CrossFire is it's later (meaningless) name to have GPU chips working on the same result. Nowadays it got replaced by high speed links.