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.

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?

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.
–
jl01Feb 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.
–
gravyfaceFeb 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-SmithFeb 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

1 Answer
1

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.