none
Publication of the big-sized Enterprise projects from Project Professional 2013 to Project Server 2013 takes about 60+ minutes. RRS feed

  • Question

  • Dear Sirs,

    I need your support over the following MS EPM 2013 issue:

     

    Publication of the big-sized Enterprise projects from Project Professional 2013 to Project Server 2013 takes about 30+ minutes. We need to reduce this total publication time down to acceptable working values 10+- minutes.

    Environment information:

    Single App Server (Virtual): 16 Gb RAM, x64 4xCPU, HDD > 50 GB free disk space, OS Windows Server 2012 Standard Edition x64 Service Pack 1, MS SharePoint Server 2013 and MS Project Server 2013 with CU December 2013 (KB 2850024) applied.

    Single RDBMS MS SQL Server (Virtual): 8 Gb RAM, x64 4xCPU, HDD > 200 GB free space, OS Windows Server 2012 Standard Edition x64 Service Pack 1, MS SQL Server 2012 x64 SP 1 Enterprise Edition.

    We have 1Gbit LAN between APP, DB server and 1Gbit LAN between APP and Proj Prof Client.

     

    Yes, we are on the way of migrating to the Prod environment with 3-tiered architecture (with SP1 slipstream and CU December 2014 applied), but this issue also presents there.

    Project’s file information:

    Tasks in the file: [~4900], resources in the file [~396] enterprise task’s custom fields used in the file [~23].

    Project save procedure for this new project would last about 7 minutes. Project publication would last about 47 minutes. We noticed that tasks synchronization process took about 1 second for each ~2,5 tasks, to add them to the sharepoint tasks list. So for all 5148 tasks it took about 5148/3/60 =  34 minutes. Other 13 min was used for reporting database publication and other tasks relevant for new sharepoint site creation.

    Case 1: Issue description:

    During the Enterprise project’s file save and publication we have the following sharepoint 2013 log messages:

     

    07.31.2014 12:43:17.22 Microsoft.Office.Project.Server (0x0358) 0x3D5C SharePoint Foundation Monitoring b4ly High Leaving Monitored Scope (Persisting list changes). performing time =376.068676326181 22dca99c-4696-70f1-e9e2-06851d0bcffd

    07.31.2014 12:43:17.69 Microsoft.Office.Project.Server (0x0358) 0x3D5C SharePoint Foundation Monitoring b4ly High Leaving Monitored Scope (Persisting list changes). performing time =361.652807828928 22dca99c-4696-70f1-e9e2-06851d0bcffd

     

    It shows that sharepoint spend at least ~350 milliseconds (or 0,35 sec*4900 tasks = 1715 sec, or 28,5 min) for each task update during project publication. And we also have another log file that shows that about 0,7 sec (or 0,7 sec*4900 tasks = 3430 sec, or 57 min) sharepoint spend for save each task in project file to project server. So total save and publication time more then 60+ minutes for that project file. The same result we have even if user didn’t do any changes at the project file.

     

    We use only enterprise projects (dbo.MSP_EpmProject_UserView.projectvisibilitymode = «False»), and do not use sharepoint tasks lists, but the synchronization between MSP Plan and SharePoint tasks list works at any case.

    Case 2: Issue description:

    - For the second test we created a new project with new sharepoint project’s site on basis of our «issue» project, with total amount of tasks in it of 5148 (yes, we increased the tasks list default limit at the sharepoint site up to 6000 items in it – standard limits for sharepoint view list – 5000 items).

    - Project save procedure for this new project would last about 7 minutes. Project publication would last about 47 minutes. We noticed that tasks synchronization process took about 1 second for each ~2,5 tasks, to add them to the sharepoint tasks list. So for all 5148 tasks it took about 5148/3/60 =  34 minutes. Other 13 min was used for reporting database publication and other tasks relevant for new sharepoint site creation.

    -Then we deleted the tasks list for that new test project from the sharepoint site and republish the project plan one more time. This time project save procedure took about 7 minutes, project publication about 2 minutes and 3 minutes for other relevant queue jobs. So total time is 12 minutes.

     

    As a conclusion: yes, we have determined the exact problem - during synchronization process (from Project Server to SharePoint) it perform copying all tasks and related data from Project to SharePoint in spite of fact that you changed only ONE task or ALL of them. At any case, synchronization will copy ALL of them from Project Server to SharePOint task’s list.

    Our workaround is to disable the task’s synchronization for such big-sized project plans:

    – to delete the SharePoint «tasks» list at the SharePoint site tied with project plan.

    - or deattach the SharePoint site from the project plan.

     

    Thank you for reading this topic, please if you also forced with such issue provide us any known workaround or maybe any official response \ feedback from MS about it.

     

    Thank you in advance,

    Best Regards, Andrey

     


    Wednesday, February 18, 2015 1:57 PM

