Azure SQL Database: Engineering for PaaS Performance [5 Steps]

[ServicesExperts]

With nearly half of all software developers using Microsoft SQL Server [Source], the chances are your applications and databases rely on them to operate efficiently.

But what happens to data performance when you move to Azure SQL database?

49% of developers use Microsoft SQL server for database hosting

If you’re already using Azure SQL PaaS, you might have experienced configuration and performance issues similar to those in traditional SQL database development. Fortunately, with Azure, you have more tools on hand to overcome these challenges.

Let’s take a look at how you can engineer an Azure SQL database for efficient PaaS performance.

Firstly, we start at the beginning...

What is an Azure SQL Database?

Azure SQL PaaS enables you to host applications and data in the cloud, without the need to build your own infrastructure. It retains many of the same features you’ll find in traditional SQL databases, with a few added bonuses:

• On-demand scalability at low cost
• Reduction or elimination of up-front capital expenditure (e.g. for licences or server hardware)
• High speeds and minimal downtime

You also get integrated tools in your Azure Portal to help identify and analyse performance problems, including:

• Query Performance Insight
• SQL Database Advisor

But as useful as these automated functions are, you may still need to do some manual tuning to bring your databases up to scratch.

5 ways you can Improve the Performance of an Azure SQL Database

There are five main areas to look to optimise the performance of your Azure SQL database:

1. Service tier selection
2. Efficient indexing
3. Query optimisation
4. Batch queries
5. Database partitioning

Here’s a run-down of each in more detail.

1. Service Tier Selection

Every organisation has a choice between four Azure SQL database service tiers:

• Basic – supports steady performance in smaller databases that aren’t running multiple concurrent requests. Ideal for developing or testing databases at minimal cost.
• Standard – improved performance predictability and multiple concurrent request support. Ideal for web applications that service more than one user at a time and standard I/O traffic requirements.
• Premium – predictable, second-over-second performance, allowing you to size databases based on peak loads. If your application requires high levels of CPU, memory or I/O to complete operations, the premium service tier is the way forward. Unlimited concurrent requests and low-latency are also available at this level.
• Premium RS – exclusively for I/O-intensive workloads such as analytics where the database isn’t the main point of record.

Deciding which service tier is right for your applications depends on the peak load requirements for each resource.

2. Efficient Indexing

The physical design of a database can play a big part in its performance. Database schemas aren’t always tested at scale, which can lead to design faults when the load or data volume is increased. Making sure you have the right indexes in place from the offset can speed up inefficient queries.

Azure SQL database can help you identify and fix missing indexes. When a query is run, the database tracks the execution of the query plan and calculates the difference a more efficient index would make.

You can use this feature to decide where key indexes are missing in database workloads. Using the correct indexes can:

• Reduce the latency of each individual query
• Improve the throughput of concurrent requests
• Minimise the cost of running each query

This will make a significant difference to the performance of Azure SQL PaaS at very little cost to your organisation.

3. Query Optimisation

Another clever trick the Azure SQL database has up its sleeve is the query optimiser. While this is similar to the query optimiser in traditional SQL databases, you get the additional benefit of minimising aggregate resource demands. In this scenario, an application can run at a lower performance level, saving your organisation money.

The query optimiser’s job is to evaluate the value of query parameters and decide whether it can produce a better query plan. This is known as ‘query sniffing’, but it’s not a perfect art form. Sometimes you’ll need to override the query optimiser to fully tune each query.

There are two main ways you can improve the results of the query optimiser:

• Tailor the optimiser to pick a plan that is optimised for the average use case rather than the specific case the query was first designed for.
• Use a query hint to tell the optimiser which specific value to use during the compilation process. The aim here is to reduce the total amount of resources used to run this query.

For more detailed information on tuning queries, check out this Microsoft video guide.

4. Batch Querying

Network latency can be an obstacle in applications handling high volumes of ad-hoc queries. Batching these queries reduces the burden on the network by sending them to your Azure SQL database in one, large group.

Alternatively, you can consolidate these queries in a stored procedure and send them to the database in the same way. Stored procedures enable you to use this query plan again in the future for similar workloads.

Here’s another helpful Microsoft guide with further instructions on batching queries.

5. Database Partitioning

You might already use SQL servers to house many functions in one database. While this makes it easier to backup and recover data en masse, you also have to invest in enough hardware to handle peak loads across each separate component.

When scaling in Azure SQL PaaS, you can avoid this by partitioning each application function into a different database. This way you can select function performance levels independently during busier periods. Designing flexible architecture like this means application workloads can be spread across several machines.