Windows Azure SQL Database Billing Numbers Directly From Transact-SQL

Windows Azure SQL Database Billing Numbers Directly From Transact-SQL

Windows Azure SQL Database exposes two Dynamic Managed Views called sys.database_usage and sys.bandwidth_usage that show you the activity for your account. These can queried to understand your account usage from a billing perspective.

Disclaimer

The prices for SQL Database editions and IO charges may change in the future. Make sure to utilize current costs in the queries provided to make sure your results are accurate.

Database Usage

For example, here is a query that will show your database storage usage in US dollars for the current month on all the databases on your server:

SELECT    SKU,
          SUM ( CASE WHEN USAGE.SKU = 'Web'
                   THEN (Quantity * 9.99/31)
                WHEN USAGE.SKU = 'Business'
                   THEN (Quantity * 99.99/31)
                END ) AS CostInDollars
FROM      sys.Database_Usage USAGE
WHERE     datepart(yy, TIME) = datepart(yy, GetUTCDate())
AND       datepart(mm, TIME) = datepart(mm, GetUTCDate())
GROUP BY  SKU

This query has to be run when you are connected to the master database of your server. The output looks like this:

Storage is not the only cost in using SQL Database, you can also have costs associated with transferring data from the data center in which the SQL Database resides. There is no charge for inbound data tranfers. To calculate the cost of outbound data transfers in US dollars for the current month, execute this Transact-SQL on your master database for the server:

SELECT    USAGE.Time_Period,
          USAGE.Direction,
          CASE WHEN USAGE.Direction = 'Egress'
              THEN 0.15 * USAGE.BandwidthInKB/(1024*1024)
          END AS CostInDollars
FROM (
          SELECT    Time_Period,
                    Direction,
                    SUM(Quantity) AS BandwidthInKB
          FROM      sys.Bandwidth_Usage
          WHERE     Direction =
'Egress'
                    AND    datepart(yy, TIME) = datepart(yy, GetUTCDate())
                    AND    datepart(mm, TIME) = datepart(mm, GetUTCDate())
                    AND    class = 'External'
          GROUP BY  Time_Period, Direction
) AS USAGE

The output looks like this:

Summary

Do you have a better way to accomplish the same thing in an Azure SQL Database? Post it in the comments below; make sure to test it on an Azure SQL Database first. Do you have questions, concerns, comments? Post them below and we will try to address them.

 

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Richard Mueller edited Revision 10. Comment: Removed (en-US) from title, added tag

  • Patris_70 edited Revision 9. Comment: added en-US tag and title

  • PatrickWood edited Revision 8. Comment: I see that my changes were used for the article. Thanks. Patrick Wood patwood@gainingaccess.net

  • PatrickWood edited Revision 7. Comment: There are no longer any charges for data ingress. I have a modified version of the html to make the changes. You can contact me at patwood@gainingaccess.net.

  • patmas57 edited Revision 6. Comment: Branding update

  • Ed Price - MSFT edited Revision 4. Comment: TOC and tags

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Ed Price - MSFT edited Revision 4. Comment: TOC and tags

  • patmas57 edited Revision 6. Comment: Branding update

  • PatrickWood edited Revision 7. Comment: There are no longer any charges for data ingress. I have a modified version of the html to make the changes. You can contact me at patwood@gainingaccess.net.

  • PatrickWood edited Revision 8. Comment: I see that my changes were used for the article. Thanks. Patrick Wood patwood@gainingaccess.net

  • Patris_70 edited Revision 9. Comment: added en-US tag and title

  • Richard Mueller edited Revision 10. Comment: Removed (en-US) from title, added tag

Page 1 of 1 (6 items)