SQL Azure provides the following System Views
sys.bandwidth_usage returns the following output columns
Using this System view you can calculate the Bandwidth usage and cost using the following queries
DECLARE @Cost AS MONEY = 0.15;
SELECT CAST (time AS DATE) AS DateofUse,
direction,
class,
time_period,
sum(Quantity) AS QuantityUsed,
sum(Quantity) * @Cost / (1048576) AS [CostOfUsage (in $)]
FROM sys.bandwidth_usage
WHERE class = ‘External’
GROUP BY CAST (time AS DATE), direction, class, time_period;
This query gives you the following detailed output
Using the following Pivot query, you can get date wise InBound and OutBound traffic and its associated cost
DECLARE @Cost AS MONEY = 0.15;
SELECT DateOfUse,
[Egress] AS [OutBoundTraffic],
[Egress] * @Cost / 1048576 AS [OutBoundCost],
[Ingress] AS [InBoundTraffic],
[Ingress] * @Cost / 1048576 AS [InBoundCost],
[Egress] + [Ingress] AS [TotalTraffic],
([Egress] + [Ingress]) * @Cost / 1048576 AS [TotalCost]
FROM (SELECT CAST (time AS DATE) AS DateofUse,
direction, quantity
FROM sys.bandwidth_usage
WHERE class = ‘External’) AS BU PIVOT (SUM (Quantity) FOR Direction IN ([Egress], [Ingress])) AS Pvt;
You can use these queries to collect data from SQL Azure and monitor usage from your application itself on day to day basis.
SQLXpertise 