Introduction
Overview
Intelligent Insights
SQL Server Management Studio
Wait Statistics
Index and Statistics
Storage
Alerts
Summary
The course is part of this learning path
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 performance is an umbrella term for performance recommendations, query performance insight, and automatic tuning. This is not to be confused with intelligent insights which we shall look at shortly. At the top of the monitoring hierarchy for Azure SQL is the database advisor performance recommendations.
Currently, the performance recommendations are limited to the creation and deletion of indexes and recommendations for queries that are being unnecessarily recompiled when only parameters are changing. You could say that this is not even a monitoring feature as you can switch on auto-tuning which will automatically implement the recommendations.
In the context of index-related performance and to a much lesser extent query performance, this could be viewed as a set and forget option. From my experience, I would not rely on this feature to give you the most optimal solution.
You can sit performance recommendations at the database level by turning the features off or on or inherit from those same features that have been set at the server. You are able to clear your performance recommendations via PowerShell scripts with Get-AzSqlDatabaseAdvisor. This command will allow you to see the state of each recommendation feature that is whether it is turned on or off. And you can also add an edit parameters to return the recommendations.
Query performance insight provides a view of the top five resource consuming and long-running queries by CPU usage, data IO and log IO. This information is presented graphically as well as in a tabular form. You have the ability to customize the output by selecting the type of metric to display over what period you want to view the data, the number of queries to be included and how you want the data to be aggregated.
To find out more about query performance monitoring and tuning, check out our Azure SQL and SQL server query performance tuning course.
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.