Information is at the heart of most software systems and the lifeblood of many organizations, so you want the database that stores this information to be efficient and reliable. But as we know, things happen; sometimes bad things. One of the ways that we can prevent bad things from happening is to know about them in advance like the old saying says, "To be forewarned is to be forearmed."
Azure SQL in its many forms has an abundance of features that help you to monitor the state of your databases and database server. Ranging from prebuilt automated monitoring that is augmented with artificial intelligence through to dynamic management views, SQL Server monitors and logs all aspects of the database engine’s operation and configuration. Intelligent Insights and Azure SQL analytics enable you to easily access the wealth of diagnostic and performance data in an easily digestible format.
This course introduces you to the different database monitoring and notification technologies available, how they work, and how to use them. If you have any feedback relating to this course, feel free to contact us at support@cloudacademy.com.
Azure SQL and SQL Server Query Performance Tuning
Learning Objectives
- Understand the key elements of database monitoring
- Learn about the features of Intelligent Insights, Azure's AI-based database monitoring service
- Create graphical reports using SQL Server Management Studio
- Understand how wait statistics can show you where threads have to wait and how this can be used to monitor performance
- View and fix index fragmentation
- Monitor database storage
- Implement notification alerts on various database platforms
Intended Audience
This course is aimed at database administrators or anyone who wants to learn how to implement systems that can find potential issues that may disrupt the delivery of their database services.
Prerequisites
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
https://github.com/cloudacademy/azure-sql-database-monitoring-dp-300
Intelligent Insights which applies to Azure SQL and SQL Managed Instances, uses artificial intelligence based on data gained from monitoring many thousands of Azure SQL databases, to proactively warn you of performance issues within your database, what the root cause of the problem is, and recommend a solution.
While the AI of Intelligent Insights can leverage off all Azure SQL databases to some extent, for any one database it compares current performance and workload over the last hour, with that of the past week, or seven days. This is a way for Intelligent Insights to perform a kind of baseline comparison analysis, with a moving average baseline.
While a baseline comparison gives you an idea of relative performance, there are absolute metrics like errors and corrupted data that are also monitored and reported. Not only does Intelligent Insights use AI to monitor performance, but artificial intelligence is also utilized to diagnose and establish the root cause of any detected issues. This all sounds great in theory, but at the same time a little vague.
So how does Intelligent Insights actually detect performance issues? Well, it uses a combination of query duration, timeout requests, excessive wait times, and errors. Query duration is reasonably self-explanatory, as in a query taking a long time to complete, in comparison with its baseline performance, that is average completion time over the last seven days.
In a similar mode, it monitors the number of requests that are currently timing out versus the baseline. Excessive wait times can give a more granular view of what is happening with query execution, and what might be the root cause of slow or failing queries.
Errors are never a good thing and artificial intelligence helps to determine whether the error, is due to a database or degrading performance issue. An insight is an instance of a database issue or performance degradation. Each insight is recorded in the SQL Insights Log, with the following information.
There is the database where the issue has occurred, along with when it occurred and for how long it continued to be an issue. The impact or issue is defined in terms of those metrics we have just talked about, query duration, excessive wait time, timed-out requests, and errored-out requests, with their respective values.
Any queries that have been impacted are identified via a query hash or error code. Intelligent Insights will try to categorize the problem, as one of, what Azure calls 15 detection patterns. These detection patterns are things like, running out of resources due to an increase in workload, or reaching the limit of the current pricing tier, or due to a pricing tier downgrade.
There are several contention patterns, like page latch or TempDB contention, or excessive locking. There are configuration patterns, as in a database configuration change has been detected, or the MAXDOP setting has been changed, affecting the degree of parallelism. There are also query-based patterns, like a missing index, a new query is impacting performance, or a query is suffering from plan regression.
For a full description of the detection patterns, visit docs.microsoft.com/azure/azure-sql/database/intelligent-insights-troubleshoot-performance. The insight also includes a human-readable root cause analysis of the issue, with a performance improvement recommendation if applicable.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.