O SQL Server usa toda a memória disponível por design. Uma questão melhor é como essa memória está sendo usada? Você pode ajustar a memória máxima via sp_configure:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO sp_configure 'max server memory', 1024; --Set to 1GB
GO RECONFIGURE;
GO
Em seguida, você precisará reiniciar o processo do SQL Server.
Você está vendo uma quantidade significativa de IO aguardando por consultas? Sua memória está sendo usada para armazenar em cache os planos de consulta para solicitações ad-hoc versus procs armazenados? Glenn Berry tem algumas ótimas consultas de ajuste básicas disponíveis em: link
Quanto ao dimensionamento na AWS, mais memória significa uma maior taxa de acertos do cache de buffer, mas aproveitar o RDS é simplesmente abstrair a manutenção do seu log. Você ainda verá a utilização da memória e terá que escolher um tamanho de instância.