1. Home
  2. Training Library
  3. Microsoft Azure
  4. Courses
  5. Azure SQL and SQL Server Database Monitoring

What to Capture?

Contents

keyboard_tab
Introduction
1
Overview
2
Baseline
PREVIEW1m 51s
SQL Server Management Studio
Wait Statistics
Index and Statistics
Storage
Summary

The course is part of this learning path

Start course
Overview
Difficulty
Intermediate
Duration
1h
Students
365
Ratings
5/5
starstarstarstarstar
Description

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

Transcript

I like to think of these metrics in terms of rates of change. The slowest moving or static elements are physical resources or the environment in which your database operates. Changes to these metrics don't happen by accident and in fact require considerable effort to change.

Next, we have the state of the database, which can change organically and can be easily changed through deliberate manipulation. The last category of metrics I've called behavior and these are factors that are in a constant state of change and are very dynamic fluctuating by the millisecond.

Environment is the configuration of your service level tier or virtual machine or the attributes of a physical machine. So, CPU and the number of cores, the amount of RAM, and the amount of disk space. State includes a database's schema, configuration of the database and server, and the database's layout in terms of files and partitions.

I've classified size as part of this moderately changing category. While size can change minute by minute, that change is normally only a small proportion of the overall size. What I mean by that is that the database of a few tens of gigabytes rarely becomes one of hundreds of gigabytes or terabytes in a short space of time.

Without a doubt, the dynamic metrics of CPU and memory utilization and more significantly wait statistics is where the real gold of diagnostic information resides. These metrics are vast, numerous and not always easy to comprehend.

CPU and memory utilization tell you how hard the server is working, but not whether it's working efficiently. To use an automotive analogy, high CPU readings are like an engine revving at its redline, but if the car is not in gear then you aren't going anywhere, just burning a lot of petrol.

About the Author
Avatar
Hallam Webber
Software Architect
Students
14816
Courses
27
Learning Paths
3

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.