March Madness – SQL Azure – sys.bandwidth_usage

Yesterday we talked about one aspect of SQL Azure pricing but it was only tied to database usage. Today we will talk about the other piece of SQL Azure costs: pushing and pulling data to SQL Azure. The dirty details can be found inside the sys.bandwidth_usage system view:

SELECT *
FROM sys.bandwidth_usage

Go ahead and run the above code and check out the result set that is returned.

What is this system view for?

This system view returns two rows for each database used (one row for data ingress and one for data egress) for each date that the database was used on your SQL Azure server. And yes, I enjoy using the word egress, and yes it does sound like an exotic animal.

Why should you care?

Besides the obvious choice of billing this system view is also going to help you understand which databases are being used more as well as which days the usage is occurring. That means you can figure out the exact cost per database, per day, in order to isolate the ones that are costing you the most.

Here is a query that will help you visualize things a bit more:

SELECT database_name, SUM(quantity) AS [KB Transferred]
FROM sys.bandwidth_usage
GROUP BY database_name
ORDER BY SUM(quantity) DESC

But that doesn’t break things down into prices for you, it just shows you which database is using the most bandwidth. For billing you are really only concerned about a few things:

• Your “Zone” (North America and Europe datacenters are Zone 1, everyone else is Zone 2)
• Your price for that Zone (currently $0.12 for Zone 1 and $0.19 for Zone 2)
• Your egress, because currently inbound data transfers are at no charge

For knowledge on billing then we want to sum our egress amounts and multiply by the correct price. This piece of code should do just that (courtesy of Paras Doshi):

-- Get Bandwidth cost by direction and type
SELECT SUM(case when sys.bandwidth_usage.direction = 'Egress'
THEN (0.12 * 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 the above code is still doing a summation that includes the ingress but it is being multiplied by zero. I like using this code because should Microsoft decide to start charging again for ingress then it is easy for me to input the new price, and I like easy.

The past two days we have talked about some billing aspects of SQL Azure. Tomorrow we will shift our focus a bit and look back inside our databases to get more information from them.