SQL Express 2008 R2 on Amazon EC2 instance: tons of free memory, poor performance The old SQL Express 2005 was running on a low-end single Xeon CPU Dell server, RAID 5 7200 disks, 2 GB RAM (SBS 2003).

I have not done any baseline measurements on the old physical server, but the Web app is used by half a dozen people (maybe 2 concurrently), so I figured "how bad can an Amazon EC2 instance be?".

It's pretty horrible: a difference of 8 seconds of load time on one screen. 

First of all, I'm not a SQL guru, but here's what I've tried:


Had a Small Instance, now running a c1.medium (High Cpu Medium) Windows 2008 32-bit R2 EBS-backed instance running IIS 7.5 and SQL Express 2008 R2.  No noticeable improvement.
Changed Page File from fixed 256 to Automatic.
Setup a Striped Mirror from within Disk Management with two attached 1 GB EBS volumes. Moved database and transaction log, left everything else on the boot EBS volume.  No noticeable change.
Looked at memory, ~1000 MB of physical memory free (1.7 GB total).  Changed SQL instance to use a minimum of 1024 RAM; restarted server, no change in memory usage.  SQL still only using ~28MB of RAM.


So I'm thinking: this database is tiny (28MB), is the whole thing cached in RAM?  Can I confirm? Surely that would speed up performance.  The transaction log is 241 MB.  Seems kind of large in comparison -- has this not been committed?  Is it a cause of performance degradation? I recall something about Recovery Models and log sizes somewhere in my travels, but not positive.

Another thing: the old server was running SQL Express 2005.  Not sure if that has any impact, but I tried changing the compatibility level from SQL 2000 to 2008, but that had no effect.

Anyways, what else can I try here?  Seems ridiculous to throw more virtual hardware at this thing.  I know I/O is going to be rough on EBS volumes, but surely others are successfully running small .NET/SQL apps on reasonably priced instances?
Decided to uninstall SQL Express 2008 and install SQL Express 2005.  Restored the database (same one I had originally backed up for the migration), load time for the one page was down to ~6 seconds from ~13 seconds.

Reindexed the database for fun and profit and checked consistency.  Seemed fine (return code 0).