10. Monitoring and tuning

This chapter covers different techniques to monitor and tune a SQL database and a managed instance. You will learn how to monitor Azure SQL Databases and managed instances using the Azure portal, dynamic management views (DMVs), and extended events. You will learn how to tune an Azure SQL Database using automatic tuning and Query Performance Insight. You will also learn how to implement in-memory features to improve workload performance.

By the end of this chapter, you will be able to:

• Monitor and tune an Azure SQL Database or SQL Managed Instance from the Azure portal
• Monitor an Azure SQL Database or SQL Managed Instance using DMVs
• Monitor an Azure SQL Database using extended events
• Implement in-memory technologies to improve database performance
• Monitor an Azure SQL Database and SQL Managed Instance using Azure SQL Analytics
• Monitor and tune an Azure SQL Managed Instance using HammerDB and the QPI library

The following...

Monitoring an Azure SQL Database and SQL Managed Instance using the Azure portal

Firstly, it is easy to monitor Azure SQL Database and SQL Managed Instance storage utilization using the Azure portal. The Database data storage option in the Azure SQL Database Overview section provides a chart of used space, allocated space, and the maximum storage size:

Figure 10.1: Database data storage chart

Similarly, the Storage utilization section in the SQL Managed Instance Overview tab can give you a quick overview of the used storage and maximum storage of a managed instance:

Figure 10.2: Storage utilization overview chart

Beyond storage, the Azure portal provides some other more sophisticated monitoring options, which are available in the Monitoring section for Azure SQL Database and SQL Managed Instance.

The Monitoring section for Azure SQL Database and SQL Managed Instance in the Azure portal has the following options:

...

Analyzing diagnostic logs using Azure SQL Analytics

Azure SQL Analytics (which is in preview at the time of writing this book) is a cloud monitoring solution that can be used to monitor one or more Azure SQL Databases, SQL Managed Instances, or elastic database pools. This solution shares the same method of configuration for both SQL Database and SQL Managed Instance, so all the activities will be concerned with Azure SQL Database only.

Azure SQL Analytics analyses diagnostics data that was logged by enabling the diagnostic settings (as discussed previously in the Diagnostic Settings and Logs section) to provide insights into things such as blocks, resource limits, deadlocks, wait stats, and timeouts. Moreover, custom monitoring rules and alerts can also be set up to enhance existing monitoring capabilities.

Azure SQL Analytics uses diagnostic logs for Azure SQL Database. Therefore, the first step is to enable diagnostic logs to send the logs to the Log Analytics workspace.

...

Activity: Monitoring Azure SQL Database with Log Analytics and Power BI

In this activity, we'll learn how to import Log Analytics workspace data into Power BI, and we'll create a report in Power BI.

Note

The purpose of the activity is not to create a performance monitoring dashboard. The purpose is to explain how to get Log Analytics workspace data into Power BI, which can then be used to create a dashboard as and when required.

Follow these steps to complete the activity:

1. Log in to the Azure portal. Search for and open the previously created Log Analytics workspace:
   

   Figure 10.51: Navigating to the created Log Analytics workspace

2. On the Log Analytics workspace page, find and open Logs:
   

   Figure 10.52: The Log Analytics workspace

3. On the New Query 1 page, copy and paste the following query:

   AzureMetrics
   | where ResourceProvider=="MICROSOFT.SQL" | where ResourceId contains "/ SERVERS/"
   | where ResourceId contains "/DATABASES/"...

Monitoring queries using the Query Performance Insight pane

In this section, we will learn how to monitor queries using the Query Performance Insight pane for Azure SQL Database. Consider a scenario where Mike plans to keep track of Query Performance Insight and monitor queries of Toystore Ltd.. He runs through a workload to generate some database activity and then observes the Query Performance Insight pane for the queries. He can follow these steps in order to achieve this:

1. To start the workload, open a new PowerShell console window and execute the following command:

   powershell.exe "C:\Code\Chapter10\Start-Workload.ps1 -sqlserver toyfactory
   -database toystore -sqluser sqladmin -sqlpassword Packt@pub2 -workloadsql "C:\Code\Chapter10\workload.sql"
   -numberofexecutions 10"

   Note

   You may get the following warning—ignore it:

   "WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure. Commands...

Monitoring an Azure SQL Database and SQL Managed Instance using DMVs

DMVs return diagnostic data that can be used to monitor a database's health and performance. We'll cover monitoring data metrics, connection statistics, blocking status, and query performance in the following sections.

Monitoring database metrics

The Azure SQL Database metrics available on the Azure portal can also be monitored using the sys.resource_stats DMV. This DMV returns the historical analysis for all the databases in an Azure SQL server. For SQL Managed Instance, you can use the sys.server_resource_stats DMV to monitor SQL Managed Instance CPU storage usage. The data for this DMV is collected and aggregated every 5 minutes and is retained for 14 days.

The following query returns the resource utilization from the last six hours for the Azure SQL Database:

-- Execute in master database
-- Get utilization in last 6 hours for the toystore database 
Declare @StartTime DATETIME = DATEADD...

Tuning an Azure SQL database

In this section, we'll look at the out-of-the-box performance tuning features provided by automatic tuning in an Azure SQL database.

Automatic tuning

Azure SQL Database automatic tuning utilizes artificial intelligence to continuously monitor and improve queries executed on an Azure SQL database.

Automatic tuning observes the workload and applies recommendations to speed up performance. The recommendations are applied when database activity is low so that there aren't any performance impacts when applying recommendations.

The following options are available for automatic tuning:

• Create Index: Automatically identifies and implements missing indexes to improve workload performance. It also verifies whether the indexes created have improved the performance. The Create Index option is disabled by default.
• Drop Indexes: Automatically identifies and removes duplicate, redundant, and unused indexes. The Drop Indexes option is...

Summary

In this chapter, we covered different ways of monitoring and tuning Azure SQL Databases and SQL Managed Instances. We learned how to use Azure SQL Database performance metrics and Query Performance Insight to monitor database metrics and queries from the Azure portal.

The chapter talked about using Azure SQL Analytics to monitor Azure SQL Database and SQL Managed Instance. Intelligent Insights, provided by Azure SQL Analytics, can be used to set up alerts on different metrics such as CPU, log I/O, blocks, and deadlocks for Azure SQL Database. Intelligent Insights can also be used to fine-tune long-running and CPU- or I/O-intensive queries to further optimize an Azure SQL database.

We also learned how to set up alerts on database metrics, and proactively acted as and when alerts were raised. We learned about important DMVs and how to set up extended events to monitor a SQL database or a managed instance.

Following this, we set up automatic tuning for an Azure SQL database...