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:
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
which facilitates a report showing time spent between connection and actual query time
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 😉
