SQL Azure introduces two Database views database_usage and bandwidth_usage which lets you view the billing information. you can run the TSQL code in the SQL server management studio or in the web based SQL Azure DB manager. These views are only available to the server level principal login and only available in the Master Database.

1. sys.database_usage

This view let's you know the billing information specific to database storage. There are three types of information available through this view. they are time, sku and quantity. time is simply the timestamp for the event. sku refers to the provisioned database type i.e business or web. And quantity refers to the max number of databases that existed for a given day per database type.

I have used the web based DB manager for this article but  you can use SSMS, if you wish.

Let’s execute ‘select time,sku,quantity from sys.database_usage

here is the result:

time                    sku          quantity 
----------------------- ------------ ----------
2011-03-12 00:00:00.000 Web          4 
2011-03-13 00:00:00.000 Web          4 
2011-03-15 00:00:00.000 Web          2 
2011-03-16 00:00:00.000 Web          2 
2011-03-17 00:00:00.000 Web          2 
2011-03-18 00:00:00.000 Web          2 
2011-03-19 00:00:00.000 Web          3 
2011-03-20 00:00:00.000 Web          3 
2011-03-21 00:00:00.000 Web          3 
2011-03-22 00:00:00.000 Web          3 
2011-03-23 00:00:00.000 Web          3 
2011-03-24 00:00:00.000 Web          3 
2011-03-25 00:00:00.000 Web          3 
2011-03-26 00:00:00.000 Web          3 
2011-03-31 00:00:00.000 Web          5 
2011-04-01 00:00:00.000 Web          12 
2011-04-03 00:00:00.000 Web          1

(17 row(s) affected)

Let’s modify the query to view billing information for only ‘current’ month. Here is the TSQL code:

 
select time,sku,quantity 
    from sys.database_usage    
    where datepart(yy,time) = datepart(yy, getutcdate()) and     
    datepart(mm,time) = datepart(mm, getutcdate())

The result of the above query is:

time                    sku   quantity 
----------------------- ----- --------- 
2011-04-01 00:00:00.000 Web   12 
2011-04-03 00:00:00.000 Web   1

Now, wouldn’t it be wonderful to view the cost for databases on our server for a current month? so for this we will require some simple calculation. Now, a Web edition database is priced at $9.99 per month. That means, if we have used one web edition SQL Azure database for a single day, the price is $9.99 / [number of days in the month]. If I have consumed say ‘4’ quantity of web edition SQL Azure database for a single day, the price is 4 * $9.99 / 31. Similarly, we can extend the same logic for Business edition DB.

TSQL code to calculate Database storage cost ( in dollars ) for ‘current month’ :

select sku, 
    sum (
    case when sys.database_usage.sku = 'web' 
            then (quantity*9.99/31)   
        when sys.database_usage.sku = 'business' 
            then (quantity*99.99/31)   
    end ) as 'cost'     
    from sys.database_usage     
    where datepart(yy,time) = datepart(yy, getutcdate()) and     
    datepart(mm,time) = datepart(mm, getutcdate())     
    group by sku

2. sys.bandwidth.usage

This view let's you know the billing information specific to data transfers. There are six type of information that is available for this view. they are database_name, direction, class, time, time_period and quantity. database_name is simply the database that consumed the bandwidth. direction is type of bandwidth that is used for SQL Azure.  In SQL Azure world,they are– Ingress and Egress. Ingress refers to the data moving into the SQL Azure database whereas Egress refers to the data moving out of the SQL Azure database. Class is the type of bandwidth that is used for Azure platform. Here Internal class means data moving into Azure platform whereas External is the data moving out of Azure platform. Time is the hour at which bandwidth was consumed. Time_period can have two values – peak or off peak. The peak time are based on the location at which server was provisioned. Quantity is the amount of bandwidth used in KB’s.

Now, As of now (16 April 2011), following is the Data Transfers cost:
o North America and Europe regions: $0.10 per GB in / $0.15 per GB out
o Asia Pacific Region: $0.10 per GB in / $0.20 per GB out
o Inbound data transfers during off-peak times through June 30, 2011 are at no charge. Prices revert to our normal inbound data transfer rates after June 30, 2011.

Now, let’s understand importance of each column in cost calculation. If the data transfer occurred inside the data center then no charges apply! so we will calculate cost only for class = “External”. My server is in US, so for me the ingress direction is charged $0.10 per GB and egress direction is charged $0.15 GB. Also not that there is no charge during off peak times for inbound data transfers.

Note: It is important to keep a tab on price changes and change the TSQL code, if you are using it.

TSQL code to view data transfer (in KB) for a SQL Azure subscription based on direction and time_period for current month:

select direction, 
    time_period,    
    sum(
        case when sys.bandwidth_usage.direction = 'Egress' 
                then (0.15 * quantity/ (1024*1024) )     
            when sys.bandwidth_usage.direction = 'Ingress' 
                then (0.10 * quantity/ (1024*1024))
        end ) as "cost"
        from sys.bandwidth_usage 
        where datepart(yy,time) = datepart(yy, getutcdate()) and     
        datepart(mm,time) = datepart(mm, getutcdate()) and 
        class = 'external' 
        group by direction, time_period

result:

direction  time_period  cost 
---------- ------------ ---------
Egress     OffPeak      0.000006 
Ingress    OffPeak      0.000000 
Egress     Peak         0.000575 
Ingress    Peak         0.010554

TSQL code to calculate the total cost of data transfer for current month*

select sum(
    case when sys.bandwidth_usage.direction = 'Egress' 
            then (0.15 * quantity/ (1024*1024) )  
        when sys.bandwidth_usage.direction = 'Ingress' 
            then (0.10 * quantity/ (1024*1024))  
    end 
    ) as "cost"
 from sys.bandwidth_usage 
 where datepart(yy,time) = datepart(yy, getutcdate()) and    
 datepart(mm,time) = datepart(mm, getutcdate()) and 
 class = 'external' and 
(direction!='Ingress' and time_period!='offpeak')
  

*Values based on Data transfer rate as on 16th April 2011.

Also note the part of TSQL code ' (direction!='Ingress' and time_period!='offpeak')' for not calculating the inbound data transfers at off_peak time_period. you need be aware about such offers so that you can include the logic in your code to get accurate numbers.

result:

cost 
---------
0.000575

Thus, we are able to view the billing information through these SQL Azure Database views,

Update : The "ingress" is now free. So the updated Query for calculating the bandwidth charge is:

select sum(
    case when sys.bandwidth_usage.direction = 'Egress' 
            then (0.15 * quantity/ (1024*1024) )  
        when sys.bandwidth_usage.direction = 'Ingress' 
            then (0.00 * quantity/ (1024*1024))  
    end 
    ) as "cost"
 from sys.bandwidth_usage 
 where datepart(yy,time) = datepart(yy, getutcdate()) and    
 datepart(mm,time) = datepart(mm, getutcdate()) and 
 class = 'external' 

Note: that when the direction is "ingress" the charge is 0.00 and i have updated the query accordingly.

Please keep in Mind:  The pricing Model of SQL Azure may change over time, so you need to modify the query as per the latest pricing model. I hope these queries would help you in doing so.

Author: Paras Doshi

Inspired from a SQLPASS 2010 talk – “SQLCAT: Administering SQL Azure and new challenges for DBAs” by Lubor Kollar.

Earlier i had titled the blog post: viewing SQL Azure database billing information through dynamic management view's 'sys.database_usage' and 'sys.bandwidth_usage' but i realized that sys.database_usage and sys.bandwidth are not DMV's - they are SQL Azure Database views and so i made appropriate changes. i apologize for the confusion.