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_statsDECLARE @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.