What’s Up With the Slow SSRS R2 Rendering in SharePoint 2010 Integrated Mode?

Note: this entry has been updated since its original posting due to new information available.

We are experiencing performance degradation with SQL Server Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode, as compared to Native Mode.  Along with my fellow Mariner Derek Sanderson, I’ve spent the last few days working with Microsoft Partner Technical Support to find the root cause and determine if performance can be improved.  This entry discusses what we found during the troubleshooting process and, if you are having the same issues, give you some ideas of where to look.

The Executive Summary

For the foreseeable future, we have chosen to revert to Native Mode for our Education Group client implementations.  We are reverting because the performance decrease in SP Integrated Mode is unacceptable, for these reasons:

• The bulk of the time spent during report rendering is due to the number of HTTP calls between the Web Front End (WFE) and the Reporting Services servers.  This is due to:
  • Integrated Mode results in more calls than Native Mode, which results in more overhead.
  • Version 10 of Microsoft.ReportViewer.WebForms is slower than Version 9 was.
• Our dashboards are very visual.  They contain indicators, sparklines, gauges, and charts which are treated as images by the browser.  This means we have a lot of HTTP calls devoted to calling images.  Most images appear to be rendered in a single thread, and each HTTP image call experiences some blocking and waiting.  Our experience with a visual dashboard is that the images are taking about 90% of the rendering time.  Also, since the images are being created dynamically by SSRS, they are given a unique identifier which is not cached - so the end result may be the same image that is already displayed multiple times.
• Microsoft testing has discovered that we are experiencing a performance penalty because charts & gauges are embedded within a tablix.  This is due to the new "filling" behavior supported in R2 which means the image rendered in HTML grows to fit the available space.  Report Viewer Control 10 handles this "better" than RVC 9 did, which is proving out to be costly.
• This SQLCAT article concludes that, although there’s a 400 millisecond performance penalty expected in SP Integrated Mode, that SP Integrated Mode with SQL Server 2008 R2 ultimately outperforms Native Mode.  We found the performance penalty to be far more than a 1/2 second delay in our situation and therefore could not risk performance on existing and future client implementations.
• Microsoft Partner Technical Support concurred our best solution at this time was to revert to Native Mode.  After Microsoft reviewed HTTP traces, IIS logs, FREB analysis, SharePoint logs, PerfMon data, Profiler traces, and network analysis, they concluded the bulk of the time is being spent in the Report Viewer control.
• Everyone we spoke with at Microsoft reiterated this is known behavior, and to be expected.

Below are further details, should you choose to dive in.

Background:  Our Environment

We recently changed our SQL Server 2008 R2 Reporting Services instance from Native Mode to SharePoint Integrated Mode, for several reasons.  Most notably we wanted to use fields within the Reports Library for metadata, and to take advantage of versioning and check out features in SharePoint.

We are using SAN storage in a hosted environment.  We are running SharePoint 2010 Foundation (currently on CU3).  There are 3 machines in our SharePoint farm:  (1) SQL Server (two instances:  one which has our relational DBs, the Report Catalog, as well as SSAS; and the other instance for the SP Content databases); (2) a Web Front End; and (3) a Domain Controller.  We are running the 64-bit SQL Server Enterprise Edition on Windows Server 2008 R2 Standard.  There’s a big backbone in our hosted solution environment, so resources aren’t of primary concern (although they were evaluated during the troubleshooting process – I won’t be speaking to hardware resources in this blog entry).

My SSRS report is set up as a snapshot because the data changes infrequently.  Its data source is an Analysis Services cube.  There is one group hidden initially, expanded by clicking a toggle.  If all collapsed data were expanded, it would be a grand total of 58 rows.  There are 3 parameters present, all of which have default values.  Within the report, I’m using one tablix which embeds one gauge, one bar chart, two indicators, and a sparkline.  It’s a very visual dashboard-like report.  What’s shown in the following screen shot represents 4 rows of data:

          

Our Issue with Slow Performance

After converting to SP integrated mode, we quickly discovered that the same report ran significantly longer within the SharePoint Reports Library we created, as compared to how it ran in Native Mode.  In SP Integrated mode, it was taking around 11 seconds for the report to initially render in the browser.  When I expanded one toggle, it took 5-7 seconds to display the collapsed data.  Basically unacceptable, particularly for such a small set of data (12 rows initially displayed; 58 total rows with the hidden toggles) running off of a snapshot.

