The Right Tools for Optimizing Azure SQL Managed Instance Performance

[RieMerritt]

Note: This blog is the second half of a series on maximizing performance in Azure SQL Managed Instance. If you missed the first half, check out Set Yourself Up for Performance Success on SQL Managed Instance. 

The Right Tools for Optimizing Azure SQL Managed Instance Performance

Once you have setup your managed instance to meet your initial requirements, you have laid the foundation for excellent performance. But, given the dynamic nature of demands placed on your managed instance – indeed to any SQL server – outstanding performance is an active pursuit more than a destination. In today’s blog we’ll look at some of the tools and features you can use to both monitor and optimize performance, many of which are automatic and done on your behalf, so your database is always ready for whatever demands are placed on it. 

Resource governance  

Have you been using either a simple or bulk recovery model prior to migration to SQL Managed Instance? To provide high availability and guarantee no data loss, Azure SQL Managed Instance requires full database recovery, which generates more log data than a simple/bulk logged recovery model.  This can make your DML transaction processing a bit slower. 

Azure SQL Managed Instance includes built-in resource governance that ensures 99.99% availability. Resource governance also guarantees that management operations will be completed even under high workloads. As an example, Azure SQL Managed Instance limits log throughput to ensure that you do not load more data than the instance can backup. Resource governance weighs performance cost against potential data loss and automatically opts to protect data. As a result, you may detect unfamiliar wait statistics. For more information, see Key causes of performance differences between SQL Managed Instance and SQL Server and be sure to check wait statistics:  

• sys.dm_os_wait_stats returns instance-level wait stats 

• sys.dm_exec_session_wait_stats provides wait stats for your session 

• sys.query_store_wait_stats returns query/plan-level wait statistics. 

Monitoring and Tuning 

Azure SQL Managed Instance gives you several tools both to monitor and to tune performance on the fly. You need to understand what is happening before you can develop and execute a performance tuning plan. Performance tuning therefore begins with monitoring. 

To monitor the performance of your managed instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier and performance level. To accomplish this, Azure SQL Managed Instance emits resource metrics that can be viewed in the Azure portal or by using either Azure Data Studio, based on Visual Studio Code, or SQL Server Management Studio (SSMS), based on Microsoft Visual Studio. 

Azure SQL Managed Instance provides options within and outside Azure portal for troubleshooting and optimizing performance.  Within the portal, you can leverage automatic tuning and Intelligent Insights. Outside of the Azure Portal, you can take advantage of the capabilities that are already in the database engine, such as query store and dynamic management views (DMV). In addition, Microsoft offers several monitoring options that are in preview: Azure SQL Insights inside Azure Monitor, which requires an agent on a VM you own, Azure SQL Analytics, and Azure diagnostic telemetry. 

Automatic tuning in SQL Managed Instance supports FORCE LAST GOOD PLAN, which identifies queries using an execution plan that is slower than the previous good plan. It forces queries to use the last known good execution plan. Since the system automatically monitors the workload performance, in case of changing workloads, the system dynamically adjusts to force the best performing query execution plan. 

Automatic tuning automatically validates all tuning actions performed to ensure that each tuning action results in a positive performance gain. In case of performance degradation due to a tuning action, the system automatically learns and promptly reverts such tuning recommendation. Tuning actions performed by automatic tuning can be viewed by users in the list of recent tuning recommendations through Azure portal and T-SQL queries.  

Intelligent Insights in SQL Managed Instance continuously monitors database usage through artificial intelligence and detects events that negatively impact performance. The feature performs a detailed analysis that generates a resource log with an intelligent assessment of the issues. The assessment consists of a root cause analysis and, where possible, recommendations for improving performance. Intelligent Insights provides proactive monitoring, tailored performance insights, early detection of database performance degradation, root cause analysis of the issues detected and performance improvement recommendations. You can scale Intelligent Insights to hundreds of thousands of databases.  

Intelligent Insights compares database workloads from the last hour against the past seven-day baseline workload. Database workload is comprised of the queries deemed most significant to performance. Because each database is unique based on structure, data, usage, and application, each workload baseline is specific to each workload. Intelligent Insights, independent of the workload baseline, also monitors absolute operational thresholds and detects issues with excessive wait times, critical exceptions, and issues with query parameterizations that might affect performance. When Intelligent Insights detects a performance degradation issue, the service performs an analysis and generates a diagnostics log. Each detected issue is tracked through its lifecycle from initial detection and verification of performance improvement to completion. 

Intelligent Insights lets you stream output to different destinations for analysis.  

• Output streamed to a Log Analytics workspace can be used with Azure SQL Analytics to view insights through the user interface of the Azure portal. This is the integrated Azure solution, and the most typical way to view insights. 

• Output streamed to Azure Event Hubs can be used for development of custom monitoring and alerting scenarios. 

• Output streamed to Azure Storage can be used for custom application development for custom reporting, long-term data archival, and so forth. 

Azure diagnostic telemetry is a separate, streaming source of data for Azure SQL Managed Instance. SQLInsights is a log inside Intelligent Insights and is one of several packages of telemetry emitted by Azure diagnostic settings. Diagnostic settings are a feature containing Resource Log categories (formerly known as Diagnostic Logs). For more information, see Diagnostic telemetry for export. Azure SQL Analytics (preview) consumes the resource logs coming from the diagnostic telemetry (configurable under Diagnostic Settings in the Azure portal). 

Azure SQL Analytics (preview.) is an integration with Azure Monitor and is an advanced cloud monitoring solution for monitoring performance of your SQL Managed Instance databases at scale and across multiple subscriptions, in a single view. Azure SQL Analytics collects and visualizes key performance metrics with built-in intelligence for performance troubleshooting. This allows you to create custom monitoring rules and alerts. Azure SQL Analytics helps you to identify issues at each layer of your application stack. Azure SQL Analytics uses Azure Diagnostics metrics along with Azure Monitor views to present data in a single Log Analytics workspace.  

Finally, dynamic management views detect performance bottlenecks and determine if performance issues occur during query run or during a waiting state. Query run issues tend to be compilation problems that result in a suboptimal query plan or execution issues related to insufficient or overused resources. Wait-related problems are usually a question of locks/blocking, I/O, contention related to tempdb usage, or memory grant waits.  

Azure SQL Managed Instance offers a variety of monitoring and tuning tools that provide deep insights, recommendations, and, in some cases, actions to ensure peak performance. Azure SQL Managed Instance makes it easy to leverage the configuration that best optimizes your time, money, and most importantly, your database performance. 

To learn more check out our on demand webinar:  Set Yourself Up for Performance Success on Azure SQL Managed Instance