AWS RDS Mssql performance issues.

This could be due to latency from the client. What is the distance between the on-prem client and SQL versus the on-prem client and RDS MSSQL? Also, run the query on both platforms and record the query stats to determine exactly where the latency is. Does the query involve any indexes? Are these indexes present on both platforms? Have you checked the query plan of each platform for any differences.. Are statistics up to date? Finally, is the performance of the storage the same with regard to IOPS and throughput.. perhaps there is more caching of data on-prem versus in RDS. I hope these ideas will help you to locate the reason for the difference in query speed.

Great. So, from you answer above I understand that you now have the same execution time as on-prem after updating the indexes. And a SQL Server on EC2 instance that you launched also has the same performance. Looks like you're good.

Also, I do not think that your RDS instance matches the specs you mention.