Considering the RTX 6000 ADA is like $8k, you could add three 4090s for half the cost.

You know what, that's actually fair. If your workload needs tons of GPU memory, nothing probably beats the Mac Pro's up to 192GB.

Except, I guess, for the Mac Studio.

Is there a workload that actually needs more than 24GB of VRAM that can't be offloaded to regular RAM.

I honestly don't have any examples out of my head. LLM inference is compute bound AFAIK.

LLM inference is GPU bound and VRAM bound. Given quantization however, 2x3090 or 4090 (48GB) is enough VRAM to load 65B quantized llama derivatives (30-40GB). With exllama you can get maybe 20-30 tok/s with dual 4090s on ~1200W. With an M2 Ultra maybe 10 tok/s on ~178W.

(I don't have these devices but have been researching LLM inference performance in the interest of buying a machine for them.)

Advantage goes to M2 Ultra if you ever might need more than 48GB VRAM. I think it's unlikely nvidia is going to release a significantly higher VRAM consumer card anytime soon, since that's what their A100s are for.

If only the big ML stuff didn't all use CUDA. Guessing that world won't transition to Metal compute any time soon.

Are there any such non AI use cases? For AI anyway its all Nvidia.