How is it possible to set the minimum amount of CPU for a database inside a pool in Azure?

Bruno Silva 5 Reputation points
2023-07-20T17:15:05.3566667+00:00

Good afternoon,

For some time now, we have been experiencing issues regarding resources in a database pool. as a first solution we decided to increase the amount of vCore to improve processing, for a few days everything went well, but today a strange situation happened …

Even with resources available in the pool, at the peak moment of a db it considerably decreases the CPU % of another db... with that it ended up generating a slowdown in our entire system.User's image

In consultation with our database using the query

SELECT * FROM SYS.DM_USER_DB_RESOURCE_GOVERNANCE AS RG WHERE DATABASE_ID = DB_ID();

We have identified that the MIN_CPU is set to 0.

How can we perform an operation to change this MIN_CPU value?

How is it possible to set the minimum amount of CPU for a database inside a pool in Azure?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,256 questions
{count} votes

2 answers

Sort by: Most helpful
  1. RevelinoB 2,380 Reputation points
    2023-07-20T17:31:41.9633333+00:00

    Good afternoon Bruno,

    To change the MIN_CPU value for a database inside an Azure SQL Database pool, you can use the ALTER DATABASE SCOPED CONFIGURATION statement. This statement allows you to configure various settings for individual databases within the pool, including the MIN_CPU_PERCENT value.

    Here's an example of how you can set the MIN_CPU_PERCENT value for a specific database within the pool:

    -- Connect to the specific database you want to configure

    USE [YourDatabaseName];

    -- Set the MIN_CPU_PERCENT value

    ALTER DATABASE SCOPED CONFIGURATION SET MIN_CPU_PERCENT = 10; -- Replace 10 with the desired minimum CPU percentage

    -- Verify the configuration change

    SELECT * FROM SYS.DM_USER_DB_RESOURCE_GOVERNANCE WHERE DATABASE_ID = DB_ID();

    In the above example, I've set the MIN_CPU_PERCENT value to 10 as an example. You can replace it with the desired minimum CPU percentage for your database. Keep in mind that the value must be within the range defined by the maximum and minimum values specified at the pool level.

    After executing the ALTER DATABASE SCOPED CONFIGURATION statement, you can verify the configuration change by querying the SYS.DM_USER_DB_RESOURCE_GOVERNANCE view, as you did before, to ensure that the MIN_CPU_PERCENT has been set to the desired value for the specific database.

    By setting the MIN_CPU_PERCENT value, you can ensure that a certain percentage of the CPU resources will be guaranteed for a particular database, preventing excessive resource contention and potential slowdowns in the system

    I hope this helps with your query?

    1 person found this answer helpful.

  2. Oury Ba-MSFT 15,116 Reputation points Microsoft Employee
    2023-07-21T22:47:32.3266667+00:00

    @Bruno Silva Please see answer below.

    The minimum number of vCores reserved for any database in the pool. Consider setting a min vCores per database when you want to guarantee resource availability for each database regardless of resource consumption by other databases in the pool. The min vCores per database may be set to 0, and is also the default value. This property is set to anywhere between 0 and the average vCores utilization per database.

    Customizing min and max vCores for individual databases in the pool is not supported.

    Resource limits for elastic pools using the vCore purchasing model

    Regards,

    Oury