4

We are using SQL Azure and we have our databases in Elastic pool. It has following parameters:

  • Number of DTUs allowed for the entire pool is 400.
  • Maximum allowed DTUs per database is 100.
  • Number of databases in pool is 50

Simply said 4 of 50 databases can be at its peak load at the same time.

Question is - what happens if 4 databases are at its peak load and 5th gets to peak load too?

Will 5th get 0 DTUs (and thus queries will time out) or will elastic pool distribute DTUs more effectively (that each database gets 400 / 5 = 80 DTUs)?

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63

1 Answers1

3

To my knowledge the number of the databases you estimated that can peak the limit is not correct. The number of databases that can simultaneously peak to their eDTU limit is 2/3 (or 67%) of the databases. But that is considering you estimated the eDTUs needed for the pool as follows:

  1. MAX( [Total number of DBs X average DTU utilization per DB], [Number of concurrently peaking DBs X Peak DTU utilization per DB)]

  2. You should also estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool. Then determine the eDTU pool size that provides this amount of storage.

  3. Finally you should then find the smallest eDTU pool size that is greater than the biggest of the previous 2 eDTUs calculations

If you this math to calculate the eDTU pool size, not only 4 but 2/3 of your databases can reach the limit eDTU per database.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • thanks for answering, i was familiar with this strategy, but i'm curious what happens if as, you said, 67% of databases get to their peak? Lets say I have current pool settings (400 DTU per pool, 100 DTU max per DB, 50DBs im pool). I know it's a worst case scenario, that might never happens, but what if? Will the rest of DBs get 0 DTUs or does pool always keep some DTUs in backup to ensure all DBs get served? – michal.jakubeczy Mar 11 '18 at 20:21
  • I am also asking that currently we've been fine with this setting for a few months. But we noticed MS allows to set more than 100 DTU per DB. And during its peak it will help to improve the performance if single DB gets 200, 300 DTUs. But the problem might be how many DTUs gets the rest of databases. – michal.jakubeczy Mar 11 '18 at 20:23
  • You need to remember you set Min eDTU for the pool, if you are worry about more than 67% of the databases using Max eDTU, please consider setting Min eDTU. You should also set alerts when your databases are spiking more than usual. – Alberto Morillo Mar 12 '18 at 03:17
  • You should also knpw if all DTUs of an elastic pool are used, then each database in the pool receives an equal amount of resources to process queries. The SQL Database service provides resource sharing fairness between databases by ensuring equal slices of compute time. Elastic pool resource sharing fairness is in addition to any amount of resource otherwise guaranteed to each database when the DTU min per database is set to a non-zero value. – Alberto Morillo Mar 12 '18 at 03:19
  • regarding the 'elastic pool resource sharing fairness' - does this only apply when DTU min per DB is set to non-zero value. or does it also apply when this setting is 0? – michal.jakubeczy Mar 12 '18 at 09:34
  • Databases share pool resources when the min DTU per database is 0. For example, consider a 100 DTU pool with 20 databases and a max DTU per database of 10 DTUs and a min DTU per database of 0. This pool wouldn’t support all 20 databases concurrently peaking to 10 DTUs (since that would require 200 pool DTUs), and all 20 databases would share the 100 DTUs of the pool. – Alberto Morillo Mar 12 '18 at 16:49
  • How we can calculate the [Number of concurrently peaking DBs X Peak DTU utilization per DB)] ? – MAFAIZ May 19 '20 at 04:02