Critical Issue on Project Server 2016: Reporting(Project Publish) performance is very slow and occasionally may error when publishing (On Premise Install) RRS feed

  • General discussion

  • I am posting some key information about performance issues with Project Server 2016 when publishing large projects. This is for on premise only, and after a successful data migration from Project 2013. 

    First some background. 

    We have a highly tuned and well maintained Project 2013 Farm with minimal issues when it comes to publishing. Our database sizes currently sits at around 140+GB for PWA(SharePoint) and our Project DB is 40+GB. The average project timeline is 10+ years with 2500+ tasks and 100+ resources. 

    The source farm is running on VMWare servers, Windows 2012 R2 and SQL Server 2012 R2 and disks running on SAN storage. The target / migrated Farm is running on VMWare servers, Windows 2012 R2 and SQL Server 2016 CU1 SP1 and disks running on SAN storage.

    The setup and migration of the 2013 to 2016 Farm worked well. The Migrate-SPProjectDatabase ran for 18 hours, and then a day or two to bring the system up. 

    The Issue
    As we started testing our project publishing in 2016 we found the Reporting(Project Publish) times increase by 50 to 60 percent, on some projects, and others up to 90 percent. Keep in mind this is an exact data copy from the 2013 servers. After we had done further testing and SQL diagnostics we found that the DBNAME.pjrep.MSP_Epm_InsertTaskByDay sql was running way longer than in 2013. We opened a ticket with Microsoft. After a lengthy investigation, Microsoft identified that for each SQL INSERT there where multiple table-valued functions being triggered. This was not the case in 2013, also not just on DBNAME.pjrep.MSP_Epm_InsertTaskByDay. We asked why this design change was made and the response was:

    "What’s happening in Project Server 2016 is a SQL stored procedure for handling Task by Day time phased data is taking a lot of time to process in the Reporting tables. In order to make ODATA performance better, in Project Server 2016 there’s a process that creates the task timephased by day data and it does this by recursively going to the assignments, getting their data and then writing it to the tasks and summary tasks and so forth. This can be a time consuming process behind the scenes. Either ODATA reporting performance pays the toll or the publishing process pays the toll for the extra activity"

    Obviously this brought our upgrade to a standstill. We have now been working on this issue with Microsoft for a few months. The good news is they have identified this as a bug. The latest we have back from Microsoft (as of this posting), is that the fixes/performance improvements will be at least 2-3 months, so June/July of 2017 is the best case scenario we have.

    In Summary, if you are going to upgrade from 2013 to 2016 on premise make sure you test all the projects and different scenarios carefully, and benchmark them against your 2013 performance times! I will update this post as I hear any new news.

    • Edited by Stefan.D.N Thursday, March 16, 2017 3:19 AM
    Thursday, March 16, 2017 3:17 AM

All replies

  • Thanks for posting and sharing.  That is good to know. If in the future you find more information about this, please post it here.

    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Blog contains my field notes and SQL queries

    Wednesday, March 29, 2017 11:19 PM
  • any fix or solution for this issue... ? I am also facing same problem.. .

    waiting to hear from your, 

    - Ramesh

    Wednesday, November 14, 2018 7:55 PM
  • What is the latest CU have you applied to SharePoint 2016?

    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website <br/> Blog contains my field notes and SQL queries

    Wednesday, November 14, 2018 11:18 PM