It's hard to tell which games used more or less of that memory; the big thing about game complexity in that era was always ROM size limiting asset complexity, rather than RAM size limiting computational complexity, so the games released toward the end of the console's lifecycle were just ones with the biggest ROMs and therefore most assets, rather than games that used the available CPU+RAM more efficiently.[1]
Now I'm considering writing a memory profiler patch for a SNES emulator, to see how much of the 128KB is "hot" for any given game. I would bet the hardest-to-fit game would be something like SimCity or SimAnt or Populous.
On the other hand, the SNES also had "OAM" memory—effectively the 2D-sprite equivalent to GPU mesh handles. And those were a very conserved resource—I think there was space to have 128 sprites active in total? Developers definitely had problems fitting enough live sprites into a level. Super Mario World's naive answer was to basically do aggressive OAM garbage-collection, for example: any sprite scrolled far-enough offscreen ceases to exist, and must be spawned again by code. Later games got more clever about it, but it was always a worry in some form or another.
---
[1] There were also those that used expansion chips to effectively displace the SNES with something like an ARM SoC in the cartridge, once that became affordable. It's somewhat nonsensical to talk about how much SNES system memory some games used, because they came with their own.