SSRS 2008 R2 and Sharepoint 2010 - performance problems
Hi all, I'm currently in a DWH project using SSRS 2008 R2 x64 in Sharepoint Integrated mode along with Sharepoint 2010, and we're seeing massive performance problems even though traffic on the server is at a bare minimum. We're in development with a maximum of a few users online at any given time and the server itself isn't doing a whole lot...cpu is usually < 20%. We have timed the database queries and 90% of them are taking less than a second to execute while reports takes anywhere from 5 to 30 seconds to load (some heavier reports can occasionally take a lot longer). I am simply unable to get anything to load in less than 5 seconds, even the simples grid from a SELECT TOP 5 * FROM table query takes this long. The server setup is suboptimal as SQL Server, Reporting Services and Sharepoint are all running on the same virtual machine but with the minimal amount of traffic we have this shouldn't really be a problem. The server is a Win 2008 x64 Datacenter with 4 CPU's and 16GB RAM. A standard sharepoint page that gets data from the database is returned in milliseconds...why doesn't reporting services do the same? What can I do to increase performance?Lumbago www.thefirstsql.com
November 2nd, 2010 9:49am

Ok, I found a solution to my problem and...well...I have a *really* hard time forgiving Microsoft for this one. What I basically did was to change SSRS from Sharepoint Integrated to Native mode. I'll create a blog post about exactly how I did this shortly because to be honest: this pisses me off. Lumbago www.thefirstsql.com
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2010 4:40am

Hi Lumbago, In SQL Server Reporting Services(SSRS), when a report is executed, the SSRS retrieves the request, retrieves data, processess the request, renders the report, and then logs the execition information. That(the time logs the exection information - the time the request is got) provides the end-to-end duration of the request. This is known as TimeStart and TimeEnd in the ExecutionLog in SSRS report server catalog database. For the three other actions I mentioned before, they also need time to be finished. The time for these actions are logged in the catalog database as TimeDataRetrieval TimeProcessing TimeRendering Addtionally, the whole execution time should include the time for compiling the SSRS(compiling for .NET application). In this case, we also need to include the time for compiling the SharePoint application. In other words, the execution time = compiling time + time for some other actions(e.g. create a work thread, create exectution, add execution to workthread) + TimeDataRetrieval + TimeProcessing + TimeRendering + time for logging exection information. In this case, the TimeDataRetrieval is short(as you mentioned, may be less than a second), the bottleneck should be TimeProcessing and TimeRendering, or the compiling time. In order to find out the bottleneck, could you please help to check the TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing and TimeRendering in the ExecutionLog2? As a workaround, please try to open the SharePoint site and the SSRS web services URL before rendering a report. This may reduce the compiling time. For more information, please see: ExecutionLog2 View - Analyzing and Optimizing Reports: http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx Reporting Services Performance Optimizations: http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx If there is anything unclear, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
November 4th, 2010 4:52am

Hi Jin, I appreciate your answer but it really isn't relevant at all. We timed the complete page rendering time using a regular stopwatch on quite a few of our report pages (some of them having 2-4 different reports in them). For one particular report page we clicked the refresh button in the browser in Sharepoint Integrated mode the average time for the entire page to appear was 22 seconds (we refreshed the page 5 times). When we change to Native mode we were simply unable to time the refresh with our stopwatch because it was simply too fast. I would guess the average refresh times were less then a second. The timings in ExecutionLog2 are "obsolete" because the overhead is in the Sharepoint add-on and not in Reporting Services it self. The timings in ExecutionLog2 have not change noticeably since the switch. Lumbago www.thefirstsql.com
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2010 5:07am

