Tuning Azure DB and MS SQL on an Azure VM
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:
- User experience
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.
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.
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
customerstable is large, this can be very CPU-intensive.
- 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:
- Inefficient database code: A poorly designed stored procedure that performs unnecessary operations can result in high CPU usage. Consider the following procedure:
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.
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.
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 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 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.
An extensive database slows database performance in many ways, including shutdown and startup time, backups, and restoration.
The number of connections increases the consumption of CPU and memory resources, thus increasing disk activity and lock contention.
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.
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:
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.
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.
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.
Data types have different specific storage space requirements and performance characteristics.
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.
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.
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.
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.
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.
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.
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.
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.
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.