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
Best Answer
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