locked
SSRS start make database slow RRS feed

  • Question

  • User-1636183269 posted

    Hello All,

    Our database contains around 34 GB of data, if I fire any query then it works fine but when we start SSRS then it CPU memory reaches around 30% in task manager but when I fire below query then CPU consumtion it shows 5%.

    SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
    AS [%signal (cpu) waits],
    CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
    AS [%resource waits]
    FROM sys.dm_os_wait_stats OPTION (RECOMPILE);

    Please guide me, what can be bottelneck? I have check all 490 waits. Everything is fine.

    Tuesday, July 26, 2011 4:30 AM

All replies

  • User-1636183269 posted

    Any suggestion, please.

    Friday, July 29, 2011 1:37 AM
  • User-2064262021 posted

    Hi,

    What processes are showing CPU usage in Task Manager at the time?

    Thanks,

    Cathy Miller

    Friday, July 29, 2011 11:34 AM
  • User-395610763 posted

    Hello,

    In addition to the above question could you answer the following for me -

    Which version of RS are you using?

    How many reports are running at that time?

    Where are the reports being run from?

    Do you see anything significant under event viewer?

    Do you find any error in RS logs everytime you stop RS?

    Do you find any error in RS logs everytime you stop RS?

     

    Thanks,

    Swetha

    Tuesday, August 2, 2011 3:42 PM
  • User-60558687 posted

    I guessing here:

    Look at these possible issues

    How many days you keep your report execution logs

    How many subscriptions you have

    Possibly you need to shrink the database logs (after backup) both the ReportServer and ReportServerTemp

    How much space is left on your host server's partitions that houses the SSRS.

    I suggest you go through the recommendations found here 

    http://technet.microsoft.com/en-us/library/cc966413.aspx download the the word document found in the bottom of the page 

    direct link: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc 

    Tuesday, August 2, 2011 5:33 PM
  • User-1636183269 posted

    Hello,

    Thanks Swetha.

    We are facing Perfomance issue on our Production Server. We are using SSRS(SqlServer 2008 R2) in our Silverlight Application(.Net 4.0).

    Our ReportServiceService.exe take more Memory Usage. We have to forcefully Stop Reporting Service every time to resolve this issue.

    The server is running on

    Microsoft Windows Server 2003 R2
    SP 2
    2.33 GHZ, 10.0 GB of RAM

    ReportingServicesService.exe is using 311,156 K memory.

    Thanks,

    Sandeep

    Wednesday, August 24, 2011 3:47 AM
  • User-395610763 posted

    Hey Sandeep,

    Just wondering if you had a chance to go through recommendations suggested in the links below:

    http://technet.microsoft.com/en-us/library/cc966413.aspx --download the the word document found in the bottom of the page 

    Direct link: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc 

     

    -Swetha

    Friday, September 9, 2011 11:06 AM
  • User-1200560727 posted

    Might be you get chance to look into this article: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx

    I hope this link will helpful to you.

    Tuesday, October 18, 2011 5:55 AM