none
Projects publishing takes a lot of time RRS feed

  • Question

  • Hello! I've experienced a very annoying behavior with my Project Server. I have a lot of projects (about 500) and resources (about 300) in 1 PWA at my production environment. Publishing of projects and building resource assignments report takes a LOT of time. About 10-30 minutes or even doesn't complete.

    I've found a very useful article about similar symptoms: http://blogs.technet.com/b/projectserverpfeteamblog/archive/2014/06/30/longpublishtimesreportingpublish.aspx, but can't comment there, so i ask a question here:

    Is it true, that deleting old projects will no affect performance of publishing projects and building reports? How can i speed-up this process without touching disk subsystem? i've already done all possible tasks such as rebuilding index, adding tempdb files, tuning databases but i can not achieve  desired performance.

    • Edited by Lehus Monday, February 9, 2015 3:28 AM
    Monday, February 9, 2015 3:25 AM

All replies

  • Many things can cause a project schedule to take a long time for publishing

    1) having custom tasks fields with calculated values

    2) tasks with assignments with a great number of resources (7 or more resources)

    3) poorly defined dependency between tasks and summary tasks

    4) using FS dependency

    cheers


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Monday, February 9, 2015 3:44 AM
    Moderator
  • To add to Michael's excellent answer, most publishing performance issues are linked to SQL i/o - this should be the 1st place to look.  Recently (ie just last week) I migrated a production system where it took 12 mins to publish a project to the customer's test environment, where it took 1 minute to publish the same project. This week I hope to review the different SQL configurations on the two systems, and if I find anything conclusive, I'll let you know.


    Ben Howard [MVP] | web | blog | book | P2O

    Monday, February 9, 2015 8:20 AM
    Moderator
  • Hi,

    I agree with Ben, SQL i/o should be the first place to check. If available, increase the memory and CPU of the SQL Server.

    Also, if your SQL Server is used by other environments check their impact on the SQL servers - an option would be to move project server database(s) to its own dedicated SQL Server.

    Paul

    Monday, February 9, 2015 11:02 AM
  • Hi! Thank you, Michael, Ben and Paul for your replies! Probably i word my question a bit incorrect..

    Yes, i've experienced some SQL troubles, with the  help of great tool PAL (pal.codeplex.com) i discovered that some performance counters exceeds its thresholds, such as: SQLServer:Latches\Total Latch Wait Time, SQLServer:Access Methods\Workfiles Created, SQLServer:Locks\Lock Requests. And yes, i'm investigating reasons of that behavior (though i don't realize how to fix this except for moving DB's to faster disk). Also i trying to figure out projects details as Michael advised in first reply.

    But the main question i want to know, can i effectively speed-up publishing\reporting process by cleaning-up my environment? Just months ago, when only a little projects was started and not much resources were added to PWA, all processes like publishing performed almost instantly! And now, when i have an opportunity to remove some projects and projects' sites from PWA, can it be helpful for performance? Does anyone tried this?


    • Edited by Lehus Monday, February 9, 2015 3:47 PM
    Monday, February 9, 2015 3:45 PM
  • This week I hope to review the different SQL configurations on the two systems, and if I find anything conclusive, I'll let you know.


    Ben Howard [MVP] | web | blog | book | P2O

    And yes, Ben, please let me know if you find any glues in SQL-configurations! Though i've installed farm with all best-practices i found..but now it doesn't help )
    Monday, February 9, 2015 3:52 PM
  • Hi, Make sure you have SQL maintenance plans in place

    Ben Howard [MVP] | web | blog | book | P2O

    Monday, February 9, 2015 3:55 PM
    Moderator
  • My only maintenance plan consist of checking consistency and rebuilding index every 2 weeks with fillfactor of 70. Isn't pretty enough?
    • Edited by Lehus Tuesday, February 10, 2015 9:06 AM
    Tuesday, February 10, 2015 9:05 AM
  • Hi,

    Make sure the update statistics is included in your maintenance plan as well.

    Paul

    Tuesday, February 10, 2015 9:31 AM