Moving your database to the cloud using a PaaS option such as Azure SQL Database or Azure SQL Managed Instance reduces the maintenance overhead required from the database administrator (DBA). The DBA no longer must worry about managing backups or configuring high availability, for example.
That said, the DBA still needs to tune the database’s performance and monitor SQL workloads. The Azure Portal offers several services to quickly identify performance issues for Azure SQL Databases.
Monitoring Metrics
The first place to start performance tuning is with monitoring, and Azure inherently does some level of baseline monitoring. Inside the Azure Portal, you can enable monitoring using the Metrics option under the Monitoring blade of your Azure SQL Database, as shown in Figure 1.
Azure enables visibility of processor, memory, IO resources, DTU consumption, deadlocks, Worker usage, and much more through easy-to-read and customizable graphs. By default, metrics are stored for 45 days, and each graph you design can be pinned to a reusable and shareable dashboard.
Figure 1: Database metrics in Azure
You can also build alerts on metrics to, when the specific event happens, either notify you or take automated actions against your Azure SQL Database.
Query Performance Insight
Query Performance Insight is one of the most useful metrics within the portal. Using the functionality of the Query Store inside SQL Server Management Studio to track runtime stats, including duration, CPU consumption, execution counts, and more, this feature allows the DBA to quickly identify the queries most affecting performance within a time range.
Query Performance Insight gives you the ability to drill down to even see the actual text run and provides a query ID. You can then use Query Store to dive into more information and take corrective measures by forcing a new plan if needed.
The example display in Figure 2 shows query ID 20649733 is a top resource consumer and has been run 170 times in the past 24 hours. Another thing that should catch your attention is the final query ID 19537085, which has an execution count of 8,957. If you’re familiar with your normal workloads, this level of executions may indicate an abnormality.
Figure 2: Spikes in particular query times shown by Query Store
You can drill into the first query ID by clicking on its row, thus seeing the actual query that ran and the hourly runtime information (Figure 3). A skew in run times could indicate a plan regression you could fix by forcing a previous plan to be used.
Figure 3: Details about a particular query
This breakout makes it very easy to spot any outliers that may need tuning. In this case, the @P variables listed near the top clearly show the problem is caused by an entity framework query—this indicates actions by an object-relational mapper (ORM) and frequently causes performance issues.
If you’re using read-only geo-replication, the data shown about the secondary replica actually comes from the primary database—no data from the secondary’s workload is reflected in Query Performance Insight.
This option requires Query Store, which must be enabled on your database to take advantage of this feature. Query Store is enabled by default for Azure SQL Databases.
Automatic Tuning
Automatic Tuning is exceptionally useful (Figure 4). It continuously monitors query performance, automatically implements indexes, and forces plan corrections to instantly improve your SQL Database.
Azure also has built-in options for choosing whether you want recommendations implemented automatically or listed along with a script to implement the changes yourself. If you choose to have them autonomously applied, there are built-in rollback mechanisms to ensure the changes are automatically reverted if significant regressions manifest after implementation.
Recommendations applied and/or suggested will remain in the portal for 24-48 hours, so it’s best to keep an eye on these because they’ll disappear. Like Query Performance Insights, this feature relies heavily on Query Store.
Figure 4: Options for Automatic Tuning
Tool Up to Sync Up
This was just a quick overview of performance troubleshooting with Azure SQL Database. And once you get beyond the basics, you may find yourself in need of more monitoring help.
And help is available, with many tools out there built to help you more effectively monitor your cloud environments. If you want deeper insight into database performance than you can get from the Azure Portal, SolarWinds® SQL Sentry® Azure database performance monitoring and SolarWinds Database Performance Analyzer for Azure SQL databases should be at the top of your “check it out” list. Both tools are designed to provide the information you need to quickly identify issues and proactively prevent database problems, with features like in-depth Azure SQL database performance monitoring, simplified analysis, and performance optimization.
You can get started with a free trial of SQL Sentry or download a free DPA trial to see for yourself what they can do.
