Asp.net takes up more memory because the webapplication stays loaded (kinda like fast-cgi php) and lots of stuff is cached. MSSQL is DESIGNED to use all the memory it can because accessing RAM is much faster than accessing disk. You can setup the maximum memory allowed for MSSQL though.
That being said, you can use any Sql server you want you're not forced to use MSSQL. MySQL, POstgresql, SqLite, MSSQL Compact and so on.
Performance wise, a well designed and developed asp.net application will thrash your standard php application any day since it doesn't need additional optimizers(not free!). When yahoo or facebook uses php (with tonnes of modules coded in C for performance) you can't say it really uses php...
How much resources an application requires depends on what it does and how is coded. For the moment I'm hosting 2 asp.net applications( blogengine.net and portfolioengine.net) on a shared account with maximum 75 or 100MB memory limit (I don't know for sure). Luckily I have the option not to use a database, but with a good hosting the Sql database would be hosted on a dedicated server and it won't count to the application memory limit.
If you're referring to a VPS, I had an online game which held almost everything in memory (the world, the players- ok there were at max 80 players) on a 1GB RAM VPS and the IIS worker process took up to 125MB while MSSQL occupied the minimum configured memory (the db file was around 16MB). Long story short I always had around 300MB of Ram free. On a VPS the OS and control panel occupies around 250-300MB of RAM)
Now, what kind of application you want to develop and you think asp.net is too expensive?