On an internal version of the same report I have a parameter which allows the user to expand or collapse all toggle items with one click (instead of needing to click 12 times).  This option takes almost 1 full minute to render – ouch!  This is extremely unacceptable – although it’s a visual report, there are only 58 rows of snapshot data being rendered.  As we’ll see later, the bottleneck is with the images.

Report Catalog Execution Log

I checked the Execution Log using the following query:

      USE ReportServer

      GO

      SELECT *

      FROM ExecutionLog2

      ORDER BY TimeStart DESC

Side note:  The ExecutionLog2 is a view specifically for reporting on SQL Server 2008.  It provides several derived fields which decode the meaning of data in the ExecutionLogStorage table.

This query confirmed the trouble wasn’t related to data retrieval.  The TimeRendering was about half a second; although this was longer the ~90ms in Native Mode, a half a second is still speedy enough in my book.  Following are a few fields from the ExecutionLog2 view, which first shows the initial rendering, then the click of a toggle button to expand some detail.

    

Report Server URL

To make things more interesting, in addition to running the report via the Report Viewer web part in SharePoint, we ran it directly using the Report Server URL (ex:  http://ServerName/ReportServer). 

Side note:  Although the /Reports web page is inactive in SP Integrated mode, the /ReportServer page is still active.

Rendering the report directly from the /ReportServer URL was significantly faster than rendering it through the Report Viewer web part – comparable to how quickly it ran in Native Mode.  This told us that rendering it within the Report Viewer web part added quite a bit to the rendering time.

Cumulative Update 3

As part of our troubleshooting process, we installed CU3.  Since our report contains 3 parameters, the Microsoft engineer assigned to our incident encouraged us to install CU3 in the hopes that a fix related to parameter values (KB2276203) would be helpful.  Unfortunately, installation of CU3 didn’t help our cause.  As of the time I’m writing this, CU5 is available – however, we have no specific reason to install it so we’ve opted not to at this time.

Snapshot versus Caching versus Live

Our intention was to run the report from a snapshot, since the data rarely changes.  I also experimented with running the report from a cache.  In this situation, the snapshot and the cache provided the exact same performance, from both the Execution Log perspective (just a few milliseconds difference in performance), as well as perception of how quickly the page renders – about 11 seconds.

In our situation, live mode did have greater TimeRendering according to the SSRS execution log – however the overall time rendering didn’t increase.  Since our dataset is so small, and is being sourced from a cube, the Processing Options weren’t critical choices for this particular report.

Overhead between SSRS and SharePoint

In SP Integrated Mode, there are a lot more WFE API calls, as well as WFE-SSRS API calls.  These contribute to the overall rendering time, and is by design according to Microsoft.  We discovered this by running traces using both Fiddler2 and Wireshark.

For example:  in the screen shot below from Fiddler, see the individual HTTP calls which are devoted to images.  In the one that is highlighted, that’s a call to show one of the indicators.  In the screen shot above, where I have 4 indicator arrows being shown, that would result in 4 separate calls. 

 

An interesting thing we discovered when comparing traces between running the report via the Report Viewer web part, versus the Report Server URL:  there’s no HTTP calls when it communicates with the Report Server.  Instead, the calls between the WFE and SSRS are RPC type calls (API calls), or SQL calls for the data.  This is to be expected; it just makes comparing the trace files between the two different.

Also, we found that the time for authentication negotiations were handled very quickly in milliseconds, so not of concern.

Image Files Within the Report

According to MSDN:  “The Chart, Gauge, and Map data regions behave like images when they are rendered and displayed in the report body.”  I would add that the Indicator and Sparkline do as well.

There’s one call per image to be displayed on the report.  If the same green up arrow will show in 18 places on the dashboard, then there will be 18 HTTP calls devoted to images.

Using Google Chrome’s Developer Tools gives us another perspective on these image calls.  In the following screen shot, you’ll see that of the 11.4 seconds to initially render a report, 8.99 seconds were devoted to the images:

   

In the next screen shot, I’m hovering on one of the images partway through the process.  Note there’s 3.15 seconds blocking and 1.22 seconds waiting:

    

In this next screen shot, I’m hovering on the last image to be rendered in the process.  Note there’s 2.10 seconds blocking and 6.87 seconds waiting:

    

In addition to the above blocking and waiting issues, we discovered the images are non-cacheable.  As one of the Microsoft engineers explained it to me, each report element has its own ID, so it can’t be cached.  Each image needs to be retrieved using a GET with a status of 200.  There aren’t any configurable settings to modify this behavior.  The Google Chrome Developer Tools “Audits” pane confirms the images are non-cacheable:    

In summary:  a number of SSRS data regions are treated as images by the browser, those images are invoked synchronously (ever seen the countdown of items in the left corner of your IE browser?), and aren’t cached. 

The HTTP calls are handled differently in Native Mode.  In speaking with a Microsoft engineer, his belief was that there’s never any image aggregation in the HTTP calls; however, the following Fiddler screen shot shows what appears to be multiple images in the same call when in Native Mode:

    

In addition to the items to be displayed on the report, there are HTTP calls devoted to toolbar items (the toolbar icon, the pagination icon, etc).  This adds a few more image calls, if the some or all of the toolbar is being displayed to the user.

Optimizing the Report Catalog

The writes to the Report Server Temp DB can become CPU intensive.  Another team member did take a look at the I/O activity and if any bottlenecks could be found.  A Microsoft engineer reviewed our PerfMon data (incident counters only) and found no signs of I/O latency.  SQLCAT has a helpful article about Report Server Catalog Best Practices. 

Different Browsers

In our circumstance, I did not see significant performance differences in different browsers.  One Microsoft engineer felt that IE 8 would perform much better than earlier IE versions because the default connection limit has been increased from two connections per host to six connections.  For me personally, Firefox and Google Chrome both performed the same as IE 7.

Report Viewer

Version 10 of Microsoft.ReportViewer.WebForms renders in AJAX, whereas Version 9 rendered in an IFRAME.  My trusty coworker, Derek, attempted to use V9 Report Viewer in our environment.  However, V9 is not compatible with the new elements of SQL Server 2008 R2 (on our dashboard, most notably this was the indicator and the sparkline).  After mentioning this test to Microsoft, they strongly encouraged us to stick with Version 10 within the SharePoint 2010 / 2008 R2 environment rather than try to revert to Version 9.

Possible Mitigation for the Integrated Mode Performance Issues

What to do, what to do?  If the additional overhead is to be expected, what to do?  The following are the options we considered (in no particular order).  Perhaps one of these options may work for you, if you’re in the same situation.

1. Install SQL Server on the same box as the WFE.  Only Reporting Services would be installed (and probably result in an extra SQL Server license, although I didn’t look into it).  The SQL Server database engine would remain as is, on a separate box.  This option wouldn’t reduce the number of API calls, but could avoid some network delays.  This is not the recommended configuration, but is an option presented by our support engineer.  We did not choose to pursue this option, but it was suggested by a Microsoft engineer as an option.
2. Make some report modifications so less images are utilized on the page.  Redesigning various reports was not an option for us.
3. Stay put in Integrated Mode & be patient.  (Perhaps a quick game of Pac-Man?)
4. Return to Native Mode.  With V10 of the Report Viewer it'll still be slower than V9 was, but Native Mode does consistently render a bit quicker in our environment than Integrated Mode does.
5. If you have the Enterprise version of SharePoint, you could render the SSRS report through a PPS web part instead of the Report Viewer.  I haven't done this as of yet, but a couple of people on the Microsoft forums have indicated they have gotten better performance this way.
6. Avoid embedding gauges & charts within the tablix where possible.  Wrapping a rectangle around each gauge or chart does help.
7. Try delivering the SSRS report inside of a PerformancePoint dashboard. 

So as it stands now, we’re going back to Native Mode, with some disappointment that we can’t take advantage of some of the SharePoint Integrated Mode features until something changes with the product.  I will keep my fingers crossed on that front.

Finding More Information

SQLCAT Article:  Reporting Services Performance in SharePoint Integrated Mode in SQL Server R2 (Our experience is that the performance penalty is more than an additional 1/2 second as stated in the resolution of this article.)

MSDN Article:  Troubleshooting Reports: Report Performance

MS Support Article:  General overhead performance differences when you view reports in SharePoint integrated mode versus SharePoint native mode

MS Connect Bug:  SSRS 2008 R2 and SharePoint 2010 Performance Issues

MS Forum:  SSRS 2008 R2 Add-In Performance Issues

Network Steve Forum:  Performance Issue with Images in SSRS 2008 R2 in SharePoint 2010 Integration

MSDN Article:  Rendering Data Regions

SQLCAT Article:  Report Server Catalog Best Practices

Derek Sanderson's Blog:  The Switch Back to SSRS Native Mode for Performance Reasons

Update on Friday, March 4, 2011 at 11:53AM by Melissa Coates

Here’s the latest information available from Microsoft on our issue described above.  A direct quote from our Sr. Support Escalation Engineer:

The product group did come back as to why the performance was bad with RVC 10. Basically, when we have a chart/gauge/map control in the tablix cell, in 2008 R2 we started supported filling, meaning the image rendered to HTML grows to fit the available space. This calculation is apparently pretty costly that is causing performance hit.

Apparently RVC 9 does not handle this properly (hence not render the report properly) and the performance looks faster because it is not completely doing the job. Currently, I am trying to work with product team to see if there is any way we can avoid the fill calculation since it is proving to be costly.

So, because we have charts & gauges embedded within tablix cells, this results in performance issues.  Because a table and list are both tablix data regions, that type of redesign isn’t an option.  As of now, Microsoft is still looking into a workaround for us.

Comments

[Pedram Rezaei]

Hi,

This is Pedram Rezaei from the Reporting Services development team. Please allow me to thank you for spending the time investigating the above issue and compiling this article. I would very much like to get a better understanding of your analysis. Please feel free to contact me at ‘pedramr’ AT Microsoft.

Thanks,

-Pedram

[jorgeghr]

This is discouraging to find out. Any updates from Microsoft?

We are on the cusp of deploying a Sharepoint 2010 integrated SSRS R2 platform.
I have not looked at the PPS web part.

Thanks

[Melissa Coates]

jorgeghr: Microsoft has created a reproduction environment with our data & our report (luckily, we had public info we could share). As recently as yesterday we got an update that the dev team is still looking into the issue.

I haven't updated the main entry above yet - but we have a suspicion that part of the issue might be related to Foundation. In one test we found that it ran faster on Enterprise - so we're suspecting Enterprise has additional caching abilities but we haven't had a chance to test further to confirm. Our test was on a laptop with a VM, so not representative of a real world scenario, but something we intend to look further into as soon as we can set up another environment to test with.

Good luck with your deployment. When we learn more from Microsoft, I'll continue to update this post so it's as complete as possible. If you learn anything interesting on your end, I'd love to hear about it.

-Melissa

[Melissa Coates]

For anyone subscribed to these comments...just wanted to point out this entry has been updated with new information. There's an issue w/ embedding charts & gauges within a tablix. More info. added to the beginning & the end of the blog entry.

[Mike Honey]

Hi Melissa,

Wow - thanks for posting such an informative entry on this topic. I'm working on migrating a client from Report Manager to SharePoint 2010, so this info was very interesting.

I'm curious whether your performance issues were limited to reports with "charts & gauges are embedded within a tablix"? Do simpler reports (e.g. tables of tex/numbers, single charts) perform OK?

Thanks
Mike

[Melissa Coates]

Mike,

Simpler reports do perform fine. There is a small performance penalty as described by the SQLCAT article, but not much for tabular reports in my experience.

The above post evolved over several weeks, so it gets a bit difficult to digest. Bottom line: it's the Report Viewer Control 10, whether you're in Integrated Mode or Native Mode, for those more complex reports. (We do employ Native Mode, yet surface those reports in SharePoint using a URL - and we still experience the same issue.) When the post first originated we thought Integrated Mode was the issue, but it's the RVC. Just wanted to clarify that for anyone perusing these comments.

Thanks for reading my blog & posting a comment. Good luck with your upcoming migration.

Melissa

[Igor Z.]

Great info on this Site !!!!

let me post my issue and hope someone can Help.
I just rolled out an upgrade from 2008 to 2008 R2 and we also scaled out from 1 front end server to 2 front end servers with a load balancer on top (Native Mode).

when we run large report after about 2 min of running, we get prompted for our credentials to authenticate. after you type it in the report does NOT return any data but we get an error on the page:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.2; Tablet PC 2.0; MS-RTC LM 8; MS-RTC EA 2)
Timestamp: Mon, 21 Mar 2011 21:48:16 UTC

Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 12031
Line: 5
Char: 62099
Code: 0

