How to monitor SQL Azure bandwidth usage by yourself ?

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,




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;


[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]


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.


SQL Diag Configuration Tool

SQL Diag Configuration Tools is an easy to use tool which will help you in configuring SQLDiag.xml to collect performance data from SQL Server 2005/2008/2008 R2

SQL Diag Configuration Tool is a free tool available on Codeplex. You can download it from the following link 

Let us walkthrough the steps of using SQL Diag Configuration Tool

After installation, run the tool


Select your desired SQL Server version and then click on “Authentication Method” option available on left side


Specify the Server name and SQL Server Instance Name on which you are planning to use the SQLDiag.xml and then click “Machine Wide Diagnostics” option


Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”


If you want to collect Blocking information, select “Blocking” and the specify the Maximum File Size of log and the maximum number of files and the polling interval on which the Blocking information should be collected from SQL Server

Next Click on “Profiler" Options


Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.

Available Trace Templates are


After selecting, you can also add further Events you want to collect and then specify the maximum file size, maximum number of files and polling interval on which profiler information should be collected from SQL Server

Next click “Perfmon Options” to proceed


Select the Performance Counters which you want to collect or select the predefined template available and then specify Maximum File Size and the polling interval on which performance counter data should be collected

Next Click on “Producers” to enable the Producers options


Next Click on the “Analyzers” to enable the required Analyzers


Click “Save” to SQLDiag1050.xml and use it as input for SQLDiag to collect SQL Server Performance Data

You can use the  “Open” option reload the saved SQLDiag xml file and modify the profiler or perfmon or other above specified options.

I hope you find this useful…

%d bloggers like this: