Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

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

share|improve this answer
    
Thanks for your answer. Will the gaps get filled by data as time carries on? Eg If my database were to grown by another 200mb would some of the data end up occupying the 130mb that is unoccupied? If not are there any commands I can run to solve this (out of hours) –  Matthew Steeples May 24 '11 at 18:34
    
can you share the results of the below query? SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB', (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed' FROM sys.database_files; –  Sankar Reddy May 25 '11 at 0:31
    
Unfortunately both sys.database_files and FILEPROPERTY don't exist in SQL Azure so I can't execute this query. –  Matthew Steeples May 25 '11 at 7:31

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.

share|improve this answer
    
SQL Azure doesn't support Fill Factors. –  Jaxidian Mar 2 '13 at 8:09

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.

share|improve this answer
    
Just as a modern update: Microsoft offers Virtual Machine Roles in many different flavors (including Ubuntu), but one comes pre-packaged (and licensed) with SQL Server already installed. They even have an Evaluation version if you want to throw one up and test with it without the add'l licensing fees. –  Jaxidian Mar 2 '13 at 8:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.