none
Takes time to Save and publish RRS feed

  • Question

  • Project server 2010 + SQL server 2005 sp3

    When the users make the required changes and when they try to save and publish it takes them around 15 seconds to complete.

    What would cause that to happen, and how can one improve the performance?

    Satyam.

    Thursday, January 29, 2015 9:29 PM

All replies

  • Hello, so all jobs relating to the save and publish actions start and complete within 15 seconds in the queue? What times are you expecting? Typically if things are taking a while to process, (15 seconds isn't too bad depending on the size of the project plan for save and publish) you would probably look at the SQL server. What specification is the SQL server? Is it virtual? Is it shared with other apps? what storage are the databases on? What patch level is Project Server 2010?

    Paul


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

    Thursday, January 29, 2015 11:28 PM
    Moderator
  • Hello Satyam,

    As Paul mentioned 15 seconds for save & publish is a good one. It also depends on the size of the project plan, Duration of the project and number of resources.

    I am providing you the database maintenance plans for project server 2010 so that it would improve the performance of project server.

    https://technet.microsoft.com/en-us/library/cc973097%28v=office.14%29.aspx.

    Thanks,

    Phani

    Friday, January 30, 2015 11:27 AM
  • Friday, January 30, 2015 12:14 PM
    Moderator
  • Paul,

    Thanks for the reply. Information is as below SQL server is Physical, cluster/dedicated, high performance storage disks and the patch level is 14.0.7108.5000 (October CU 13). Would patching the server make any difference which would fasten the process?

    Farm has one application and 1 WFE with around 50-70 users.

    Sir Paul, the user is expecting the process to be very fast. As you suggested I already informed that it would depend on amount of content and the resources, but the explanation was not satisfactory for the user.  That's why i was asking regarding the default behavior to save and publish, now as you have confirmed will re-communicate the same to the user.

    As suggested I will be working with the SQL team to check and see the options related to SQL optimizations Thanks again to Paul and Phani

    Satyam

    Friday, January 30, 2015 5:48 PM
  • Let's try to look at it in a different way! When you save and publish a project there will be a series of job which will be triggered in the queue like Project Save from Project Professional, Project Publish, Reporting Project Publish etc.

    Please let us know which among this is taking time.

    [Please note some of the jobs gets pushed into the queue at the same time so I would suggest that you monitor the queue as soon as the save and publish is triggered and track the time taken for the jobs to complete]

    Below I have also listed some of the recommended DB maintenance queries

    1. Check size of Shadow Tables : http://support.microsoft.com/kb/2598007
      i. Run the below query on both the Draft and Published databases

    select count(*) from MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW 
    select count(*) from MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW 
    select count(*) from MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW
    select count(*) from MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 
    select count(*) from MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 

      ii. Run the below query on Published database only

    select count(*) from MSP_RES_CUSTOM_FIELD_VALUES_SHADOW

    2. Update Statistics: Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates (References: http://technet.microsoft.com/en-us/library/ms187348.aspx, http://blogs.technet.com/b/projectadministration/archive/2009/12/04/sql-server-settings-for-performance-recommendations.aspx) 

    Run the below query on all the four Project databases

    sp_updatestats

    3. Defragment all the four Project Server databases by running the below query on each of the databases to help improve the performance(This mentions Project 2007 bus is applicable for Project 2010 as well): http://support.microsoft.com/kb/943345

    SET NOCOUNT ON
        DECLARE @objectid int
        DECLARE @indexid int
        DECLARE @command varchar(8000)
        DECLARE @baseCommand varchar(8000)
        DECLARE @schemaname sysname
        DECLARE @objectname sysname
        DECLARE @indexname sysname
        DECLARE @currentDdbId int
        SELECT @currentDdbId = DB_ID()

        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting'

        -- Loop over each of the indices
        DECLARE indexesToDefrag CURSOR FOR 
        SELECT 
            i.object_id, 
            i.index_id, 
            i.name
        FROM 
            sys.indexes AS i
        INNER JOIN 
            sys.objects AS o
        ON
            i.object_id = o.object_id
        WHERE 
            i.index_id > 0 AND
            o.type = 'U'

        OPEN indexesToDefrag
        -- Loop through the partitions.
        FETCH NEXT
        FROM
            indexesToDefrag
        INTO 
            @objectid, 
            @indexid,
            @indexname
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Lookup the name of the index
            SELECT 
                @schemaname = s.name
            FROM 
                sys.objects AS o
            JOIN 
                sys.schemas AS s
            ON
                s.schema_id = o.schema_id
            WHERE
                o.object_id = @objectid

            PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + @indexname + ' is now being rebuilt.'

            -- Fragmentation is bad enough that it will be more efficient to rebuild the index
            SELECT @baseCommand = 
                ' ALTER INDEX ' + 
                    @indexname +
                ' ON ' + 
                    @schemaname + '.' + object_name(@objectid) + 
                ' REBUILD WITH (FILLFACTOR = 80, ONLINE = '

            -- Use dynamic sql so this compiles in SQL 2000
            SELECT @command =
                ' BEGIN TRY ' + 
                   @baseCommand + 'ON) ' +
                ' END TRY ' +
                ' BEGIN CATCH ' +
                   -- Indices with image-like columns can't be rebuild online, so go offline
                   @baseCommand + 'OFF) ' +
                ' END CATCH '

            PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding'
            EXEC (@command)
            PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done'

            FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname
        END
        CLOSE indexesToDefrag
        DEALLOCATE indexesToDefrag

    4. Recompile Stored Procedures: Empty the stored execution plans for the below stored procedures from the cache and then recompile them so that the new execution plan get things working faster (Reference# http://blogs.msdn.com/b/brismith/archive/2012/09/19/when-your-project-server-queue-slows-down.aspx)

    Draft DB 
    EXEC Sp_recompile MSP_ProjQ_Lock_Next_Available_Group 
    EXEC Sp_recompile MSP_ProjQ_Get_Status_Of_Jobs_List 

    Published DB 
    EXEC Sp_recompile MSP_TimesheetQ_Get_Status_Of_Jobs_List 
    EXEC Sp_recompile MSP_TimesheetQ_Lock_Next_Available_Group


    Cheers! Happy troubleshooting !!! Dinesh S. Rai - MSFT Enterprise Project Management Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you. This can be beneficial to other community members reading the thread.

    Wednesday, February 4, 2015 2:15 AM