SQL Azure DTUs at 100% troubleshoot

2
votes

I am performing load testing of my app using visual studio load tests

my tests get data from about 40 tables in sql azure database in seperate api request for each table.

The load testing is simulating around 200 users over 10 mins and about 80-100 requests per second.

During the tests, I have seen that my SQL Azure server DTUs are touching 100% which is clearly not a good sign for the production performance.

This also stays the same, even if I Scale Up my Database with more DTUs.

How do I troubleshoot or address the issue , if it's a specific query which I need to look at or just a scaling issue to address

2
sql-serverazureazure-sql-databaseload-testing

2 Answers

6
votes

  1. Take a look at the Query Performance Insight blade on your Azure SQL Database. This will show you the top queries that are consuming resources and are good candidates for optimization.

  2. Also check out the Performance Recommendations blade on your Azure SQL Database. This will show indexing recommendations after the Analyzer has enough history to see usage and determine if adding (or removing) indexes will deliver benefits.

  3. If you're using an Azure SQL DB with a Web App, plug in Application Insights. This will give you a lot of visibility into what queries are long-running, and what queries are being executed a bunch of times that could benefit from minor optimization.

2
votes

Take a look at the Query Store optimize top queries showing high costs of resources and long running queries. Click here to know more about Query Store.

Run the following query to know what resources show more consumption. Then identify top queries using the most of those resources.

SELECT    
    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',   
    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',   
    AVG(avg_data_io_percent) AS 'Average Data IO In Percent',   
    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',   
    AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent',   
    MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent',   
    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',   
    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'   
FROM sys.dm_db_resource_stats;

Hope this helps.