How to find the size and cost of sql azure database

SQL query to find the cost and size of sql azure database

 SQL  Azure database is a paid service as per the usage of the database. So we should have an idea regarding our usage of data in the sql azure database. Then only we can use it as cost effective product. So we need to regularly checking the size and cost of our sql azure database.

 Query to find the size and cost of SQL azure entire database

 The  following query will return the size and cost of the sql azure database.  The latest price of the azure storage can be seen here. http://www.microsoft.com/windowsazure/pricing/

 The following query will give the size and price of the database as per the following price:

 

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    (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
 

Query to find size and cost of SQL Azure datbase as per indexes

 The following query will return the size and cost of sql azure dtabase’s indexes.

 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' )

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 SELECT idx.name,
SUM(reserved_page_count) * 8192 'bytes',
      (SUM(reserved_page_count) * 8192) * @CostPerByte 'cost'
FROM sys.dm_db_partition_stats AS ps
    INNER JOIN sys.indexes AS idx ON
idx.object_id = ps.object_id AND idx.index_id = ps.index_id
WHERE type_desc = 'NONCLUSTERED'
GROUP BY idx.name
ORDER BY 3 DESC
 

Query to find out the size and cost of each tables in SQL Azure database

 The following query return the cost per month per row for every table in the database.
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' )
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
SELECT    
      sys.objects.name,
      sum(reserved_page_count) * 8192 'Bytes',
      row_count 'Row Count', 
      (CASE row_count WHEN 0 THEN 0 ELSE
       (sum(reserved_page_count) * 8192)/ row_count END)
        'Bytes Per Row',
      (CASE row_count WHEN 0 THEN 0 ELSE
       ((sum(reserved_page_count) * 8192)/ row_count)
        * @CostPerByte END)
        'Monthly Cost Per Row'
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, row_count