Answers

  • Hi, collegue.

    It problem resolved in KB 3054804.

    Best Regards, Andrey

    • Marked as answer by Agalkin Thursday, August 20, 2015 8:27 AM
    Thursday, August 20, 2015 8:27 AM

All replies

  • Hi Andrey,

    The first thing I would do is to check the load on the SQL Server. For me that server looks as the initial bottleneck in your architecture - if possible add more memory to that server (I would even go to 24/32 Gb) as project server does lots of SQL transactions. Also, make sure the SQL Server disks/luns etc. can handle the extra load

    Then I would suggest looking into scaling properly the environment based on the #projects, # of average tasks per project, # of concurrent users etc.

    Paul

    Wednesday, February 18, 2015 2:32 PM
  • Thank you for your answer, Paul.

    I did the same tests in another enviroment as I mantioned at my topic:

    WFE:

    OS:MS Windows 2012  Server Standard edition x64 R2,

    CPU: 4 core, 64 bit, 2,5 GHz/core

    RAM: 16 GB

    HDD: 80 GB

    Soft: SharePoint 2013 SP1 (slipstream and CU December 2014) Project Server 2013 SP1 (slipstream and CU December 2014)

    APP:

    OS:MS Windows 2012  Server Standard edition x64 R2,

    CPU: 4 core, 64 bit, 2,5 GHz /core

    RAM: 16 GB

    HDD: 80 GB

    Soft: SharePoint 2013 SP1 (slipstream and CU December 2014) Project Server 2013 SP1 (slipstream and CU December 2014)

    SQL:

    OS:MS Windows 2012  Server Standard edition x64 R2,

    CPU: 4 core, 64 bit, 2,5 GHz /core

    RAM: 64 GB

    HDD: 300 GB

     The problem is still there.

    By my opinion that is not an issue of RDBMS (SQL), as I mantioned "during synchronization process (from Project Server to SharePoint) it perform copying all tasks and related data from Project to SharePoint in spite of fact that you changed only ONE task or ALL of them every time. At any case, synchronization will copy ALL of them from Project Server to SharePOint task’s list."

    Maybe anybody of you also works with big-sized projects (5000+ tasks) and have / do not have same issue?

    Best Regards, Andrey

    Wednesday, February 18, 2015 2:49 PM
  • Andrey, 

    While not as drastic as your delays, I do experience long publish times from large projects.

    E.g., 2,000~ lines, 100~ resources, 100~ custom fields

    Publish on the above in my current environment typically takes around 15 minutes. The environment is built according to Microsoft recommendations for 'Large Dataset' in the following article: 

    https://technet.microsoft.com/en-us/library/ee683978.aspx

    So you are not alone, as I'm interested as well on how to speed this up as it is the largest complaint I get from users.

    -Robert

    Wednesday, February 18, 2015 3:47 PM
  • Agalkin,

    Couple of things:

    a) Obvioulsy, the task synchronization takes time, and I do not know if there is a way to decrease that time. An alternative is to disable task list sync for this one project and see if it helps your cause. Here is how to do it for just one project. 

    b) You can also look into the "User Sync", which also adds quite a bit of time to the process. May be you could disable the project site sync for permissions and observe the publosh times.

    c) Take a look at the database maintenance plans. What applies for 2010 also applies for 2013.

    Hope these things will give you some improvement.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, February 18, 2015 4:20 PM
    Moderator
  • Robert, thank you for joining this conversation.

    Probably, if you enlarge your plan up to 5000+ it will also increase synchronization time up to 30-35 minutes.

    Because the time that SharePoint takes to transfer one item from Project Server to Share Point list is fixed (in my Case 2 it’s ~ 1 second for each ~2,5 tasks) over the environment, and I’m completely agree with you that as more powerful environment you have then less time it takes for this synchronization but even at your large environment it will be more than user’s acceptable values.

    I’m not alone, this is what I wanted to hear as part of my question :)!

    Dear Prasanna, thank for writing!

    1. This commandlet is very useful indeed. In our workaround we just deleted the task’s list from corresponded sharepoint site at all, but now will test and use this commandlet.

    1. “User sync” is what we continue to use, so we can’t refuse of it.

    1. Concerning the SQL maintenance plan, as part of it we use procedure cache cleaning scripts like this (each 24 hours), also use reindexing and other staff:

    DECLARE @intDBID INT;

    SET @intDBID = (SELECT [dbid]

                    FROM master.dbo.sysdatabases

                    WHERE name = 'ProjectWebApp');

    DBCC FLUSHPROCINDB (@intDBID);

    With cleaned procedure cache publication time reduced but not dramatically. And in some case the time could be reduced up to 30% and at other tests it could show only 10% reducing of sync time... so I can’t say that SQL maintenance provide us much help with this issue but you are right we got some improvements here as well.

    Thursday, February 19, 2015 8:36 AM
  • Regarding my topic, I also said that every time when sync works it updates All items from project’s plan at Project Server to corresponded task’s list at SharePOint server. Inspire of the fact that you changed only One task or group / all of them at your project’s plan.

    And it seems to me and my colleagues that it’s probably (maybe) a “bug” at the product. Here is what we have if looked a little bit closer to the code:

     

    Share Point determines what task to sync from Project’s plan to sharepoint list. To do that sharepoint needs to know was that task changed or not, based on the following fileds (check SQL stored procedure “[MSP_READ_TASKS_FOR_SYNCRONIZATION]”):

    TASK_UID    TASK_NAME    TASK_START_DATE    TASK_FINISH_DATE    TASK_PCT_COMP    TASK_PARENT_UID    TASK_OUTLINE_NUM    WSS_LISTITEM_UID   TASK_ID    TASK_IS_ACTIVE

    We noticed that at any case synchronization performs for all tasks, EXCEPT the ROOT one. Then we looked at the comparison of TASK_PARENT_UID field. So sharepoint compares TASK_PARENT_UID with ParentID (this is internal name for lookup field “Tasks” at the Sharepoint, and it stores their values at the format "ID;#Title").

    And comparison performs like following:

    1. SharePoint looks for Task at the Tasks’s list corresponded to Project’s plan with ID represented at the TASK_PARENT_UID field. Then it takes SharePoint ListItem ID (“int” type) and store it to the “num” parameter;

    num = this.GetCachedListItemByUniqueId(listItem.ParentList, nullable.Value).ID;

       2.Then it compares “num” with task’s “ParentID” at SharePOint as follow with operator “!=”:

    ((SPItem) listItem)["ParentID"] != (System.ValueType) num

       3. If comparison was success (true) – then it tell us that values (at the Project’s plan for tasks) was changed, then it need to be synchronized. Corresponded Method setup “true” flag, and then returns it.

     

    The “bug” is that this expression at the Step 2 will always return “true”, because in fact it compares “string” (see above – that this is lookup field at SharePoint side) with “number”. For example if the parant task ID is “55”, then we get:

    "55;#Task 1" != 55

    And by the rules of .Net the “string” will never equal “number”

    Furthermore this is approved by the SharePoint logs:

    In that case we always get the note “Setting ParentID to” at the logs (we see it if turns on Verbose for “Project Server” -> “Sharepoint Integration” category).

    So at any case of publishing project’s plan we always get that note at the logs for tasks that have Parent task, and we have Parent for all of them EXCEPT the ROOT one, exact logs represented further:

    10/15/2014 02:37:32.26    Microsoft.Office.Project.Server (0x07D8)    0x06E8    Project Server    Sharepoint Integration    ado0d    Verbose    Setting ParentID to 1    bf2fc29c-7727-b00d-fa4a-34f22ea9ec1d 10/15/2014 02:37:32.62    Microsoft.Office.Project.Server (0x07D8)    0x06E8    Project Server    Sharepoint Integration    ado0d    Verbose    Setting ParentID to 1    bf2fc29c-7727-b00d-fa4a-34f22ea9ec1d 10/15/2014 02:37:32.63    Microsoft.Office.Project.Server (0x07D8)    0x06E8    Project Server    Sharepoint Integration    ado0d    Verbose    Setting ParentID to 1    bf2fc29c-7727-b00d-fa4a-34f22ea9ec1d 10/15/2014 02:37:32.67    Microsoft.Office.Project.Server (0x07D8)    0x06E8    Project Server    Sharepoint Integration    ado0d    Verbose    Setting ParentID to 1    bf2fc29c-7727-b00d-fa4a-34f22ea9ec1d 10/15/2014 02:37:32.69    Microsoft.Office.Project.Server (0x07D8)    0x06E8    Project Server    Sharepoint Integration    ado0d    Verbose    Setting ParentID to 5    bf2fc29c-7727-b00d-fa4a-34f22ea9ec1d

     

    The following is the complete Method’s code from the corresponded reflector:

    private bool UpdateParentID(DataSet taskDS, DataRow row, SPListItem listItem, Dictionary<Guid, SPListItem> redoEntries)
        {
          bool flag = false;
          int index = taskDS.Tables[0].DefaultView.Find((object) DataRowExtensions.Field<Guid>(row, "TASK_PARENT_UID"));
          if (index >= 0)
          {
            Guid? nullable = DataRowExtensions.Field<Guid?>(taskDS.Tables[0].DefaultView[index].Row, "WSS_LISTITEM_UID");
            int num = -1;
            if (listItem.Fields.ContainsField("ParentID"))
            {
              if (nullable.HasValue)
              {
                try
                {
                  // STEP 1
                  num = this.GetCachedListItemByUniqueId(listItem.ParentList, nullable.Value).ID;
                }
                catch (ArgumentException ex)
                {
                  if (redoEntries != null)
                  {
                    if (!redoEntries.ContainsKey(DataRowExtensions.Field<Guid>(row, "TASK_UID")))
                      redoEntries.Add(DataRowExtensions.Field<Guid>(row, "TASK_UID"), listItem);
                  }
                }
              }
              //STEP 2
              if (num != -1 && ((SPItem) listItem)["ParentID"] != (System.ValueType) num)
              {
                ((SPItem) listItem)["ParentID"] = (object) num;
                ULS.SendTraceTag(845443U, (ULSCatBase) ULSCat.msoulscat_PS_ProjectSharepointIntegration, ULSTraceLevel.Verbose, "Setting ParentID to {0}", new object[1]
                {
                  ((SPItem) listItem)["ParentID"]
                });
                //STEP 3
                flag = true;
              }
            }
          }
          else if (((SPItem) listItem)["ParentID"] != null)
          {
            ((SPItem) listItem)["ParentID"] = (object) null;
            ULS.SendTraceTag(2495056U, (ULSCatBase) ULSCat.msoulscat_PS_ProjectSharepointIntegration, ULSTraceLevel.Verbose, "Resetting ParentID to null");
            flag = true;
          }
          return flag;
        }
    


    Any thoughts about it would be much appreciated!

    Thursday, February 19, 2015 9:50 AM
  • Agalkin,

    Glad some of my suggestions seem helpful.

    I also commend the amount of research you have put into this, instead of just saying we have performance issues.

    Having said that, I think only Microsoft can confirm whether it is a bug or not. I think it is time for you to open a support case with Microsoft and present them with the details you have found.

    Let us know how you eventually get this resolved.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Thursday, February 19, 2015 2:50 PM
    Moderator
  • Diar sirs.

    There an official link about walkaround this issue: http://support.microsoft.com/kb/2954895/en-us/

    Best Regards, Andrey

    Tuesday, March 3, 2015 7:47 AM
  • Андрей, какой размер вашего проекта, если сохранить его локально?

    Microsoft отвечали мне по схожей проблеме, что при размерах проекта более 200 Мб начинаются проблемы и работоспособность не гарантирована.

    Andrew, what size your project, if you save it locally?

    Microsoft responded to me on a similar problem that if size of the project will more than 200 MB so problems begin and availability is not guaranteed.

    Tuesday, March 3, 2015 3:32 PM
  • Hi.

    I don't now size. But tasks more then 6 000.

    Thank's for infirmation.

    Best Regards, Andrey

    Tuesday, March 10, 2015 11:08 AM
  • Two things:

    1. IMO, your RAM is less than half what it should be in those systems.
    2. More importantly: ~23 Custom Task Fields

    How many of those task fields are calculated? I bet calculated fields are the main culprit.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG

    Thursday, March 12, 2015 12:30 AM
    Moderator
  • Hi, collegue.

    It problem resolved in KB 3054804.

    Best Regards, Andrey

    • Marked as answer by Agalkin Thursday, August 20, 2015 8:27 AM
    Thursday, August 20, 2015 8:27 AM