Here's an easy tip that can greatly improve the performance of your report when using Windows Azure SQL Reporting: always make sure your report server and the data source your reports use are on the same Azure data center.

Here are some easy steps on how to identify if your reports are performing cross data center communications, and how to measure the performance impact due to this.


First, check the execution log of your reports. To do this, open the Windows Azure Management Portal, click reports, select your report server, and click the Download Execution Log button.

Now check to see if the Data Retrieval time in any of your reports looks abnormally long.

For example, here's my report execution data for rendering a simple chart report using the AdventureWorks sample database:

Notice how the Data Retrieval time (shown in Milliseconds) is much higher than the processing and rendering time - that's what I call suspicious.

Let’s take a deeper look at my report. My report uses a Windows Azure SQL database called let8c0j2n7.database.windows.net. My report server is called gejqh25dat.reporting.windows.net.

Now let's identify the geo location of both servers.

If you’re a co-admin in the Azure subscription of both servers, you can easily find this info in the Windows Azure Management Portal:

In my case, the portal shows the following when clicking on my database:

 

And when clicking my report server, it shows:

 

Another way to verify the location of each server is to simply ping them. For example, pinging my Azure reporting server results in the following:

While pinging my SQL Azure database results in:

Geo location is indicated in each server response. In the first ping, Dublin appears in rsrpoddublin.

When I created my report server, I selected “North Europe” as the region. Looking at the Windows Azure regions (https://www.windowsazure.com/en-us/support/trust-center/privacy/), we can see the North Europe region is in Ireland, so it makes sense my report server is mapped to an address with Dublin in its name.

Similarly, the hkn in my database means Hong Kong (which is the East Asia region).

Because my data source is located in Hong Kong and my report server is located in Dublin, the long Data Retrieval time we saw for my report (3.3 seconds) is mostly due to the latency between the data centers.

Now let's look at report execution times when the database and my report are in the same data center:

Down from 3.3 seconds to 49 milliseconds! That's how critical your data source and report server geo locations are!


Naturally, there can be other reasons that contribute to high data retrieval times for reports.

Here are some excellent references on how to read the execution log and pinpoint the bottlenecks in report execution

http://www.keepitsimpleandfast.com/2011/01/analyze-performance-of-your-reporting.html

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx