Scenario We have an existing Live SSRS server and a new SSRS server. The new server is superior to the existing server with regards to CPUs and memory. The target databases are similar in data but not identical (as the live database is constantly moving and we have not been able to set up a full copy of the Live system). There is slightly less data in the new databases. We are testing the performance of one of our more complicated reports on the new SSRS server. We are doing this both by running the report in a browser on the SSRS server itself and within our application on a client machine. Data retrieval is on average twice as fast. Processing time is on average ten times slower. Rendering time is on average four times slower. We would be celebrating right now the processing and rendering times showed the same improvement as the data retrieval time. However, instead we are completely at a loss as to why processing and rendering are taking so long. Existing Environment 2 x Intel Xeon E5420 (4 cores) 2.49 GHz 8GB RAM Windows Server Standard SP1 SSRS 2008 Standard Edition SP2 New Environment 1 x AMD Opteron 6172 2.10 GHz (12 cores) 8GB RAM Windows Server 2008 R2 Standard SP1 SSRS 2008 R2 SP2 Testing Stratgey Run the report with worst case scenario parameters. Collect the averages from ExecutionLog3. The results are as described above. Diagnostics We have checked: task managerresource monitora wide variety of performance countersteh SSRS configuration settings regarding memorythe additional data field in ExecutionLog3 Nothing is jumping out as an issue. The new server does not appear to be under any pressure whatsoever. We have also rebuild the report database. We are at a complete loss. Can anyone advise?

Can you give an indication as to what the reports design is like? Are they large extract like reports where you are rendering hundreds of pages... or are they smaller. Is the performance degradation worse on certain types of reports and better on others?Josh Ash

Hi Josh, Thankyou very much for your reply. We are currently only testing the one report. The design of this report is sub-optimal. It implements filtering, ordering and aggregation within multiple tablix elements. Our intention is to re-design the report in our next iteration. However, we have to get the new server up and running first. Currently, our UAT team are rejecting the new server based on the performance degradation. We could re-design the report now without understanding the difference in performance. However, since we do not experience the issue in our existing system, we are concerned that we have performed some sort of server configuration error (perhaps disk, memory, CPU, Windows or SSRS itself). We need to understand why we have a difference in performance before we re-design the report to ensure any re-design does not simply mask the fact that we screwed up the server setup. With regards to the performance degradation: It appears to increase in a non-linear relation to the amount of data. For example, running the report with the best case parameters results in a TimeProcessing value of double that seen in the old system. Running it with the worst case parameters (which involves less than five times the amount of data) results in a TimeProcessing value that is ten times greater. I can test some of our other reports but my suspiscion is that the issue will not manifest to the same level because the other reports do not contain large amounts of tablix processing. It may turn out that the answer is simply that R2 processes badly deisgned reports slower than previous versions. That is an acceptable conclusion as long as I can be sure that is the issue. What I can't do is ignore the difference...

It is half one a.m. where I am so if anyone else replies and I don't it will be because I've gone home. I include below the current averages. Existing System Avg Data Retrieval, Avg Time Processing, Avg Time Rendering, Avg Row Count, Avg Byte Count 48177, 3430, 2056, 53664, 102989, 760556, New System Avg Data Retrieval, Avg Time Processing, Avg Time Rendering, Avg Row Count, Avg Byte Count 18272, 26529, 6048, 50851, 103962, 720547, The data retrieval is considerably better but the overall effect is spoilt by the processing and rendering time. Please note that the data retrieval average from the exiting system has become slightly skewed due to other server activity. It is usually aroung 30 seconds meaning that the existing system is faster than the new system. I have been continuing my investigation using Process Monitor and can now identify several pairs of ReportingServicesService.exe events that are separated by relatively large gaps. However, I don't currently have much idea of what the gaps mean. I can add more details if anyone believes they might be able to interpret them.

How many cores do you have in the new server - processing time is generally core dependent for reports that are small... 26 seconds seems ridiculous. Though I'm afraid without seeing your infrastructure, installation files, and RDL files it may be hard to go any further. If you can afford to, you can engage Premier Support to go through this with you.Josh Ash

The new environment has an AMD Opteron with 12 cores. OK,our plan is to open a support ticket later today if I can't get any further with this. I will leave the thread open and update as we go though the process.

OK, so your server is obviously adequate.. Opening a support ticket seems to definitely be the best option.. It can take a while and if you could share whatever you do find on this forum it would be much appreciated Good luck with it Josh Josh Ash

Hi J Milne, Based on the current information, the issue seems to be the scenario "Performance decreases after you move a report that contains a large multi-select drop-down parameter list to SQL Server 2008 R2 Reporting Services" which is described in the following KB article: http://support.microsoft.com/kb/2522708 To check whether it is the case, let's check the report performance on the new server using URL access and hide the toolbar through parameter. The URL may be like: http://<Server Name>/reportserver?/Reports/Report1&rs:Command=Render&rc:Toolbar=false If the report performance improve significantly after hide the Report View toolbar, I suggest that you install the Cumulative Update package 1 for SQL Server 2008 R2 Service Pack 1 although the SQL Server 2008 R2 Service Pack 2 has been installed. Hope this helps. Regards,Mike Yin TechNet Community Support

http://support.microsoft.com/kb/2522708 In this scenario, the report takes a long time to be displayed. Additionally, the CPU usage is high. However, the report-creation time is the same as the report-creation time for the same report before the report is migrated to SQL Server 2008 R2 Reporting Services. Note The report-creation time is logged in the runtime logged data for the report. That wouldn't seem to be the case here as the issue is not the length of time the report takes to display, and the report-creation time is not the same as before... Additionally, Service Pack 2 already contains this hotfix, so he already has it installed :- In addition to the fixes that are listed in this article, SQL Server 2008 R2 SP2 contains the hotfixes that were included in Cumulative Update 1 through Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1 (SP1). Josh Ash