Archive

Posts Tagged ‘Azure’

How to monitor SQL Azure bandwidth usage by yourself ?

May 2, 2011 Leave a comment

SQL Azure provides the following System Views

sys.bandwidth_usage returns the following output columns

image

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

image

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;

image

You can use these queries to collect data from SQL Azure and monitor usage from your application itself on day to day basis.

Advertisements
%d bloggers like this: