Azure SQL Database Memory Limits By Service Tier

Background

The new service tiers in Azure SQL Database provide different levels of performance, by offering more of the following at each higher service level:

  • Processing (CPU) Time
  • Read Rate (from physical disk)
  • Write Rate (to the database log file).
  • Memory (space to cache and process data)

One of the frustrating aspects of the new service tiers on a technical level is that Microsoft are not providing any particularly meaningful performance units to measure the new tiers by.  Everywhere you look you find relative units, be it DTUs or be it percentages that measure resource utilisation in each tier or compare between tiers (e.g. in the sys.resource_stats / sys.dm_db_resource_stats DMVs in the master / your database respectively).

My earlier performance tests provided some information about the maximum read and write rates supported by each tier.

I’ve now performed some further tests that provide some information about the memory limits in each tier / performance level.  Results are given first.  The methodology is at the end of this post.

Test Results – Azure SQL DB Memory Allocation

The tests suggest each database is allocated memory as follows:

  • Web / Business Edition – at least 5 GB (likely variable).
  • Basic Tier – 250 MB
  • Standard Tier S0 – 512 MB
  • Standard Tier S1 – 512 MB
  • Standard Tier S2 – 512 MB
  • Premium Tier P1 – 9 GB
  • Premium Tier P2 – 9 GB

Premium P3 was not tested.  It is not possible to obtain exact figures for Web/Business edition since the DMVs don’t contain memory information in that edition.  However, repeating the same tests has proven that it is possible in Web/Business edition to cache at least 5 GB in memory. More on this below.

This test also allowed some write-rate metrics to be captured.  These are more or less identical to those captured during the earlier tests, i.e. nothing has changed here.

Thoughts

Continue reading