Tuning Azure DB and MS SQL on an Azure VM

ewen 9 months ago 8 minute read time

Azure DB and Microsoft SQL Server are powerful relational database management systems (RDBMS) that offer diverse data storage and management capabilities. While Azure DB is a cloud-based DBMS and Microsoft SQL Server is an on-premise or cloud-hosted RDBMS, both systems support a wide range of applications.

Achieving optimal database performance with an RDBMS requires careful workload-specific tuning. Database tuning is essential to enhance the following:

Performance

Responsiveness
User experience
Throughput
Cost-effectiveness
Scalability
Reliability

In this article, we’ll introduce the key metrics for monitoring database performance. We’ll also offer tuning tips for improving the performance of Azure DB and MS SQL. Finally, we’ll explore how SolarWinds Observability can help you tune and monitor your databases.

Key Metrics for Monitoring Database Performance

It's important to start with a strong understanding of key database metrics. These metrics provide valuable insights into various components that impact overall performance.

CPU usage

High CPU usage can indicate that the database is overloaded, impacting the workload. Common causes of high CPU usage include:

Non-indexed queries: Indexes are specialized data structures that store information about a table's columns, thus enabling efficient data retrieval based on specific criteria.

In the SQL query example below, if the name column is not indexed while running this query, then the operation will scan the entire customers table to find all the customers with the name Mary Adam. If the customers table is large, this can be very CPU-intensive.

SELECT CustomerId FROM customers WHERE name = 'Mary Adam';

Too many clients: A large influx of website users can overload the CPU as the database must manage all the concurrent connections and queries.
Complex queries: Queries involving multiple joins and calculations add complexity. Joins, subqueries, and aggregations are often CPU-intensive to execute. This is especially the case if tables are large. Consider the following example:

SELECT customers.*, orders.*

FROM customers

JOIN orders ON customers.id = orders.customer_id

JOIN products ON orders.product_id = products.id

LEFT JOIN payments ON orders.payment_id = payments.id

WHERE orders.total_price > 100

AND orders.created_at > '2020-11-04'

AND payments.status = 'Paid'

AND customers.country = 'USA';

Inefficient database code: A poorly designed stored procedure that performs unnecessary operations can result in high CPU usage. Consider the following procedure:

CREATE PROCEDURE UpdateEmployeeSalary

AS

BEGIN

DECLARE @MaxSalary DECIMAL = (SELECT MAX(Salary) FROM Employees);

DECLARE @SalaryAdjustment DECIMAL = @MaxSalary * 0.1;

DECLARE @EligibleEmployees TABLE (

EmployeeID INT,

CurrentSalary DECIMAL,

NewSalary DECIMAL

);

INSERT INTO @EligibleEmployees

SELECT

EmployeeID,

Salary AS CurrentSalary,

Salary + @SalaryAdjustment AS NewSalary

FROM Employees

WHERE Salary < @MaxSalary;

UPDATE Employees

SET Salary = NewSalary

FROM Employees

INNER JOIN @EligibleEmployees ON Employees.EmployeeID = @EligibleEmployees.EmployeeID;

END

This stored procedure is complex because it performs several tasks, including salary adjustment calculation based on the maximum salary. It creates a temporary table to store the eligible employees and their new salaries and then updates the eligible employees’ salaries.

Memory usage

High database memory usage can lead to performance problems, such as slow queries and data corruption. Common causes of high memory usage in databases include:

Large datasets that require more memory to store and process the data.
Inefficient SQL code that performs unnecessary operations or creates large temporary tables.
Excessive caching to improve performance. Databases often cache data in memory, but excessive caching consumes too much memory.

I/O throughput

High I/O throughput occurs when the database reads and writes data to the disk at a high rate, resulting in performance problems.

Wait statistics

Wait statistics provide insights into the causes of performance bottlenecks, measuring the wait time that database processes spend on resources, such as CPU, memory, and disk I/O.

Query execution time

Query execution time measures the time needed for a query to process a request and return the results. Slow-running queries imply that the database requires tuning to process the queries optimally.

Database size

An extensive database slows database performance in many ways, including shutdown and startup time, backups, and restoration.

Number of connections

The number of connections increases the consumption of CPU and memory resources, thus increasing disk activity and lock contention.

How to Tune Azure DB and MS SQL on an Azure VM

Tuning Azure DB and MS SQL on an Azure Virtual Machine (VM) involves a comprehensive approach focusing on various aspects to enhance database performance. This section covers the essential steps to achieve this.

