March Madness – SQL Azure – sys.dm_db_partition_stats

The past two days we talked about the billing aspects of SQL Azure and how to get an estimate of your charges. Today we will shift our focus a bit and talk about size. Since you pay for storage with SQL Azure it would be a good thing to know a bit more details about the size of your databases and objects. Go ahead and run the following code and examine the results.

SELECT *
FROM sys.dm_db_partition_stats;

What is this system view for?

This system view will return one row for each partition that has been created in your database. The results include a column named ‘rowcount’, and there is a column for ‘object_id’, which we can use to find the name of the object as needed. Both of these items are useful things to know, but the real power is in the ‘reserved_page_count’ column; this is where we can find out just how big your database has become.

Why should you care?

Since you pay for the storage of data in SQL Azure it would be worth knowing just how much your data is truly costing you. This query will do just that for you at the database level (courtesy of Buck Woody):

/* Shows Database Size - Use in a user database */
SELECT (SUM(reserved_page_count)*8192)/1024000 AS [SizeinMB]
FROM sys.dm_db_partition_stats

OK, so now you know how large your database is…so what? What you really want to know is how big the object are that logically exist inside that database. As luck would have it, we have a query for that as well (courtesy of Buck Woody):

/* Individual Objects - Use in a user database */
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as [SizeinMB]
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY [SizeinMB] DESC

And now you can see the objects listed by size, which is more insight than just the database size itself. You could even calculate how much a particular table is costing you, if desired. Tomorrow we will look at something similar to doing just that.