About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

Azure SQL Database Query Performance Insight - Intelligent Insights -Performance recommendation - Automatic tuning in Azure SQL Database

Here we will discuss

1.Azure SQL Database Query Performance Insight

2.Intelligent Insights

3.Performance recommendation in the Azure portal

4.Automatic tuning in Azure SQL Database





1.Azure SQL Database Query Performance Insight


Managing and tuning the performance of relational databases is a challenging task that requires significant expertise and time investment.
Query Performance Insight allows you to spend less time troubleshooting database performance by providing the following:

1.Deeper insight into your databases resource (DTU) consumption.
2.The top queries by CPU/Duration/Execution count, which can potentially be tuned for improved performance.
3.The ability to drill down into the details of a query, view its text and history of resource utilization.
Performance tuning annotations that show actions performed by SQL Azure Database Advisor

Prerequisites:
Query Performance Insight requires that Query Store is active on your database. If Query Store is not running, the portal prompts you to turn it on.

Permissions:
The following role-based access control permissions are required to use Query Performance Insight:

Reader, Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view the top resource consuming queries and charts.
Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view query text.


2.Intelligent Insights


Azure SQL Database Intelligent Insights lets you know what is happening with your database performance.

Intelligent Insights uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. Once detected, a detailed analysis is performed that generates a diagnostics log with an intelligent assessment of the issue.
This assessment consists of a root cause analysis of the database performance issue and, where possible, recommendations for performance improvements.

What can Intelligent Insights do for you?


Intelligent Insights is a unique capability of Azure built-in intelligence that provides the following value:


  1. Proactive monitoring
  2. Tailored performance insights
  3. Early detection of database performance degradation
  4. Root cause analysis of issues detected
  5. Performance improvement recommendations
  6. Scale out capability on hundreds of thousands of databases
  7. Positive impact to DevOps resources and the total cost of ownership

3.Performance recommendation in the Azure portal


Find and apply performance recommendations


You can use the Azure portal to find performance recommendations that can optimize performance of your Azure SQL Database or to correct some issue identified in your workload. Performance recommendation page in Azure portal enables you to find the top recommendations based on their potential impact.

Viewing recommendations


To view and apply performance recommendations, you need the correct role-based access control permissions in Azure. Reader, SQL DB Contributor permissions are required to view recommendations, and Owner, SQL DB Contributor permissions are required to execute any actions; create or drop indexes and cancel index creation.

4.Automatic tuning in Azure SQL Database


Azure SQL Database Automatic tuning provides peak performance and stable workloads through continuous performance tuning utilizing Artificial Intelligence.

Automatic tuning is a fully managed service that uses built-in intelligence to continuously monitor queries executed on a database and it automatically improves their performance. This is achieved through dynamically adapting database to the changing workloads and applying tuning recommendations. Automatic tuning learns horizontally from all databases on Azure through Artificial Intelligence and it dynamically improves its tuning actions. The longer an Azure SQL Database runs with automatic tuning on, the better it performs.

Azure SQL Database Automatic tuning might be one of the most important features that you can enable to provide stable and peak performing workloads.

  1. What can Automatic Tuning do for you?
  2. Automated performance tuning of Azure SQL Databases
  3. Automated verification of performance gains
  4. Automated rollback and self-correction
  5. Tuning history log
  6. Tuning action T-SQL scripts for manual deployments
  7. Proactive workload performance monitoring
  8. Scale out capability on hundreds of thousands of databases
  9. Positive impact to DevOps resources and the total cost of ownership

Labels: , , ,