Monitor database performance

Live monitoring platforms offer database performance overviews, including response time, slow queries, and errors. They also track the impact of changes executed to the database configuration or workload.

The screenshots below from the SolarWinds® Observability show the database and query performance overview:

Optimize the database configuration

Several database configuration settings can be optimized to improve performance:

Max degree of parallelism (MAXDOP): Specifies the maximum number of processors that the database can use to execute a query in parallel.
Optimized locking: Very few locks are held for large transactions. Therefore, optimized locking helps to reduce lock memory.
Connection pool size: This establishes the number of database connections that are available for use by applications.

Create indexes

Indexes serve as a guide for the database engine, pointing it directly to the relevant data instead of forcing it to scan through the entire dataset. This minimizes I/O operations and improves overall performance.

Normalize the database

Database normalization refers to the process of organizing data to minimize redundancy and improve performance.

While there are several different approaches to normalizing a database in MS SQL and Azure DB, the typical approach follows these steps:

Identify the different entities in the database.
Identify the relationships between the entities.
Follow these normalization rules to organize your data.

Use the right data types

Data types have different specific storage space requirements and performance characteristics.

For example, varchar allocates storage space based on the exact length of the data stored, but char allocates the same storage space by disregarding the actual length of the data stored. Therefore, using a char for a column with varying data length is wasteful.

Update the database statistics

Database statistics include information such as:

The number of rows in a table
The number of distinct values in a column
The distribution of values in a column

The optimizer uses this information to estimate how many rows a query will return and the time taken to execute the query.

Choose the right Azure VM size and type

The size and type of Azure VM you choose significantly impact the performance of your database. For example, if your database is I/O intensive, choose a VM with a high-performance storage disk.

Use Azure premium storage disks

Azure premium storage disks are high-performance SSDs designed to provide low latency and high throughput for I/O-intensive workloads. If your database workload is I/O intensive, using Azure premium storage disks improves performance.

Enable automatic tuning

Automatic tuning is an Azure DB and MS SQL feature enabled through the Azure portal or SQL Server Management Studio (SSMS). It automatically optimizes the database configuration for you, adjusting various settings, such as the MAXDOP and the cost threshold for parallelism.

How to Use SolarWinds Observability to Tune Azure DB and MS SQL on an Azure VM

Manual database tuning is a complex, time-consuming, and resource-intensive task that can lead to errors and inconsistencies. Alternatively, SolarWinds Observability is a comprehensive monitoring solution that addresses these challenges by providing a more efficient, scalable, and proactive approach to optimizing database performance. It provides unified visibility into resources—including computing resources and databases—for cloud-native, on-premises, and hybrid environments.

To use SolarWinds Observability to tune Azure DB and MS SQL on an Azure VM, follow these steps.

Monitor key metrics

SolarWinds Observability monitors the key metrics necessary to enhance the performance of an Azure DB or MS SQL database.

Monitor database host. Start by installing the SolarWinds Observability Agent on your host server to monitor key host metrics.
Add your database instance to SolarWinds Observability.
Use the Databases area overview to review the metrics from your monitored databases. These metrics display database response time, throughput, error rate, and workload information.

Analyze query performance

The Queries Explorer feature in SolarWinds Observability allows you to analyze and troubleshoot database queries. It provides a detailed view of individual query executions, including their elapsed time, performance over time, and workload consumption.

Follow these steps to use the Queries Analysis feature.

1, Access the Queries Explorer.

Navigate to the Databases section in SolarWinds Observability.
Select the database instance you want to analyze.
Click on the Queries tab.

2. Filter queries by time period.

Use the time picker to select a specific time range for which you want to view queries.
Filter queries by database type or display name.

3. View Query Details.

The Queries Explorer lists executed queries within the selected time range.
There is a list of queries to explore, or you can search by the query text.
A side panel will appear. Click the VIEW DETAILS button on the side panel to show the query insights.

Conclusion

Implementing query tuning and enabling automatic tuning allows organizations to significantly improve their database responsiveness, scalability, and cost-effectiveness. However, carefully monitoring and analyzing database performance, optimizing hardware resources, and configuring database instance settings are critical factors for successful results.

The SolarWinds Observability platform provides a comprehensive solution for monitoring, analyzing, and optimizing database performance. These features empower engineers to deliver exceptional performance and support business growth.

To get started, sign up for a SolarWinds Observability trial, which gives you access to all features, including database performance, for 30 days.