3

I am running a website of off a small EC2 instance. The memory on the instance is maxed out, mostly due to SQL server process. I am thinking about either upgrading to a medium instance or staying with a small instance, but transfering the DB to RDS (with the new SQL server instance).

In terms of price, both options are more or less the same. In terms of scalability, going with RDS seems to be the better choice, since it will allow me to add more server on a load balacer, and feed them all off of the same RDS DB server.

On the other hand, communication between the DB and IIS machines might become an issue, and session, cache, authentication will become more complex (share machine key and the likes).

What information and data points should I consider when making this decision?

Wesley
  • 32,690
  • 9
  • 82
  • 117
Elad Lachmi
  • 189
  • 1
  • 11

1 Answers1

2

SQL Server uses all available memory by design. A better question will be what is that memory being used for? You can adjust the max memory via sp_configure:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO sp_configure 'max server memory', 1024; --Set to 1GB 
GO RECONFIGURE; 
GO

Then you'll need to restart the SQL Server process.

Are you seeing a significant amount of IO waits for queries? Is your memory being used to cache query plans for ad-hoc requests vs stored procs? Glenn Berry has some great basic tuning queries available at: http://sqlserverperformance.wordpress.com/2008/01/21/five-dmv-queries-that-will-make-you-a-superhero/

As for scaling on AWS, more memory means a higher potential buffer cache hit ratio, but leveraging RDS is simply abstracting out your log maintenance. You'll still see memory utilization and have to choose an instance size.

sqlreader
  • 281
  • 1
  • 3
  • 8