I have a Windows 2008 R2 VPS with 1 GB of RAM. I'm using the VPS to run SQL Server 2008 Express and IIS 7.5. I've noticed that SQL Server tends to use as much memory as it can, to the detriment of other applications (and this makes sense with a server is dedicated to running SQL Server). Should I set a limit? If so, what would a reasonable level be?
Yes, SQL Server will - by design and by default - use as much memory as it can get its hands on. And that's really a good thing - if you have a database server, you want it to perform as fast as possible.
Of course, if you need to also run other apps in the same environment, this gets a bit hairy... it's always a trade-off: the more you restrict SQL Server's memory footprint, the tougher it gets for the database server to do its job. I don't have any hard facts and numbers which I could present - but 1 GB of RAM doesn't seem like an awful lot for a SQL Server, really. Limiting that down even further is definitely going to hurt performance - query plans won't be able to be held in cache for long, data pages will have to be discarded more frequently and thus re-loaded from disk again.
Again: there are no hard numbers, or a magic formula to determine how much memory SQL Server really needs to do its job well. You basically have to measure your performance - for both SQL Server and all other apps involved - and then make decisions based on those performance numbers you get. Reduce the memory size for SQL Server gradually, and see at what point it really starts to hurt bad - it might be OK with 700M and start to suck at 450M or whatever - there's really no deterministic way to know what. Tweak, observe, measure, and then tweak again - that's all you can do!
Ideally, I'd say get more RAM and give all but 1GB to SQL Server.
Short of that, I'd start by giving half to SQL Server, and see what hurts more, the DB or the other apps.
In my experience, it will use 250MB immediately, and after that it's all for caching -- but some degree of caching is essential to adequate performance.
More ram would be high on my shopping list! Remember that SQL server express will only use 1GB of ram for its buffer pool (The total used might be more but only 1GB for the pool). You can of course limit the maximum memory used straight from the management studio by right clicking on the server, selecting properties then going to the memory page, the option is called “Maximum server memory (MB).