Jaa

Understanding Windows Azure SQL Database Size

  • Artikkeli

The database on Windows Azure Sql Database is stored in two parts: Database data and metadata. Database data is the data in the tables and indexes and is stored in page files. Metadata is the stored procedures, functions, cached query plans, etc… that are related to your database. Metadata is stored in the pages of the system tables of the physical server.

This blog post will discuss how the two types of data that comprise your Windows Azure Sql Database are different and how they are reflected in the bill you are charged monthly for usage.

image

Page Data

The table and index data are stored in page files on three physical servers. One copy of the data is the primary and the other two copies are the secondary backups. The secondary copies are exact replicas of the primary data, but the physical storage sizes may differ. If the primary fails over or the database needs to be load balanced to another server, Windows Azure Sql Database has the option of promoting one of the secondary copies to a primary. Therefore, switching the primary replica can cause the size of the database to grow or shrink as reported in the bill.

Even though there are at least three copies of the data, the bill is based on the reserve size of the primary. The following query retrieves pages utilized on the primary replica at the time of execution:

SELECT SUM(reserved_page_count) 'pages'

FROM sys.dm_db_partition_stats

To calculate the number of bytes that are reserved for a single copy of your database all you need to know is that there are 8192 bytes per page. You can run this query when connected to your Windows Azure Sql Database to determine the number of reserved bytes:

SELECT SUM(reserved_page_count) * 8192 'bytes'

FROM sys.dm_db_partition_stats

Total database size (the reserved size) is larger than the size of the data in the database. It includes overhead storage structures required to use the database as well as potentially free space on each page that is currently unused. For more information, please refer to Understanding Pages and Extents.

The bill is generated from the size of a single replica’s reserved pages, not the size of the data in the replica. Windows Azure Sql Database samples the size of your database periodically over the billing period and your monthly bill is calculated from those samples. How the sampling of your reserved pages is done and how the bill is calculated will be addressed in another blog pox.

Metadata

Metadata is the data that is kept in the system database on the host server. This includes stored procedures, functions, etc... Meta data is specific to the Windows Azure SQL Database; however it is kept in the system database on the host physical machine. Every user database on the physical host stores it’s metadata in the same system database.

This data is also replicated to the system database on the secondary physical machines in case a replica needs to be quickly promoted to a primary. In this case, the Meta data is a row level replication and not a page level replication. And just like the user databases, the system database uses pages, and has overhead storage structures required to use the database as well as potentially free space on each page that is currently unused

At this time there is no way to query for the size of the metadata in the system database.

In addition to the page data, the bill includes a portion of the metadata. From the system database that is associated with the primary replica on the physical server, we calculate the reserve page size of the system database and divide that amongst the number of rows that the user databases consumes in the system database on that physical server. In other words the metadata proportion is a row level percentage of the user consumption over all the metadata on the physical server. This is the portion of the metadata that is added to the bill.

Because the total system database’s reserve page size, and the number of total rows on the host system can change, and the primary can failover to another host’s system database, the size of the database’s portion of the metadata can grow or shrink slightly as reported in the bill.

Database Maximum Capacity

When you set a cap for the database size, both the primaries reserve page size and the database’s portion of the metadata is figured into enforcing that cap. You can read more about setting the maximum size of your Windows Azure Sql Database by reading: CREATE DATABASE (Windows Azure SQL Database)

Summary

  • The bill is based on the size of the reserved pages on your primary partition combined with a portion of the metadata for the physical host in which the database resides.
  • Billing samples the size of your database over the billing period and uses those samples to calculate your bill.
  • The bill is in part generated from the size of a primary replica’s reserved pages, not the size of the data in the replica. You are not billed for the data in the secondary replicas.
  • The bill is in part generated from the database’s share of the system database on the primary replica’s host server.

Comments

  • Anonymous
    October 26, 2012
    Thanks for putting such an article together.  I have a few questions: > Whenever there is a switch then my primary changes correct? > If I have 3 copies of differing sizes, do I get charged for the least space consumed?  I have seen some significant changes between replica sizes, especially due to fragmentation of clustered indexes on GUID columns > Did I understand you correctly that User-Defined Programming Objects (eg SP, UDF, etc) are actually not stored in the user database but rather in the system DB?  Is this system DB the same as the resource DB found in on-premise SQL Server? Thanks again for the post!

  • Anonymous
    November 01, 2012
    Shauntj-us: You get charged for the size of your primary replica when that replica is sampled for billing.  It is not on a least space consumed stradegy.  Yes he User-Defined Programming Objects are in the resource database and yes it is simliar to the one on an on-premise Sql Server.