Why is Azure SQL DB used space larger than physical server storage used space?

Question

Why is Azure SQL DB used space larger than physical server storage used space?

Sean Meadows 20

We have an Azure SQL Server database and a physical server with a SQL Server database. The databases have the same structure, and we store similar data on both (different servers for different clients), and we've been using the physical server for longer with many more input sources than the Azure server. However, after only a few months, the total used space in the Azure database is already more than 1/3rd of the size of that in our physical server database. We have and are working on DB size management, but I'm wondering why the storage in Azure seems to be so much larger for the same amount of data on a physical server? Are there settings in Azure that affect this? Or other background operations done by Azure that take up additional space?

1 answer

Your answer

Answer 1

Erland Sommarskog 119.4K MVP

It's difficult to answer without knowledge about the specifics. However, in Azure SQL Database Accelerated Database Recovery (ADR) and Read-Committed Snapshot are both on by default, which is on the case on-prem. This means that the Persistent Version Store might take up space in Azure.

But as I understand, the databases has different sets of data, so maybe you are simply mistaken about what you think should be the same size. Attached is a file with a couple of queries to give you the size of tables in the database. (The queries handle LOB data a little differently.)

tblsizes.sql.txt

Sean Meadows 20 Reputation points

2025-03-11T15:15:01.2333333+00:00

For a little clarification, the Azure database is used as a backup/log of the same data that's on the physical server. So they receive the same data, but have different retention thresholds. Below are the results of the first of your queries. The table it was ran on is the largest in either database (it's also the only table on the Azure server), and the Azure server table currently has about 162 days of data, while the physical server table only has about 8 days of data. When I extrapolated the results of the physical server to also be about 162 days worth, the Azure data was still about 40-50% larger.

I had to look up ADR, but if I'm understanding it correctly, it makes sense that there would be more storage used for this.

There are some indexes used more often then others, but none are quite at the level of use of not being needed anymore. And the indexes of both tables have some fragmentation that I'm addressing, but is there any difference between each respective fragmentation? Can Azure Hyperscale fragmentation potentially be higher or bigger?
Erland Sommarskog 119.4K Reputation points MVP

2025-03-11T22:08:58.28+00:00
If I extrapolate the size of the heap by scaling of number of rows:

SELECT 19200 * convert(bigint, 3752502768) / 171577858

I get 419914, which is pretty close to the actual size of the clustered index on the Azure side.

It is worth pointing out that if the Persistent Version Store is taking up space, this is not reflected in the table sizes.

Share via

Why is Azure SQL DB used space larger than physical server storage used space?

1 answer

Your answer