Hi, I am facing the performance problem with SSRS 2008. starting from one day before, the report rendering is very slow. try to restart the report server. still same problem. even create empty report and render it in server that is very slow as well... Server - Window 2k8 R2 SQL server - MS SQL 2008 R2 and SSRS 2008 Could anyone give me advise that how to troubleshoot the issue ? Appreciate any helpkmhsad

Hi kmhsad, 1.Go to ReportServerDb and ExecutionLogStorage Table . 2.find the Values for DataRetrivel,Processing and Rendering. 1.If its with DataRetrivel then..try to address this bu optimizing your Queries in the Datasets.Use Profiler to help you out and also you can also try to cache the Reports.http://msdn.microsoft.com/en-us/library/dd353300(v=sql.90).aspx#Data 2.If it is with Processing <http://msdn.microsoft.com/en-us/library/dd353300(v=sql.90).aspx#Process> 3.If its with Rendering <http://msdn.microsoft.com/en-us/library/dd353300(v=sql.90).aspx#Render> Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/

Hi Rakesh, Thank you for your replied. Actually, everything is ok before yesterday. but since yesterday, all the report rendering is very slow. I tested the same in Testing envirnment that is very fast. And I create Empty Report. In this report, no control or query. I upload it to report server and render it. It is still very slow (30-40 second) to render the empty report. I think something wrong with the report server.kmhsad

What if you restart SSRS?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Uri Dimant, I restart the SSRS as well as Server. but nothing different. When the report is rendering, CPU and memory usage is less than 10%. Thanks. kmhsad

Can you check these Values in ExecutionLogStorage today and post your Results here ? We can analyse and Give you any other alternative.Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/

Hi Rakesh, Here is the execution result for Empty Report Name called "test". In the meantime, CPU and memery usage is less than 10 %. I tried from client pc as well as from server. ReportPath ReportAction TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------------------- ----------------------- ----------------- -------------- ------------- -------- ---------------------------------------- -------------------- -------------------- test Render 2011-06-15 16:38:16.503 2011-06-15 16:38:36.970 0 3 2 Live rsSuccess 568 0 test Render 2011-06-15 16:37:41.277 2011-06-15 16:37:51.900 0 2 2 Live rsSuccess 568 0 test Render 2011-06-15 16:37:35.440 2011-06-15 16:37:50.823 0 5 2 Live rsSuccess 568 0 test Render 2011-06-15 16:37:10.310 2011-06-15 16:37:20.980 0 12 2 Live rsSuccess 568 0 Thanks. Really appreciated your advise.kmhsad

Its Strange that DataRetrivel,DataProcessing,Rendering <> StartTime-EndTime Anyways, This Kind of Report Degradation will also happen due to ReportServerTempDb Space issue, Try to clear this Db or allocate more space and Check it again. -- Cleanup SessionData Table DELETE FROM [ReportServerTempDB].[dbo].[SessionData] WHERE datediff(day, CreationTime, getdate() ) > 5 GO -- Cleanup SnapshotData Table DELETE FROM [ReportServerTempDB].[dbo].[SnapshotData] WHERE datediff(day, CreatedDate, getdate() ) > 5 GO Let us know if it could resolve your Issue. Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/

Hi Rakesh, Thank you for your replied. I run your script and check the disk space ( 11GB available ). But result is still same.kmhsad

Hi Rakesh, I follow this post http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/4d5b1631-2c0b-4e53-a064-2b183d7a3f98 to remove the anti-virus software. Still no luck. The following are the result of same report run in different server. Could you please give me some adivse on that Server 1's timeprocessing and time rendering are higher than Server2's. Two days before, Server 1 is still very fast. Really appreciate your suggestion on that. Server 1 ( Report Rendering Slow ) Window 2k8 r2 32 GB memory Intel Xeon E5410 Server 2 Window 2k8 r2 8 GB memory Intel Xeon E5410 Server TimeStart TimeEnd TimeDataRetrival TimeProcessing TimeRendering Server1 2011 -06-16 11:55:57.060 2011-06-16 11:56:23.797 941 1051 3621 Server2 2011 -06-16 11:50:39.493 2011-06-16 11:50:41.583 1254 133 611 kmhsad

Are you using the same Browser in both the Machines ? ie 6 or ie 7 ?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/

Hi kmhsad, Based on your scenario, I would give you steps to troubleshoot this performance issue: Reporting services uses two application databases, reportserver and reportservertempDB. You can use build-in reports of Disk Usage By Top Tables in SSMS to track the size of the reporting services application databases and take action to reduce the database size or add disk space as needed. Besides, you can also use the Trace Log, when you need to troubleshoot report server operations, you can review Trace Log generated by Reporting services. Trace logs include records application log events, errors and exceptions, low resource warnings, report delivery details, and other details about reporting services operations. You can use any text editor to view the file. This file is located at \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles. For more information about Trace log please see http://msdn.microsoft.com/en-us/library/ms156500(v=SQL.105).aspx In addition, you can make use of Performance counter to monitor report server, this counter can help you understand how reporting services is using system resources during on-demand or scheduled report execution. Reporting services includes the following performance objects: · MSRS 2008 Web Service, which is to monitor report server performance. This performance object includes a collection of counters used to track report server processing typically initiated through interactive report viewing operations. These counters are reset whenever ASP.NET stops the Report Server Web service. · MSRS 2008 Windows Service, which is to monitor scheduled operations and report delivery. This performance object includes a collection of counters used to track report processing that is initiated through scheduled operations. Scheduled operations include subscription and delivery, report execution snapshots, and report history. · ReportServer:Service to monitor HTTP-related events and memory management. These counters are specific to Reporting Services, and they track HTTP-related events for the report server, such as requests, connections, and logon attempts. For more information about Windows Performance Counters, please see: http://msdn.microsoft.com/en-us/library/ms159809(v=SQL.105).aspx Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi Rakesh, I am using IE 8 to render the report. Thanks.kmhsad

Hi , Can you check with other Browsers and compare results ?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/

Hi Kmhsad, What's your result following the top replies, what's the information on report of Disk Usage By Top Tables in SSMS ? Is there some unusual log events, errors and exceptions, low resource warnings in Trace Log? Please remember to mark the replies as answers if they help and unmark them if they provide no help.