AWS RDS Sql Server and In-Memory OLTP

• cs_troyk

  SSCertifiable

  Points: 5383

  More actions

  April 26, 2020 at 6:36 pm

  #3746447

  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.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 27, 2020 at 7:10 pm

  #3746773

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.