Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
High CPU for ReportingService.exe in SSRS reaches 90%

Soru High CPU for ReportingService.exe in SSRS reaches 90%

  • Thursday, February 07, 2013 1:08 PM
     
     
    Sign In to Vote
    0

    We are using SSRS to generate PDF for one of the screen in our project. The application exports the data in the PDF format shown in the screen grid.

    The SSRS report is binded from a DB view which has 160 columns and PDF also generates one image. We have limited the data of view query to return max 2000 rows which are being displayed in PDF. Another feature which is being used while rendering the PDF is hiding of certain columns based on the user inputs. So this report has165 input parameters.

    This rendering is working absolutely fine with limited number of users. The main problem we are facing is when the number of hits for PDF rendering increase ,the CPU utilization of Reporting  Service reaches 80 to 90 %.

    The load details for this are as below
    1 -  The run executes 354 reports in 0.5 hr.  245 out of 354 are being served from the cache.
    2 -  While checking the execution ,  the data retrieval time is observed to be 272 milliseconds max  and rendering time is around 87 sec max.
    3 – The database and SSRS servers are on the same machine as per our current application architecture.

    We need your views/inputs to reduce the CPU utilization as well as rendering time of PDF

     

All Replies

  • Thursday, February 07, 2013 6:12 PM
     
     
    Sign In to Vote
    0

    The first thing I would check is to make sure that all of the cores are being utilized on your server.  It is possible that you have a multi-core server and have all of the processors using only one CPU.  That being said, you can split the processes to run under different CPUs.  For example, set all of your operating system processes to use one of the cores, ssrs to use another core, and the database engine to use the third and fourth cores.

    Regarding the views, which version of SQL Server is being used?  If you have Enterprise edition, then make sure you have indexes placed on the views.

    Regarding memory, it looks like you might not have enough memory on the machine.  Allocating more memory will allow more of the query data to be stored in the memory cache.  I would also review the Max Server Memory setting in SQL to see of the memory is being limited on the server.  For more information on setting the max server memory setting:

    http://msdn.microsoft.com/en-us/library/ms180797(v=SQL.105).aspx

    SSRS itself also has cache settings for caching the report.  Check these settings in the SSRS Report Manager portal.

    Finally, if all else fails, review the query the defines the view.  Review the query execution plan for less than optimal performance.

    Upgrading to SQL 2012 will give you the ability to add column-based indexes which will improve overall system performance tremendously.

     
  • Friday, February 08, 2013 5:20 AM
     
     
    Sign In to Vote
    0

    Hi John, Thanks for your reply...

    We are using SQL server 2008 R2 Enterprise.  Cache setting is already enabled. We run 354 reports in 30 Mins.  245 reports out of 354 are being served from the cache of SSRS. VIEW which is binded to the Report taking max 250 milli seconds. SSRS Server and SQL server Engine are on different machines and SQL server MDOP is set to 2.

    We are not facing any memory issue. We have 192 GB RAM on the DB server.