Slow Database Response which appears from time to time on executing some queries

Question

From time to time, we are experiencing unusual behavior (slow database response) on our databases on Azure. Some of the stored procedures, that usually are executed in milliseconds, need too much time for execution. There is no specific rule how and when this issue appears.

Common for these issues are queries (several queries) which are not accessed as frequently as other ones.

So far, this is the “procedure” how we are handling those issues when appears:

• Find which stored procedure (query) returns timeout (needs unusual amount of time to be executed)
• Run the query from SQL Management Studio - time to execute varies from 30 sec. up to 5-6 minutes - independently on input parameters and amount on return data
• After first successful execution, every further execution of the same query is in milliseconds – again, independently on input parameters and amount on return data

When issue appears, we’ve notice that DTU’s are rising to 100% and stay on that level until query finish with execution. In most cases, before running the query, DTU percentage was 0%. We’ve tried to solve this problem with upgrading pricing tire (ex. From S3 to S4), but there was no success.

As part of regular database maintenance, twice a month we are running Azure SQL DB diagnostics. Each time there are no more than 5 indexes with fragmentation more than 30% (but no more than 50%) and once in 2-3 months appears one or two missing indexes. Also, statistics are updated each maintenance session with fullscan

Any idea how to solve this?

Answer 1

Please enable asynchronous statistics update, and make sure that the configuration to wait at low priority for asynchronous statistics update is disabled. This will avoid some Sch-M locks that auto statistics update can produce and that can impact query performance.

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;  
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;  

The following query tells you which resource consumption is creating DTU spike:

SELECT   
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'  
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'  
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'  
FROM sys.dm_db_resource_stats  

Once you determine which resource is creating the spike go to Query Performance Insight and see top queries affecting that resource:

Click on any query which shows high resource usage. It will show you the query statements and give you the performance recommendations. Examine the query plan of the query and see operators showing high costs.

Please make sure you update statistics daily. You can use Ola scripts to speed up the process.

EXECUTE dbo.IndexOptimize  
@Databases = 'YourDBName',  
@FragmentationLow = NULL,  
@FragmentationMedium = NULL,  
@FragmentationHigh = NULL,  
@UpdateStatistics = 'ALL',  
@OnlyModifiedStatistics = 'Y'  

Answer 2

Thank You for Your reply. Auto Statistics parameters are set as recommended.
Since there was not any problems recent days, when (if) appears I'll check Query Performance and recommendation.

Thank You again