with that said. If I go to the server itself I can run the report, but when I go through the load balancer I am getting prompted for my credentials and the report runs…runs..runs…Done “errors on page”

Any help you provide would be greatly appreciated…

[Ted Stathakis]

Thank you for such an amazing post.
I have subscribed to updates as I am anxious to see what comes of this as I have reports with hundreds of images imbedded in a tablix.

[Nate]

Have you heard anything further from Microsoft of this issue?

[Melissa Coates]

Nate -

No new information recently. So sorry!

Melissa

[Linda]

Melissa, how about we use "Page Viewer" web part to embeded RS URL instead of using ReportViewer web part? Does PageViewer performs better? We need to show reports in the SharePoint page.

Thank you very much!

[Prash]

For Denali CTP3 SQL Server Reporting Services significant improvements in performance in SharePoint mode have been made. Aspects are available against SSRS 2008 and SSRS 2008 R2 report servers as well.

More details can be found @ http://blogs.msdn.com/b/prash/archive/2011/06/11/performance-of-ssrs-in-sharepoint-mode-reports-in-full-page-view-in-sharepoint-2010.aspx

HTH
Prash

[Pedram Rezaei (MSFT)]

We have been hard at work improving the performance of SharePoint integrated mode. SQL Server Codename “Denali” CTP3 has significant improvements in this area. Please give it a try: https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

