Performance problems occur in Project Server 2010 when the number of shadow table records increases quickly

Article ID: 2598007 - View products that this article applies to.

SYMPTOMS

The number of shadow table records that are generated in an Enterprise Flag field increases quickly in Microsoft Project Server 2010. This issue causes performance problems.

Back to the top | Give Feedback

RESOLUTION

To resolve this issue, apply the following hotfix package:

2597138

(http://support.microsoft.com/kb/2597138/ )

Description of the Project Server 2010 hotfix package (Pjsrvwfe-x-none.msp): February 28, 2012

Back to the top | Give Feedback

This hotfix manages the number of shadow table records so that the number of records does not increase too much. Every time that you open or save a project plan in Project Professional after you apply the hotfix on the Project server, the shadow table records that are relevant to the project are cleaned up. If you want to clean up the shadow table records in bulk, you can use the following script. Before you run the script, make sure that you read the following points:

Make a backup of the Project server database before you run the script.
To avoid unexpected database content issues, stop the Queue Service on the Project application servers when there has been no activity on the servers for several hours.
Validate the script in a development environment by using the current backup of the Project server database.
Time how long it takes to complete the script in the development environment. The time that is required to complete the delete script depends on the SQL server and the number of the records that have to be removed. Make sure that the script is completed within the planned time.

To clean up the shadow table records in bulk, follow these steps:

Run the following script on both the Draft and Published databases, and reclaim the free space in the databases:

--Clean up       MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW 
      IF object_id('tempdb..#TempTaskCFShadow') IS NOT NULL 
      BEGIN  
      drop table #TempTaskCFShadow  
      END  
 
    CREATE table #TempTaskCFShadow (pid uniqueidentifier, tid uniqueidentifier, revCount int, mdpid uniqueidentifier)
      
      INSERT #TempTaskCFShadow (pid, tid, revCount, mdpid)
      SELECT PROJ_UID, TASK_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                        FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW
                        GROUP BY PROJ_UID , TASK_UID, MD_PROP_UID 
      
      DELETE 
      FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW 
        FROM MSP_TASK_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempTaskCFShadow AS newtable
      where originalTable.MD_PROP_UID = newtable.mdpid AND
      originalTable.PROJ_UID = newtable.pid AND 
      originalTable.TASK_UID = newtable.tid AND
      originalTable.DELETED_REV_COUNTER <> newtable.revCount
                         
        DROP TABLE #TempTaskCFShadow
 
--Clean up MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW 
      IF object_id('tempdb..#TempAssnCFShadow') IS NOT NULL 
      BEGIN  
      drop table #TempAssnCFShadow  
      END
 
      CREATE table #TempAssnCFShadow (pid uniqueidentifier, asid uniqueidentifier, revCount int, mdpid uniqueidentifier)
      
      INSERT #TempAssnCFShadow (pid, asid, revCount, mdpid)
      SELECT PROJ_UID, assn_uid, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                        FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW
                        GROUP BY PROJ_UID , ASSN_UID, MD_PROP_UID 
      
      DELETE 
      FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW
        FROM MSP_ASSN_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempAssnCFShadow AS newtable
      where originalTable.MD_PROP_UID = newtable.mdpid AND
      originalTable.PROJ_UID = newtable.pid AND 
      originalTable.ASSN_UID= newtable.asid AND
      originalTable.DELETED_REV_COUNTER <> newtable.revCount
                         
        DROP TABLE #TempAssnCFShadow
 
 
--Clean up       MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW
      IF object_id('tempdb..#TempProjResCFShadow') IS NOT NULL 
      BEGIN  
      drop table #TempProjResCFShadow  
      END  
 
      CREATE table #TempProjResCFShadow (pid uniqueidentifier, resid uniqueidentifier, revCount int, mdpid uniqueidentifier)
      
      INSERT #TempProjResCFShadow (pid, resid, revCount, mdpid)
      SELECT PROJ_UID, RES_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                        FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW
                        GROUP BY PROJ_UID , RES_UID, MD_PROP_UID 
      
      DELETE 
      FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW 
        FROM MSP_PROJ_RES_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempProjResCFShadow AS newtable
      where originalTable.MD_PROP_UID = newtable.mdpid AND
      originalTable.PROJ_UID = newtable.pid AND 
      originalTable.RES_UID = newtable.resid AND
      originalTable.DELETED_REV_COUNTER <> newtable.revCount
                         
      DROP TABLE #TempProjResCFShadow
      
 
 --Clean up MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 
      IF object_id('tempdb..#TempProjCFShadow') IS NOT NULL 
      BEGIN  
      drop table #TempProjCFShadow  
      END  
 
      CREATE table #TempProjCFShadow (pid uniqueidentifier, revCount int, mdpid uniqueidentifier)
      
      INSERT #TempProjCFShadow (pid, revCount, mdpid)
      SELECT PROJ_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                        FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW
                        GROUP BY PROJ_UID , MD_PROP_UID    
      
      DELETE 
      FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW 
        FROM MSP_PROJ_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempProjCFShadow AS newtable
      where originalTable.MD_PROP_UID = newtable.mdpid AND
      originalTable.PROJ_UID = newtable.pid AND 
      originalTable.DELETED_REV_COUNTER <> newtable.revCount
             
        DROP TABLE #TempProjCFShadow

Run the following script on the Published database after you run the script from step 1 on both the Draft and Published databases:

--Clean up MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
 
      IF object_id('tempdb..#TempResCFShadow') IS NOT NULL 
      BEGIN  
      drop table #TempResCFShadow  
      END  
 
      CREATE table #TempResCFShadow (resid uniqueidentifier, revCount int, mdpid uniqueidentifier)
      
      INSERT #TempResCFShadow (resid, revCount, mdpid)
      SELECT RES_UID, MAX(DELETED_REV_COUNTER), MD_PROP_UID
                        FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
                        GROUP BY RES_UID, MD_PROP_UID 
      
      DELETE 
      FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW
        FROM MSP_RES_CUSTOM_FIELD_VALUES_SHADOW as originalTable, #TempResCFShadow AS newtable
      where originalTable.MD_PROP_UID = newtable.mdpid AND
      originalTable.RES_UID = newtable.resid AND
      originalTable.DELETED_REV_COUNTER <> newtable.revCount
             
        DROP TABLE #TempResCFShadow

Back to the top | Give Feedback