SSRS Performance in SharePoint Integrated Mode

pturley, 2012-05-29 (first published: 2012-05-24)

I’m working with a client to help them move all reports in their company from native mode (Reporting Services not integrated with SharePoint) to SharePoint integrated mode. At the same time, we will help them migrate hundreds of report in SSRS 2005 and 2008 R2 to SSRS in SQL Server 2012. Does it always make sense to integrate SSRS reports into SharePoint?

SharePoint 2010 really is Microsoft’s direction for BI and collaborative reporting. It offers some great features and capabilities for most business users. SharePoint has improved quite a lot over the past few versions and SharePoint 2010 is an impressive platform for hosting content, collaborating, sharing and bringing a business community together to get work done. Many businesses have already embraced SharePoint as their central content portal. A few years ago when my employer tried to convince us to use SharePoint 2003 and 2007 to replace shared folders in the network file system with document libraries, sometimes it was just a pain in the butt! Each and every file had to be uploaded separately, checked-in and tagged with required properties. Even though moving documents around in SharePoint is not always as easy as dragging and dropping them in Windows Explorer, it has certainly gotten a lot more convenient.

Back to my client… Today they’re running SSRS on some slow hardware and have some report design issues that cause a few isolated performance challenges. Now they want to move to SQL Server 2012 and integration with SharePoint 2010 – and they want to know what to expect. My reply is that there are many advantages in embracing this new paradigm and preparing to use the new generation of Microsoft BI tools in SharePoint, but the bottom line is that by adding another layer of complexity to the solution, it is inevitable that reports will slow down some. Sure, we can improve overall performance by upgrading the server hardware and users will never experience this penalty (but I suppose that’s cheating a little.)

In researching this issue, there are several good resources online. Melissa Coates, AKA Data Chick, blogged about her experience in January 2011 working on a SharePoint integration project with slow report performance issues. Her detailed observations are educational as are the long thread of chatter that followed the post. One of a few conclusions was that the ReportViewer 10 web part used in SharePoint 2010 causes more overhead than the earlier version especially when images (as rendered charts, sparklines and other graphic visuals) are repeated in tablix cells and resized to fill cells. The resizing issue can be mitigated by using rectangles to control image resizing. She reverted her SSRS SQL Server 2008 R2 client back to native mode because of poor report performance, awaiting better news from Microsoft about pending improvements for SQL Server 2012.

Another blogger, Giles Hamson, has observed some significant performance improvements with SSRS integrated with SharePoint in SQL Server 2012 when compared to SQL Server 2008 R2. According to his tests, performance improved from 30% to as well as 249% in some cases.

So, what am I telling my client as we prepare to make the move from native mode in SSRS 2008 R2 and SSRS 2005 to SharePoint integrated mode with SQL Server 2012? I think we’ll move cautiously forward and test the water before we jump in head first. One of the redeeming elements is that they have few graphical-intensive reports which seemed to be one of the big show-stoppers in Melissa’s project.

This particular client will probably take a little while to make the move but in the meanwhile, we have several other clients who have already taken the plunge and – so far, so good. Stay tuned.