SQL Centre of Excellence

Over the last few months we have been developing reporting services reports on top of a SSAS tabular model. One of the banes of my life has been the unusable performance of our reports, especially if they open a lot of connections.

This week I finally got some time to triage the issue and found the root cause, so I’ll post it here in case anyone else is hit by this.

How Can we measure Connection Time on Reports ?

Reporting services 2012 onwards has additional performance data on datasets on the ExecutionLog3 view  in the AdditionalInfo XML column. In my case it looked something like this:

image

As you can see it is taking 420ms to open a single connection to the Analysis Server This should be around 20ms or less. One of the annoying this about reporting services is it will open a fresh connection for every dataset, so with more complex reports this became unusable very quickly with some seemingly simple reports taking 30 seconds plus.

We used and xPath query and another report to visualise this performance data per data

image

which facilitates a report showing time spent between connection and actual query time

image

 

What was the cause

After trying it on various servers, laptops, desktops we managed to isolate it down to any of our SSAS Servers running inside Hyper-V on Windows 2012R2. Some further triage isolated the issue down to VMs which were using a broadcom network card with a known issue with Hyper-V and the NIC

http://www.dell.com/support/article/us/en/19/SLN132131/EN

https://support.microsoft.com/en-ie/kb/2902166

After some reconfiguration, the connections came down to under 20ms Smile

blog comments powered by Disqus

Page List

Page List