Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
2  
Ah the glories of "cloud" computing... – JNK♦ Feb 16 '12 at 14:52
What TSQL is being executed for the load? If the old environment is still available, any way to compare the query plan there with the cloud? How many hops are being made between the typical desktop and the new environment? Thinking tracert and maybe some network latency due to the new set up. – jl01 Feb 16 '12 at 17:27
@jl01 TSQL is identical: backed up/restored DB to EC2 instance; not a client-side network issue as pulling up the slow query page locally from within the RDP session on the EC2 server is as slow as over the Internet. – gravyface Feb 16 '12 at 17:32
1  
Have you run a Profiler trace to confirm that it is definitely the queries that are running slowly (not the application)?Could also try sp_whoisactive logging to table while you hit the page on the slow server. – Mark Storey-Smith Feb 16 '12 at 18:12
2  
Why do you think SQL Server is only using 28MB of RAM? I hope you're not relying on Task Manager to tell you. This is horrifically inaccurate especially on 32-bit systems. Use Perfmon or sys.dm_os_buffer_descriptors instead. stackoverflow.com/questions/1289558/… Anyway if the database is only 28MB then I wouldn't expect SQL Server to use more memory (at least for buffer pool; memory is used for other reasons). And I would suspect the slowness issues are not memory-related at all. – Aaron Bertrand Feb 16 '12 at 18:20
show 7 more comments

1 Answer

up vote 1 down vote

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).

share|improve this answer
Did you update the statistics and rebuild all the indexes on your instance after the upgrade to 2008? – Marian Feb 23 '12 at 15:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.