Configure Azure SQL database/elastic pools for scale and performance

In this we will learn about how to Configure Azure SQL database/elastic pools for scale and performance.

Scaling out with Azure SQL Database

Using the Elastic Database tools, you can quickly scale out databases in Azure SQL Database. These tools and capabilities enable you to leverage Azure SQL Database’s database resources to build solutions for transactional workloads, particularly Software as a Service (SaaS) applications. Elastic Database features, on the other hand, are made up of the following:

Firstly, Elastic Database client library: The client library is a feature that allows you to create and maintain sharded databases.
Secondly, Elastic Database split-merge tool: moves data between sharded databases. This tool is useful for moving data from a multi-tenant database to a single-tenant database (or vice-versa).
Thirdly, Elastic Database jobs: Use jobs to manage large numbers of databases in Azure SQL Database. Easily perform administrative operations such as schema changes, credentials management, reference data updates, performance data collection, or tenant (customer) telemetry collection using jobs.
Next, Elastic Database query (preview): Enables you to run a Transact-SQL query that spans multiple databases. This enables connection to reporting tools such as Excel, Power BI, Tableau, etc.
Lastly, Elastic transactions: This feature allows you to run transactions that span several databases. Elastic database transactions are available for .NET applications using ADO .NET and integrate with the familiar programming experience using the System.Transaction classes.

In this graphic, colors of the database represent schemas. Databases with the same color share the same schema.

Firstly, a set of SQL databases is hosted on Azure using sharding architecture.
Secondly, the Elastic Database client library is used to manage a shard set.
Thirdly, a subset of the databases is put into an elastic pool. (See What is a pool?).
Fourthly, an Elastic Database job runs scheduled or ad hoc T-SQL scripts against all databases.
After that, the split-merge tool is used to move data from one shard to another.
Then, the Elastic Database query allows you to write a query that spans all databases in the shard set.
Lastly, elastic transactions allow you to run transactions that span several databases.

Elastic Database tools — Image Source: Microsoft

Horizontal and vertical scaling

The following figure shows the horizontal and vertical dimensions of scaling, which are the basic ways the elastic databases can be scaled.

Horizontal versus vertical scale-out — Image Source: Microsoft

Horizontal scaling, on the other hand, refers to the addition or removal of databases in order to change capacity or overall performance, often known as “scaling out.” Data is partitioned over a series of similarly constructed databases using sharding, which is a typical method of horizontal scalability.

And, Vertical scaling refers to increasing or decreasing the compute size of an individual database, also known as “scaling up.”

Sharding

Sharding is a technique to distribute large amounts of identically structured data across a number of independent databases. However, it is especially popular with cloud developers creating Software as a Service (SAAS) offerings for end customers or businesses. These end customers are often referred to as “tenants”. Sharding may be required for any number of reasons:

Firstly, the total amount of data is too large to fit within the constraints of an individual database
Secondly, the transaction throughput of the overall workload exceeds the capabilities of an individual database
Thirdly, tenants may require physical isolation from each other, so separate databases are needed for each tenant
Lastly, different sections of a database may need to reside in different geographies for compliance, performance, or geopolitical reasons.

Multi-tenant and single-tenant

Some apps use the most straightforward method and create a separate database for each tenant. The single tenant sharding method enables isolation, backup/restore functionality, and resource scalability at the tenancy level. Each database is paired with a unique tenant ID value when using single tenant sharding (or customer key value). However, that key does not have to be included in the data. It is the job of the application to route each request to the correct database, and the client library can help with this.

Single tenant versus multi-tenant — Image Source: Microsoft

Scale elastic pool resources in Azure SQL Database

Change compute resources (vCores or DTUs)

After initially picking the number of vCores or eDTUs, you can scale an elastic pool up or down dynamically based on actual experience using the Azure portal, PowerShell, the Azure CLI, or the REST API.

Impact of changing service tier or rescaling compute size

Changing the service tier or compute size of an elastic pool follows a similar pattern as for single databases and mainly involves the service performing the following steps:

Firstly, create new compute instance for the elastic pool
- Here, a new compute instance for the elastic pool is created with the requested service tier and compute size. For some combinations of service tier and compute size changes, a replica of each database must be created in the new compute instance which involves copying data and can strongly influence the overall latency. Regardless, the databases remain online during this step, and connections continue to be directed to the databases in the original compute instance.
Secondly, Switch routing of connections to new compute instance
- In this the Existing connections to the databases in the original compute instance are dropped. Any new connections are established to the databases in the new compute instance. For some combinations of service tier and compute size changes, database files are detached and reattached during the switch. Regardless, the switch can result in a brief service interruption when databases are unavailable generally for less than 30 seconds and often for only a few seconds. However, if there are long running transactions running when connections are dropped, the duration of this step may take longer in order to recover aborted transactions. Accelerated Database Recovery can reduce the impact from aborting long running transactions.

Tune applications and databases for performance in Azure SQL Database and Azure SQL Managed Instance

Tune your application

In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. Hardware and product licences are acquired initially, followed by performance tweaking. When using Azure SQL, however, it’s a good idea to combine the processes of running and tweaking an application. Instead of over-provisioning on hardware based on predictions of future growth plans for an application, you may optimise your application to utilise the lowest resources needed now with the approach of paying for capacity on demand. Some clients may prefer to over-provision hardware resources instead of tuning an application.

Application characteristics

Although the Azure SQL Database and Azure SQL Managed Instance service tiers are intended to increase an application’s performance stability and predictability, some best practises can help you configure your application to make the most of the resources available at a compute size. Although migrating to a larger compute capacity or service tier might improve the performance of many applications. However, in order to benefit from a better level of service, some applications require further customization. Consider extra application tweaking for apps that have these qualities for improved performance:

Firstly, applications that have slow performance because of “chatty” behavior
Secondly, databases with an intensive workload that can’t be supported by an entire single machine
Thirdly, applications that have sub-optimal queries
Lastly, Applications that have sub-optimal data access design

Identifying and adding missing indexes

The physical database architecture is a typical source of OLTP database performance issues. Database schemas are frequently created and distributed without extensive testing (either in load or in data volume). Unfortunately, query plan performance may be satisfactory on a small scale but degrades dramatically when dealing with large amounts of data. However, the lack of proper indexes to fulfil filters or other requirements in a query is the most prevalent cause of this problem. When missing indexes occur, the result is frequently a table scan when an index seek would suffice.

In this example, the selected query plan uses a scan when a seek would suffice:

SQL
DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @a < 20000
BEGIN
INSERT INTO dbo.missingindex(col2) VALUES (@a);
SET @a += 1;
END
COMMIT TRANSACTION;
GO
SELECT m1.col1
FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
WHERE m1.col2 = 4;