[Melissa Coates]

Pedram & Prash,

Thank you for providing the links. I'm very excited to dig in & check out Denali. Just haven't had time yet...will update this post if I see a major change with the particular circumstance I'm facing.

Thanks again!
Melissa

[Eric Monas]

Using Performance Point as a container for the SSRS reports and then consuming the reports in sharepoint via a PPS web part we noticed much better performance than using the reporting services web part.

We are using Integrated mode and do have charts & gauges embedded within tablix cells and have very quick report generation times.

[Melissa Coates]

Eric,

Thanks for the feedback. That's great to hear it's a valid option for those customers with an Enterprise license.

Melissa

[Srikanth Kasturi]

Do we have any update on this ? Any hotfix/service pack on report viewer 10.0 addressing the speed would be pretty encouraging.

[Melissa Coates]

Srikanth -

Sorry, I've heard no updates.

Melissa

[Gavin]

Nice blog Melissa. I have been having this issue for a while. We have some great looking reports with gauges imbedded into tables. This has caused me to hold back on the rollout of SSRS 2008 r2 for our reporting services area. A real shame. It's been a long time for Microsoft to be working on a fix for this in an update :( :(.

[DDog]

We have a similar situation. We've applied SP1 to our SQL 2008 R2 installation but the problem remains. I assume there aren't any updates to this thread? Thanks again for the great post.

