Determining Database Table Sizes in SQL Azure

As the popularity of Microsoft's Azure platform continues to rise, I find myself constantly adding new scripts to my library. As technology continues to evolve we need to find different solutions to meet technology needs. SQL Azure has introduced a number of changes to processes and protocols as it relates to development & support. One of the most viewed topics on this blog is my Determining SQL Server Table Size, a quick post from 2007 after helping a co-worker solve a problem. Fast forward 8 years and this post still sees more than 1,000 views in a month. However, try to run that on SQL Azure and you will have an issue. SP_SpaceUsed is not available to us on that platform! In this post, I will provide a few helpful queries to get around this limitation.

Database Size

For those that are familiar with the sp_spaceused solution, you would simply call this procedure and the results would show you the size of the current database. The SQL Azure ready solution for this is to query sys.dm_db_partition_stats and make a proper conversion for the sum of reserved pages and convert to MB. The following is a helpful Stored Procedure that I often add to databases to help.

CREATE PROCEDURE dbo.Support_DBSize
AS
SELECT
	SUM(reserved_page_count) * 8.0 / 1024 AS "DB Size(MB)"
FROM sys.dm_db_partition_stats	
GO

All Object Sizes

For those familiar with the sp_spaceused process, you could expand the call to sp_spaceused by simply calling "sp_spaceused objectname." to see the same metrics but for an individual database object, and with my older query you could enumerate the tables calling this procedure to see the results for all tables. Given that we don't have sp_spaceused available in SQL Azure we need to look towards a new process to complete this task as well. The following stored procedure expands on the example for the entire database but divided out by object.

CREATE PROCEDURE dbo.Support_ObjectSizes
AS
SELECT    
      obj.NAME AS "ObjectName", 
      SUM(reserved_page_count) * 8.0 / 1024 AS "Size (MB)"
FROM sys.dm_db_partition_stats DPS
	INNER JOIN sys.objects obj
		ON (DPS.object_id = obj.object_id)
GROUP BY obj.name
ORDER BY obj.name
GO

Single Object Sizes

Using the information from the prior procedure, we can easily then create a procedure to help us go after a specific object. The final helpful query in my toolbox is as follows

CREATE PROCEDURE dbo.Support_SingleObjectSize
	@ObjectName sysname
AS
SELECT    
      obj.NAME AS "TableName", 
      SUM(reserved_page_count) * 8.0 / 1024 AS "Size (MB)"
FROM sys.dm_db_partition_stats DPS
	INNER JOIN sys.objects obj
		ON (DPS.object_id = obj.object_id)
WHERE obj.Name = @ObjectName
GROUP BY obj.name
ORDER BY obj.name
GO

You can call this using "EXEC Support_SingleObjectSize 'EventLog'" for example to get the size of the EventLog table. It should also be noted that the parameter to this is sysname NOT a varchar column for proper matching.

Summary

Although a bit different SQL Azure supported features will work on non-SQL azure installations. SO the above queries will be helpful for those of us working in dual environments as well. I hope this has been helpful!