In my testing I have not found any issue of SQL CE using excessive memory, but one difference is that SQL CE runs in process with the web application so it uses the same available memory as the web app whereas SQL Server has its own process and its own memory and in fact often is on a different machine than the web server. So, if a host has set tight limits on the web app memory it could be not enough given that SQL CE need s to use some of that memory too. A symptom of a problem of low memory would be if the application pool is recycling frequently. You would see this in the mojoportal log as frequent application start and end events being logged. What happens is that when a web app reaches the memory limit configured by the host it forces the application to restart to clear the memory.
Definitely SQL Server is way more robust than SQL CE and will handle more traffic.