[Nate]

Eric's comments about using Performance Point containers to render the reports also produced favorable results for us. The process added a little bit of overhead on the set up side, but works quite well once you get Department pages set up with dashboards and reports built together for them. With no previous Performance Point experience it was still pretty easy to get through.

[jorgeghr]

Here we are 9 months later. Has anyone experienced any improved performance? or figured anything out? Would it be worth contacting Microsoft?

[Thomas Hansen]

Thank you for a very informative blog. Have you ever checked if an antivirus program is affecting the response time. We see that Mcafee is running heavily when running a report (Sharepoint 2010 integrated mode + SQL 2008 R2 ). Even when moving from one parameter field to another Mcafee scans and delays response for several seconds.

[Mohd]

we just applied SQL Server Reporting Services 2012 RC0 Add-in for SharePoint in our environment and it is backward compatible with SQL Server 2008 r2 reporting services, and I can confirm we noticed significant improvement on the speed of rendering report, once SQL server 2012 goes RTM we will upgrade immediately the entire environment

[Gavin]

Microsoft have just release CU5 for SQL Server 2008 R2 SP1 and a problem with drilldown rendering is listed as a fix. Could it sort this out....finally?

Back in work tomorrow where I will be testing to see if this fixes things.

Gavin

[Ly]

I am very interested to know the result as well. Has MS fixed this bug yet?

[JHadden]

I'm also experiencing this issue. Given the dynamic nature of our dashboards and parameter driven behavior, We chose SharePoint Integrated Mode to address this need. We're too far down the road to change directions now. Unfortunately, the performance is causing the end users some pain.

[Gavin]

Applied the latest cumulative update as stated in my earlier post and this didn't fix the issue. :( :(

[Random visitor Steve!]

I had the same performance issue. This was after spending hours figuring out just how to get the sparkline to correctly line up and represent the data in the matrix.

I was pretty upset at Microsoft for releasing what was obviously a barely tested feature.

At any rate, in one of the videos i was watching on youtube that showed the use of sparklines, I remember that the presenter dropped a rectangle in the matrix cell before adding the sparkline.

I thought that was a useless step, but after going back and recreating all of the sparklines within rectangles within the cell, the performance issue was significantly diminished.

Good luck all.

[Gavin]

Random Visitor Steve has found the solution here. It's not ideal, and will require a fair bit of re-work but adding the rectangle first does fix this issue for me.

Embarrassing for Microsoft, but nice one RVS!