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:
- Breakdown of top performance tuning recommendations from the SQL Database Advisor.
- Overview of the current state of Auto-tuning features.
- Overview of the query performance for the recent history.
Performance Dashboard is accessed via the “Performance -> Overview” settings entry, or via the new icon on the SQL Database main view:
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:
- Providing tailored performance tuning recommendations for your database, based on observed historical usage.
- 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.
In this release, Database Advisor covers the following aspects of database performance tuning:
Area | Scope |
Optimize the layout of non-clustered indexes |
Create missing indexes to improve query performance, and drop duplicate indexes to save on disk space. |
Fix database schema issues |
Covers situations where database schema and query definitions do not match, causing queries to fail. |
Fix database parameterization issues |
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.
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.
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!
For more detailed information, check out the online documentation: