Performance degradation: increased stored procedure compilation rate after RDS MSSQL Web Edition migration

Question

We recently completed a migration from an RDS SQL Server Standard edition database to the Web edition a few days ago. During the migration, we ensured that the instance class, memory (db.m5.4xlarge with 16 vCPU and 64 GB), parameter group settings, and data volume were all configured to match our previous Standard edition database. Neither the Web Edition nor the Standard Edition are NOT accessible to the public. The Web Edition of RDS MSSQL is running on Engine version 15.00.4316.3.v1, while the Standard Edition is on Engine version 15.00.4312.2.v1.

Additionally, we made sure that the settings for "optimize for ad hoc workloads" and "ad hoc distributed queries" are disabled in the web edition which is just like our previous Standard edition.

However, after the migration to the Web edition, we observed a significant increase in the number of stored procedures being compiled per second, going from approximately 20 to 500. It appears that the Web edition database is not caching execution plans as effectively as the Standard edition. We are uncertain about the reason behind this substantial difference and are trying to determine if this behavior is expected with AWS RDS SQL Server Web edition?

If you have encountered a similar issue before or can provide any insights or criteria to help us investigate and resolve this matter, it would be greatly appreciated.

Answer

One of the limitations of SQL Server Express edition is [scale](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16#Cross-BoxScaleLimits).

To be more precise, Express edition is limited to 4vCPUs and 1410 MB of RAM no matter the size instance.

This means that each time the buffer pool goes above 1Gb (75%) it will cause plan cache pressure, triggering plan evictions hence higher compilations. For more details on cache size management see this [link](https://learn.microsoft.com/en-us/previous-versions/tn-archive/cc293624(v=technet.10)#cache-size-management).

Note "optimize for ad hoc workloads" should not be disabled. When disables SQL Server cannot take advantage of storing just a stub until the second execution, adding to the memory pressure.

Performance degradation: increased stored procedure compilation rate after RDS MSSQL Web Edition migration

Relevant content