Both will use as much or as little RAM as you care to give them. With MySQL, however, you have to do a lot more tuning by hand (key cache size, query cache size, sort buffer size, InnoDB buffer pool size, etc.)
With SQL server, set "Maximum Server RAM", and if you're on a 32-bit system having virtual address space issues, the "Mem To Leave" startup option as well. That's about it.
Also, I find that MySQL does a somewhat ungraceful job of handling thread scheduling, and running unexpectedly intensive queries can easily make the server nearly unresponsive. I almost never have that issue with SQL Server (save for keeping a transaction running too long and causing massive lock contention, but that isn't a performance/scheduling issue).
I work with both platforms very frequently, and that's what I've observed.