Hello All;
I'm working through evaluating whether In-Memory OLTP can provide benefit on an RDS instance of SQL Server. I'm using the "Quick test..." script here to observe the behavior:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver15#f-quick-test-to-prove-speed-improvement
What I'm seeing:
- On a local instance (laptop-grade), the script behaves as expected. Approximately the same timings as given in the comments at the bottom of the script - 1 second for the memory-optimized portion, and 5 seconds for the tempdb portion.
- On an RDS instance, SQL Server Web edition provisioned on a "standard class" EC2 instance (db.m5.xlarge, 4vCPUs, 16 GB RAM), the script performs abysmally - about 4.5 minutes to execute each loop of 5001 iterations, for a total of 9 minutes, with no significant distinction between the two methods being compared.
- On an RDS instance, SQL Server Web edition provisioned on a "memory-optimized" EC2 instance (db.r4.large, 2vCPUs, 15.25 GB RAM), same behavior - about 4.5 minutes to execute each section of the script (9 minutes total)
Two questions arising from the above observations:
1) Is there an obvious setting I'm overlooking when configuring my AWS RDS instances?
2) Ignoring the memory-optimized results, is there an explanation as to why the "tempdb" version performs so poorly on RDS?
Thanks for any insight and ideas for further investigation.