Hi Lumbago, In SQL Server Reporting Services(SSRS), when a report is executed, the SSRS retrieves the request, retrieves data, processess the request, renders the report, and then logs the execition information. That(the time logs the exection information - the time the request is got) provides the end-to-end duration of the request. This is known as TimeStart and TimeEnd in the ExecutionLog in SSRS report server catalog database. For the three other actions I mentioned before, they also need time to be finished. The time for these actions are logged in the catalog database as TimeDataRetrieval TimeProcessing TimeRendering Addtionally, the whole execution time should include the time for compiling the SSRS(compiling for .NET application). In this case, we also need to include the time for compiling the SharePoint application. In other words, the execution time = compiling time + time for some other actions(e.g. create a work thread, create exectution, add execution to workthread) + TimeDataRetrieval + TimeProcessing + TimeRendering + time for logging exection information. In this case, the TimeDataRetrieval is short(as you mentioned, may be less than a second), the bottleneck should be TimeProcessing and TimeRendering, or the compiling time. In order to find out the bottleneck, could you please help to check the TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing and TimeRendering in the ExecutionLog2? As a workaround, please try to open the SharePoint site and the SSRS web services URL before rendering a report. This may reduce the compiling time. For more information, please see: ExecutionLog2 View - Analyzing and Optimizing Reports: http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx Reporting Services Performance Optimizations: http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx If there is anything unclear, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
November 4th, 2010 11:52am

Hi Lumbago, According to Reporting Services Performance in SharePoint Integrated Mode in SQL Server 2008 R2, when users request multiple-second reports, the performance difference is a small fraction of the total time required to render the first page, and, when they exporting to Microsoft Excel, SharePoint integrated mode in SQL Server 2008 R2 Reporting Services is equal to or faster than native mode with SQL Server 2008 Reporting Services. In this case, could you please apply the latest hotfixes for the deployment, and then check if the issue is solved? Thanks, Jin ChenJin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 1:36am

Thank you for the SQLCAT link...it was very interesting reading even though their results differ quite a bit from what we are experiencing. A major difference between their test environment and ours is that we have everything running on one single virtual machine (vmware). I don't know if this makes any difference but on our setup the difference in performance between integrated and native mode is *substantial*. There is no mention of hotfixes in the article and personally I think it has to be something else but it could be worth a shot. Unfortunately we have been developing in native mode for a few days now and it's too much work to go back. But if SQLCAT can get consistent good performance using integrated mode using the standard R2 releases I don't see any reason why we shouldn't as well...maybe I'll set up two brand new vm's and do some testing when I find the time.Lumbago www.thefirstsql.com
November 5th, 2010 4:13am

Hi Lumbago, According to Reporting Services Performance in SharePoint Integrated Mode in SQL Server 2008 R2, when users request multiple-second reports, the performance difference is a small fraction of the total time required to render the first page, and, when they exporting to Microsoft Excel, SharePoint integrated mode in SQL Server 2008 R2 Reporting Services is equal to or faster than native mode with SQL Server 2008 Reporting Services. In this case, could you please apply the latest hotfixes for the deployment, and then check if the issue is solved? Thanks, Jin ChenJin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 8:34am

Hi Lumbago, It seems that the problem has nothing to do with the vm. We have the same problem using physical servers. Queries end in 1-2 seconds, but the report takes form 30-90 seconds to render. We've tried installing the Sharepoint 2010 on a different server, installing SSRS on a third server, but still the same problem appears. If we use the Native mode and Report manager application reports are rendered in 2-5 seconds. Have you found a solution in the meanwhile? Thanks, Ivan
November 11th, 2010 11:26am

The solution we are using is to basically use Native mode and use the native mode web parts in Sharepoint. This means that there is basically no real integration (no common secutrity model, etc) but other than that we are not seeing any problems at all actually. There have been some minor issues where we had created pages in Sharepoint with parameters in sharepoint that were sent to the reports, but we simply changed them so that the parameter "windows" in SSRS is being displayed instead. To switch from Integrated mode to native on an existing installation we basically uninstalled the sharepoing add-in and then I ran the following commands in a cmd.prompt to enable the native mode web part: cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN STSADM.EXE -o addwppack -filename "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall You might have to change the paths to stsadm and RSWebParts.cab but this is where I found them on my Win 2008 x64 with Sharepoint 2010. I you decide to go down the native road, please let me know...I'm very interested to hear about it :) Lumbago www.thefirstsql.com
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 2:47am

Thank you, I will try it by the end of this week and let you know. -- Ivan
November 15th, 2010 7:09am

Lumbago - I saw your reply at http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c140d26e-1c02-4f8d-bc61-f6ac75706556/#6a264b15-f40b-4f88-9c3b-16a87b436c34 and you appear to have the same problem as us. At least native mode works well. It seems Microsoft don't have an anwer to this problem?
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2010 6:39am

