High DTU consumption in SQL Server Azure Database when executing queries

Iván Cáceres Díaz 0 Reputation points
2023-10-10T22:06:29.0266667+00:00

Hi,

I have a single database migrated to a SQL Server Azure server in a subscription, which has been experiencing high resource consumption problems at the DTU level when storing procedure queries are executed to extract a volume of 180,000 records, which which takes around 5 minutes with P4: 500 DTU and 12 minutes with P2: 250 DTU.

Previously, we had the same database in a previous subscription with the same characteristics of the SQL Server server in Azure, where the query for the 180,000 took 3 minutes with P2: 250 DTU.

Please if anyone has any idea what could be causing this.

Thank you.

Azure SQL Database
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Carlos Robrto Domingues 1 Reputation point
    2023-10-11T01:43:16.3833333+00:00

    Hi.

    Wellcome to Q&A.

    A few questions first.

    Is the database in the same region as the previous one?

    Is the query execution direct or in SQL Manager? Ex.

    Try run the query directly in the Query Editor (preview)

    0 comments No comments

  2. GeethaThatipatri-MSFT 27,987 Reputation points Microsoft Employee
    2023-10-23T16:24:12.04+00:00

    @Iván Cáceres Díaz Based on the information you provided, it seems like you are experiencing high resource consumption problems at the DTU level when executing stored procedure queries to extract a volume of 180,000 records on your Azure SQL Database. This can be caused by a number of factors, such as the size of your database, the complexity of your queries, or the service tier you are using.
    You can use the following link to diagnose and troubleshoot high CPU usage on your Azure SQL Database:

    Diagnose and troubleshoot high CPU - Azure SQL Database | Microsoft Learn

    This link provides information on how to utilize Query Performance Insight to analyze resource consumption over time and identify the top CPU-consuming queries. You can then apply performance recommendations to tune these queries. Review top CPU-consuming queries.

    If you have already tuned the queries and are still experiencing high CPU usage, you may want to consider upgrading the database compute size or service tier to increase CPU capacity. The link also provides information on vCore and DTU purchasing models.

    For more information on detectable types of query performance bottlenecks in Azure SQL Database, you can refer to the link "Detectable types of query performance bottlenecks in Azure SQL Database".

    Detectable types of query performance bottlenecks in Azure SQL Database for more information.

    vCore and DTU Purchasing Models

    If you still have issues I would recommend raising a support case.

    Regards

    Geetha

    0 comments No comments