SQL Azure Fragmentation / Database Size

Question

I have a database running on SQL Azure that is currently 280mb. It is a test database for a system that we're going to be putting into production soon so the data frequently gets deleted in batches and then recreated.

When I used the "Copy" feature on SQL Azure the new database that it created was only 156mb. When running a query to show how much data each table is using it appears that each table is pretty much half the size it used to be.

I've worked out that this is going to be down to data fragmentation, but my question is what can I do about this? It doesn't appear that Microsoft perform any maintenance on the data itself, and as it's a pay-per-use model I'll end up hitting the 1gb limit when I've not got 1gb of data!

For reference this is the query I ran to show table size:

select sys.objects.name, (reserved_page_count * 8.0 / 1024)
from sys.dm_db_partition_stats, sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id

Answer 1

Matthew,

I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance.

280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried.

The above is because when SQL Server creates a new table, it doesn't know how big will that table be. Data is stored in pages of 8 kb and an extent is a collection of 8 contiguous pages. There are 2 types of extents, mixed and uniform extents. Uniform extent means all the 8 pages will be used by the same object (say TableA) and mixed extent means that this extent can be used by as many as 8 different tables. Since SQL Server doesn't know how big the table will be, it will start with single page allocations until the table reaches 3 extents and there after it uses uniform extents. This is the reason, fragmentation is high in small tables and but you shouldn't be worried abt it.

Instead of looking how big is the database, you should look at how much of it is empty. I am NOT sure how this affects the billing though. HTH

Answer 2

Also check the page fill factor. This percentage decides how much of a page should be filled with data before a new page is allocated. This is to reduce fragmentation, but will take up more disk space.

Answer 3

Remember, SQL Azure separates logical administration from physical administration. Per the SQL Azure Overview:

Unlike administration for an on-premise instance of SQL Server, SQL Azure Database abstracts the logical administration from the physical administration; you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. This approach helps SQL Azure Database provide a large-scale multi-tenant database service that offers enterprise-class availability, scalability, security, and self-healing.

This is the benefits/costs of Azure. You lose the ability to manage the physical aspects of your database as a tradeoff for things like scalability, availability, etc. Which is why the commands to examine those properties don't exist. If you need a SQL Server in the cloud that gives you access to the physical layer, go with Amazon.