Why is the data IO percentage for my elastic pool around 50% while a database in the pool is hitting the 100%?

Question

I switched from dtu pricing model to the vCore model (seems to have higher IOPS limit). But it looks like every DB in the pool can only use part of this IO operations limit? Here there are 2 dbs in the pool and only the one shown has any usage. The per database slider is set from 0-max vCores (standard setting). P.S.: I know we need to optimize the queries, but the db is hosting big datasets around 100gb that we calculate on using stored procedures. IO seems to be the throttling limit.

Answer 1

@Roel Horeman

FYI, I found a dm view that may explain the difference between throttling at the pool level and database level. I also found a bit of documentation may explain the behavior as well.

I suspect the question has started to come up frequently, given the language in the documentation, and the prominence at the top of the page. Here is the documentation for starters:

https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-elastic-pools?view=azuresql

For the same number of vCores, resources provided to an elastic pool may ***exceed*** the resources provided to a single database outside of an elastic pool. 

 ... in an extreme case with only one database in an elastic pool where database Data IO utilization is 100%, it's possible for pool Data IO utilization to be 50% for certain workload patterns. This can happen even if max vCores per database remains at the ***maximum*** supported value for the given pool size.

...I suppose what they are saying is that in an elastic pool Microsoft generously gives us more IO than we would otherwise have in a standalone Azure-sql-database which is similarly configured (with the same number of vcores). However the generosity at the pool level is somewhat limited - it is a communal benefit that is provided to more than one member-database within the pool. The extra allowance of IO bandwidth cannot be allocated entirely to a single database within the pool.[ ](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-user-db-resource-governor-azure-sql-database?view=azuresqldb-current

)

I think there is a dm view that might be used to quantify the IO that is generously provisioned at the pool level, and contrasted with the IO for an individual database.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-user-db-resource-governor-azure-sql-database?view=azuresqldb-current

              -- Get the governance restrictions. 
              select * from sys.dm_user_db_resource_governance

              where database_name = 'MyDatabase02'

...

The IOPS are the interesting part. In my case I have these values:

pool_max_io = 2800

primary_group_max_io = 1280

... and I suspect this indicates that any given database is only allowed to use 1280 iops, but the pool as a whole is able to do 2800 iops.

The bothersome part is that, if my assumptions are correct, this information directly contradicts other docs I had seen which indicates that the vcore allocation (max vcores per databases) will be respected for the purpose of allocating the other database resources as well.

Bonus - Another dm view that I found useful is dm_resource_governor_resource_pools_history_ex . I think you have to be an "entra admin" to use it, but that one will actually tell you when your IOPS are being throttled. And it will show you your total throughput in IOPS and bytes/sec for the pool.

Hope this helps.

Answer 2

This limits are enforced by the Resource Governance and we can see those limits using the DMVs that @David Beavon mention in his post above.

For more details about the Resource Governance and how it handles the Data IO I advise to read the sections related to the Resource Governance on this documentation page https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server?view=azuresql#resource-governance