It seems Microsoft don't have an anwer to this problem? You can file a bug report at https://connect.microsoft.com/SQLServer?wa=wsignin1.0 If you do, can you post the link here? Thanks.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
November 19th, 2010 7:40am

Experiencing the same issue. Bug report filed, link is as follows: https://connect.microsoft.com/SQLServer/feedback/details/635080/ssrs-2008-r2-and-sharepoint-2010-performance-issues
Free Windows Admin Tool Kit Click here and download it now
January 13th, 2011 10:29am

I've had some free time on my hands recently and I decided to put it to some use. So I created 3 virtual machines on my laptop using Oracle VirtualBox where one machine was a domain controller and the two others had SSRS 2008 R2 and Sharepoint 2010 installed, one of them in Sharepoint Integrated Mode and one in Native Mode. All servers were Win2008R2x64 with 2048MB RAM. Other than the integrated mode vs native mode, the two machines were 100% identical. All services were running udner equal accounts, all features installed were identical. I then created a *very* simple report using the Adventureworks DW 2008 R2 database with one single parameter, and ran it on both servers and then timed the result. And even though my timings are highly unofficial I believe the result speaks for itself. Sharepoint integrated mode was consistently 2-3 times slower than Native mode for this rediculously slimple report...I might try some more reports later. I actually made a youtube video of it as well so you can see for yourself. I must apologize for the poor production quality though...this is the first video I've created and put online ever. -> http://www.youtube.com/watch?v=ChZs3tNYdgkLumbago www.thefirstsql.com
January 24th, 2011 9:46am

Sorry for the delay in the response and I apologize for the issue you are running into. SSRS 2008 R2 (or earlier) SharePoint mode report rendering is slower than Native mode, because in essence it is Native mode functionality with the additional overhead of SharePoint object model calls / synchronizations. Additionally, I believe you are on the right track for small reports with rendering times <500ms or <1s wrt "Sharepoint integrated mode was consistently 2-3 times slower than Native mode". This can also be inferred / approximated from the "First Page Report Performance" graph in the SQLCAT performance whitepaper published @ http://sqlcat.com/technicalnotes/archive/2010/11/03/reporting-services-performance-in-sharepoint-integrated-mode-in-sql-server-2008-r2.aspx. Having said that, this difference to a large degree does not seem to increase with longer running reports, which can also be inferred from the "First Page Report Performance" graph. In any case, we are actively looking into addressing this performance slowness as we speak, there isn't an ETA at this point. Additionally, your filing the connect bug is appreciated and would recommend others that are running into this to add /update the "customer vote count" for this bug to ensure it reflects the customer demand / need for this to be addressed. Finally, if the patterns / conclusions I mention above do not match what you are seeing, it woud be most helpful if you could upload more details to the connect bug on at least profiler / netmon traces, your rdl and executionlog2 table rendering times for the report. Thank you. Your time and patience is appreciated. thanx, PrashThis posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
January 25th, 2011 3:43pm

Like everyone else on this thread, I am having the same performance issues. I saw some mention of hotfixes. Are there any hotfixes available and for which product? We are running in integrated mode on SharePoint 2007 WSS. I would prefer to not switch to native mode since we would lose the integrated security and we would need to change our installation routines. Also, upgrading to SP 2010 is not an option at this point. Thanks, Brian
February 16th, 2011 3:16pm

I am having the same problems right now, but we are already in Native mode. The queries perform fine, but the rendering time is terrible. Like 11 minutes for some reports.
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2011 3:32pm

We are in the midst of a POC to establish our BI stack - I was really hoping SSRS would be at the center of this since we've selected Sharepoint 2010 as our central portal. We are seeing the idential problems with integrated mode which I'm affraid will knock SSRS out of the picture unless we can find a work around. It's been about 6 months since your post - have you been able to make any progress on resolving this issue and/or have a suggested work around? Thansk in advance for your assistance. James.
July 1st, 2011 11:22am

Hi James, the resolution to the problems was to use Native Mode for the Sharepoint integration and if you can live with this solution it works great even though it's not a real "integration", but merly and iFrame or something in the Sharepoint pages. I haven't investigated the matter any further since I posted this but I don't think a whole lot has changed. I work as a consultant and haven't worked with sharepoint at all lately. Maybe some of the other contributors to this thread have different experiences. Lumbago www.thefirstsql.com
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2011 2:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics