Performance improvement in Azure SQL Database

Question

Hi,

Our Azure SQL Database is configured with Standard-series (Gen5). Most of the time, we experience performance issues, as shown in the attached snippet.

Could you please guide me on how to track down the root cause and suggest possible solutions?

Especiaaly need to figure it out causes for peak Data IO percentage.

Much appreciated.

Answer 1

Hi @MAGUDEESWARAN, THARUNKUMAR

It sounds like you’re experiencing some performance challenges with your Azure SQL Database, especially regarding peaks in Data IO percentage. Here’s a comprehensive approach you can take to address this issue:

Step 1: Monitoring and Diagnostics

Query Performance Insight: Start by using the Query Performance Insight tool to identify the top resource-consuming and long-running queries in your workload. This can provide insight into which queries are contributing most to IO and CPU usage.

Understanding Resource Stats: You can check the sys.resource_stats view to analyze the resource usage over time. Use the following SQL query:

SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'YourDatabaseName'
ORDER BY start_time DESC

Check for Blocking and Deadlocks: Use the documentation on blocking and deadlocking to diagnose if these issues are affecting performance.

Step 2: Performance Tuning

Automatic Tuning: Enable automatic tuning for your database, which automatically applies performance tuning recommendations, like adding missing indexes or fixing query plans that are causing inefficiencies.

Update Index Statistics: Ensure that your index statistics are up-to-date to help the query optimizer make informed decisions. Refer to how to maintain Azure SQL indexes and statistics.

Batch Your Operations: If your application is write-intensive, consider batching your write operations. This can significantly reduce the total IO load on your database. You can find techniques on batching techniques for database applications in Azure.

Step 3: Resource Management

Scale the Database: If you haven’t done so already, consider scaling your database to a higher service tier that can provide more resources (CPU, memory, I/O). You can do this through the Azure portal. When performance improves, you can scale back down if necessary.

Use In-Memory Database Objects: Explore using in-memory database objects to enhance the performance of your workloads, especially for high-performance transaction processing.

Additional Follow-Up Questions

To better assist you further, could you clarify:

• What specific performance metrics (e.g., response time, query duration) are you seeing, and how do they compare to your expected performance?
• Have you identified any specific queries or operations that you suspect may be contributing to high IO usage?
• Have any recent changes been made to your database schema or query patterns that coincide with the start of the performance issues?

Relevant Documentation Links

• Azure SQL Database Overview
• Architecture Best Practices for Azure SQL Database
• Improve Performance of Database Restores
• Resolve Performance Issues in Azure SQL Database

Hope this helps you track down that root cause and improve your database performance! Let me know if you have any more questions!