none
Inconsistent (Bad) Performance Publishing Projects and Generating SSRS Reports RRS feed

  • Question

  • We recently migrated from Project Server 2010 to Project Server 2013.

    We have two performance problems that plague us. They come and go, seemingly randomly. Efforts by inhouse SQLServer DBAs and system techs, Microsoft Premier and other MS partners to discover the bottlenecks have been futile.

    1) Project Publish gets stuck in Reporting (Project Publish) step - can take up to 15 minutes. At other times, the same project will publish all its steps, including that one, in a minute. It is not always the same project. It can be one with a 20 task schedule or one with a 300 task schedule.

    2) We have three SSRS reports that pull together data from SP List on Project Site (Risks, Issues), PDP data (out of box and custom fields), and the project schedule. These three reports sometimes (especially one of them) take up to 10 minutes to run. But usually, the same report for the same project will run in less than four (4) seconds.

    We've run traces and checked system logs for load, blocks, etc. The SQLServer databases have been reviewed and tuned for best practices. The web/app servers and the DB server (all VMs) barely blink with the workload from our users.

    There seems to be no consistent time of day when the problems occur.

    The problem will occur with only one or two users on the system.




    Friday, August 5, 2016 7:15 PM

All replies

  • Hello,

    If this was raised to Microsoft Premier support what was the outcome of the Reporting Publish job step?

    Regarding the SSRS report time, take the report out of the equation, how long does the SQL query take to execute? Is this via a SQL Stored Procedure rather than a SQL query directly in the SSRS Report? How are you getting the SharePoint list data? Or are you only getting the default Issues and Risks data from the Reporting schema in the Project Web App database?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, August 5, 2016 8:31 PM
    Moderator
  • Hi, thanks. I'm an Project Server app side admin - I don't have these answers - will need to inquire to system/DB techs.

    We have Premier open for two months - no solution. Do you mean technically what is the outcome of the Report (Project Publish) itself, besides the job eventually finishing successfully (which it does)?

    SSRS/SQL Query - I will need to pass your questions along. We grab Issues, Risks (out of box lists) and Change Orders (our own custom list) from the SP Project Site, but I don't know from what store - directly or reporting schema.

    Opportunity for answers from others has passed today (Friday afternoon ;-) Will aim for answers on Monday.


    Friday, August 5, 2016 8:58 PM
  • Hello,

    Sorry, I meant what was the outcome of the support ticket raised to Microsoft Premier support for the queue job processing time? Is the case still open with them?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, August 5, 2016 9:05 PM
    Moderator
  • Hi Paul,

    Yes, it is still open. For the Reporting (Project Publish) we have sent them a couple [huge] trace log files (from a special monitor they provided called PSSDiag) that include the guilty job(s).

    We also checked/tuned a variety of things on the Project Server web/app servers and the SQLServer DB per their best practice suggestions, but nothing really genuinely diagnostic specific to a root cause.


    what is this

    Friday, August 5, 2016 9:15 PM
  • Hello,

    I'm sure they will get to the bottom of the Reporting Project Publish job soon with the details you have sent over.

    Let use know more about the SSRS reports and we will see what / if there is anything we can suggest remotely via this forum without access.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, August 5, 2016 9:17 PM
    Moderator
  • I wonder if database statistics maintenance is needed. Project database statistics can get out of date quickly in dynamic environments. If the database is configured to auto-update statistics but not configured to update them asynchronously, you can get pauses measured in minutes while the database updates the stats, then runs the query. Other times, the query runs quickly. Ask your DBA as to the Auto Update Statistics and Auto Update Statistics Asynchronously settings set to on the database properties page for the Project database.

    Treb Gatte, Project MVP | Blog | Twitter | Academy

    Wednesday, August 10, 2016 3:51 AM
    Moderator
  • Wednesday, August 10, 2016 9:00 AM
    Moderator