• 4 min read

Simplify performance tuning and troubleshooting with Azure SQL Database

Learn how SQL Database helps you improve the performance of your databases by providing intelligent tuning actions and recommendations based on historical database usage. Improve your database performance using SQL Database Advisor and Performance Dashboard.

Azure SQL Database service runs millions of customer databases today. It learns from running all these databases and then adapts to offer customized experiences for each one of them, making you feel as if the service has been specially tailored to your needs. As a result, you are able to focus on delivering business value for your database applications as never before.

SQL Database helps you improve the performance of your databases by providing intelligent tuning recommendations via a new feature called Database Advisor. Recommendations are based on historical database usage, and as your workload evolves over time, the recommendations will automatically adjust to stay relevant.

Database Advisor replaces the former Index Advisor feature. The recommendations provided by SQL Database now go beyond just index management, so we have updated the feature name to reflect the new, broader scope.

In addition, the new SQL Database Performance Dashboard makes performance troubleshooting easier by providing a holistic view of your database performance, replacing the need to switch between many different views to get a good understanding of the current database state.

This blog post will provide a high-level overview of these new capabilities, and the subsequent posts will provide more details on each of these areas.

SQL Database Performance Dashboard: All-in-one overview of your database performance

Performance Dashboard is a new view in Azure Portal that provides the key information related to the performance of your database in one convenient place, including:

  1. Breakdown of top performance tuning recommendations from the SQL Database Advisor.
  2. Overview of the current state of Auto-tuning features.
  3. Overview of the query performance for the recent history.

Performance Dashboard v2

Performance Dashboard is accessed via the “Performance -> Overview” settings entry, or via the new icon on the SQL Database main view:

Performance Tile

Clicking on the tiles on the Performance Dashboard will provide a deeper view into that specific area. Let’s explore each of the areas in more detail.

SQL Database Advisor: Tailored performance tuning recommendations for your database

Clicking on the Recommendations tile on the Performance Dashboard takes you to Azure SQL Database Advisor (formerly known as Index Advisor), which helps you achieve and maintain great performance for your SQL Databases by:

  1. Providing tailored performance tuning recommendations for your database, based on observed historical usage.
  2. Making it easy to implement and validate the recommended tuning actions. All it takes are a couple of clicks to start the fully-managed implementation process, which applies the recommended action and generates a report on the outcome.

13a

In this release, Database Advisor covers the following aspects of database performance tuning:

Area Scope

Optimize the layout of non-clustered indexes
(previously available via Index Advisor)

Create missing indexes to improve query performance, and drop duplicate indexes to save on disk space.

Fix database schema issues
(new in this release)

Covers situations where database schema and query definitions do not match, causing queries to fail.

Fix database parameterization issues
(new in this release)

Covers situations where lack of proper query parameterization leads to excessive resource usage for query compilation.

 

Automated Performance Tuning

Tired of managing the indexes for your databases? Well you don’t have to do this yourself anymore – you can now tell Azure SQL Database that you’d like to automate the implementation of certain types of recommendations. All it takes are a couple of clicks, and SQL Database will automatically implement the new recommendations of the selected type as soon as they are published. Simple and effective.

Set up automation

Query Performance Insight: Real-time insights into the performance of your database queries

Clicking on the chart on the Performance Dashboard opens the Query Performance Insight feature, which allows you to understand what is driving your database resource usage and detect any query performance issues before they become a problem.

Query Performance Insight provides customers with:

  • Deeper insights into your databases resource consumption.
  • The top CPU consuming queries, which can potentially be tuned for improved performance.
  • The ability to drill down into the details of a query.

14

Query Performance Insight is built on top of Query Store and surfaces some of the top insights from the rich query performance data captured by the Query Store. Users that are interested in more details can leverage the Query Store views in SQL Server Management Studio (explained in this blog post) or explore the data using Query Store built-in catalog views as described in this article.

Summary

Azure SQL Database makes performance tuning and troubleshooting easier and faster than ever before, helping you to deliver great performance for your database applications while saving you time and effort in the process. SQL Database provides its users with:

  • Tailor-made performance tuning recommendations based on historical database usage.
  • “Hands-free” performance optimization via auto-tuning mode that automates the implementation and validation of database tuning actions.
  • Insights into query workload performance over time that make it easy to find and fix potential issues.

So let Azure SQL Database manage and tune your databases, while you focus your energy on building awesome database applications and impacting the bottom line of your business.

Next steps

If you’re new to Azure SQL Database, sign up now for a free trial and discover how built-in intelligent capabilities of Azure SQL DB make it easier and faster than ever to build amazing database applications.

If you’re already using Azure SQL Database, try SQL Database Advisor and SQL Database Performance Dashboard today and share your feedback with us using the built-in feedback mechanism on the Azure Portal, or in the comments section of this post. We’d love to hear back from you!

Feedback Button

For more detailed information, check out the online documentation: