SQL Server Management Studio
Index and Statistics
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 email@example.com.
Azure SQL and SQL Server Query Performance Tuning
- 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
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.
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
Hi and welcome to this Azure SQL and SQL Server Database monitoring course. In this course, we will be focusing on monitoring the overall health and performance of Azure and SQL servers and their databases. We will start with relatively new technology that greatly simplifies and enhances system monitoring through to traditional database mechanisms which underlie that technology.
This course is squarely aimed at database administrators and how you can put in place mechanisms to automatically and proactively look for potential issues that may disrupt the delivery of your database services. With that in mind, this course assumes that you have the pre-requisite experience using the Azure portal, SQL Server Management Studio, and familiarity with T-SQL.
My name is Hallam Webber and I'll be your instructor for this course. We welcome all comments and feedback, so please feel free to reach out to us at firstname.lastname@example.org with any questions or comments.
Let's dive in.
In an ideal world, our databases would perform optimally all the time. But for obvious reasons, primarily because databases just by their nature are dynamic, meaning they grow and change over time, what was once an ideal configuration or state, no longer is. Not only can changes be in the form of more data, requiring index and statistics maintenance, but also functional changes by way of different and sometimes competing queries that result in contention for database resources.
Just from this very brief and general description, we can deduce a broad array of metrics and problems that come into play. Database growth means increased storage, I/O, CPU and possibly DTU requirements amongst other things. Without the appropriate metrics, it would be difficult to tell which of these factors were impeding performance. When new functionality is added to a database there can potentially be conflict contention with existing process and queries, resulting in excessive wait times and deadlocks.
Imagine a scenario where your database is experiencing rapid growth and you are at the same time implementing new functionality when users start to complain of slowness and a decline in performance. Without the appropriate performance data, a quick and relatively easy fix would be to upgrade the database service level.
Doing this may appear to solve or minimize the problem, but if the slowness is due to query wait times then you have just masked the problem at some expense. Because there can be many causes of poor database performance, the right diagnostic information is essential. Not just for quickly fixing problems, but as we shall see, preventing them before they occur.
SQL Server, which Azure SQL is closely related to, is a mature product with many ways to access diagnostic information. Most of this diagnostic data comes from dynamic management views and functions that are accessed via T-SQL. Built on top of these are graphical tools like query store that can be found in SQL server management studio.
In the Azure portal within the context of a database, there are various pre-configured metrics related to database activity and load. At the top of the monitoring hierarchy, Azure has intelligent insights and SQL analytics that not only use your databases' historical diagnostic data but also learnings gleaned from the performance of millions of Azure-hosted databases combined with artificial intelligence to model and predict potential performance issues.
We'll start by talking about creating a baseline, so you have a context for your database's metrics and then we'll move on to Azure's pre-built intelligent monitoring solutions, before delving into the details behind the metrics. Finally, we'll look at how you can set up notifications to warn of specific or critical situations.
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.