Myth: Azure SQL Databases performs slower than other types of SQL Server installations

It is something I hear regularly. Often in context of somebody who has/had a performance issue with some query. I acknowledge, that people have experienced improvements when switching between DTU, vCore and Serverless instances, perhaps even migrate to Managed Instances or even to on-prem. There is never an explanation to why it worked and for how long it worked. Anyways, the myth is not correct.

In my experiences, it is rarely the Azure Data SQL instance tier itself witch causes slow performance. Often the instances are over dimensioned, and often a quick glance on the execution plan reveals queries missing indexes. Specially, often a review of the queries reveals anti-pattens or the data models doesn’t scale.

This post is not about tuning. It is about giving an explanation about how the Azure SQL Database works for DTU, vCore and Serverless. Hopefully giving insights why it performs. Also it might explain why changing the infrastructure as first action of remedy to bad performance, is properly not the right decision.

Gathering server information and storage latencies

Azure SQL Database is built upon the SQL Server engine. Common for all SQL Server databases are the need of a server. When provisioning an Azure SQL Database, including an Azure SQL Database Serverless, we have to either associate it with either a existing Azure SQL Server, or a new created together with an Azure SQL Database. Despite of being a SaaS service Azure SQL Databases runs on servers under the hood, even vCore Serverless.

I have created an example, where I have created one Azure SQL Server and attached a DTU based, a vCore based and a vCore based Serverless Azure SQL Database. Sizes of the databases are not that important. For comparison, I have an Azure SQL Managed Instance as well

When query the Master database with following query, we get some information about our CPU and memory.

SELECT
	@@SERVERNAME AS [server_name]
	,[cpu_count]
	,[hyperthread_ratio]
	,[cpu_count] * [hyperthread_ratio] AS [core_count]
    ,[physical_memory_kb] / 1024/1024 AS [physical_memory_gb]
	,virtual_machine_type_desc
	,@@VERSION AS ‘SQL Server Version’
FROM [sys].[dm_os_sys_info]

Result for Azure SQL Databases

Result for managed instance

I did a check on latencies on the Azure SQL databases. My obsevations, using following query, was Master databases had latencies approximately 1–2 ms, while user databases was a little slower, approximately 10 ms in average. It was excactly the same for managed instances

SELECT
	[databases].[name],
	[file_stats].[database_id],
	[io_stall_read_ms]/(1.0 + [num_of_reads]) [Average Read ms],
	[io_stall_write_ms]/(1.0 + [num_of_writes]) [Average Write ms] ,
    ([io_stall_read_ms] + [io_stall_write_ms])/(1.0 + [num_of_reads] + [num_of_writes]) [Average Total ms]
FROM [sys].[dm_io_virtual_file_stats](NULL, NULL) [file_stats]
INNER JOIN [sys].[databases] [databases] ON [file_stats].[database_id] = [databases].[database_id]

What can we read from the results

Azure SQL Databases and Managed instances are equal when it comes to access storage. Their lantencies are not bad but could be better. Many on-premise SQL Servers I have worked on, have had 1 ms latencies across all disks. There is also a significant cost to achieving 1 ms, this has to be into consideration.

As mentioned, not particular bad latency, or a problem. The reason for this is, SQL Server is an in memory database, and primarily operates from memory and only resorts to accessing storage when necessary due to data not being in memory.

We can also read that both Azure SQL Databases and Managed Instances, are attached to SQL Servers running on VMs. Servers are the same version. While the Managed Instance is running on a slighty larger dimensioned VM, in regards to the input I gave upon creating it. The Azure SQL Databases have a huge VMs with almost a tera byte of memory.

So why don’t Azure SQL Database run with blazing performance on all tiers?

SQL Server Resource Governor

When we provision a Azure SQL Server, we are getting this huge VM. The number of cores matches, the maximum number of cores a Azure SQL Database vCore can have, and memory matches it as well. Knowing this about the VM, the default limit of 20 Azure SQL Servers per region per subscription makes sense. This VM, even without databases, takes many resources and is free.

The SQL Server engine has a feature named Resource Governor. This feature was original created to define quotas for user in SQL Server. Microsoft is also using this feature to throttle an Azure SQL Database according to tiers. So a DTU is x percent of the VM’s CPU, Memory and I/O. E.g. a B1 DTU Azure SQL Database is around 1% or so of the VM, giving it around 1 core. DTU resource-to-cost slope is not linary as far as I have seen, but I haven’t checked the resource governor settings for all tiers. vCore Azure SQL Databases are much more easy to figure out, because we are specific around the number of cores we want.

Setting an Azure SQL Database tier, creates workload groups and a resource pool in the Resource Governor. Registrations are using a naming standard of post fixing with DB and the database id. If switching database tiers, previous records of tiers are still registered in the Resource Governor. In mine examples, the database has id 5

--- View pools

SELECT [pool_id], [name] FROM [sys].[dm_resource_governor_resource_pools] 

Result is reduced see sys.dm_resource_governor_resource_pools for more information.

--- View workload groups

SELECT [group_id], [pool_id], [name] FROM [sys].[dm_resource_governor_workload_groups]

Result is reduced see dm_resource_governor_workload_groups for more informationt

When logging into an Azure SQL Database, you are assigned to a workload group. When having more pools and workload groups, this query is an easy way to find your current workload group. Original_login_name, was an easy way for to find my user. Your way might be different.

SELECT group_id FROM sys.dm_exec_sessions 
WHERE original_login_name like 'live.%'

Result is reduced see sys.dm_exec_sessions for more information.

Conclusion

Azure SQL Database not matter edition, is running on a very powerfull VM. There is no indication of latencies being higher, than other Azure services. Switching between DTU, vCore and vCore Serverless will not change the server. Switching tier will change the Resource Governor, changing the Azure SQL Database’s utilisation of the VM.

Increasing tiers, increases costs. It should be the last solution after having analysed the execution plans, checked indexes, app layer and the data model. It can’t stand alone, but PLE (Page Life Expectancy) can give a hint of a change in tier is needed.