Check SQL Azure Database Associated Cost

  Ok so I found out exactly the size of every table in my SQL Azure Database, but now I’d like to know a little bit more, since I’m paying and I want to know how the payment is related to each of my tables. So in order to do this and after reading this blog post from the SQL Azure Team, this is the statement I’ve come up with:

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

Select @Edition, @SizeInBytes

   So now I have the Size used in Bytes and the Edition of the SQL Azure Database that I’m using, and so I can now calculate how much is the cost per byte.

DECLARE @CostPerByte float
SELECT   @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN (CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN (CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes

   Ok. Now I know exactly what is the cost per byte, and so what I need to know is exactly how much cost is associated with each Table.

select sys.objects.name, @CostPerByte * (sum(reserved_page_count) * 8192)
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

   So with this I know exactly how my cost is being split by each table. So now I only want to see if everything in the end comes up to the correct result.

Select ROUND(SUM(Cost.TotalCost), 2)
from (    select sys.objects.name TableName, @CostPerByte * sum(reserved_page_count) * 8192 TotalCost
        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) Cost

   Ok. So the result is correct. Sweet.