3

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), why isn't the whole thing cached in RAM? 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?

2
  • This would probably be better suited on the DBA SE: dba.stackexchange.com.
    – Colin
    Feb 15, 2012 at 3:13
  • If the moderators want to move it, I'm all for it.
    – gravyface
    Feb 15, 2012 at 13:17

4 Answers 4

1
  1. Whenever I upgrade databases, and I've been doing it for at least twelve years, among the first things that I do after bring the database back up is to reindex all of the tables. I do this before I let anyone use the database. Reindexing is simple and it should only take a couple of seconds to reindex 28 MB of data on anything faster than a laptop from 2005.

  2. Make sure that the database is set to AUTOCLOSE = FALSE. Some environments default this to TRUE, which is wrong for any production server.

  3. As cmenke says, unless you are using a point-in-time recovery strategy, you should set your database to SIMPLE recovery mode. Was the database set to SIMPLE recovery mode on the SBS server?

  4. Does the database on EC2 have the same indexing in place as the old database? You didn't mention how you moved your data, so we don't know if you did a backup and restore, used the Copy Database Wizard or wrote your own SSIS packages. Some methods of moving databases won't preserve your indexes. For any future readers of this answer who are trying to move databases between servers, you should always try to use BACKUP/RESTORE or copy the MDF and LDF files. Using SSIS/DTS for all but the most trivial databases will drive you mad.

  5. If the database is still slow, fire up SQL Profiler (on the server, not on your desktop) and capture a little bit of traffic. Is the problem one big, slow SQL statement or lots and lots of little SQL statements? How many SQL statements are issued by that first page?

Keep in mind that you've already made a bunch of changes to your SQL Server and didn't see any behavior change. Maybe the problem is somewhere else. Is your web page interacting with your AD setup in an unexpected way? Is something failing on the web page without showing you any error? Is the web page looking for something on the old SBS setup that didn't get migrated to EC2? If you have the code and some development chops, I'd have a look through that code.

1

The answer is that EBS is too slow for SQL Server.

I did extensive testing. For SQL reads EBS is very bad. For SQL writes it is so slow that it should never (ever) be used.

There are new EBS volumes with dedicated IOPS. That is the only way.

Set up two EBS volumes: One for ldf, one for mdf.

Tune IOPS until you get perf good.

Put tempdb on local instance storage.

0

It sounds like the majority of your DB is, in fact, cached in RAM. You can use the SQLServer:BufferManager Buffer cache hit ratio counter in perfmon to see what percentage of your queries are hitting the cache rather than disk. The higher the better.

I would also suggest checking your disk and CPU usage, your slowness may not be related to RAM at all.

1
  • "it sounds like the majority of your DB is, in fact, cached in RAM": what makes you say that, because the sqlserver.exe mem usage is roughly equivalent to the database size?
    – gravyface
    Feb 15, 2012 at 13:18
0

The huge transaction log compared to the db size is interesting. I'm not sure whether it is an actual problem, but you can try to get rid of it by doing the following: make sure the recovery model for the db is set to "simple" (not "full"!), then backup the database to a file. That should trigger a checkpoint and allow SQL Server to truncate the log.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .