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 firstname.lastname@example.org.
- 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
For this Azure SQL database, I'll go into alerts underneath the monitoring and create a new alert rule. The first thing I need to do is create a condition that will trigger the alert. Under configure signal logic I'll select disk space used. Once we have selected the signal, we get a handy little graphic telling us what the current value of that metric is.
For the purposes of demonstration, I will set a threshold value for disk space slightly above the current size of the database, using the greater than operator within aggregation type of maximum. The condition preview states in text what the alert condition is. We can also change how often the data that will trigger the alert is sampled using the aggregation granularity and frequency of evaluation drop-downs.
Before we move on let's have a quick look at how a dynamic threshold looks. I'll do that by selecting CPU percentage as my signal and changing the threshold to dynamic with a greater than operator and an aggregation type of average.
We can see on the graph that the CPU has been at 100% in the last 12 hours and with the threshold sensitivity at medium looks like it will be triggered at just below 120% utilization. If I change the sensitivity to high then we can see the threshold goes down to just above 80%, and for low sensitivity, the threshold rises to over 140%.
Under advanced settings, you can specify the number of events over an evaluation period that will trigger the alert. Once we have set up our trigger condition, we need to set up the actual alert. This involves setting up an action group if there isn't one there already.
An action group is a named entity that groups notifications. Once the action group has been created, we then need to set the notifications that will be triggered when our signal threshold has been met. Not only can you send a notification from an action group, but you can also instigate an action to be performed when the alert rule is triggered.
I'm just going to set up an email notification when the database size exceeds the threshold. I'll give the alert rule a name, assign it to a resource group, and set the severity level. Now I'll just generate some data to trigger the alert. We can see the alert has been triggered on the alerts dashboard and if I flick over to Outlook I can see the notification email.
Before we leave alerts let's quickly look at how you can perform the same action through metrics. It's as simple as selecting the metric of interest in this case CPU percentage, the aggregation type, and then click on the new alert rule.
This takes us back to the create alert rule page that we have just been looking at. Not only can you be alerted of performance or metrics degradation, but you can also be alerted to database and server configuration changes.
When configuring the signal logic if you select activity log from the signal type drop-down then you can be notified of events ranging from creation or deletion of a database, the import or export of a database through to executing a vulnerability assessment scan.
As an aside, within Azure SQL databases you can set a trigger to prevent DDL actions like dropping or altering a table within a database. Here is an example of such a trigger that will prevent a user who is not a DB owner from deleting or changing a table within a database. If I try to add a field to the customer table, the trigger prevents me and at the same time instructing me to contact the DBA.
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.